KPIs and Scorecards
Key Performance Indicators, or KPIs, are a core element in most business intelligence. SSRS has been able to display KPIs for some time now, and they can be invaluable when it comes to delivering essential performance data in a succinct and meaningful way.
A set of KPIs is often referred to as a scorecard. Here I will not be delving into the management theory of what makes up a “balanced” scorecard, but will only refer to a scorecard as being a collection of KPIs.
Nonetheless, there are many ways to display KPIs. You may be used to a more traditional style of presentation where each KPI is on a separate row in a table, with visual elements to alert the user to deviations from an expected result. However, there are many other ways to deliver KPIs that need not involve tabular data. These can include using gauges to present the information. They may also include using text and colors to highlight changes in status and showing trends with the aid of sparklines. This chapter will begin with traditional KPIs, both to explain the concepts and to demonstrate the techniques. Then I will move on to some examples of less traditional KPIs. The underlying principle will always be the same, nevertheless. I will be comparing a metric to a target and highlighting any deviation from the objective, be it positive or negative. Similarly, I will display a trend over time.
In any case, I hope that you will discover that SSRS can deliver visually arresting KPIs in many ways, and I also hope that the examples in this chapter will be of use in your organization.
What Are Key Performance Indicators?
Before you can start using SSRS to display KPIs, you need to define what they are. Without wishing to get lost in layers of management-speak, let’s say that Key Performance Indicators are a way of measuring progress towards a defined organizational objective. In practical terms, this usually means displaying the following elements:
These two elements are the core of any KPI. However, they can be extended with one or both of the following elements:
What you want to display in a KPI is up to you, or rather, up to the business you are working in. I consider that as long as you have a target figure and valid data to compare to the target, along with some indication of progress, then you have a KPI that you can use to convey meaningful information to your audience.
KPI Value
These metrics are often the easiest to understand. They are the figures that represent the business reality. In practice, however, delivering high-level data can require a lot of work preparing the source data. So you must be prepared to aggregate, calculate, filter, and rationalize your data in order to produce a meaningful set of KPI values.
KPI Goal
It probably sounds a little obvious, but you need to have goals (or targets) for a KPI to function. This means getting your business users to specify exactly what the targets are; they can be simple values (such as budgetary data) or they can be calculated values such as a defined percentage increase or decrease. In any case, you need to store and/or calculate the goal data for each metric. In practice, this can be tougher than it sounds. The difficulties are often more operational than technical, because
Resolving these problems is outside the scope of this book. In the following examples, you will be using a set of budgetary data from the sample database that has been pre-crunched to map to the business data and that allows you to create meaningful KPIs.
KPI Status
Status is probably best thought of as a visual signal of how well you are doing. Frequently a status indicator is a symbol like a traffic light. Alternatively, it can be an image that indicates success or failure. It may even be a symbol that changes color to indicate good, average, or bad (or even under-achievement, on target, or over-achievement). As these examples imply, most status indicators will only display three values. You can extend this to five or even more values if you want. Just remember that you can easily lose the visual effect and clarity if you make it too complex for instant and intuitive understanding by the user.
What you need to retain is that the KPI status is essentially a flag indicator that has to be calculated. So you will need to know three things:
KPI Status Thresholds
Let’s take a simple example of a status flag in a KPI. Suppose that you have a target of 100. The business has said that any result under 80 is bad news, whereas anything over 125 will win a vacation. This means that you have the target and the thresholds. Once you have the value, you can then divide the result by the target. Under 80 percent is bad news, between 80 percent and 125 percent is expected, and anything over 125 percent is good news indeed. It is that easy. (In the real world, threshold calculations can be more complex than this, but I want to demonstrate the principle.) Of course, the thresholds can be absolute values rather than percentages; it will all depend on the business requirements. In the case that I just mentioned, the example only has three status flags; you can have as many status flags as you wish in your visualizations. However, I will never exceed three status indicators in the examples in this book as I firmly believe that using more than three status flags obscures the information rather than clarifies it.
Threshold Flags
If you are carrying out any status calculations in a database (relational or dimensional), I generally advise that you convert the status to a simple indicator, such as 1 for bad, 2 for OK, and 3 for good. This is because
This threshold value will then be used by SSRS to display a more visual and meaningful indicator.
You can, of course, perform calculations and apply business logic in SSRS, and there are many valid reasons for this approach, too. However, as I mentioned in Chapter 1, in this book I will always place business logic at a lower layer of the solution.
KPI Trends
KPIs can also give a visual indication of how the current results compare to past outcomes. Here again, exactly how you define the time-based comparison will depend on the business. For example, you may need to
KPI Trend Thresholds and Flags
The principles that I outlined for status thresholds and flags apply in a very similar way to trend thresholds and flags. You need the current value and the value you are comparing it with. Then you need to compare the two and indicate what the trend is on a simple scale. Interestingly, trend indicators in KPIs tend to show at least five values, and can show more; though, once again, clarity is essential, and a trend indicator that has nine different levels can be hard to read. So I will stick to a maximum of five in this book. Later I will show you how to use sparklines to display trends in a different type of detail.
As for the indicators, I feel that trend indicators are best calculated at the database layer and a simple flag (say 1 through 5) is sent to the report to be displayed in a more visual and intuitive way. This is what you will do in the next few examples.
A Simple KPI
I suspect that you have had enough theory and want to get down to delivering KPIs with SSRS. So let’s move on to a simple KPI that will let you see how the principles can be applied in practice. This KPI takes sales data for the selected year (up to the selected month) and does the following:
You will use a single stored procedure to gather the data and apply business logic. Status and trend indicators will be delivered in the same dataset as the business data and target metrics. At the risk of belaboring the point, I prefer to centralize the business logic and source data in a single place (a stored procedure in a reporting database) wherever possible. Of course, the reporting suite that you develop may take another approach, and there is nothing to prevent you carrying out the calculations and applying business logic in SSRS.
As this is the first example in this chapter (indeed in the whole book) I will be explaining it in more detail than the subsequent KPIs in this chapter. So this is an excellent place to start if you have a basic grasp of SQL Server Reporting Services and need to consolidate your knowledge before moving on to some of the more advanced techniques that you will find later in this chapter.
Figure 2-1 shows the KPI that you are trying to create. It displays sales and budget figures alongside the status and trend indicators for all the makes of car sold in 2014 up to the end of June.
Figure 2-1. A KPI showing sales by make for the first half of 2014
The Source Data
First, you need some data to work with. The following code snippet (available as Code.pr_CarSalesYearToDateKPISimple in the CarSales_Reports database) gives you the data that you need for June 2014:
DECLARE @ReportingYear INT = 2014
DECLARE @ReportingMonth TINYINT = 6
IF OBJECT_ID('Tempdb..#Tmp_KPIOutput') IS NOT NULL DROP TABLE Tempdb..#Tmp_KPIOutput
CREATE TABLE #Tmp_KPIOutput
(
ReportingYear INT
,Make NVARCHAR(80) COLLATE DATABASE_DEFAULT
,Sales NUMERIC(18,6)
,SalesBudget NUMERIC(18,6)
,PreviousYear NUMERIC(18,6)
,StatusIndicator SMALLINT
,TrendIndicator SMALLINT
)
INSERT INTO #Tmp_KPIOutput
(
ReportingYear
,Make
,Sales
)
SELECT ReportingYear
,Make
,SUM(SalePrice)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingYear
,Make
-- Previous Year Sales
;
WITH SalesPrev_CTE
AS
(
SELECT ReportingYear
,Make
,SUM(SalePrice) AS Sales
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear - 1
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingYear
,Make
)
UPDATE Tmp
SET Tmp.PreviousYear = CTE.Sales
FROM #Tmp_KPIOutput Tmp
INNER JOIN SalesPrev_CTE CTE
ON Tmp.Make = CTE.Make
;
WITH Budget_CTE
AS
(
SELECT SUM(BudgetValue) AS BudgetValue
,BudgetDetail
,Year
FROM Reference.Budget
WHERE BudgetElement = 'Sales'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
GROUP BY BudgetDetail
,Year
)
UPDATE Tmp
SET Tmp.SalesBudget = CTE.BudgetValue
FROM #Tmp_KPIOutput Tmp
INNER JOIN Budget_CTE CTE
ON Tmp.Make = CTE.BudgetDetail
AND Tmp.ReportingYear = CTE.Year
-- Internal Calculations
-- Year on Year Delta
-- TrendIndicator
UPDATE #Tmp_KPIOutput
SET TrendIndicator =
CASE
WHEN ((Sales - PreviousYear) / Sales) + 1 <= 0.7 THEN 1
WHEN ((Sales - PreviousYear) / Sales) + 1 > 1.3 THEN 5
WHEN ((Sales - PreviousYear) / Sales) + 1 > 0.7
AND ((Sales - PreviousYear) / PreviousYear) <= 0.9 THEN 2
WHEN ((Sales - PreviousYear) / Sales) + 1 > 1.1
AND ((Sales - PreviousYear) / PreviousYear) <= 1.3 THEN 4
WHEN ((Sales - PreviousYear) / Sales) + 1 > 0.9
AND ((Sales - PreviousYear) / PreviousYear) <= 1.1 THEN 3
ELSE 0
END
-- StatusIndicator
UPDATE #Tmp_KPIOutput
SET StatusIndicator =
CASE
WHEN ((Sales - SalesBudget) / Sales) + 1 <= 0.8 THEN 1
WHEN ((Sales - SalesBudget) / Sales) + 1 > 1.2 THEN 3
WHEN ((Sales - SalesBudget) / Sales) + 1 > 0.8
AND ((Sales - SalesBudget) / SalesBudget) <= 1.2 THEN 2
ELSE 0
END
-- Output
SELECT Make, Sales, SalesBudget, StatusIndicator, TrendIndicator
FROM #Tmp_KPIOutput
Running this snippet gives the output in Figure 2-2.
Figure 2-2. The data for a simple KPI
How the Code Works
This T-SQL snippet is quite simple, really; it does the following:
Note I have got into the habit of defining status indicators as 1-3 and trend indicators as 1-5. This is far from the only way of doing this, and many other approaches exist. You could (for status) use -1, 0, and 1, for instance. My only advice is to set a standard and stick to it across all of your KPIs.
Building the KPI
Now that you have your source data, you can get into the fun bit: building the KPI itself. As this is not only the first example in this chapter, but the first in the entire book, I will try to make the explanation reasonably comprehensive so that SSRS novices are not left floundering. In any case (and whatever your level of SSRS knowledge), remember that you can refer back to Figure 2-1 at any time when you are building this KPI if you need to check that you are doing things the right way.
Figure 2-3. The Select Indicator Type dialog
The dialog should look like Figure 2-4.
Figure 2-4. Setting indicator values
This is your first KPI. If you select 2014 and 6 (for the year and month parameters) it should look like Figure 2-1 (which you saw at the start of this example) and can be delivered as it is (on a tablet or smartphone if you want) or it can become part of a scorecard containing other KPIs.
Note You can replace steps 1-4 by using a copy of the report __BaseReport.rdl, which is in the sample SSRS project CarSalesReports on the Apress web site. This report contains the datasource, datasets, and parameters that you need for most of the examples in this book.
How It Works
Once the data has been defined, this KPI is largely a question of using the right datasource field in the right way. This means applying the numeric fields to certain text boxes, and then adding indicators to the remaining fields. Then you apply the fields that contain the precalculated status and trend flags to these indicators. As you saw in steps 18 and 21, you finish by telling SSRS which status indicator translates into which indicator shape and color.
The indicators for status and trend were defined in the code, so all SSRS had to do was to attribute the value returned in the data to the corresponding icon for that value. This is why the States measurement unit was set to numeric, so that it would recognize the value in the data. If your data contains a range of values for each status or trend element, then you need to set the corresponding start and end values, and not the same values for each icon as you did here.
I formatted this KPI in a slightly subdued way using grays for borders and text, as this lets the figures and indicators speak without shouting. You are, of course, free to choose the formatting style that you prefer. Explaining the formatting was more a quick revision of formatting techniques than any attempt at imposing a choice of presentation style.
A More Complex KPI Using Sparklines
Now that you have seen how to create a “starter” KPI, let’s move on to a more complex example. Specifically, I want now to replace the trend indicator with a sparkline so that you can see the evolution of sales over the last 12 months. Also, I want to make the status indicator into a data bar that indicates the extent of sales and uses color to indicate the status relative to the target.
Figure 2-5 show the kind of visualization that you are trying to create, for August 2013. It shows sales by color of vehicle (let’s imagine that the head of marketing requested this).
Figure 2-5. A complex KPI using sparklines and data bars
The Source Data
This KPI cannot really be delivered using a single stored procedure. I found it much easier to use separate stored procedures to feed the necessary data into the KPI. The four ‘sprocs are the following:
DECLARE @ReportingYear INT = 2013
DECLARE @ReportingMonth TINYINT = 8
-- Code.pr_WD_MonthlyColorSales
IF OBJECT_ID('tempdb..#Tmp_Output') IS NOT NULL DROP TABLE tempdb..#Tmp_Output
CREATE TABLE #Tmp_Output
(
NoSales INT NULL
,Color VARCHAR(50) COLLATE DATABASE_DEFAULT NULL
,SortDate DATE NULL
)
INSERT INTO #Tmp_Output
(
NoSales
,Color
,SortDate
)
SELECT NoSales, Color, SortDate
FROM (
SELECT COUNT(Color) AS NoSales
,Color
,CAST(CAST(YEAR(InvoiceDate) AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(MONTH(InvoiceDate) AS VARCHAR(2)),2) + '-01' AS DATE) AS SortDate
FROM Reports.CarSalesData
GROUP BY Color
,CAST(CAST(YEAR(InvoiceDate) AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(MONTH(InvoiceDate) AS VARCHAR(2)),2) + '-01' AS DATE)
,YEAR(InvoiceDate)
,MONTH(InvoiceDate)
) A
WHERE SortDate > DATEADD(mm,-12,CAST(CAST(@ReportingYear AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(@ReportingMonth AS VARCHAR(2)),2) + '-01' AS DATE))
AND SortDate <= CAST(CAST(@ReportingYear AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(@ReportingMonth AS VARCHAR(2)),2) + '-01' AS DATE)
SELECT * FROM #Tmp_Output ORDER BY SortDate
-- Code.pr_WD_ColorSalesCurrentMonth
CREATE TABLE #Tmp_KPIOutput
(
ReportingYear INT
,Color NVARCHAR(80) COLLATE DATABASE_DEFAULT
,NoSales NUMERIC(18,6)
,SalesBudget NUMERIC(18,6)
,StatusIndicator SMALLINT
)
INSERT INTO #Tmp_KPIOutput
(
ReportingYear
,NoSales
,Color
)
SELECT ReportingYear
,COUNT(Color) AS NoSales
,Color
FROM Reports.CarSalesData
WHERE YEAR(InvoiceDate) = @ReportingYear
AND MONTH(InvoiceDate) = @ReportingMonth
GROUP BY ReportingYear, Color
;
WITH Budget_CTE
AS
(
SELECT SUM(BudgetValue) AS BudgetValue
,BudgetDetail
,Year
FROM Reference.Budget
WHERE BudgetElement = 'Color'
AND Year = @ReportingYear
AND Month = @ReportingMonth
GROUP BY BudgetDetail
,Year
)
UPDATE Tmp
SET Tmp.SalesBudget = CTE.BudgetValue
FROM #Tmp_KPIOutput Tmp
INNER JOIN Budget_CTE CTE
ON Tmp.Color = CTE.BudgetDetail
AND Tmp.ReportingYear = CTE.Year
-- StatusIndicator
UPDATE #Tmp_KPIOutput
SET StatusIndicator =
CASE
WHEN ((NoSales - SalesBudget) / NoSales) + 1 <= 0.8 THEN 1
WHEN ((NoSales - SalesBudget) / NoSales) + 1 > 1.2 THEN 3
WHEN ((NoSales - SalesBudget) / NoSales) + 1 > 0.8
AND ((NoSales - SalesBudget) / SalesBudget) <= 1.2 THEN 2
ELSE 0
END
SELECT Color, NoSales, StatusIndicator FROM #Tmp_KPIOutput
-- Code.pr_WD_ColorSalesInLast12Months
IF OBJECT_ID('tempdb..#Tmp_Output') IS NOT NULL DROP TABLE tempdb..#Tmp_Output
CREATE TABLE #Tmp_Output
(
NoSales INT NULL
,Color VARCHAR(50) COLLATE DATABASE_DEFAULT NULL
)
INSERT INTO #Tmp_Output
(
NoSales
,Color
)
SELECT SUM(NoSales) AS NoSales, Color
FROM (
SELECT COUNT(Color) AS NoSales
,Color
,CAST(CAST(YEAR(InvoiceDate) AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(MONTH(InvoiceDate) AS VARCHAR(2)),2) + '-01' AS DATE) AS SortDate
FROM Reports.CarSalesData
GROUP BY Color
,CAST(CAST(YEAR(InvoiceDate) AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(MONTH(InvoiceDate) AS VARCHAR(2)),2) + '-01' AS DATE)
,YEAR(InvoiceDate)
,MONTH(InvoiceDate)
) A
WHERE SortDate >= DATEADD(mm,-12,CAST(CAST(@ReportingYear AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(@ReportingMonth AS VARCHAR(2)),2) + '-01' AS DATE))
AND SortDate <= CAST(CAST(@ReportingYear AS CHAR(4)) + '-' + RIGHT('0'
+ CAST(@ReportingMonth AS VARCHAR(2)),2) + '-01' AS DATE)
GROUP BY Color
SELECT * FROM #Tmp_Output
-- Code.pr_WD_ColorSalesCurrentMonthMAX
SELECT MAX(NoSales) + 1 AS MaxSales
FROM
(
SELECT COUNT(Color) AS NoSales
,Color
FROM Reports.CarSalesData
WHERE YEAR(InvoiceDate) = @ReportingYear
AND MONTH(InvoiceDate) = @ReportingMonth
GROUP BY Color
) A
These stored procedures return the four datasets shown in Figure 2-6 (the monthly color sales are not shown in full):
Figure 2-6. The four datasets needed to produce a complex KPI
How the Code Works
The four datasets to produce this KPI are the following:
It might be perfectly possible to coerce all the data you are using into a single dataset for this KPI. However, reality frequently dictates that it is easiest to use separate datasets and combine them in a single visualization. So I want here to give an idea of how such an approach can be used in practice.
Building the KPI
As this is the second KPI that you are creating in this chapter, I will describe the process a little more succinctly, especially where there are common features shared with the previous example. If you are relatively new to SSRS, you may want to build the previous example before beginning this one.
=Lookup(Fields!Color.Value, Fields!Color.Value, Fields!NoSales.Value, "ColorSalesCurrentMonth")
Section | Property | Value |
---|---|---|
Markers | Marker type (expression) | =IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 1, "Square",IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 2, "Circle","Diamond")) |
Marker size | 7pt | |
Marker color (expression) | =IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 1, "LightCoral",IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 2, "LightGreen","CornflowerBlue")) | |
Marker border color (expression) | =IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 1, "LightCoral",IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 2, "LightGreen","CornflowerBlue")) | |
Fill | Fill style | Gradient |
Color | =IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 1, "Red",IIF(Lookup(Fields!Color.Value, Fields!Color.Value, Fields!StatusIndicator.Value, "ColorSalesCurrentMonth") = 2, "LightGreen","CornflowerBlue")) | |
Secondary color | White | |
Gradient Style | Diagonal left |
That is your KPI completed. If you preview it, it should look like Figure 2-5.
How It Works
The first point concerning this KPI is that it is centered on the sparkline. This is why the dataset ColorSales is used for the table; it contains the sales per month per color, which are needed to display the sparklines for each color of car sold over the 12-month period. However, this dataset has to group the data by color for the sparkline to display correctly. Hence the need to group the detail row using the Color field. Indeed, if you try to add the sparkline without the grouping element you will get an error message.
With the sparkline in place you can add the figures for the current month’s sales and the sales over the last twelve months. However, there is a trick here, too. As the data for each of these metrics does not come from the dataset used by the table, you need to link the core dataset to the dataset used for these metrics. This is done using the Lookup function. This function is in four parts, and indicates the following:
Lookup fields can be used in many ways, and this KPI shows how you can use them to return values and status indicators. The status indicator is then used to set the color of the data bar for the month’s sales and the shape of the marker as well. This is not just a stylistic flourish. It is always recommended to use shape as well as color in status indicators to cater for any color-blind readers of your KPIs.
Note Using color and shape to indicate status is less traditional (and consequently less soporific) than using classic indicators. However, you may have to educate your users to their meaning-and use this approach across all your BI delivery.
Above all, this KPI has demonstrated that you can use multiple datasets for a single visualization. However, this can require some careful preparation of the source data so that the various datasets can be joined in the KPI.
Gauge-Based KPIs
The aim of a KPI is to show how well your business area is doing. In the previous example, you looked at a tabular KPI, where the figures delivered the information. However, figures are not the only way of presenting high-level information. SSRS includes a wealth of gauges to display data, and gauges can be adapted extremely easily to show values, target status, and trend.
If you are using gauges to present your KPIs, then it is probably worth seeing the advantages and drawbacks of each approach. Table 2-1 resumes the essential aspects of both tabular and gauge-based KPIs.
Table 2-1. Tabular vs. Gauge-Based KPIs
Tabular KPIs | Gauge-Based KPIs |
---|---|
Multiple elements per page | Intuitive comparison over multiple elements |
Can present lots of information | Can take longer to build |
Can be rolled up and drilled down like any table or matrix | Nearly always take up more space |
Easy to overload and obscure key data | Fewer gauges than rows in a table |
Harder to maintain | |
Greater “wow” factor |
Suppose you want to create a series of gauge-based KPIs that look like Figure 2-7.
Figure 2-7. Gauge-based KPIs
Each gauge shows the sales (the bar) and budget (the marker) for a make of car as well as the status (using the bar color). They also have a trend indicator in the center of the gauge. Once an initial gauge has been created, it is simply copied and pasted (and the country filter tweaked) to show the data for several different makes of car.
There are a couple of subtleties that I want to make clear before you start configuring these gauge-bases KPIs:
So core KPI elements are nonetheless present. They are, however, presented differently from a more traditional KPI.
The Source Data
These gauges only need a single source dataset. It is on the source database as Code.pr_YearlyCarSalesKPIGauges. Although the T-SQL is nearly identical to that used in the first example in this chapter, I will nonetheless reproduce it all here as there are enough differences to justify this. The code output and the gauges show the data for May 2013.
IF OBJECT_ID('Tempdb..#Tmp_KPIOutput') IS NOT NULL DROP TABLE Tempdb..#Tmp_KPIOutput
CREATE TABLE #Tmp_KPIOutput
(
ReportingYear INT
,Make NVARCHAR(80) COLLATE DATABASE_DEFAULT
,Sales NUMERIC(18,6)
,SalesBudget NUMERIC(18,6)
,PreviousYear NUMERIC(18,6)
,StatusIndicator SMALLINT
,TrendIndicator SMALLINT
,ScaleMax INT
)
INSERT INTO #Tmp_KPIOutput
(
ReportingYear
,Make
,Sales
)
SELECT ReportingYear
,Make
,SUM(SalePrice)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingYear
,Make
-- Previous Year Sales
;
WITH SalesPrev_CTE
AS
(
SELECT ReportingYear
,Make
,SUM(SalePrice) AS Sales
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear - 1
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingYear
,Make
)
UPDATE Tmp
SET Tmp.PreviousYear = CTE.Sales
FROM #Tmp_KPIOutput Tmp
INNER JOIN SalesPrev_CTE CTE
ON Tmp.Make = CTE.Make
;
WITH Budget_CTE
AS
(
SELECT SUM(BudgetValue) AS BudgetValue
,BudgetDetail
,Year
FROM Reference.Budget
WHERE BudgetElement = 'Sales'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
GROUP BY BudgetDetail
,Year
)
UPDATE Tmp
SET Tmp.SalesBudget = CTE.BudgetValue
FROM #Tmp_KPIOutput Tmp
INNER JOIN Budget_CTE CTE
ON Tmp.Make = CTE.BudgetDetail
AND Tmp.ReportingYear = CTE.Year
-- Scale maximum
UPDATE #Tmp_KPIOutput
SET ScaleMax =
CASE
WHEN Sales >= SalesBudget
THEN (SELECT CarSales_Reports.Code.fn_ScaleDecile (Sales))
ELSE (SELECT CarSales_Reports.Code.fn_ScaleDecile (SalesBudget))
END
-- Internal Calculations
-- Year on Year Delta
-- TrendIndicator
UPDATE #Tmp_KPIOutput
SET TrendIndicator = CASE
WHEN ((Sales - PreviousYear) / Sales) + 1 <= 0.7 THEN 1
WHEN ((Sales - PreviousYear) / Sales) + 1 > 1.3 THEN 5
WHEN ((Sales - PreviousYear) / Sales) + 1 > 0.7
AND ((Sales - PreviousYear) / PreviousYear) <= 0.9 THEN 2
WHEN ((Sales - PreviousYear) / Sales) + 1 > 1.1
AND ((Sales - PreviousYear) / PreviousYear) <= 1.3 THEN 4
WHEN ((Sales - PreviousYear) / Sales) + 1 > 0.9
AND ((Sales - PreviousYear) / PreviousYear) <= 1.1 THEN 3
ELSE 0
END
-- StatusIndicator
UPDATE #Tmp_KPIOutput
SET StatusIndicator = CASE
WHEN ((Sales - SalesBudget) / Sales) + 1 <= 0.8 THEN 1
WHEN ((Sales - SalesBudget) / Sales) + 1 > 1.2 THEN 3
WHEN ((Sales - SalesBudget) / Sales) + 1 > 0.8
AND ((Sales - SalesBudget) / SalesBudget) <= 1.2 THEN 2
ELSE 0
END
-- Output
SELECT Make, Sales, SalesBudget, StatusIndicator, TrendIndicator, ScaleMax
FROM #Tmp_KPIOutput
The T-SQL used in this example returns the data shown in Figure 2-8.
Figure 2-8. The data for a collection of KPI gauges
How the Code Works
As I said previously, this code is largely similar to the first code block at the start of this chapter. It uses a temporary table to hold the sales by make for a specific period, adds the budget figures and previous year’s figures, and then calculates the status and trend calculations.
This addition to the code uses a tiny “helper” function that you can find in the CarSales_Reports database. It rounds up the maximum value of the gauge scale to the nearest decile, which makes the scale easier and more pleasant to read.
Now that you have the data you need, you can start by building the first gauge that will be the model for all the gauges in this composite KPI. If this example seems a little complex, feel free to look ahead at Chapter 3 first, as it explains more of the techniques used when creating gauge-based KPIs.
Section | Property | Value |
---|---|---|
General | Minimum | 0 |
Maximum | Sum([ScaleMax]) | |
Layout | Scale Radius (percent) | 39 |
Start angle (degree) | 20 | |
Sweep angle (degree) | 320 | |
Scale bar width (percent) | 0 | |
Labels | Placement (relative to scale) | Cross |
Distance from scale (percent) | 0 | |
Label Font | Font | Arial |
Size | 11 point | |
Color | Dark Blue | |
Bold | Checked | |
Number | Category | Number |
Use 1000 separator (,) | Checked | |
Decimal places | 2 | |
Major Tick Marks | Check Hide major tick marks | |
Minor Tick Marks | Uncheck Hide minor tick marks | |
Minor tick mark shape | Diamond | |
Width (percent) | 1 | |
Minor tick mark placement | Cross | |
Length (percent) | 2 |
Section | Property | Value |
---|---|---|
Pointer Options | Value | [Sum(Sales)] |
Pointer type | Bar | |
Placement (relative to scale) | Inside | |
Distance from scale (percent) | 15 | |
Width (percent) | 12 | |
Pointer Fill | Fill style | Gradient |
Color | =Switch( Sum(Fields!StatusIndicator.Value) = 1, "Red" ,Sum(Fields!StatusIndicator.Value) = 2, "DarkGray" ,Sum(Fields!StatusIndicator.Value) = 3, "Blue" ) | |
Secondary color | White | |
Gradient style | Vertical center | |
Cap Options | Hide pointer cap | Checked |
Shadow | Shadow Offset | 1 Point |
Shadow intensity | 25 |
Section | Property | Value |
---|---|---|
Pointer Options | Value | [Sum(SalesBudget)] |
Pointer type | Marker | |
Marker style | Diamond | |
Placement (relative to scale) | Cross | |
Distance from scale (percent) | 15 | |
Width (percent) | 15 | |
Length | 25 | |
Pointer Fill | Fill style | Gradient |
Color | Black | |
Secondary color | Silver | |
Gradient style | Left right | |
Shadow | Shadow Offset | 1 point |
Shadow intensity | 25 |
Section | Property | Value |
---|---|---|
General | X position (percent) | 40 |
Y position (percent) | 36 | |
Width (percent) | 25 | |
Height (percent) | 25 | |
Values and states | Value | [Sum(TrendIndicator)] |
States Measurement Unit | Numeric | |
Icon 1 | Color: Black, Start and End: 1 | |
Icon 2 | Color: Black, Start and End: 2 | |
Icon 3 | Color: Black, Start and End: 3 | |
Icon 4 | Color: Black, Start and End: 4 | |
Icon 5 | Color: Black, Start and End: 5 |
Section | Property | Value |
---|---|---|
Back Fill | Color | White |
Frame | Style | Edged |
Shape | Circular 1 | |
Frame Fill | Color | WhiteSmoke |
Your gauge is now complete, and should resemble the top left gauge in Figure 2-7. It is now easy to copy the gauge as many times as there are makes of cars in the dataset and set the filter for each gauge to a different make of car to produce a visualization that covers the entire range of products sold. You can add text boxes containing the names of the car makes as a final flourish.
How It Works
This KPI uses two gauge pointers to display the sales figure and the corresponding budget. The visual trick is to use two different pointer types so that the marker (the diamond) indicates the target value. The status is indicated by the color of the bar pointer using the Switch code shown in step 10. The trend indicator is just another trend indicator chosen from those available in SSRS-but it is added as a child indicator of the main gauge.
In this example, the maximum value for each scale varies according to the make displayed in the gauge. This means that the gauges are not standardized on a single scale-and consequently that the data in the gauges is not strictly comparable. In practice, this may not be what your users are expecting, and so you may have to indicate this to them in some way.
Hints and Tips
Here are some tips and tricks to keep in mind.
The previous sections showed you what a KPI is and what it can deliver. Sometimes, however, you may need to produce more complex KPIs, where several pieces of information are presented to the user as a coherent whole. This is where a well-constructed text-based KPI can be truly useful. Through the use of font size, color, and position, you can present several essential pieces of data to a user, as well as provide an idea of how the information is to be prioritized.
A Simple Text-Based KPI
To show you that KPIs that do not have indicators in them can be just as powerful as “classic” KPIs, let’s look at a first example of a text-based KPI. Suppose that you want to display the value for a collection of key enterprise metrics. For each one you want to show the percentage attainment of the target-which will display the metric’s status through changing the color of the percentage. You then want to display the trend as a sparkline. Here is how it can be done.
Figure 2-9 shows a text-based KPI. This KPI relies on color to convey the status of certain metrics; this is less obvious in a black and white book than on a color screen, so you may need to preview the report in the sample application to get the full effect.
Figure 2-9. A simple text-based KPI
The Source Data
This particular KPI requires six datasets: one for each sparkline (though they could, admittedly, be refactored as a single dataset) and another dataset for the grouped metrics and targets for each cost area. This dataset will then be filtered to use the appropriate figures for each component of the visualization. The stored procedures in the sample database that produce the data are the following (for May 2015):
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 5
-- Code.pr_WD_KeyFiguresForMonthLaborSparkline
SELECT
ReportingMonth
,SUM(LaborCost) AS MetricValue
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingMonth
-- Code.pr_WD_KeyFiguresForMonthDeliverySparkline
SELECT
ReportingMonth
,SUM(DeliveryCharge) AS MetricValue
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingMonth
-- Code.pr_WD_KeyFiguresForMonthDiscountSparkline
SELECT
ReportingMonth
,SUM(TotalDiscount) AS MetricValue
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingMonth
-- Code.pr_WD_KeyFiguresForMonthPartsSparkline
SELECT
ReportingMonth
,SUM(SpareParts) AS MetricValue
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingMonth
-- Code.pr_WD_KeyFiguresForMonthSalesSparkline
SELECT
ReportingMonth
,SUM(SalePrice) AS MetricValue
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY ReportingMonth
-- Code.pr_WD_KeyFiguresForMonth
IF OBJECT_ID('tempdb..#Tmp_Output') IS NOT NULL DROP TABLE tempdb..#Tmp_Output
CREATE TABLE #Tmp_Output
(
Sales NUMERIC(18,2)
,SalesTarget NUMERIC(18,2)
,Parts NUMERIC(18,2)
,PartsTarget NUMERIC(18,2)
,Labor NUMERIC(18,2)
,LaborTarget NUMERIC(18,2)
,Delivery NUMERIC(18,2)
,DeliveryTarget NUMERIC(18,2)
,Discount NUMERIC(18,2)
,DiscountTarget NUMERIC(18,2)
,SalesStatus TINYINT
,PartsStatus TINYINT
,LaborStatus TINYINT
,DeliveryStatus TINYINT
,DiscountStatus TINYINT
)
-- Sales
INSERT INTO #Tmp_Output (Sales)
SELECT SUM(SalePrice)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
UPDATE #Tmp_Output
SET SalesTarget =
(
SELECT SUM(BudgetValue)
FROM Reference.Budget
WHERE BudgetElement = 'Sales'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
)
-- Parts
UPDATE #Tmp_Output
SET Parts =
(
SELECT SUM(SpareParts)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
)
UPDATE #Tmp_Output
SET PartsTarget =
(
SELECT SUM(BudgetValue)
FROM Reference.Budget
WHERE BudgetElement = 'Costs'
AND BudgetDetail = 'Parts'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
)
-- Labor
UPDATE #Tmp_Output
SET Labor =
(
SELECT SUM(LaborCost)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
)
UPDATE #Tmp_Output
SET LaborTarget =
(
SELECT SUM(BudgetValue)
FROM Reference.Budget
WHERE BudgetElement = 'Costs'
AND BudgetDetail = 'Labor'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
)
-- Delivery Charge
UPDATE #Tmp_Output
SET Delivery =
(
SELECT SUM(DeliveryCharge)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
)
UPDATE #Tmp_Output
SET DeliveryTarget =
(
SELECT SUM(BudgetValue)
FROM Reference.Budget
WHERE BudgetElement = 'Costs'
AND BudgetDetail = 'DeliveryCharge'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
)
-- Discount
UPDATE #Tmp_Output
SET Discount =
(
SELECT SUM(TotalDiscount)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
)
UPDATE #Tmp_Output
SET DiscountTarget =
(
SELECT SUM(BudgetValue)
FROM Reference.Budget
WHERE BudgetElement = 'Costs'
AND BudgetDetail = 'TotalDiscount'
AND Year = @ReportingYear
AND Month <= @ReportingMonth
)
-- StatusIndicators
UPDATE #Tmp_Output
SET SalesStatus =
CASE
WHEN ((Sales - SalesTarget) / Sales) + 1 <= 0.8 THEN 1
WHEN ((Sales - SalesTarget) / Sales) + 1 > 1.2 THEN 3
WHEN ((Sales - SalesTarget) / Sales) + 1 > 0.8
AND ((Sales - SalesTarget) / SalesTarget) <= 1.2 THEN 2
ELSE 0
END
UPDATE #Tmp_Output
SET PartsStatus =
CASE
WHEN ((Parts - PartsTarget) / Parts) + 1 > 1.2 THEN 1
WHEN ((Parts - PartsTarget) / Parts) + 1 <= 0.8 THEN 3
WHEN ((Parts - PartsTarget) / Parts) + 1 > 0.8
AND ((Parts - PartsTarget) / PartsTarget) <= 1.2 THEN 2
ELSE 0
END
UPDATE #Tmp_Output
SET LaborStatus =
CASE
WHEN ((Labor - LaborTarget) / Labor) + 1 > 1.2 THEN 1
WHEN ((Labor - LaborTarget) / Labor) + 1 <= 0.8 THEN 3
WHEN ((Labor - LaborTarget) / Labor) + 1 > 0.8
AND ((Labor - LaborTarget) / LaborTarget) <= 1.2 THEN 2
ELSE 0
END
UPDATE #Tmp_Output
SET DeliveryStatus =
CASE
WHEN ((Delivery - DeliveryTarget) / Delivery) + 1 > 1.2 THEN 1
WHEN ((Delivery - DeliveryTarget) / Delivery) + 1 <= 0.8 THEN 3
WHEN ((Delivery - DeliveryTarget) / Delivery) + 1 > 0.8
AND ((Delivery - DeliveryTarget) / DeliveryTarget) <= 1.2 THEN 2
ELSE 0
END
UPDATE #Tmp_Output
SET DiscountStatus =
CASE
WHEN ((Discount - DiscountTarget) / Discount) + 1 <= 0.8 THEN 1
WHEN ((Discount - DiscountTarget) / Discount) + 1 > 1.2 THEN 3
WHEN ((Discount - DiscountTarget) / Discount) + 1 > 0.8
AND ((Discount - DiscountTarget) / DiscountTarget) <= 1.2 THEN 2
ELSE 0
END
-- Output
SELECT * FROM #Tmp_Output
The six outputs are shown in Figure 2-10.
Figure 2-10. The data used to create a text-based KPI
How the Code Works
These pieces of T-SQL are quite a bit more complex than most of those that you have seen so far. This reflects the fact that several steps are necessary to collate and extract all the necessary data. This is what happens:
Building a Text-Based KPI
Although this KPI is designed to be read as the sum of its parts, you only really need to learn how to make one of the text blocks. Once an initial KPI is finished, it can be duplicated and then the dataset filtered and any expressions tweaked to display the relevant data.
Here is how to create a text-based KPI. You will build the Sales KPI as the initial model.
=Switch(
Sum(Fields!SalesStatus.Value, "KeyFiguresForMonth")=1,"Red",
Sum(Fields!SalesStatus.Value, "KeyFiguresForMonth")=2,"Gray",
Sum(Fields!SalesStatus.Value, "KeyFiguresForMonth")=3,"White"
)
You now have your initial KPI. You can now make four copies of the KPI and set the dataset for each to one of the remaining datasets: Discount Sparkline, Parts Sparkline, Labor Sparkline, and Delivery Sparkline. As there are no filters applied, you will have to tweak the expressions that are used to show the key metric, the percentage, and the status from the KeyFiguresForMonth dataset. In practice, this means replacing the field in step 15 with the field required by the specific KPI. So, for example, in the table that displays Parts KPIs you need to change SalesStatus.Value to PartsStatus.Value. Similar changes are required in steps 12 and 14.
How It Works
This KPI shows that you can use multiple datasets to populate KPIs. However, it can necessitate some forethought and a little work. At its heart, this KPI is similar to the KPI using a sparkline that was the second KPI example in this chapter. However, each table only shows one set of data; there is no data grouping. The metrics and status indicator are specific to each KPI, and rather than have multiple datasets, they are returned in a single dataset that contains a single row where it is the column that defines the metric. Consequently, when writing the functions to return a value from a dataset that is not linked to the table, you have to specify the column that will be used, as well as the dataset.
Step 10 sets the sparkline properties. You need to set the Category Groups so that the month data will appear on the horizontal axis.
The threshold indicator in this example is simply the text color. While not difficult, this can be effective-if it is not overused. This is why I left the sales figure in black. More generally, in this KPI I have chosen a slightly “harder” color scheme, trading grey for black and primary colors for pastel shades.
The table structure is essentially part of the presentation. However, I think that this part of the KPI development should not be overlooked, as it is the visual quality that will make your KPI memorable. So be prepared to spend some time tweaking row heights (and even the font sizes for blank cells) as well as column widths to add spacing to your table, both outside the central data and also inside the table.
A Complex Text-Based KPI
There are occasions when merely having the value, target, status, and trend for a metric are not enough. You may need to display the value for previous periods, such as the preceding month or the same month last year. All of these figures need to show the percentage difference and a status indicator. It is when faced with this level of complexity that a text-based KPI can be really effective. To give you an idea of what you are looking for, take a glance at Figure 2-11.
Figure 2-11. A complex text-based KPI
The Source Data
This KPI needs two datasets. They are Code.pr_ScorecardTimeCountryAndMake and Code.pr_ScorecardTimeCountryAndMake12MonthSales in the CarSales_Reports database. The code for both is as follows:
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
-- Code.pr_ScorecardTimeCountryAndMake
IF OBJECT_ID('Tempdb..#Tmp_KPIOutput') IS NOT NULL DROP TABLE Tempdb..#Tmp_KPIOutput
CREATE TABLE #Tmp_KPIOutput
(
Country NVARCHAR(100) COLLATE DATABASE_DEFAULT
,Sales NUMERIC(18,6)
,SalesTarget NUMERIC(18,6)
,PrevMonthSales NUMERIC(18,6)
,PrevYearSales NUMERIC(18,6)
,Delta NUMERIC(18,6)
,PrevMonthDelta NUMERIC(18,6)
,PrevYearDelta NUMERIC(18,6)
,DeltaPercent NUMERIC(18,6)
,PrevMonthDeltaPercent NUMERIC(18,6)
,PrevYearDeltaPercent NUMERIC(18,6)
,SalesStatus TINYINT
,PrevMonthStatus TINYINT
,PrevYearStatus TINYINT
)
-- Sales
INSERT INTO #Tmp_KPIOutput (Country, Sales)
SELECT
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END
,SUM(SalePrice)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth = @ReportingMonth
GROUP BY
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END
-- Targets
-- Previous Months Sales
;
WITH PrevMonthSales_CTE
AS
(
SELECT
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END AS Country
,SUM(SalePrice) AS MetricValue
FROM Reports.CarSalesData
WHERE InvoiceDate BETWEEN DATEADD(mm, -1, DATEADD(dd, 1,
EOMONTH(CONVERT(DATE, CAST(@ReportingYear AS CHAR(4))
+ RIGHT('0' + CAST(@ReportingMonth AS VARCHAR(2)),2) + '01', 112), -1)))
AND DATEADD(mm, -1, EOMONTH(CONVERT(DATE,
CAST(@ReportingYear AS CHAR(4)) + RIGHT('0'
+ CAST(@ReportingMonth AS VARCHAR(2)),2) + '01', 112)))
GROUP BY
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END
)
UPDATE Tmp
SET Tmp.PrevMonthSales = CTE.MetricValue
FROM #Tmp_KPIOutput Tmp
INNER JOIN PrevMonthSales_CTE CTE
ON Tmp.Country = CTE.Country
-- PrevYearSales
;
WITH PrevYearSales_CTE
AS
(SELECT
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END AS Country
,SUM(SalePrice) AS MetricValue
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth = @ReportingMonth
GROUP BY
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END
)
UPDATE Tmp
SET Tmp.PrevYearSales = CTE.MetricValue
FROM #Tmp_KPIOutput Tmp
INNER JOIN PrevYearSales_CTE CTE
ON Tmp.Country = CTE.Country
-- Targets
;
WITH Targets_CTE
AS
(
SELECT
CASE
WHEN BudgetDetail = 'United Kingdom' THEN 'United Kingdom'
WHEN BudgetDetail = 'France' THEN 'France'
WHEN BudgetDetail = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END AS Country
,SUM(BudgetValue) AS BudgetValue
FROM Reference.Budget
WHERE BudgetElement = 'Countries'
AND Year = @ReportingYear
AND Month = @ReportingMonth
GROUP BY
CASE
WHEN BudgetDetail = 'United Kingdom' THEN 'United Kingdom'
WHEN BudgetDetail = 'France' THEN 'France'
WHEN BudgetDetail = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END
)
UPDATE Tmp
SET Tmp.SalesTarget = CTE.BudgetValue
FROM #Tmp_KPIOutput Tmp
INNER JOIN Targets_CTE CTE
ON Tmp.Country = CTE.Country
-- Calculations
UPDATE #Tmp_KPIOutput
SET Delta = SalesTarget - Sales
,PrevMonthDelta = (Sales - PrevMonthSales)
,PrevYearDelta = (Sales - PrevYearSales)
,DeltaPercent = (SalesTarget - Sales) / Sales
,PrevMonthDeltaPercent = (Sales - PrevMonthSales) / Sales
,PrevYearDeltaPercent = (Sales - PrevYearSales) / Sales
-- Status
UPDATE #Tmp_KPIOutput
SET SalesStatus =
CASE
WHEN ((Sales - SalesTarget) / Sales) + 1 <= 0.8 THEN 1
WHEN ((Sales - SalesTarget) / Sales) + 1 > 1.2 THEN 3
WHEN ((Sales - SalesTarget) / Sales) + 1 > 0.8
AND ((Sales - SalesTarget) / SalesTarget) <= 1.2 THEN 2
ELSE 0
END
,PrevMonthStatus =
CASE
WHEN PrevMonthDeltaPercent <= 0.9 THEN 1
WHEN PrevMonthDeltaPercent > 1.1 THEN 3
WHEN PrevMonthDeltaPercent > 0.9
AND PrevMonthDeltaPercent <= 1.1 THEN 2
ELSE 0
END
,PrevYearStatus =
CASE
WHEN PrevYearDeltaPercent <= 0.7 THEN 1
WHEN PrevYearDeltaPercent > 1.4 THEN 3
WHEN PrevYearDeltaPercent > 0.7
AND PrevYearDeltaPercent <= 1.4 THEN 2
ELSE 0
END
-- Output
SELECT * FROM #Tmp_KPIOutput
-- Code.pr_ScorecardTimeCountryAndMake12MonthSales
SELECT
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END AS Country
,SUM(SalePrice) AS Sales
,DATENAME(mm,InvoiceDate) + '-' + CAST(YEAR(InvoiceDate) AS CHAR(4)) AS MonthYear
,CAST(YEAR(InvoiceDate) AS CHAR(4)) + RIGHT('0' + CAST(MONTH(InvoiceDate) AS VARCHAR(2)),2) AS SortOrder
FROM Reports.CarSalesData
WHERE InvoiceDate BETWEEN '2012-08-01' AND '2013-08-01'
GROUP BY
CASE
WHEN CountryName = 'United Kingdom' THEN 'United Kingdom'
WHEN CountryName = 'France' THEN 'France'
WHEN CountryName = 'Switzerland' THEN 'Switzerland'
ELSE 'Other'
END
,DATENAME(mm,InvoiceDate) + '-' + CAST(YEAR(InvoiceDate) AS CHAR(4))
,CAST(YEAR(InvoiceDate) AS CHAR(4)) + RIGHT('0' + CAST(MONTH(InvoiceDate) AS VARCHAR(2)),2)
ORDER BY SortOrder
The data returned from these two scripts (although only partially for the second) looks like the output shown in Figure 2-12. This data is for June 2015 in the sample data.
Figure 2-12. The data needed to create a complex text-based KPI
How the Code Works
As was the case for the second example in this chapter (the KPI with a sparkline), it is easier to separate out the data for the sparkline from the data used for all the other metrics in the KPI. This means two queries:
While a little long, these procedures are not complicated. If anything, they emphasize that BI depends on the source data and that if you want a visualization to work, you must prepare the source data cogently.
Building a Complex Text-Based KPI
Once again, with the data defined you can start building the KPI itself. Here is how.
Section | Property | Value |
---|---|---|
General | Auto fit all gauges in panel | Unchecked |
X position (percent) | 4 | |
Y position (percent) | -15 | |
Width (percent) | 92 | |
Height (percent) | 92 | |
Values and States | Value | [Sum(SalesStatus)] |
States Measurement unit | Numeric | |
Icon 1 | Start: 1, End: 1 | |
Icon 2 | Start: 2, End: 2 | |
Icon 3 | Start: 3, End: 3 |
Section | Property | Value |
---|---|---|
General | Auto fit all gauges in panel | Unchecked |
X position (percent) | 10 | |
Y position (percent) | 20 | |
Width (percent) | 70 | |
Height (percent) | 70 | |
Values and States | Value | [Sum(PrevMonthStatus)] |
States Measurement unit | Numeric | |
Icon 1 | Start: 1, End: 1 | |
Icon 2 | Start: 2, End: 2 | |
Icon 3 | Start: 3, End: 3 |
Cell | Value |
---|---|
Row 1, Column 2 | [Sales] |
Row 1, Column 4 | [PrevMonthDeltaPercent] |
Row 1, Column 6 | [PrevYearDeltaPercent] |
Row 2, Column 2 | [SalesTarget] |
Row 2, Column 4 | [PrevMonthSales] |
Row 2, Column 6 | [PrevYearSales] |
=Switch(
Fields!PrevMonthStatus.Value=1,"Red",
Fields!PrevMonthStatus.Value=2,"Green",
Fields!PrevMonthStatus.Value=3,"Blue"
)
Section | Property | Value |
---|---|---|
General | Title text, as a function | =Fields!Country.Value |
Title position | Bottom center | |
Font | Font | Arial Black |
Size | 16pt | |
Color | Black |
Section | Property | Value |
---|---|---|
Labels | Disable auto-fit | Selected |
Label rotation angle (degrees) | 21 | |
Label Font | Font | Arial |
Size | 6pt | |
Color | Gray | |
Major Tick Marks | Hide major tick marks | Checked |
Minor Tick Marks | Hide minor tick marks | Checked |
Line | Line color | Dim Gray |
Section | Property | Value |
---|---|---|
Axis Options | Always include zero | Checked |
Labels | Disable auto-fit | Selected |
Label rotation angle (degrees) | 0 | |
Label Font | Font | Arial |
Size | 6pt | |
Color | Gray | |
Number | Category | Number |
Decimal places | 0 | |
Use 1000 separator (,) | Checked | |
Major Tick Marks | Hide major tick marks | Unchecked |
Position | Outside | |
Length | 1 | |
Line color | Gray | |
Minor Tick Marks | Hide minor tick marks | Checked |
Line | Line color | Dim Gray |
Well, that took some time and needed close attention to detail. The result is, however, worth the effort; I am sure that you will agree. This visualization displays not only the data and status for the current and previous month’s sales (you will need to choose June 2015 to get the same result as Figure 2-11), but also the sales and status for the same month in the previous year. The trend is a chart, rather than a sparkline, that clearly shows the direction of travel.
You can now duplicate the two elements, table and chart, and set the filters on both to another country, and thus create a complex visualization for data from multiple countries.
How It Works
Once again, the secret to this KPI is in the source data. If the two datasets are accurate, then building the KPI is essentially simple. As the chart is a separate object, the decision to create one stored procedure for the chart data and one for all the data in the table seemed self-evident.
The table is, once again, an “Excel-like” structure where the art is in creating the right structure and merging cells where appropriate. As SSRS does not let you merge cells vertically, you group a parent group to “merge” vertically in the leftmost column. Then you place the appropriate fields and format them, and add three indicators that you customize. As you can see, indicators can be copied just like most other objects, and it can save a lot of time to get the first one right-and then copy and paste it as a basis for other indicators.
To make a change from nested IIF statements, you use a Switch() function in step 17. If this is new to you, consider it a bit like a T-SQL CASE statement.
When building complex scorecards like this, you must be careful to apply the relevant filters to all the objects that make up a single visualization. This means applying the filter to the chart and to the table for this example.
One interesting thing about the chart is setting the custom inner plot position. Altering this allows you to override SSRS’s sense of proportion, and to use the available space more efficiently. Another space-saving trick is to set the horizontal axis titles at an angle.
In this visualization, I chose slightly bolder colors for the indicators (and the status given by text colors where appropriate). You may prefer to use more pastel shades or alter the color scheme completely.
Conclusion
This chapter introduced you to Key Performance Indicators. As you saw, there are many types of KPI that you can build in SSRS. These range from the simple to the complex and from the classic to the adventurous. KPIs can be based on text, image indicators, or gauges-or a combination of some or all of these types. Then you can combine KPIs to make scorecards or dashboards, as you will see later in this book.