Dynamic parameter not pulling list of values out of field

Question

I have two kinds of parameters in a specific Crystal Report I am making -- two dates that I use directly in the SQL query, and a parameter that will filter the report based on sales reps within the query.

I defined the two dates (date1 and date2, if you will) when I created the command, and those work fine. However, when adding the dynamic parameter to choose which values I want for the 'sales rep' field, only a text box appears, meaning the parameter list hasn't been generated dynamically like advertised.

I know the reason for this -- the query hasn't run yet, so there aren't any dynamic values to pull. The date range is a necessary evil as well, since the report takes five minutes to run if a date range isn't set due to so many older records (which need to stick around do to history purposes).

Is it possible to generate this list dynamically at all, or am I stuck having to update a list with static values?

Thanks.

Solution 1

To get the dynamic parameter to work, you'll have to add whatever table to the report that contains the 'sales rep' field. You can pull the table in next to the SQL Command that you're creating, and just link them however you want with an outer join so as not to limit your records (You could even not link them at all, but Crystal will complain about having "Multiple starting points" or whatever.

Now you can pull the sales reps into the dynamic parameter and use it in either the SQL Command itself as a parameter, or you can just use it in the record selection formula to filter results from the SQL Command. In both cases, CR will query the database twice; the first time to simply get the entire list of reps and the second time to run the SQL Command.

The main takeaway here is that the table, not a command, needs to be in the report in order to be a candidate to populate dynamic parameters... even if you won't use that table again in the report. Hopefully that helps, even if it is 7 months late.

Solution 2

Finally figured this out:

1. Using SQL create the stored procedure that gathers your report data. This stored procedure CAN NOT have parameters. It must generate all your report data upon execution

Example 1: Exec MyReportStoredProcedure

2. Create another stored procedure to gather your parameter list values. Again no parameters used in the stored procedure.

Example 2: Exec MyCrystalParameterList

3. In Crystal Reports under Database -> Database Expert add both stored procedures (Called tables) to the "Selected Tables" section. Click "OK". Crystal will then try to make links between the two tables. Click "Remove All Links". Click "OK" on all link warnings that follow.
4. Now build your report using the columns from MyReportStoredProcedure
5. Run you report. You will NOT get prompts to enter parameters. All possible data will be displayed in the report.

* Now time to link the parameter to the data. *

• Crystal reports will execute both stored procedures as soon as you open the report.
• Crystal reports does not pull data based on the parameter like reporting services (SSRS).
• Crystal filters the data based on the parameter value you selected after all data is gathered.

Running MyReportStoredProcedure returns the following:

1 Stop XX 7A [columns5] [columns6] [columns7] [columns8]
1 Gone CC 88 [columns5] [columns6] [columns7] [columns8] 
1 Gone CC 88 [columns5] [columns6] [columns7] [columns8] 
2 Nice XX C3 [columns5] [columns6] [columns7] [columns8] 
3 Loop EE C3 [columns5] [columns6] [columns7] [columns8] 
3 Loop DD C3 [columns5] [columns6] [columns7] [columns8] 
3 Loop DD C3 [columns5] [columns6] [columns7] [columns8]
... 
... 700,000,000 Additional records
...

Running MyCrystalParameterList returns the following:

1 Stop XX 7A
1 Gone CC 88
2 Nice XX C3
3 Loop EE C3
3 Loop DD C3
>> A distinct list of values only 5 rows <<

* Link the parameter data column to the corresponding report column *

Now link the parameter Column1 to the report column1. This link ties the main report values (MyReportStoredProcedure) to the parameter values (MyCrystalParameterList).

In Crystal Reports under Database -> Database Expert Click the tab called Links.

In this section you link the tables/columns together. You could have done this in the steps above but for better understanding I want you to do it here after showing the data returned for both stored procedures (tables).

* Create your cascading parameter *

In Crystal Reports find the Field Explorer panel. Usually its on the right side of the screen.

• Now Right click [Parameter Fields] and select [New]
• Name the parameter
• leave the type as [String]
• set "List of Values" to [Dynamic]
• set "Choose a Data Source" radial button to [New]
• In the table below the radial button, click in the first row under the word "Value"
• A drop down showing both tables will be displayed ([MyReportStoredProcedure and MyCrystalParameterList])
• Select the columns for your first parameter from your MyCrystalParameterList stored procedure (table).
• Your column is now listed in the first row.
• Now click the next row and select your next column from your parameter stored procedure (table).
• repeat until all of your desired parameters are displayed in the table rows.
• On the right side of this table you will see a column called "Parameters" there is text in this column for each row.
• Click each field in the "Parameters" column and a parameter is created in Crystal report that corresponds to your parameter stored procedure (table) column.
• Click "Okay" to close the "Create New Parameter" dialog box.

If you run your report, you will be prompted to select values for your parameters and the parameters will list available values.

ALL OF THE DATA WILL BE RETURNED IN THE REPORT!!! We are not done.

* Last step (Finally!) *

Tell Crystal reports to filter the report data based on the parameter value.

In crystal reports, click on Report -> Select Expert -> Record

• A "Chose field" dialog box will display

• Fields panel Look for the report stored procedure and expand it so the columns are showing

• Select one of the columns that linked to the parameter stored procedure (table).

• You will now see a formula dialog box with a default value of "is any value".

• change this value to "is equal to"

• A drop down box will appear to the right.

• The drop down box will show your parameters!!!!!!

• Select the parameter that corresponds to the report field you select.

• repeat for all of your parameters.

• Now run your report. The data is now filer by your parameter value!!

• You are done!!!! Thank GOD!

Note: You can make a single stored procedure for each parameter. Just add them to the report using Database Expert.

In my example I made one stored procedure that pulls all possible parameter combinations.

DO NOT ADJUST YOUR REGISTREY SETTING AND MAKE CRYSTAL REPORTS READ MORE THAN 1000 RECORDS!!!

Your report stored procedure can return 1 Billion records but your list of parameters should not be more than 1000 unique values.

You cannot build your parameter list from the report stored procedure because crystal reports will only look at the first 1000 records.

Your parameter list must be a separate stored procedure that pull distinct values.

* You have the foundation, Now build the rest. Good luck. *

Comments

[ryebr3ad]

Heh, in my seven months since using CR I've learned a bit about multiple table imports -- so I've since rectified the situation with my own devices. I appreciate the effort all the same.

[Sun]

Depending on the Crystal Reports version, I've read Crystal will only compile a list based on the first 200 or 1000 rows. I had into an issue where pulling up sales rep is incomplete because Crystal compiles a unique list after the initial select. So, my workaround to this was to use a static list. The benefit is that the report criteria list pulls up faster, but the list has to be maintained.

[Sun]

I'm still not getting a dynamic value list. Dynamic values populates if and only if I'm using a report parameter. As soon as I include a command parameter, Crystal doesn't want to show a list of values for the report parameter.