Sunday, April 09, 2017

Create KPIs using SSRS 2016

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

KPI1

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”

KPI2

c) The new KPI will be displayed as shown below.

KPI3

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.

KPI4

KPI5

Select “connKPI” and click Open

KPI7

b) Click on “Create” button

KPI8

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.

KPI10

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”

KPI11

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.

KPI12

No comments:

Post a Comment