Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using a Parameter in a SQL Server Reporting Services 2016 Mobile Report


By:   |   Read Comments (2)   |   Related Tips: > 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.

sample query

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.

create new item using 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:

create new data source

When we create the new dataset, you need to pick the data source you just created.

create new data set - select data source

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.

specify query

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.

specify query

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.

create 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.

add data

Since we have a shared dataset on the server, we need to choose the Report Server option.

report server

After choosing your report server, you can pick the shared dataset you just created.

choose shared data set

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.

imported data set

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.

dashboard design

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.

configure selection list

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.

context menu

In the context menu, click on the curly brackets (with Param.) to open up the parameter menu.

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:

grid properties

When we run the preview, you'll see that the grid is empty.

no data returned

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.

disable All

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:

preview with data 2013

By changing the selection, we can run the orders dataset for a different year:

preview with data 2016

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


Last Update:






About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, April 18, 2017 - 4:59:18 AM - Koen Verbeeck Back To Top

Hi Hodges,

what exactly do you mean connect other data sets to the same parameter? A parameter is specific to a dataset (meaning a dataset will be filtered by a parameter). Do you want to filter multiple data sets using one listbox control for example?


Monday, April 10, 2017 - 11:46:53 AM - M.Hodges Back To Top

I have done what you prescribed about adding a parameter to a report and for one of the controls I can pass the parmeter in the URL as intended.  When I try to connect other data sets to that same parameter in the mobile report there are no other options besides a blank and default value and below source report parameters I have no options.  Any idea what I did wrong?


Learn more about SQL Server tools