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

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.

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.

This is a great review. However, I'm wondering if you've found how to make parameters optional? I did this in Datazen, but after migrating to SSRS it throws an error on my definition. I set the parameter to "Allows null value", and use a WHERE clause like "WHERE Year([Order Date Key]) = @Year OR @Year IS NULL" (to leverage your example). This works in SSMS and Datazen, but SSRS doesn't like it. Any ideas?

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?

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?