未加星标

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

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

By:Koen Verbeeck || 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 .


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

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.


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

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:


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

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


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

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 .


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

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.


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

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 .


Using a Parameter in a SQL Server Reporting Services 2016 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.


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

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


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

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


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

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.


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

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.


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

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.


Using a Parameter in a SQL Server Reporting Services 2016 Mobile Report
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.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSQL ServerVisual StudiVisual StudioKPISUTIEPA
分页:12
转载请注明
本文标题:Using a Parameter in a SQL Server Reporting Services 2016 Mobile Report
本站链接:http://www.codesec.net/view/521912.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(162)