Key performance indicators, or KPIs, are at-a-glance metrics for simple insight into the business. These are each designed to provide a glimpse into one aspect of a measurable business activity. KPIs are present in various implementations in most every reporting tool, including SQL Server Reporting Services. Until recently, KPIs in SSRS were not a native part of the SSRS reporting stack. Although you could create and expose KPIs through Reporting Services, this was a tedious and manual task. With the most recent release of SQL Server 2016, KPIs have been promoted to first class citizens in the new SSRS report portal.
KPIs in SSRS
The new KPI is a report type all in itself. In previous versions of SSIS, you’d have to create a report and manually build the pieces to display a KPI value and status. In 2016, KPIs are exposed as a distinct entity which is prominently displayed on the report portal.
As shown above, KPIs provide for easy visibility into key metrics used by the business to determine success. Now that these metrics are shown directly in the report portal, the SSRS web interface is no longer just a place where reports are deployed. It now functions more like a dashboard that can be shared with stakeholders at every level of the organization.
Creating a KPI
Under the covers, KPIs use a shared data set as a source. Once that data set has been created, you can build the KPI directly in the report portal. To create a new KPI, navigate to New –> KPI which will bring up the New KPI window.
In exploring this window, you’ll find the following configurable settings:
- Value format: Set the formatting (basic data type and precision) of the output label in the KPI.
- Value: This sets the actual value to be displayed in the KPI.
- Goal: The target value for this KPI, which can be compared to the actual value above to determine status indicator (good/neutral/bad).
- Status: The status for this KPI, which sets the color for the visual; green is good, yellow is neutral, and red is bad.
- Trend Set: When using a visualization that includes a trend indicator, this is the set of values used for that trend.
- Visualization: This sets the visualization, if any, used to show trending on the KPI.
- Related content: This setting lets you define a link for more information on this KPI. The link can either be a mobile report on the same SSRS instance, or a custom URL. For the latter, it’s typical to link to a paginated SSRS report, but since it’s just a plain URL, it can point to any valid HTTP address. It is worth noting that in SQL Server 2016 SP1, a new option called Direct Navigation was added to the Custom URL selection, essentially treating the specified link as a drillthrough link.
Once the above settings have been specified, the KPI can be published to the SSRS portal. Like traditional paginated reports, KPIs can be published anywhere in the SSRS folder hierarchy. This makes it possible to have business unit-specific KPIs at their fingertips by navigating directly to their folder.
Although the concept of the key performance indicator has been present for every version of Reporting Services, using KPIs in SSRS 2016 is much easier for both the creator and the consumer.
Good article. I think it is important to mention that KPI’s are only available in the SSRS 2016 ENTERPRISE edition. They are NOT available in Standard, making this feature “out-of-reach” for small businesses and some SME’s!