Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Advanced KPI Configurations in SQL Server Reporting Services 2016


By:   |   Read Comments (3)   |   Related Tips: > Reporting Services KPI

Attend these FREE MSSQLTips webcasts >> click to register


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:

DECLARE @KPIGoal INT = 75000000;
SELECT
  YTD_TotalExcludingTax = SUM([Total Excluding Tax])
 ,KPIGoal    = @KPIGoal
 ,KPIStatus    = CASE WHEN SUM([Total Excluding Tax]) / @KPIGoal < 0.70 THEN -1
         WHEN SUM([Total Excluding Tax]) / @KPIGoal > 0.90 THEN 1
         ELSE 0
         END
FROM [Fact].[Order]
WHERE [Order Date Key] >= '20160101'
 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:

KPI in SQL Server Reporting Services 2016

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.

Add to Favorites in SQL Server Reporting Services 2016

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

Star denotes your KPI Favorites in SQL Server Reporting Services 2016

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.

Browse your Favorite KPIs in SQL Server Reporting Services 2016

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.

Trend query to return all of the data points need to draw the trend line

The following query retrieves the monthly sales amount from the orders table:

SELECT
  Trend_TotalExcludingTax = SUM([Total Excluding Tax])
  ,OrderMonth    = MONTH([Order Date Key])
FROM [Fact].[Order]
WHERE [Order Date Key] >= '20160101'
 AND [Order Date Key] <= '20161231'
GROUP BY MONTH([Order Date Key])
ORDER BY 2;

In the KPI editor, you need to set the Trend Set to Dataset trend.

Dataset Trend Editor in SQL Server Reporting Services 2016

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.

Trend dataset in SQL Server Reporting Services 2016

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

Trend editor in SQL Server Reporting Services 2016

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:

Trend visualizations in SQL Server Reporting Services 2016

The finished KPI looks like this in the portal:

Finished KPI with trend in portal in SQL Server Reporting Services 2016

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.

related content options to configure a drill through link in SQL Server Reporting Services 2016

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

Choose a mobile report in SQL Server Reporting Services 2016

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.

Thumbnail to navigate to the report in SQL Server Reporting Services 2016

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).

Specify a custom URL in SQL Server Reporting Services 2016

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

Custom URL is displayed in the related content seciton in SQL Server Reporting Services 2016

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





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     



Tuesday, November 15, 2016 - 3:42:44 AM - Koen Verbeeck Back To Top

Hi Austin,

I've asked the SSRS team and the answer is that it is currently not possible.

Sorry to bring bad news.
Koen


Tuesday, November 08, 2016 - 5:56:54 AM - Koen Verbeeck Back To Top

Hi Austin,

I'm not sure how to achieve your goal. Documentation is sparse and google searches teach me that other people are struggling with the same thing.
I've asked the question to some people of the SSRS team. Hopefully they can come up with an answer :)


Monday, November 07, 2016 - 5:11:30 PM - austin Back To Top

 Hey there Koen,

I'm attempting to leverage KPIs in an SSRS landing page, but wish for the KPI to be evaluated to pass the username and report back figures with respect to the viewer. I've got a Multidimensional datasource with dimension relationships in place to support this but am hitting a wall in terms of Cacheing vs always-fresh and any form of parameterization requiring a default value which will not come hrough without prompting the user. Is there a means about this sort of personalization?


Learn more about SQL Server tools