image.png

Creating KPIs in SQL Reporting Services

In this blog I will explain how to create a Key Performance Indicatory (KPI) in SQL Reporting Services 2016.  Let’s get started.

The first step is ensuring that you install the latest CTP for SQL Server 2016.  After that, you need to create a dataset.  Datasets can be created using Report Builder or SQL Server Data Tools.  For the sake of brevity, I am going to assume that everyone knows how to create Shared Data Sources and Datasets.  If not, reference the following YouTube videos, authored by me:

In my example I will be using the following object and query below:

–Script used to create and populate table that will be used to create the KPI
DROP TABLE IF EXISTS dbo.AnnualSalesAndQuota
GO
CREATE TABLE dbo.AnnualSalesAndQuota
(
SalesYear int,
AnnualSales money,
AnnualSalesQuota money
)
GO
INSERT INTO dbo.AnnualSalesAndQuota
VALUES
(2016, 281123.54, 371000.00),
(2015, 515622.90, 544000.00),
(2014, 406620.07, 455000.00),
(2013, 32567.91, 35000.00)
GO

–Query used as source for Dataset
SELECT
SalesYear,
AnnualSales,
CASE
WHEN AnnualSales > AnnualSalesQuota THEN 1
WHEN AnnualSales < AnnualSalesQuota THEN -1
ELSE 0 END STATUS,
AnnualSalesQuota
FROM dbo.AnnualSalesAndQuota

Ok, with that out of the way, lets create a KPI.  In the current CTP of SQL Server 2016, you will need to navigate to this link:  http://<your server name>/Reports_Preview.  Replacing your server name with the name of the Report Server you will be using.   Once the new SSRS web portal opens, navigate to the folder where you want to create the KPI.

Once there, move your cursor to the top-right corner of the web portal, click New, and click KPI as seen below:

image

You will be presented with the New KPI page.

image

Enter, Company Sales Goal in the KPI name text box.  Then select Currency with decimals from the Value format drop down list.

Easy enough.  Now time to use the dataset that was created earlier.  Select Dataset field from the Value drop down list.  Click the ellipses (…) in the text box to the right.  It currently contains Not set.  Browse to the dataset location On the Pick a Dataset window.  Click the dataset and the Pick a Field window will open.  Notice that only a single row appears. Why?  This is normal behavior for this release.  Check the radio button labeled AnnualSales and click OK.

image

The KPIs value should correspond to the value of AnnualSales.

image

Repeat these steps for Goal and Status, using AnnualSalesQuote for Goal and STATUS for Status.  A few things you may have noticed is that a percentage is displayed when goal is set, which is the percentage the value is from the goal, (Value-Goal)/Goal.  You may have also noticed that the STATUS column contained a –1 in the dataset.  This is typical to any other KPI implementation, 1 = Green, 0 = Amber, and –1 = Red.

image

Finally, select Dataset trend from the Trend set drop down list and click the the ellipses (…) in the text box to the right.  This time, things are a little different.  Instead of a single row, notice that all rows are visible.

image

Check the radio button labeled AnnualSales and click OK.

image

Now a bar chart is displayed.  You can change the format of the bar chart to other visuals by selecting them in the visualization section.  Give it a try.

image

One thing that I noticed is that you have to choose whether you are going to display the trend or the percentage.  Will that change in the future?  Not sure what’s going to happen, but I am anxiously waiting.

Wait a minute, what about refreshing the data?  Well, I am happy you asked, but you are going to have to wait until the next blog post.

As always, if you have any questions or comments email me at pleblanc@sqllunch.com.

Talk to you soon,

Patrick LeBlanc

12 thoughts on “Creating KPIs in SQL Reporting Services

  1. Permalink  ⋅ Reply

    Jacque

    February 22, 2016 at 5:38pm

    Hi Patrick, I’m working in SSRS 2014 currently, so this looks like something I’m going to need to start looking into. Do you ever make it out to SQL Saturday in San Diego? You have an easy to understand style that works well for me. I’m just starting to learn PowerBI to go with SSRS and would love to learn more.

  2. Permalink  ⋅ Reply

    pleblanc

    February 22, 2016 at 10:29pm

    I haven’t spoken there yet. I may reach out to organizers. Thanks for reading

  3. Permalink  ⋅ Reply

    » Refreshing KPI Data in SSRS 2016

    February 23, 2016 at 2:22pm

    […] my blog from last week, Creating KPIs in SQL Reporting Services, I am now going to explain how to configure scheduled data refresh for the KPI that was created in […]

  4. Permalink  ⋅ Reply

    jGm

    February 24, 2016 at 4:46pm

    Any plans for an SSQL 2k14 version ?

    • Permalink  ⋅ Reply

      pleblanc

      October 6, 2016 at 12:37pm

      Not that I know of.

  5. Permalink  ⋅ Reply

    Santosh Kumar

    May 16, 2016 at 10:04am

    what are the limitations for this Tiled KPI
    1) Can the size of this KPI can be Changed?
    2) On clicking on this Tiled KPI displays a report in detail?

    • Permalink  ⋅ Reply

      pleblanc

      October 6, 2016 at 12:38pm

      1. You cannot change the size of the Toile.
      2. You can add a custom URL or Navigate to a Mobile report

  6. Permalink  ⋅ Reply

    Neil

    June 18, 2016 at 1:33pm

    Is there any way to filter KPI’s by role or with a dimension permission? It would be useful to be able to create a personalized KPI that accepts the logged in user as a parameter and then applies a security or data authorization filter. I read your recent blog post on row level security in SQL Server 2016 – that is a wonderful feature. We use dimension permissions in Analysis Services. However, after you cache the data in SSRS, there is no way to apply any sort of personalized filter as far as I can tell.

    • Permalink  ⋅ Reply

      pleblanc

      October 6, 2016 at 12:32pm

      Not that I know of, but that is actually a great idea.

    • Permalink  ⋅ Reply

      pleblanc

      October 6, 2016 at 12:36pm

      You are correct. I am not sure of a way to do this yet.

  7. Permalink  ⋅ Reply

    dave

    September 30, 2016 at 2:05pm

    Nice article, I’m currently running SSRS 2016 and I don’t have the KPI option in the drop down is this only available in the enterprise option?

    • Permalink  ⋅ Reply

      pleblanc

      October 6, 2016 at 12:32pm

      Yes, KPI’s are Enterprise only.

Leave a Reply

Your email will not be published. Name and Email fields are required.