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.
That opens up the Dataset Properties. We want to click the Query Designer button at the bottom to open up the Query Designer.
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.
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.
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.
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!
Displaying our selected value USA.
You can also choose ALL to display all countries like the report ran before.
You can also Deploy the changes to your SSRS server so the report gets updated so it can be run in a browser.
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.
Jim Salasek's SQL Server Blog 