<html xmlns="http://www.w3.org/TR/REC-html40" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:w="urn:schemas-microsoft-com:office:word"> <head><meta http-equiv="Content-Type" content="text/html; charset=utf-16le"> </head> <body lang="EN-US" link="blue" style="tab-interval:.5in" vlink="purple"> <div class="WordSection1"> <div id="container"> <div id="mainContent"> <div id="sidebar"> <div id="sidecontainer"> <div id="navcontainer"> <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="font-size:14px;"><span style="font-family: trebuchet\ ms, helvetica, sans-serif;"><a href="../index.html"><span style="color:#000080;">Home</span></a></span></span></li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="font-size:14px;"><span style="font-family: trebuchet\ ms, helvetica, sans-serif;"><a href="../Services-Crystal-Clear-Solution/Services-Crystal-Clear-Solution.html"><span style="color:#000080;">Services</span></a></span></span><o:p></o:p></li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="font-size:14px;"><span style="font-family: trebuchet\ ms, helvetica, sans-serif;"><span style="color:#000080;"><a id="current" name="current"></a></span><a href="Crystal-Reports-Tips.html"><span style="color:#000080;">Tips And Tricks</span></a><span style="color:#000080;"> </span></span></span><o:p></o:p></li> <li>&nbsp; <ul type="circle"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt: auto;mso-list:l0 level2 lfo1;tab-stops:list 1.0in"><span style="font-size:14px;"><span style="font-family: trebuchet\ ms, helvetica, sans-serif;"><a href="../Crystal-Reports-Tips/Intermediate-Crystal-Reports-Tips/Intermediate-Crystal-Reports-Tips-1.html"><span style="color:#000080;">Intermediate</span></a></span></span><o:p></o:p></li> </ul> </li> </ul> <ul type="disc"> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="font-size:14px;"><span style="font-family: trebuchet\ ms, helvetica, sans-serif;"><a href="../Contact-Crystal-Clear-Solution/contact.php"><span style="color:#000080;">Contact</span></a></span></span></li> <li class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto; mso-list:l0 level1 lfo1;tab-stops:list .5in"><span style="font-size:14px;"><span style="font-family: trebuchet\ ms, helvetica, sans-serif;"><a href="../Sample-Reports/Sample-Reports.html"><span style="color:#000080;">Sample Reports</span></a></span></span><o:p></o:p><br /> <span style="mso-fareast-font-family: &quot;Times New Roman&quot;"><o:p></o:p></span></li> </ul> </div> </div> </div> <div id="content"> <div id="content_text"> <div align="center" class="MsoNormal" style="text-align:center"> <hr align="center" size="2" width="100%" /></div> <p class="MsoNormal"><span style="mso-fareast-font-family:&quot;Times New Roman&quot;; mso-no-proof:yes"><img alt="crystal clear solution llc" border="0" height="106" id="_x0000_i1031" src="../files/ccs-blue-new-reisling-7-copy-2.jpg" width="236" /></span><span style="mso-fareast-font-family: &quot;Times New Roman&quot;"><o:p></o:p></span></p> <div align="center" class="MsoNormal" style="text-align:center"> <hr align="center" size="2" width="100%" /></div> <p class="MsoNormal"><br /> <br /> <span style="font-family:trebuchet ms,helvetica,sans-serif;"><span style="font-size: 18pt; color: rgb(199, 135, 75);">Tips &amp; Tricks</span></span><br /> <br /> <span style="font-family:trebuchet ms,helvetica,sans-serif;"><span class="GramE"><span style="font-size: 10pt; color: rgb(184, 112, 45);">To</span></span><span style="font-size: 10pt; color: rgb(184, 112, 45);"> display a multiple value parameter with the JOIN function</span><br /> <br /> <span style="font-size: 10pt;"><span style="color: rgb(0, 59, 99);">NOTE:<br /> The formula described below only works for multiple discrete values.<br /> It does not work with multiple range values.<br /> <br /> 1. On the &#39;Insert&#39; menu, select &#39;Formula Field&#39;. The Field Explorer appears.<br /> 2. Click the &#39;New Formula&#39; icon. The &#39;Formula Name&#39; dialogue box appears.<br /> 3. Enter your formula name and click &#39;OK&#39;. The &#39;Formula Editor&#39; appears.<br /> 4. Type the below formula:<br /> <br /> //{@<span class="SpellE">DisplayParam</span>} is the formula name.<br /> <br /> //{?Parameter} is a place holder for this example.<br /> <br /> //Replace {<span class="GramE">?Parameter</span>} with your own<br /> <br /> //parameter when inserting this formula onto your report.<br /> <br /> //To <span class="GramE">change</span> the separator,<br /> <br /> //change the character between the quotes.<br /> <br /> <span class="GramE">Join(</span>{?Parameter}, &quot;, &quot;)<br /> <br /> 5. Click the &#39;Save and Close&#39; icon.<br /> 6. Insert the {@<span class="SpellE">DisplayParam</span>} formula field on the report instead of the<br /> parameter field.<br /> <br /> The next time you preview the report, all the parameter values display. </span></span></span><span style="mso-fareast-font-family:&quot;Times New Roman&quot;"><o:p></o:p></span></p> <div align="center" class="MsoNormal" style="text-align:center"> <hr align="center" size="2" width="100%" /></div> <p class="MsoNormal"><br /> <span style="font-family:trebuchet ms,helvetica,sans-serif;"><span style="font-size: 10pt;"><span style="color: rgb(193, 121, 57);">Pass a Multi-value Parameter to a Stored Procedure</span><br /> <br /> <span style="color: rgb(0, 59, 99);">Crystal Reports considers the multi-value parameter to be an array.<br /> Here are the steps to pass a multi-value parameter to a stored procedure:<br /> <br /> 1. <span class="GramE">Create</span> a Crystal report, and add a multi-value parameter.<br /> 2. Since the multi-value parameter is treated as an array, create a formula that uses the JOIN function. Create a formula as below:<br /> <br /> //Formula: @<span class="SpellE">JoinFormula</span><br /> <br /> Join ({<span class="GramE">?Multi</span>-value parameter array},&quot;;&quot;)<br /> <br /> 3. Within the main report, create a <span class="SpellE">subreport</span> based on the stored procedure, and include the parameter to be populated with the multi-value list.<br /> 4. Link the Join formula in the main report to the stored procedure parameter in the <span class="SpellE">subreport</span>.<br /> <br /> Doing so passes a multi-value parameter to the stored procedure. </span></span></span><span style="mso-fareast-font-family:&quot;Times New Roman&quot;"><o:p></o:p></span></p> <div align="center" class="MsoNormal" style="text-align:center"> <hr align="center" size="2" width="100%" /></div> <p class="MsoNormal"><br /> <span style="font-family:trebuchet ms,helvetica,sans-serif;"><span style="font-size: 10pt; color: rgb(185, 121, 57);">Creating Fake Page Footers in <span class="SpellE">Subreports</span></span><br /> <br /> <span style="font-size: 10pt;"><span class="GramE"><span style="color: rgb(0, 59, 99);">To</span></span><span style="color: rgb(0, 59, 99);"> create fake page footers in <span class="SpellE">subreports</span> you need to insert and format a second Details section and a second Report Footer section.<br /> These sections will act as page footers.<br /> You must decide how many records you want per page.<br /> The number of records must be static, as must the fields.<br /> The &#39;Can Grow&#39; option cannot be selected for any fields.<br /> It is also recommended that you have a static number of groups per page.<br /> The following example creates ten records per page in the Details section without any groups<br /> <br /> 1. Right click the gray area to the left of the Details section and then click &#39;Insert Section Below&#39;. You will now have a &#39;Details a&#39; and a &#39;Details b&#39; section.<br /> 2. On the &#39;Report&#39; menu, click &#39;Section Expert&#39;.<br /> 3. Click &#39;Details&#39; (not &#39;Details a&#39; or &#39;Details&#39; b) and then click the &#39;X+2&#39; button next to &#39;New Page After&#39;.<br /> Insert the following formula in the &#39;Format Formula Editor&#39;:<br /> <br /> <span class="GramE">Remainder(</span><span class="SpellE">RecordNumber</span>, 10) = 0 Click the &#39;Save and Close&#39; button.<br /> (This ensures the <span class="SpellE">subreport</span> will have 10 records per page. Change the second argument of the Remainder function in every formula to the number of records you want to see per page.)<br /> <br /> 4. In the Section Expert, click &#39;Details b&#39; and then select the &#39;Print at Bottom of Page&#39; check box.<br /> Then click the &#39;X+2&#39; button next to &#39;Suppress (No Drill-Down)&#39;.<br /> Insert the following formula in the &#39;Format Formula Editor&#39;:<br /> <br /> <span class="GramE">Remainder(</span><span class="SpellE">RecordNumber</span>, 10) &lt;&gt; 0 Click the &#39;Save and Close&#39; button.<br /> (This makes the &#39;Details b&#39; section appear at the bottom of each page, every tenth record.)<br /> <br /> 5. In the Section Expert, click &#39;Report Footer a&#39; and then select the &#39;Print at Bottom of Page&#39; check box.<br /> Then click the &#39;X+2&#39; button next to Suppress (No Drill-Down).<br /> Enter the following formula in the &#39;Format Formula Editor&#39;:<br /> <br /> <span class="GramE">Remainder(</span><span class="SpellE">RecordNumber</span>, 10) = 0 Click the &#39;Save and Close&#39; button.<br /> <br /> 6. Click &#39;OK&#39; to close the Section Expert.<br /> 7. Insert fields and other objects that you want in the fake page footer into both the &#39;Details a&#39; and &#39;Report Footer a&#39; sections. Format these two sections so that they are identical.<br /> <br /> When the report is previewed, the <span class="SpellE">subreport</span> will have ten records per page with the &#39;Details b&#39; section at the bottom of each page acting as the page footer. If the last page has less than ten records, the &#39;Report Footer a&#39; section will act as the page footer instead. </span></span></span><span style="mso-fareast-font-family:&quot;Times New Roman&quot;"><o:p></o:p></span></p> <div align="center" class="MsoNormal" style="text-align:center"> <hr align="center" size="2" width="100%" /></div> <p class="MsoNormal" style="margin-bottom:12.0pt"><br /> <span style="font-family:trebuchet ms,helvetica,sans-serif;"><span style="font-size: 10pt; color: rgb(0, 59, 99);">Next:</span> <span style="font-size: 10pt;"><a href="../Crystal-Reports-Tips/Beginner-Tips/Beginner-Tips-1.html" title="BEGINNER">Beginner Level Tips...</a></span></span><span style="mso-fareast-font-family: &quot;Times New Roman&quot;"><o:p></o:p></span></p> <p align="center" style="text-align:center"><o:p>&nbsp;</o:p></p> <div align="center" class="MsoNormal" style="text-align:center"> <hr align="center" size="2" width="100%" /></div> <p class="MsoNormal"><span style="mso-fareast-font-family:&quot;Times New Roman&quot;"><o:p>&nbsp;</o:p></span></p> </div> </div> <p class="MsoNormal"><br clear="all" style="mso-special-character:line-break" /> <span style="mso-fareast-font-family:&quot;Times New Roman&quot;"><o:p></o:p></span></p> </div> <div id="footer"> <p class="MsoNormal"><span style="font-family:trebuchet ms,helvetica,sans-serif;"><span style="color:#000080;">&copy; 2001-2018 Crystal Clear Solution LLC </span></span><br /> <span style="mso-fareast-font-family:&quot;Times New Roman&quot;"><o:p></o:p></span></p> </div> </div> </div> </body> </html>