Pretty much every organisation would love to store KPIs (Key Performance Indicator) in a centralised portal and manage access. SSRS 2016 allows user to create KPIs and track progress against those KPIs through the new Web Portal. Visualisations can also be set for those KPIs.
In SSRS 2016 when you create KPIs you can either set values manually or as a Dataset field. Let us see how to do both the options.
Set Manual KPIs
a) Navigate to the SSRS 2016 Web Portal and click New > KPI
b) Type KPI name as Sales 2017 KPI, Description of the KPI, data format for the KPI, select value as Set Manually, Type 200 as value, select goal as Set Manually, Type 1500 as Enter goal, select status as Set Manually, type –1 as status and select the visualisation you like and click “Create”
c) The new KPI will be displayed as shown below.
Set KPIs using Dataset
a) Create a new table and load data using the script below on an existing database.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KPIs](
[KPI_Name] [varchar](100) NOT NULL,
[Target] [int] NOT NULL,
[Progress] [int] NOT NULL,
[Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[KPIs] ([KPI_Name], [Target], [Progress], [Status]) VALUES (N'Sales KPI', 1500, 200, -1)
GO
b) Create a new dataset using SQL Server 2016 Report Builder. Note: Make sure to create the connection first in the SSRS Web Portal.
Select “connKPI” and click Open
b) Click on “Create” button
c) Expand “Tables” tree and select “KPI” table and click the save button on the menu bar and provide the name “KPI” as dataset name.
d) Now your dataset has been successfully created. Now navigate to your SSRS portal. URL format: https:// (use it only if you have configured SSRS for HTTPS) or http://<servername>/Reports and you can see the the dataset named “KPI”
e) Now follow the same steps as what you followed for creating a manual KPI instead of set manually select “dataset field” as shown below.
