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:
You will be presented with the New KPI page.
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.
The KPIs value should correspond to the value of AnnualSales.
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.
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.
Check the radio button labeled AnnualSales and click OK.
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.
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
