Dynamic Cascading Prompts

Crystal Reports XI gives you a wonderful new feature for working with parameters: Dynamic Cascading Prompts, commonly referred to as DCPs. This gives parameters the ability to be dynamic because they are linked directly to the database. They can also be cascading because the value in one parameter is used to filter the list of values in the next parameter. This collection of related prompts is defined as a “Prompt Group”.

Up to now, when discussing how to create a list of values for prompts for the user to choose from, this chapter has focused on building a static list of values. This was done by either manually typing in the default values or selecting them from a live database connection. The drawback to both of these methods is that the list of values can only be guaranteed to be accurate as of the date you create the report. For example, if a list of values lets the user choose from a list of current customers and your company is always adding new customers, then the list of values will quickly be out of date. You, as the report designer, will have to continuously update the list of values to reflect the new customers.

DCPs solve this problem by letting you create a list of values that is linked directly to the data source. The default values presented to the user are always accurate and you never have to revise the report to make sure they stay current.

The way that cascading prompts work together is that when you select a value from the first prompt, Crystal Reports queries the database for all the records that have that value in them and uses this result to populate the next prompt list. This is repeated for each prompt in the group until you get to the last prompt. At which point, the last prompt in the list should be fairly small depending upon how much filtering was done prior to it.

As an example, assume the first prompt is Country and the second prompt is Region. If you select USA for the country, then the second prompt group displays a list of all the regions where the records have the value USA in the Country field. Now, let’s assume that the first prompt is a multi-value prompt and you select USA and Canada as the two values. The second prompt will list all the regions where the records have a country equal to USA or Canada. Thus, when using a multi-value prompt, then the next prompt will list all records that match at least one of the selected values.

When using DCPs, you have to make sure that everyone who opens the report will have an active link on the network. If they can’t connect to the database then they can’t enter parameters.