技术控

    今日:113| 主题:57850
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] Advanced KPI Configurations in SQL Server Reporting Services 2016

[复制链接]
無可奈何的無賴 投递于 2016-10-3 11:43:16
413 6
By:Koen Verbeeck |  |   Related Tips: > Reporting Services KPI
   Problem

   SQL Server Reporting Services 2016 (SSRS) introduces the concept of mobile reports and KPI, followed by Microsoft's acquisition of Datazen. The tip Create a basic KPI in SQL Server Reporting Services 2016 explained how you can create such a KPI in the brand new Reporting Services portal. In this tip, we'll take it a step further and explain additional configuration options for a KPI.
  Solution

  Test Set-up

   We will use the same test set-up as in the tip Create a basic KPI in SQL Server Reporting Services 2016 , so it's advised to read it first. The tip explains how to create a shared data source, a shared data set and a basic KPI using the result set of the dataset. For the sake of completeness, this is the query used in the dataset:
  1. DECLARE @KPIGoal INT = 75000000;
  2. SELECT
  3.   YTD_TotalExcludingTax = SUM([Total Excluding Tax])
  4. ,KPIGoal    = @KPIGoal
  5. ,KPIStatus    = CASE WHEN SUM([Total Excluding Tax]) / @KPIGoal < 0.70 THEN -1
  6.          WHEN SUM([Total Excluding Tax]) / @KPIGoal > 0.90 THEN 1
  7.          ELSE 0
  8.          END
  9. FROM [Fact].[Order]
  10. WHERE [Order Date Key] ¡Ý '20160101'
  11. AND [Order Date Key] ¡Ü '20161231';
复制代码
With the dataset created, you can create a KPI by following the instructions in the tip. Ultimately, your KPI should look like this:
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-1-技术控-additional,Microsoft,explained,further,reports

  Using Favorites

   In the Portal, you can mark one or more KPIs (or reports) as your favorites. You can simply do this by clicking on a KPI, which will result in a pop-up. In the pop-up, you simply select Add to Favorites .
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-2-技术控-additional,Microsoft,explained,further,reports

  When you return to the portal, you can recognize your favorite KPIs by a star in the upper right corner.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-3-技术控-additional,Microsoft,explained,further,reports

   Now, at the top of the portal you can click on the Favorites header. This will take you to a separate page where all your favorite KPIs and dashboards are displayed together. This page should also be your starting page when you browse to the report portal for the first time. Favorites should also be supported by the native Power BI app.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-4-技术控-additional,Microsoft,explained,further,reports

  Adding a Trend to a KPI

  The KPI visualization in Reporting Services 2016 also supports the display of a trend. In order to achieve this, we are going to need another dataset. This dataset will return all of the data points needed to draw the trend line.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-5-技术控-additional,Microsoft,explained,further,reports

  The following query retrieves the monthly sales amount from the orders table:
  1. SELECT
  2.   Trend_TotalExcludingTax = SUM([Total Excluding Tax])
  3.   ,OrderMonth    = MONTH([Order Date Key])
  4. FROM [Fact].[Order]
  5. WHERE [Order Date Key] ¡Ý '20160101'
  6. AND [Order Date Key] ¡Ü '20161231'
  7. GROUP BY MONTH([Order Date Key])
  8. ORDER BY 2;
复制代码
  In the KPI editor, you need to set the Trend Set to Dataset trend .
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-6-技术控-additional,Microsoft,explained,further,reports

  In contrast with the other dataset, you don't need to choose an aggregation function because now it's completely normal to have a multi-row result set. Keep in mind that the KPI is just a visualization container. It's up to the author of the dataset queries to make sure everything makes sense and that the trend corresponds correctly with the KPI values shown.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-7-技术控-additional,Microsoft,explained,further,reports

  With the trend configured, the KPI now looks like this:
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-8-技术控-additional,Microsoft,explained,further,reports

  With the trend included, we can easily deduce that orders have been rising since the start of the year, with a great peak in the last two months. It's also clear that the current month is not finished yet (hence the low bar). There are also other visualization methods for the trend: a line graph, a stair-stepped graph or an area chart. All the other options are shown in the figure below:
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-9-技术控-additional,Microsoft,explained,further,reports

  The finished KPI looks like this in the portal:
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-10-技术控-additional,Microsoft,explained,further,reports

  Configuring Related Content

  A final option of a KPI is the ability to set a drill-through link. This link can either be a mobile report published on the portal or a custom URL.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-11-技术控-additional,Microsoft,explained,further,reports

   When you choose Mobile Report , you can choose the destination in a dialog.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-12-技术控-additional,Microsoft,explained,further,reports

  When you now click on the KPI in the portal, a thumbnail of the mobile report will be shown under related content. Clicking on this thumbnail will simply navigate you to this report.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-13-技术控-additional,Microsoft,explained,further,reports

  You can also specify a custom URL. This can be anything: a website, a SharePoint site, an URL to an SSRS report (which would allow you to pass along hardcoded parameters).
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-14-技术控-additional,Microsoft,explained,further,reports

  When you now click on the KPI, the URL will be shown under related content.
   

Advanced KPI Configurations in SQL Server Reporting Services 2016

Advanced KPI Configurations in SQL Server Reporting Services 2016-15-技术控-additional,Microsoft,explained,further,reports

  Unfortunately it's only possible to add one mobile report or one custom URL.
  Conclusion

  This tip has shown how you can easily add a trend to a KPI. It also explained how to favorite a KPI and how to add related content to a KPI.
  Next Steps

  
       
  • Try it out yourself! Go to the SSRS portal and create a new KPI with a trend. Sample values are automatically provided.   
  • Please refer to the tip Create a basic KPI in SQL Server Reporting Services 2016 for information on creating KPIs in SSRS 2016.   
  • For more SQL Server 2016 tips, you can use thisoverview.  
Last Update:  10/3/2016



上一篇:小白学react之SASS实战
下一篇:CurrencyFormatter.js – A super simple currency formatting library
昆仑之巅 投递于 2016-11-6 09:18:38
昆仑之巅涨姿势了
回复 支持 反对

使用道具 举报

笑白 投递于 2016-11-6 15:14:51
出来混,老婆迟早是要换的.
回复 支持 反对

使用道具 举报

自我催眠 投递于 2016-11-9 10:35:23
不明觉厉,先回再看!
回复 支持 反对

使用道具 举报

白晴 投递于 2016-11-10 11:19:48
不作死就不会死
回复 支持 反对

使用道具 举报

李茂 投递于 2016-11-13 12:55:08
传说中的沙发???哇卡卡
回复 支持 反对

使用道具 举报

贾叶洋 投递于 2016-11-14 08:23:26
无论是不是沙发都得回复下
回复 支持 反对

使用道具 举报

我要投稿

推荐阅读


回页顶回复上一篇下一篇回列表
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 | 粤公网安备 44010402000842号 )

© 2001-2017 Comsenz Inc.

返回顶部 返回列表