This article will explain detailed usage of parameters in mobile reports in SSRS. Parameterized reports are important because report readers want to filter and customize their report data with parameters and this option helps report readers to focus on particular data. A parameterized SSRS report uses parameters as an input value and filters the report data according to these parameters. In this article we will focus, in particular, on how to use parameters and data sources together and how can we manage the parameters in mobile SSRS reports.
In my previous article How to create a Mobile Dashboard with SQL Server Microsoft Mobile Publisher, I described various details of dataset creation methodology and designing mobile reports. I recommend taking a glance at the “Creating data source and dataset” section in this article because we will use the same dataset sample and skip data source and dataset creation steps.
Customizing SSRS datasets with parameters
In this section, we will add a parameter to a dataset. The reason for doing this is that a mobile report connects to SQL Server Reporting Server and gets data over this dataset. Also, for our demonstration we must create a new dataset to get a list of parameters. This new parameter list dataset provides us with data to populate parameter values to mobile report. Now, we will connect to the SSRS web URL and click (…) three points and select Edit in Report Builder. This process allows us to edit the dataset and add parameter.
To complete this dataset editing process we need to use the SSRS Report Builder. If you haven’t installed Report Builder before, you can click the Get Report Builder navigation image and easily download and install the Report Builder. Report Builder will open automatically for dataset editing if it was previously installed.
Visual Studio 2017/2015 is another option to edit the dataset but ensure that the SQL Server Data Tools (SSDT) installation is completed for Visual Studio.
We will customize the dataset query with parameter for which we want to filter the column with this parameter. This parameter name has to start with “@” sign.
|
1 2 3 |
SELECT * FROM [dbo].[DemoForMobileReport] WHERE SalesPerson IN (@SalesPerson) |
After completing these steps, we will click Set Options Dataset and change the dataset parameter settings. In the Parameters tab we will change the data type of parameter to text and click Allow multiple values option. This option allows us to populate data when we select “All” option in the mobile report parameter. Then we will click Allow null value and then we can save the dataset to report server.
In this step, we will create a new dataset for parameter list. This dataset will populate the parameter data to a selection list in the mobile report. We will change the query in the Query Designer as below and then click the (!) exclamation button to execute the query and then click Save As. Be careful about the save as process as otherwise you may overwrite your previous dataset.
|
1 2 |
SELECT DISTINCT SalesPerson AS [KeyVal], SalesPerson AS [LabelVal] FROM [DemoForMobileReport] |
We will give a name to the parameter list dataset and then click the Save As button and save the dataset to the SSRS report server.
After completing these steps, we will see two datasets when we connect the report server web URL. One of them is for report data and other one is for report parameter list data. In the mobile report designer, we will use both of them.
Mobile report design
We will launch Microsoft SQL Server Mobile Report Publisher and then select the datasets from the SQL Server Reporting Service. Click Add data and select Report server, this process allows us to connect to SQL Server Reporting Service and select datasets which were previously saved.
In the Connect to a server screen we will enter the report server web service URL and fill the proper credentials and then click Connect.
We will select and then add our report dataset (MobileReportDataset) and filter dataset (ReporParameterListDataSet).
We will add these two datasets into a new mobile report and the mobile report dataset tab will look like the following image.
In the previous image you will notice a green image placed near the MobileReportDataset label. This green image defines that report dataset incudes parameter. Now, we will click Layout tab and two components in the report designer panel. The first one is a selection list which helps us to use parameter lists in reports and send parameter selections to the report dataset as a filter to data. Drag and drop the selection list to the report design panel. Another component is the number component which helps us to show total sales.
We will navigate the Data tab and make the settings of these two components. First of all, we will make the selection list setting. Specify the data settings of selection list which are in the below image.
Now we will hook the selection list parameters to MobileReportDataset. Click the Setting button near the MobileReportDataset label and select Param.
In the set dataset parameters tab we will change the @SalesPerson value to Selected Items and then click Apply. With this setting we have created a bridge between selection list and @SalePerson dataset. When the end user selects any parameter, it directly goes through the dataset parameter.
We will select the number component and change the following settings.
Finally, we will run our mobile report for test. Click the Preview button and run the report.
When we change the Sales Person, Total Sales value will automatically change. The first image shows the selection of one sales person (George) Total Sales and the second one shows all sales person total sales.
When we run the profiler, we can see the parameter and query interaction.
Summary
In this article we demonstrated parameterized mobile reports in SSRS. This type of mobile report is for development purpose of mobile devices. For this reason, using parameters make reports easier and offers a more functional and flexible user experience.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023