Dynamic SQL for Cascading Parameters

[CrystalProgDev]

I need to generate Dynamic SQL for cascading Parameters. Here is the scenario.

Parameters:
Department ---- Values: A-Department, B-Department
Category ---- A1, A2, A3, B1, B2
Product --- A1P1, A1P2, A2P1, B1P1, B2P1, B2P2, B2P3

If user selects A-Department the SQL Should use Dept_A table
and the category should be A1,A2,A3 (Data should come from Cat_A table) And if user selects Category A1,A2 the product should be A1P1, A1P2, A2P1 (Data hould come from Product table where dept=A and Catcode in (A1,A2))

Is there any way we can implement this in Crystal report? Please suggest. It is urgent.

Thank you in advance

 

[PeterDimitrov]

There are 2 ways to handle this :
1. Create a command for the Category parameter. This command will accept the department as a parameter and will return different values based on that. When you run the report Crystal will try to fill the category list and will prompt for the department value. Once this value is set the report will run the command, will fill the categories and will ask one more time for a value - this time for the category. It might be a problem if you need both the department and category values in the report because the department id will be available just in the command for the categories. Also there will be always 2 separate prompts for parameter values.

2. Check if your viewer supports real cascading parameters. Here is an example:

http://www.r-tag.com/Pages/Preview_CascadingParameters.aspx

This approach has advantages but requires 3rd party viewer.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[CrystalProgDev]

Thank you Peter.. I really appreciate your help

 

[IdoMillet]

You can create a VIEW, SP, or Command that uses a UNION ALL to append all categories from all category tables, but adds a column indicating which department each row belongs to.
This can then be uses As-Is for a cascading dynamic parameter.

Alternatively, one of the 3rd-party viewers listed at

http://kenhamady.com/bookmarks.html

allows you to use Crystal Reports that act as linked dynamic parameter dialogs. And these parameter reports can have their own parameters to create cascading parameters. So you can create a Category parameter report that contains several subreports: one in RHa listing categories from A department table, one in RHb that lists categories from B department table, etc. The report can then be assigned aits own Department parameter, and depending on the value of that parameter, you can conditionally suppress all sections except the one associated with the department specified in the parameter. A Product parameter report can then use the parameter report above as its own Category parameter...

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com