In this blog post, we’ll see steps to Parametrize a SQL Server Reporting Services report where data source is Analysis services (SSAS) cube:
Step 1: Let’s say you have reached to a point with your SSRS report where you’ve configured your data source, data sets and data fields that you want to use for your report. For the purpose of this blog post, I’ll be starting with a SSRS report that shows Sales VS country names:
Step 2: Now, let’s say the requirement are such that you want to parametrize the report by a data field in the Analysis services cube: continents
Step 3: Switch to Design View. Now navigate to query designer: Select your Data-set > Right Click > DataSet Properties > Query > Query Designer
Step 4: Drag the field to the filter area. For the purpose of this blog post, I am going to select Continent Name and add it to Filter area.
To add a field to filter area, there are two options:
#1: Select the field > Right click > Add to Filter
#2: Select the field > use your mouse to drag it to filter area
Step 5: Once you’ve added your desired field to the filter area, we’ll have to add it as parameter.
Now chances are that you are not seeing the parameter check box for this field because the dialog box is minimized. You can either maximize the dialog box or scroll to the right side of the filter area.
Once you see it, check it > click ok
Step 6: Once you’re back on Design View. Try “preview” report. you should be able to see the option to select parameter value before the report gets populated with data:
I selected Europe and then clicked on view report:
Step 7: One last thing, Let me also point out how you can change the properties of the parameters.
Go Back to design view > from the report data pane > Expand parameters folders > select the parameter > Parameter Properties
I’ll leave you with exploring what you can do with parameter properties! And with that I conclude this blog post, Your comments are very welcome!
This was a beginner’s level post, I’ll encourage you to follow up by watching three videos here: http://sqlserverbiblog.wordpress.com/2013/12/03/reporting-services-mdx-queries-video-tutorials/
- SQL SERVER – Determine if SSRS 2012 is Installed on your SQL Server (sqlauthority.com)
- SQL Server Reporting Services: Why am I not seeing every axis label in a chart? (ParasDoshi.com)
- SQL SERVER – Data Sources and Data Sets in Reporting Services SSRS (sqlauthority.com)