Dynamic Parameter pick list from other parameter

[hansdebadde]

I am using CR 2008 and I have a large slow report that users can choose to filter the report by only one of many areas. For example they can choose to filter by Facility, Department, Manager, or Director. There are a total of 12 different choices, but for here lets assume there are 4. Currently the report works by listing a pick list for each of the different choices with the default being "all". The problem is twofold: having multiple parameters is overload for the end user and because I populate the 12 parameter pick lists manually due the "all" option whenever the data updates i have to update the report manually.

To make it more complex there are two datasources
SQL database BROWN for Facility and Department
Excel worksheet org.xls for Manager and Director

I would like the user to first make the filter choice of
manager/director/facility/director and then the next parameter dynamically changes to offer the picklist of that filter. I hope this makes sense. I was thinking maybe a command parameter but the two datasources threw me. Is this possible?
Thanks for your time and I hope you had a great Christmas season.

PB

 

[hansdebadde]

I see that Ken Hamady replied to a similar post i did with the answer:
To do this in XI you would need to use a SQL Command that returned two columns like this:
Facility / Facility A
Facility / Facility B
Department / Department A
Department / Department B
Then you only need two parameters, the first filters to one list and the second select the value from that list (regardless of which list they pick). The trick would be creating the command to generate the master list - probably a UNION of several queries, or maybe another spreadsheet that you create to hold these lists.

Okay, from this i figured it out I think. I am creating another report with two columns. The two columns will be filter type and value. I will have a subreport for each filter type. Each subreport will have the a filter name, for example the first will be Facility in the first column repeated for all possible facilities as listed in column two. I will then have the results spit out each night by crystal enterprise and pulled in as a datasource for my bigger report. I will use a dynamic parameter, with the first level choosing the filter type, and then second one choosing the values that are populated. I will post back if it works.