Cascading Parameters using DataSet Filter (Cube datasource) SSRS 2008 10.0

Question

Hi,

I'm trying to get my parameters to cascade.  I can do it with T-SQL but I'm currently working with a cube and I'm not that familiar with MDX.  I was hoping I could do it by applying filters to my data sets that are used to populate my parameters.

I have two parameters on my report.  One is product group and the other is product.  When the user selects a product group I would like the second parameter to filter on only products that are included in that group. 

I first created two datasets based on the same datasource.  The first dataset contained the product group values with a measure.  The second data set contained the product group, product and a measure.  The reason I brought in a measure was so the values would show up when I query them.

Next, I created two parameters.

Product Group Parameter:  General - Allow multiple values; Available Values - Get values from a query, Dataset ProductGroupDS, Value field ProductGroup, Label field ProductGroup; Default Values - Get values from a query, Dataset ProductGroupDS, Value field ProductGroup

Product Parameter:  General - Allow multiple values; Available Values - Get values from a query, Dataset ProductDS, Value field Product, Label field Product; Default Values - Get values from a query, Dataset ProductDS, Value field Product
 
I then went into the dataset properties used for the Product parameter.  On the filter tab, I added the following:  Expression [ProductGroup], Operator In, Value [@ProductGroup] (=Parameters!ProductGroup.Value)

When I run the report, all the product groups are displayed correctly as well as all the products in the second parameter.  The problem is when I choose a value from the product group parameter, the second parameter doesn't get updated.

I tried changing the the product dataset filter to on =Parameters!ProductGroup.Value(0) but that doesn't work either.  When I run the report, the second parameter doesn't contain anything in it and it never gets updated after selecting a value from the product group paramter.

If I change the product group parameter to not allow multiple values and change the product dataset filter to Expression - [ProductGroup], Operator =, Value [@ProductGroup] (=Parameters!ProductGroup.Value).  When I run the report, the product parameter will filter on the right products based on the first selection of the product group parameter.  However, if I change the product group value to something else, the product parameter doesn't get updated.

Thanks.

Answer 1

Hi,

 

This is not supported now. You can only create cascading parameter with query string. So why not create the parameters with embed parameter in query string. You don’t need to write MDX, because the query designer will create the MDX statements automatically.  You just need to create a filter in dataset Product.

 

You can try the below steps:

1)    Created the first dataset (the mail dataset) contained everything you want to display in report.  Because you need a parameter @product, so drag the demission to the filter area, select the parameter option, then it will create a new hidden dataset - product automatically.

2)    In data view pane, right click the datasource, click ‘show hidden dataset’. Modify the dataset in design view mode.  For the second dataset, I mean product dataset, because you need a product group parameter, so create a filter for this dataset, drag the product group demission to the filter area, select the parameter option, then it will create the third dataset – product group dataset automatically.

3)    After that, you will see there are two cascading parameters been created.

 

Note: Change the dataset to design view mode may lost the query string, but it doesn’t matter, because you can simply drag-and-drop the demission to create the dataset.

 

Hope this helps,

Raymond

Answer 2

Hi,

I still can't get it to work.  Here are the steps that I performed:

1.  Removed the existing parameters and parameter datasets
2.  Removed the filters used in the Tablix Properties
3.  Opened the main dataset (all report fields) in Query Designer
4.  Dragged the product to the filter area and checked the parameter check box
5.  Right clicked on DataSource and seleced show hidden datasets
6.  Opened the product dataset in Query Designer and clicked on the Design Mode icon
7.  Dragged product and a measure to the bottom pane
8.  Dragged product group to the criteria pane and checked the parameter check box
9.  Previewed Report - Error due to ParameterLevel
10. Opened the Parameter Properties for the product and selected product for the Value Field (was empty) and Product for Label Field
11. Opened Product Dataset Properties and removed the Parameter Caption Indented field in the Fields Section
12. Previewed Report - Able to select Product Group and the Product Parameter filters on the appropriate values :)
13. Click View Report after selecting paramters - Error during report processing.  Query execution failed for main dataset.  The syntax for "Product123" is incorrect.  Product123 is one of the products I selected in the Product paramter.

Thanks.

Answer 3

I almost have it working.  It works fine as long as there aren't any spaces in the parameter values.  When I run the report I get a syntax error if I select any values with spaces.  Any idea how to fix this?

Answer 4

Hi Lucas,

 

When creating a parameter base on a cube, you’d better set the available value and default value from a cube. Otherwise, the string you entered could not be recognized by the reporting services. For example, it will treat [product].[a] as a string. So make sure you set the available value and default value from a cube.

 

Also, you will find the MDX code created by Reporting Service always use the function STRTOSET(). It will keep changing the parameter value to a set which can be recognized in MDX statement. So in query string, if you want to use parameter value, do not directly write like this:

@DateCalendarYear, ensure it will change to set like this:

STRTOSET(@DateCalendarYear, CONSTRAINED)

 

Hope this helps,

Raymond

Answer 5

Hi I was having the same problem , 

I have created the parameter and all 

And after thet clicked on the parameter dataset and when it open in design mode , the query being lost 

then how should i add the parameter 

 

Regards

Sooraj