Recently a client had an issue with creating a dynamic parameter based on the Customer Name field returning over 3000 values, and the filtering feature only worked if one was in the correct page.
Crystal Reports allows by default dynamic paramters to return 1000 values, which can be changed by amending the LOV software registery values, however the down side of doing this is that it can negatively impact on the performance of the report.
Solution:
The most effective way around this would be to create a cascade dynamic paramter, which uses an index of the Customer Name field values, which would then filter the values down to a manageable number, without impacting on the report performance.
- Create a new report
- In the Database Expert, select the Add Command option for the datasource
- Create the following SQL statement
SELECT DISTINCT Left(Customer.[Customer Name],1) as Index, Customer.[Customer ID], Customer[.Customer Name]
FROM Customer
- Press the OK button
- Click on the Command
- Rename the Command to Parameter_Qry
- Add the other table(s) and/or view(s) to the report
- Create the required links between the table(s) and/or view(s)
- Delete any link between the table(s) and/or view(s) and the Parameter_Qry
- Press the OK button
- Ignore Crystal Reports Linking the warning messages
- In the Field Explorer, select the Parameter Fields category
- Press the New button
- Create Dynamic Cascade Parameter:
- Press the OK button
- Apply the Select Expert using the Customer ID field from the Dynamic Parameter
If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us |
