As you start working on SSRS reports, it is only imperative that you will eventually be asked to create a parameterized report at one point in time. This is because this is one of the most common type of reports as it gives the power of filtering the report based on end user preference.

For e.g., a business analyst might just want to focus on North America sales today but might want to view the sales of another region tomorrow. A static report without parameters and created just for North America sales would not work here and another report would be needed to be created to meet the Analyst’s requirements. On the other hand, creating a parameterized report will enable the Analyst to just select the region that he wants to focus on and view the report.

In this article, we will be looking into the detailed steps as to how to create a parameterized report.

Since we will be using Analysis Services as the source here, we will use MDX to query the cube. MDX is a powerful language and SSRS supports this wonderful language.

Step 1: Open BIDS (All Programs > Microsoft SQL Server 2012 > SQL Server Data Tools). In the start screen, click on New Project (or go to File > New > Project).Provide a name for the project and also browse to the location where we want the project to be created (tick the check box to create a directory for solution). Click OK.

Once the new project is created, in the Solution Explorer, we can see a Solution named SSRS_2012_Reports with three folders: Shared Data Sources, Shared Datasets and Reports.

Step 2: The first thing we need to do is to create a data source followed by a dataset. To create a Shared Data Source, right-click Shared Data Sources and select Add New Data Source.

The Shared Data Sources Properties window opens up. Provide a Name for the shared dataset. In the Type selection box, select Microsoft SQL Server Analysis Services (since we will be querying an SSAS Cube to build our report).

Then in the connection string, click the Edit button. The Connection Properties window box opens up. Provide the server name, in my case it is the local server and I just put a (dot) and then select the AdventureWorksDW2012Multidimensional-EE database (This is the SSAS database that contains the Adventure Works cube that we will be querying). Click OK to exit the Connection Properties window.

Click Ok again.

Step 3: Now that we have a Data Source, we can go ahead and start creating our report. (A dataset is still required which we would create a little later). Right-click on Reports folder in the Solution Explorer, Select Add option and New Item.

In the Add New Item window, select Report template, provide a name for the same and click on Add to create a new blank report.

Step 4: Now, we have created a blank report with no data source (the one we initially created was a Shared Data Source with Project scope). We still need to have a Data Source that the new report can have access to. So, let’s create a new Data Source specific to the report and point it to our Shared Data Source we initially created.

In the Report Data pane (if you are not able to locate the Report Data pane, press Ctrl + Alt + D to toggle the same), right-click on Data Sources folder and select Add Data Source… that will open up the Data Source Properties window.

In the Data Source Properties window, provide a Name for the Data Source and select the ‘Use shared data source reference’ radio button and select the shared data source that we had initially created.

Click OK to close the window and return to the report.

Step 5: The next step is to add a Dataset. Right-click on Datasets folder and select ‘Add Dataset…’ to open the Dataset properties window.

In the Dataset properties window, provide a name for the dataset and select the ‘Use a dataset embedded in my report.’ radio button. Then select the data source that we created in the previous step and click on Query Designer button.

This will open up the Query Designer window that allows us to browse the Adventure Works cube. This Query Designer supports both graphical interface (Design View) and code interface (Query View). While the Design View allows us to drag and drop the required measures and dimensions and set filters, the Query View allows us to manually modify the MDX query to perform advance operations.

But note that once we switch from Design View to Query View and make even a small change, we cannot switch back to Design view. So we need to use caution here. Eventually though, we would need to switch to Query View to take advantage of the full power of MDX.

Browse to Internet Sales Amount measure (under Internet Sales measure group) and drag it to the white space. We can also right-click a measure or dimension & select Add to Query.

Next, let us add some dimensions.

Browse to the Product dimension and in the Product.Product attribute hierarchy, drag the Product level to the query results window.

In the below screenshot, Product.Product is an attribute hierarchy and within this hierarchy, Product (highlighted in red) is a level.

Now, let us add some filters to our dataset. There are multiple ways to add a filter. We can either browse to the hierarchy, select it and click ‘Add to Filter’, drag the hierarchy to the filters pane or use the <Select dimension>’ drop down box to select the dimension and the Hierarchy.

Let us drag and drop the Category hierarchy to the filters tab. Select Equal in the Operator column and in the Filter Expression column, select ‘All products’. Similarly, drag the Product.Subcategory, Product.Product and Date.Calendar hierarchies to the filter pane and set it to the values mentioned in the screenshot.

Note: Since we need to provide a way for users to modify the filter expression such as category, sub-category or product, we will make them as parameter by marking the Parameters check box.

Once done, click the OK button to go back to Dataset Properties.

Now we can see the MDX code behind our drag and drop actions and filters in the Query section of the Dataset Properties window. Click Ok to exit the Data Properties window.

Since we have opted to parameterize the filters, parameters and datasets are automatically created in the background (the parameter datasets cannot be initially seen under datasets).

To see these hidden parameter datasets, we need to right-click Datasets folder and select Show Hidden Datasets.

  

Step 6: Now that we have the dataset, we can go ahead and add report items to the report body to display the report. In the report body, right-click and select Insert > Table.

Align the table at the top of the report and assign the columns to the dataset fields as shown below.

When we select a cell in the detail row of the table, we see a small blue and white icon. When we click this icon, a drop down list of all the dataset fields appears. We can select the required dataset field to assign for the column.

Step 7: We can apply formatting for the various report items, like background colors for the headers, font type and size, and for numeric and currency fields, we can assign the respective formatting so that they appear correctly in the final report.

We also have some pre-defined currency and number formats. E.g., For a Currency field, we can go to the Format property for the cell and set it to either C0, C1 or C2. Similarly for Number fields, we can set it to N0, N1 or N2.

Now, the report is ready to be previewed. When we click Preview, we (hopefully 🙂 ) see a beautifully designed report.

But, wait a minute. The report is not yet final!

If we observe closely, we can see that the Prompts for parameters are not so friendly.
E.g., we see labels like Product.Product for the Product slection box and Date.Calendar for the date parameter. We need to change them.

Step 8: Go back to the Design mode and in the Report Data toolbar, under parameters, open each parameter and modify the Prompt: to a more user friendly prompt.

Click Preview again. Now the report looks good. But is it perfect?

Just click on the Calendar Year prompt and see how the parameter is being populated.

Does not look good. Does it? Let’s fix it.

 

Step 9: Fixing the Date parameter to accept only years:

Go back to the design view. In the Report data, under parameters, copy the Query from any other existing parameter: E.g., from ProductCategory parameter

Step 10: Create a new dataset as below.

In the Dataset Properties, provide a Name for the dataset and select the ‘Use a dataset embedded in my report’ option. Select the Data Source from the drop down box and click on Query Designer.

Paste the copied query into the new dataset and modify the query by replacing the hierarchies/ levels with the required ones as below.

OLD Query: The below is the old query from the ParoductCategory parameter. We need to change the text highlighted in Red.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Product].[Category].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Modified Query: The below is the modified query.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Date].[Calendar Year].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Click the exclamation mark ! to test the query results. You should see similar results for ParameterValue containing Years.

 

Click Ok to come out of the Query Designer.

Click OK and the new dataset is created.

Step 11: Now that we have our dataset ready, it is time that we make use of it.
Open the existing DateCalendar parameter.

In the General tab, change the Name to Year and Prompt to Select Year.

Next, go to the Available Values tab.

Change the Dataset name to LoadYear from DateCalendar.

Next go to the Default values tab.

Change the option from Specify Values to No Default value as below. Click OK.

Step 12: Now that we have created a new dataset and mapped that to the parameter, we can delete the Original, now unused DateCalendar dataset.

Now when you click preview, you can see that the correct year values appear in the drop down and the report executes properly 🙂

 

—