Creating a tiled KPI in SSRS 2016 CTP 3.3

SSRS 2016 CTP 3.3 has introduced the new web portal which features KPI’s and mobile reports.

KPIs are small tiled reports pinned to the main SSRS web portal, they give users a quick glance on performance.

This blog will focus on creating a KPI tile based on theMicrosoft Adventure works data warehouse installed on a SQL Server 2016 CTP 3.3, with the latest SSDT and report builder installed.

As of CTP 3.3 the only way of creating a new KPI is through report builder (v 3.0). This blog assumes you have already created and deployed a Data Source to the SSRS server.

1) Open report builder, click on “File” and then on “new dataset”: select the Data Source of your choice and hit create.

2)      Once in the dataset editing window, select a stored procedure that you have previously created  (the one i used can be found here: spObtainInternetSalesByYearKPIV1.sql (386.00 bytes)). Go to file and then save the dataset with a name of your choice on the SSRS server. 

3) Go to the SSRS web portal, click on new and then on KPI. 

4) In the new window enter a name for your KPI and then go to “Trend set”, in the drop down select “Dataset trend” and in the “Pick dataset trend” box click on the three dots.

You will be prompted with a view of all SSRS folders, navigate to the folder where the dataset you created earlier is and click on it.

A new window will open with a preview of the data from your dataset, select the column your KPI is based on, in my case is “SalesKPI” and click “OK”.

5) Go back to the SSRS web portal where you’ll see the newly created KPI.

The value displayed on the KPI is a sum of all values of the column we selected earlier. In our example this doesn’t say much. You’ll also notice the KPI colour is green despite sales falling sharply.

To change the KPI displayed value and the background colour we need to change the underlying stored procedure. At this stage no calculation, filtering or any kind of logical operation can be done on the values returned by the datasets.

6)
After modifying the stored procedure (spObtainInternetSalesByYearKPIV2.sql (939B)) you need to update the dataset and replace the one we created earlier.

7)
We can then head back to the SSRS web portal and change the KPI by clicking on the 3 dots and then on “Manage”. Once in the KPI edit window:

a.
Set “Value” to “Dataset field” and “Pick Dataset field” to the name of your dataset, if you used my stored procedure select the column “KPIFaceValue” and click “OK!.

b.
Do the same for “Goal”, Status and “Trend Set” selecting the columns “target”, “KPIColour” and “salesKPI” respectively.

c.
Apply all changes and head back to the SSRS web portal.

8)
We now have a modified KPI with a target progress(expressed as a percentage) and a colour that indicates the status of our measure

Conclusions

The new tiled KPI’s are a nice addition to SSRS and give the tool a more modern look. This feature is clearly still in development stage band things might change with new releases of SSRS 2016.