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

 

How to report KPIs with gauges in SQL Server Reporting Services Mobile Reports


By:   |   Read Comments   |   Related Tips: > Reporting Services KPI

Problem

Key Performance Indicators (KPIs) are the most common and included data elements on mobile reports. KPIs are frequently tracked at every level of an organization. Gauges are the most logical visualization for representing KPIs. Also gauges are very compact visualizations making them ideal for mobile reporting. In this tip we would learn how to use KPIs as a data source and report the same using a bulletgraph gauge on a mobile report.

Solution

Mobile reports can access published shared datasets, consume the same in visualizations and render in mobile reports.

Mobile Report Publisher can be used to author mobile reports that can be hosted in SQL Server Reporting Services (SSRS) 2016. For this tip, we assume that a KPI is available and hosted on the SSRS report portal as a shared dataset. You can learn more about creating KPIs and hosting them on a report portal from this tip.

Follow the below steps to add a KPI and consume it on a bulletgraph gauge.

1) I have a sample KPI already published on the SSRS report portal. This KPI is the Net Income from the AdventureWorks sample cube. A KPI has goal, value, status and trend as fields available in the dataset.

A KPI has goal, value, status and trend as fields available in the dataset

2) Open Mobile Report Publisher and you should have a blank report open that is ready to author. Click on the Add Data button and navigate to the path on your report server where the KPI has been published.

Click on the Add Data button and navigate to the path on your report server where the KPI has been published

3) Once you have added the KPI successfully to your report, you should be able to see the data similar to the below image. Take a note that the value of the KPI is 12.6 million and the Goal is almost 5.6 million. That means the KPI has reached its target that is much higher than the goal.

Bullet Graph Fields

4) Drag and drop the bulletgraph gauge on the report and click on the data tab. Configure the data properties of the bulletgraph as shown below. The main value should be the actual value field of the KPI and comparison value should be the goal field of the KPI.

Configure the bulletgraph gauge properties

5) Go back to the report layout and you should be able to see the bulletgraph value as shown below. The black vertical bar shows the goal and the horizontal grey bar shows the actual value of the KPI. The graph clearly shows that the value of the KPI is much ahead the goal.

Bullet Graph Displaying the KPI values

6) If you swap the goal and value fields in the main and comparison metric properties, you will find the bulletgraph as shown below. In this case the goal has become 12.6M and actual value has become 5.6M. If you carefully compare the below and above graphic, you should be able to see that the scale is different in both of them. The above graphic has a scale of 12.6M and the below has a scale of 18.9 million. The scale is dynamically adjusted based on the actual value and goal of the KPI.

scale is dynamically adjusted based on the actual value and goal of the KPI

7) The thresholds are different as well in both the graphics. These thresholds can be configured using the range properties as shown below. These thresholds define the colors displayed on the bulletgraph.

theresholds can be configured using the range properties

In this way we can use KPIs, represent the same using gauges and display the same on a mobile report.

Next Steps


Last Update:






About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

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 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools