Cascading parameters as dropdown list

[Former Member]

Hi,

I am creating a crystal report in which I have to create four cascading dropdown lists as parameters. The issue that I am facing is, the database table from where I have to fetch the data for these dropdowns is not normalised. Hence all the data, that is required to show in these four cascading drop down lists are coming from one single table.

Which is a better way to create these cascading parameters?

1. Creating four different queries in add command for each dropdown list?

2. Creating a single query with self join four times?

Here, just to select the parameters by user, the cascading dropdown lists is taking to long to open each time (around 5 mins). How this Add command-Sql query works internally in crystal reports? How to increase the performance?

[JWiseman · Accepted solution]

hi Apurva, since the cascading parameters will require data for the fourth level, creating a single command object with all four required fields should be the best option.

what you may wish to do is to create a new report and just put those four fields on the report. then go to the Database menu and choose Select Distinct Records. now what is the performance like?...this should in theory be a lot better performance.

now go back to the Database menu > Show SQL Query and copy that syntax. you now have the syntax to make your Command object. to use the syntax, create a new report but this time choose Add Command (don't select any tables) and then paste in the syntax. what is the performance like now?

if the performance is good, go back to your original report and then the Database Expert and choose to Add a Command. paste in the syntax again. go to the link expert and clear all the links. when you're finished there, ignore the warning about multiple starting points. now you can build the dynamic cascading parameter using fields from the command you just added to your report.

-jamie