Parameterize a SSRS report using SSAS Data.

In a previous posting we created our first SSRS report pulling from our SSAS cube data.  It ran fine, but it brought back all of the data in the database.  This posting will show you how to add a parameter so only selected information is brought back.  You may think of this like a WHERE clause in your SQL statements.

Opening up our report project in Visual Studio, in the Report Data box, right click Datasets and open up the properties like below.

sp1

 

That opens up the Dataset Properties.  We want to click the Query Designer button at the bottom to open up the Query Designer.

sp2

 

We want to create a parameter that specifies what Country Name that the query will bring back data for.  To do that open up the Dim Country dimension and drag Country Name into the top section.  You can do this with any of your dimensions and can even have multiples specified, but we are just going to use Country for our example.

sp3

 

That will automatically fill in the boxes: Dimension, Hierarchy, and Operator.  You will then need to check the Parameters box to activate it.  We will use the Equal operator to bring back all Countries that match the selected name.

sp4

 

When we go back to Design view, we can see there is a parameter created in the Report Data box and a Country Name drop down box was created in our report.

sp5

If we flip to the Preview tab, we can see that it displays all of the unique values for the country names in our cube.  I select USA and then click View Report and just the values for USA display!

sp6

 

Displaying our selected value USA.

sp7

 

You can also choose ALL to display all countries like the report ran before.

sp8

 

You can also Deploy the changes to your SSRS server so the report gets updated so it can be run in a browser.

sp9

 

You just added a powerful feature to your report.  There are still many, many items that can be changed to make the report even more user friendly.  Future articles will include drill down reports and sub totaling.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s