Advanced KPI Configurations in SQL Server Reporting Services 2016

By:   |   Comments (3)   |   Related: > 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:

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




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

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 8, 2016 - 5:56:54 AM - Koen Verbeeck Back To Top (43721)

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 7, 2016 - 5:11:30 PM - austin Back To Top (43717)

 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?















get free sql tips
agree to terms