Cascading Parameters

Hi,

I am using SQL Server 2005 reporting services. I am having a problem with Cascading Parameters. In my report there are 4 parameter . (Product Class, Product Type, Product SubType and Activites). when user open the report page he/she will have to select a value for product class depending on the selection Product Type and Sub type will be populated. So far its good, but Activities also depends on the Product Class. My question is can we have 2 parameters depend on same parameter. Here in this case Product Type and Activities both depend on the Product Class. I want only those activies in the dropdown which belongs to the Product Class that is selected.

Can anyone please give an ideas?

Thanks

Ashwini

Hi Ashwini,

I am assuming that you are using datasets or stored procedures to fill the parameters. If that is the case, This link -MIGHT- get you in the right direction.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=67984&SiteID=1

It is concerning about which dataset is being executed first, and with this, you might be able to execute certain datasets to get what you need and then based the next dataset on the previously executed dataset.

Hope this helps!

Bernard

|||

Hi Bernard,

Yes I have created 4 datasets with stored procedures to fill these report parameters.

SP1 : SelectProductClassAll -->which brings in all the product class

SP2: RptSelectCascadedProductType --> which has a parameter @.selected_product_class and will bring in all the product types for the given product class.

SP3: RptSelectCascadedProductSubType --> which has a parameter @.selected_product_type and will bring in all the product sub-types for the given product types.

SP4: RptSelectCascadedProductSubType --> which has a parameter @.selected_product_class and will bring in all the Activities for the given product class.

Problem is 2nd and 4th SP expects the same parameter. After adding these datasets if you go to Report -->Report Parameters you will only have 2 parameters (which would act as a drop down filter) @.selected_product_class , @.selected_product_type and I added one for Subtypes these 3 drop downs are fine. But I am not able to get the dropdown for activites since my SP4 expects the same parameter as SP1 and I cant use the same name parameter name twice?

I hope I am clear...

I looked at the link which you send it did not give me enough information so I am still confused...

Thanks

Ashwini

|||

HI Ashwini.

Actually you should be able to use the same parameter name.

Have you tried using only 3 parameters instead of 4 parameters ?

I mean, logically speaking, since two SPs are using ONE parameter, I don't see a reason why you would try and use another Report Parameter. Just make sure that the 2 stored procedures are pointing to one Parameter.

The link I posted tells of how datasets can be executed in order depending on what you need. In this case, you would want to execute SP1, before executing Sp2 and Sp3 right ?

Of course unless, I am getting it all confused myself which is not a good thing.

Bernard

|||

Without reading in detail too much, I'd say yes to you can re-use same parameter (same name) for different parameters

e.g. our reports have performance venue dropdown based on the date range selected (@.start_date to @.end_date)

then performance type, based on the venue AND date range

then say seating zones of the venue, based on type AND venue AND date range

|||

Ok may be I am understanding the concept of report parameter wrong...If I only have 3 parameters it will only show 3 dropdown menu when I actually run the report right? for ex: Product class, type and subtype. I need to have another dropdown menu for Activities as well how would I get this if I dont have a report parameter for it?

As per my understanding SRS will create these dropdown menus for you based on the report parameter. Please correct me if I am wrong...

|||In your case, you only have 3 params, isn't it. The #2 and #4 sp share the same parameter which is your Selected_Product_Class. So when user makes a selection for ProductClass both sp should run based on that Product Class. If you want to pass different values of Selected_Product_Class to these 2 sp, then you need to define 2 separate parameters and fill them both with your #1 sp. But in that case you will have 2 Selected_Product_Class, not sure if that's what you intend to do.|||

Ashwini Ramaiah wrote:

I need to have another dropdown menu for Activities as well how would I get this if I dont have a report parameter for it?

Define another parameter and fill it with your #1 sp, just liek you did earlier. And pass the value of this param to your #4 sp. But this way, user will be able to select 2 different values of your ProductClass. If you want them to be same, then just fill this new param with the value of your already selected product class parameter.

|||

Thanks Everyone!!!

Its working now....I am not sure what was the problem. All I did was delete all my parameter and datasets and re do it. I created a 4th parameter and hooked it up with Activities Data which in turn is looking fo SP4 which expects @.selected_Product_Class parameter. Trust me I did try this like 100 times it didnt work that time when I deleted the whole thing and recreated all the parameters its all good now.

I have 4 dropdowns in my Report when user comes in He/She would only be able to select a value from Product Class rest 3 will be grayed once product class is selected Product type and Activities dropdown's data gets populated and the user can select Product Sub type which is 4th dropdown. This is exactly what I wanted.

Once again thanks to everyone!!!

Ashwini