Add Cascading Parameters in a SSRS Report using MDX and SSAS Multi-Dimensional


By:   |   Updated: 2017-03-22   |   Comments   |   Related: > Analysis Services Development

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.

Configure the data source to connect to your SSAS database

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.

create a dataset for the first parameter

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.

In the query designer, drag and drop Internet Sales Amount measure and Categories attribute from the Product dimension, to create a dataset

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.

create a new parameter for Category and configure

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.

Create another dataset which will be used for SubCategory parameter

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.

create dataset in the query designer and then close it, you will find the MDX query

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.

MDX Parameter Name

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.

Sub Category Parameter

10) Preview the report. Select any value in the Categories parameter and watch the values populate in the SubCategories parameter as shown below.

Select any value in the Categories parameter and watch the values populate in the SubCategories parameter

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.


Last Updated: 2017-03-22


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools