By: Koen Verbeeck | Updated: 2017-01-06 | Comments (4) | Related: > Reporting Services Parameters
Problem
We are using SQL Server Reporting Services (SSRS) 2016. I have created a mobile report using Mobile Report Publisher. The dashboard works fine, but loads kind of slow. I'd like to pre-filter my dataset using a parameter. How can I achieve this?
Solution
Reporting Services has a ton of new features and mobile reports is probably one of the most important ones. Microsoft acquired the company DataZen, which specialized in dashboards and KPIs for mobile devices. In the SQL Server 2016 release, this technology is incorporated into the Reporting Services product. An introduction to mobile reports can be found in the tip SQL Server 2016 Reporting Services Mobile Report and an introduction to KPIs in the tip How to create a basic KPI in Reporting Services 2016. The tool used to create mobile reports is called Mobile Report Publisher and can be downloaded here. Luckily this editor has parameter support built-in.
Test Set-up
Let's use a fairly basic query that retrieves Order Quantities for each month from the Wide World Importers data warehouse.
SELECT
[Month] = DATENAME(MONTH,[Order Date Key])
,[MonthOrder] = DATEPART(MONTH,[Order Date Key])
,[Quantity] = SUM([Quantity])
FROM [Fact].[Order]
--WHERE YEAR([Order Date Key]) = @Year
GROUP BY DATENAME(MONTH,[Order Date Key]),DATEPART(MONTH,[Order Date Key])
ORDER BY [MonthOrder];
The query will return 12 rows, but each month will contain data for all of the years present in the data. For example, January will contain the aggregated order quantities of January 2013, January 2014, January 2015 and January 2016. In the final query we will use a parameter on year, so we will return data for one single year. Keep in mind that if you try out this query on your system, you probably will have different data returned, as data for Wide World Importers is randomly generated and you can add data any time. You can read more about it in the tip Generate more data for the Wide World Importers sample databases.
Before we can use this query in the Mobile Report Publisher, we need to turn it into a shared data set. You can either create one using Visual Studio (SQL Server Data Tools) or using Report Builder. Let's create one with Report Builder. On the SSRS portal, you can click on the New menu to create different kinds of objects. Selecting Dataset will open up Report Builder.
Before we continue, we need to make sure there's a shared data source our dataset can use. In the following screenshot a data source to the Wide World Importers data warehouse is created:
When we create the new dataset, you need to pick the data source you just created.
Next we need to write the query for the dataset. The easiest option would be to write the query using Management Studio and to copy paste it into the editor. Make sure you are using the Text Editor by clicking on Edit as Text.
Before we save the dataset, we need to set some additional options for the parameter. We can do this by clicking on the Set Options button in the ribbon. In the Dataset properties dialog, go to the parameter pane.
Here we'll configure the following options:
- The data type is Integer.
- The default value for the parameter is 2016.
- It won't allow empty or multiple values.
Now we can save the dataset and give it a name. Now we're going to repeat the process and create a shared dataset that will supply the parameter values to the mobile report. In other words, a simple query that will return the different years. The following SELECT statement is used:
SELECT DISTINCT Years = YEAR([Order Date Key]) FROM [Fact].[Order] ORDER BY 1;
Using a Parameter in a Mobile Report
Let's start by creating a new Mobile Report. On the portal, click New and then Mobile Report.
This will open up Mobile Report Publisher. If it isn't installed on your system yet, you'll have to download and install it first. In the Data section of the mobile report, we're going to add our two shared datasets. You can do this by clicking on the Add Data button.
Since we have a shared dataset on the server, we need to choose the Report Server option.
After choosing your report server, you can pick the shared dataset you just created.
The data will now appear in the Mobile Report Publisher editor. The editor has recognized that the dataset contains a parameter. This is indicated with the curly brackets inside the green circle, right next to the dataset name. You can repeat the same process to add the dataset with the distinct years.
Before we proceed further, let's add two objects to the dashboard canvas. You can do this in the Layout section of the tool. The first one is a Selection List, which will provide us with a dropdown box for choosing the parameter value. The other object is a Simple data grid, which will display the tabular data.
Now we need to hook these up with the datasets we imported. Back to the Data tab! It's possible that there are some simulated tables there. These are automatically generated by the Mobile Report Publisher to provide the dashboard objects with sample data. These will disappear once you hook them up with the actual datasets. For the Selection List, change the keys value to the dataset that contains the distinct years. In the example, this is Param_Years. Change the column to Years. There are no other options to set. Do not configure the selection list to filter out any other dataset, since this will be done by the parameter itself.
The next step is to configure the parameter for the OrderswithParam dataset. Click on the gear icon next to the dataset to open its context menu.
In the context menu, click on the curly brackets (with Param.) to open up the parameter menu.
You have three configuration options for the parameter value:
- Default value. This will take the value configured inside the shared dataset as the default value.
- A hardcoded value.
- Source report parameters. These are values provided by objects inside the report. In our case, the selection list.
For the selection list, you can choose between SelectedItem or SelectedItems, depending if you want to have multi-select or not. Here, we want a single selection of a value, so we set the parameter value to SelectedItem of the selection list. Click Apply to close the menu. The next step is hooking up the data grid with the orders dataset. Select the grid in the left pane and set the following properties:
When we run the preview, you'll see that the grid is empty.
This is caused by the selection list we has as default value the All member. The Orders dataset doesn't know any year that is equal to All, so it will not return any data. You can either avoid this by turning off the All selection in the selection list options.
Another option is to take this into account in the source query. If the parameter value equals "All", all data should be returned. After disabling the all selection, we get the following result when the mobile report is in preview mode:
By changing the selection, we can run the orders dataset for a different year:
Remarks
Although the shared dataset for the orders has a configured default value of 2016 for the year parameter, the mobile report just uses the first item of the list instead. In this case, the year 2013. There seems to be no way of influencing this behavior.
There seem to be issues when you add parameters to a dataset at a later point in time. It's possible that the Mobile Report Publisher doesn't detect that there are changes in the dataset, even though you refresh it. Adding the dataset a second time will detect the parameter in the second dataset, but this means you have to relink every report object to the new dataset. This is something you'd like to avoid, so think ahead of which datasets will need parameters.
Conclusion
Adding parameters to a mobile report is straight forward. However, if you need more advanced options, such as using the all member or multi-selections, you need to take care of it in the source query. Some sources will handle this better than others.
Next Steps
- Try it out yourself! You can follow along with the sample provided in the tip.
- The tip SQL Server 2016 Reporting Services Mobile Report goes into more detail about how to create mobile reports using the DataZen technology.
- For more SQL Server 2016 tips, you can use this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-01-06
