Tips & Tricks
To display a multiple value parameter with the JOIN function
NOTE:
The formula described below only works for multiple discrete values.
It does not work with multiple range values.
1. On the 'Insert' menu, select 'Formula Field'. The Field Explorer appears.
2. Click the 'New Formula' icon. The 'Formula Name' dialogue box appears.
3. Enter your formula name and click 'OK'. The 'Formula Editor' appears.
4. Type the below formula:
//{@DisplayParam} is the formula name.
//{?Parameter} is a place holder for this example.
//Replace {?Parameter} with your own
//parameter when inserting this formula onto your report.
//To change the separator,
//change the character between the quotes.
Join({?Parameter}, ", ")
5. Click the 'Save and Close' icon.
6. Insert the {@DisplayParam} formula field on the report instead of the
parameter field.
The next time you preview the report, all the parameter values display.
Pass
a Multi-value Parameter to a Stored Procedure
Crystal Reports considers the multi-value parameter
to be an array.
Here are the steps to pass a multi-value parameter to a stored procedure:
1. Create a Crystal report, and add a multi-value parameter.
2. Since the multi-value parameter is treated as an array, create a formula
that uses the JOIN function. Create a formula as below:
//Formula: @JoinFormula
Join ({?Multi-value parameter array},";")
3. Within the main report, create a subreport based on the stored procedure,
and include the parameter to be populated with the multi-value list.
4. Link the Join formula in the main report to the stored procedure parameter
in the subreport.
Doing so passes a multi-value parameter to the stored procedure.
Creating
Fake Page Footers in Subreports
To create fake page footers in subreports you need
to insert and format a second Details section and a second Report Footer
section.
These sections will act as page footers.
You must decide how many records you want per page.
The number of records must be static, as must the fields.
The 'Can Grow' option cannot be selected for any fields.
It is also recommended that you have a static number of groups per page.
The following example creates ten records per page in the Details section
without any groups
1. Right click the gray area to the left of the Details section and then click
'Insert Section Below'. You will now have a 'Details a' and a 'Details b'
section.
2. On the 'Report' menu, click 'Section Expert'.
3. Click 'Details' (not 'Details a' or 'Details' b) and then click the 'X+2'
button next to 'New Page After'.
Insert the following formula in the 'Format Formula Editor':
Remainder(RecordNumber, 10) = 0 Click the 'Save and Close' button.
(This ensures the subreport 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.)
4. In the Section Expert, click 'Details b' and then select the 'Print at
Bottom of Page' check box.
Then click the 'X+2' button next to 'Suppress (No Drill-Down)'.
Insert the following formula in the 'Format Formula Editor':
Remainder(RecordNumber, 10) <> 0 Click the 'Save and Close' button.
(This makes the 'Details b' section appear at the bottom of each page, every
tenth record.)
5. In the Section Expert, click 'Report Footer a' and then select the 'Print at
Bottom of Page' check box.
Then click the 'X+2' button next to Suppress (No Drill-Down).
Enter the following formula in the 'Format Formula Editor':
Remainder(RecordNumber, 10) = 0 Click the 'Save and Close' button.
6. Click 'OK' to close the Section Expert.
7. Insert fields and other objects that you want in the fake page footer into
both the 'Details a' and 'Report Footer a' sections. Format these two sections
so that they are identical.
When the report is previewed, the subreport will have ten records per page with
the 'Details b' section at the bottom of each page acting as the page footer.
If the last page has less than ten records, the 'Report Footer a' section will
act as the page footer instead.
Next: Beginner Level Tips...