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.

Kpi’s 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 the
Microsoft 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.

Leave a Reply

Your email address will not be published. Required fields are marked *