Problem
Cascading parameters is a very routine and basic requirement of any report. While using SQL Server Analysis Services (SSAS) Multi-dimensional mode as the data source, MDX has to be used as the data source. MDX is a very sophisticated query language and developing the query logic to accept parameters can be complex. In this tip, we will look at a very easy method of how to add cascading parameters in a SSRS report using SSAS as the data source and MDX as the query language.
Solution
MDX Query Designer provides options to specify parameters while designing datasets, which can be used to configure cascading parameters.
1) It is assumed that you have already installed SQL Server Analysis Services (Multi-dimensional mode), SQL Server Reporting Services, and a sample SSAS database. I will be using AdventureWorks SSAS database as the data source. Open SQL Server Data Tools and create a new blank Report project.
2) Right-click on the project explorer, and add a new blank report to the project. The first step is to create a new data source for the report. Right-click on the Data Sources folder in the Reports data tab and create a new data source. Configure the data source to connect to your SSAS database as shown below.
3) After creating a data source, the next step is to create a dataset for the first parameter. We want to create a parameter Categories, based on which another parameter sub-categories should be populated. So let’s create a dataset for Categories. Right-click on the Datasets folder, and create a new dataset. Configure the same as shown below and click on the Query Designer button.
4) In the query designer, drag and drop Internet Sales Amount measure and Categories attribute from the Product dimension, to create a dataset as shown below, and click OK. This designer will create the required MDX query behind the scenes.
5) Now it’s time to create our first parameter – Category. Right-click on the parameters folder, create a new parameter, and configure the same as shown below. Name this parameter as “Category”. This parameter will act as the input value to filter the dataset for the SubCategory parameter that we will create later.
6) Create another dataset which will be used for SubCategory parameter. In the query designer, configure the data as shown below. Here we have added one more attribute – SubCategories from the Product dimension. The rest of the fields are similar to the one we selected in Step 4. On the top portion of the query designer, there is an option to configure the filter criteria. Configure the filter criteria as shown below, as we intend to accept input parameters for the Categories attribute.
7) Once you create dataset in the query designer and then close it, you will find the MDX query. If you analyze this query, you will find the MDX input parameter name in this query as highlighted below.
8) Click on the Parameters tab of this dataset and configure the parameter value to the name of parameter we created in Step 5 – Categories. This means the input value of the Categories parameter will be used to filter this dataset. We will use this dataset to populate values for the SubCategory parameter.
9) Create a new parameter SubCategories and configure it as shown below, to populate the values from the dataset we created in the previous step.
10) Preview the report. Select any value in the Categories parameter and watch the values populate in the SubCategories parameter as shown below.
In this way, we can create cascading parameters using the MDX query designer while using SSAS as the data source in SSRS.
Next Steps
- Add more parameters to your SSRS report using MDX query designer and filter report data using the same.
- Analyze the MDX created after configuring parameter in the query designer which will empower you to easily manipulate the MDX query.
