Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Be prepared to lose once in a while
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

12
December

How Do I Create an Index for a Dynamic Parameter in Crystal Reports 2008?

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.
  1. Create a new report
  2. In the Database Expert, select the Add Command option for the datasource
  3. Create the following SQL statement

    SELECT DISTINCT Left(Customer.[Customer Name],1) as Index, Customer.[Customer ID], Customer[.Customer Name]
    FROM Customer


  4. Press the OK button
  5. Click on the Command
  6. Rename the Command to Parameter_Qry
  7. Add the other table(s) and/or view(s) to the report
  8. Create the required links between the table(s) and/or view(s)
  9. Delete any link between the table(s) and/or view(s) and the Parameter_Qry
  10. Press the OK button
  11. Ignore Crystal Reports Linking the warning messages
  12. In the Field Explorer, select the Parameter Fields category
  13. Press the New button
  14. Create Dynamic Cascade Parameter:

    Crystal Reports 2008 Dynamic Cascade Parameter with Index to Filter Customer Name

  15. Press the OK button
  16. 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

MAKE A COMMENT

Name *

Email Address *

Comment *