Charts for Business Intelligence
Charts may be a staple of spreadsheets and PowerPoint presentations, but that does not mean that you cannot use them in dashboards (and in BI generally) to great effect. The main thing to remember if you want your dashboards to be striking and memorable is that a BI chart needs to convey key information succinctly. So you may well find that you end up avoiding the more traditional (not to say overused) chart types in your BI reports in favor of less tired ways of visualizing your data.
Charts have been around in SSRS for nearly 15 years, so I won’t provide a chart primer here. What I want to do is to outline some of the ways in which a well-designed chart can be an intrinsic part of an effective BI dashboard. Charts are, however, infinitely varied and a matter of taste. So I will not be prescribing which chart type to use in which scenarios; I can only advise you to consult some of the excellent books on this subject. Nonetheless, one fundamental point to bear in mind when creating your reports is that charts used for BI will nearly always benefit considerably from being simplified rather than overloaded. It is nearly always better to create several charts rather than to create a single chart containing many data series.
What I will do is show you some of the ways in which charts can be used (almost) as KPIs, and how using some of the rarer chart types can add real visual effect to a dashboard. This chapter aims to be largely a collection of tips and tricks which I hope you will find useful. I am not attempting to provide complete coverage of how to use charts in Reporting Services for business intelligence because there are simply too many ways that charts can be used to convey information succinctly and coherently in dashboards and other BI output to be handled in a short book. However, this chapter should encourage you to experiment with charts for business intelligence and to deliver some really cool visualizations to your users.
Some Chart Presentation Ideas
All the charts that you will be creating in this chapter are designed to be used in a business intelligence context. For me, this means that they must be designed for clarity and simplicity, so that the key message and metrics are conveyed succinctly and transparently.
We all have our ideas about what constitutes taste and effective delivery, especially where design and color are concerned. Consequently, I expect many people to disagree with my ideas, and I can only encourage you to do your own thing. In this chapter, however, you will generally be adopting the following design principles for all of the charts that you will develop:
The logic behind this design is to make text and boundaries less obtrusive so that the data can stand out. Equally, I will be using mostly lighter colors except in a few cases. However, this is my approach, and you are, of course, free to apply your own design ideas and strictures.
As was the case in previous chapters, I will be fairly expansive when explaining the first example, and a little more concise for the remaining examples in this chapter.
Charts to Compare Metrics with Targets
Charts can be ideal when you need to show how a series of metrics compares to target values. They have the advantage of being largely intuitive, as can be seen in the number of charts in BI reports and dashboards the world over.
Admittedly, comparing targets to results is largely what most KPIs do. Where charts are concerned, though, I will not be adding any form of trend indicator. As this would really be necessary for a true KPI, I do not consider target-metric charts as KPIs. This does not in any way, however, mitigate their usefulness and value in a BI context.
Basic Target Comparison Charts
In its most simple form, a target comparison chart could have only two series of data:
The only trick here is to differentiate the two series sufficiently. Mixing a standard chart type that represents one series with custom markers for another series can be an efficient way of doing this, as Figure 4-1 shows for June 2015 in the sample data.
Figure 4-1. Comparing a metric to a target
The first thing that you need is your business data. In this example, let’s suppose that all you want is a list of the principal makes of car, plus a catch-all category for minor makes, along with the budget figures for a selected year and month. The following code snippet (available as Code.pr_MonthlyCarSalesWithTarget in the CarSales_Report database) provides the data:
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
IF OBJECT_ID('Tempdb..#Tmp_Output') IS NOT NULL DROP TABLE Tempdb..#Tmp_Output
CREATE TABLE #Tmp_Output
(
Make NVARCHAR(80) COLLATE DATABASE_DEFAULT
,Sales NUMERIC(18,6)
,SalesBudget NUMERIC(18,6)
)
INSERT INTO #Tmp_Output
(
Make
,Sales
)
SELECT CASE
WHEN Make IN ('Aston Martin','Bentley','Jaguar','Rolls Royce') THEN Make
ELSE 'Other'
END AS Make
,SUM(SalePrice)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY CASE
WHEN Make IN ('Aston Martin','Bentley','Jaguar','Rolls Royce') THEN Make
ELSE 'Other'
END
;
WITH Budget_CTE
AS
(
SELECT SUM(BudgetValue) AS BudgetValue
,BudgetDetail
FROM Reference.Budget
WHERE BudgetElement = 'Sales'
AND Year = @ReportingYear
AND Month = @ReportingMonth
GROUP BY BudgetDetail
)
UPDATE Tmp
SET Tmp.SalesBudget = CTE.BudgetValue
FROM #Tmp_Output Tmp
INNER JOIN Budget_CTE CTE
ON Tmp.Make = CTE.BudgetDetail
-- Output
SELECT *
FROM #Tmp_Output
Running this snippet gives the output shown in Figure 4-2.
Figure 4-2. The data used to compare vehicle sales to sales target for a specific month
How the Code Works
This short code snippet calculates the sales for the year to date (as defined by the selected month) and places the result into a temporary table. It then adds the budgetary figure for the same period to the table. This data only looks at four major makes of car plus an “Other” category.
Time, then, to build the chart. As this is the first chart you’re creating, I will go into some detail of the base techniques that are necessary to create it.
Section | Property | Value |
---|---|---|
Labels | Disable auto-fit | Selected |
Label rotation angle (degrees) | 45 | |
Label Font | Font | Arial |
Size | 8 point | |
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 | 6 point | |
Number | Category | Custom |
Custom format | #,0," K";(#,0," K") | |
Major Tick Marks | Hide major tick marks | Checked |
Minor Tick Marks | Hide minor tick marks | Checked |
Line | Line color | Dim gray |
That is it. You have delivered a chart that avoids the more “classic” approach by using an image rather than a column to display the target value. Moreover, you have superposed the two data series by using a 3D chart that you have “flattened” to remove the perspective.
Note You may prefer to leave the axes and fonts on the axes in black, or another color of your choice. I am merely trying to show that you can avoid distracting the user’s gaze if you tweak axes and number formatting (and remove any superfluous decoration such as titles) in this way.
Of course, when it comes to setting properties in SSRS charts, you have several alternative ways of achieving your objectives. I am merely suggesting one way of doing this here. If you have another method and prefer it, the choice is yours.
This chart required quite a few steps to complete, and some of the techniques you applied may not be immediately comprehensible. Some may even seem superfluous. Here are some of the reasons for doing what you did.
Another way of showing how a metric compares to a target value is to use the same chart type for both data series in a stacked bar or column chart. One of these charts is shown in Figure 4-3. If you are only opening the sample file, set the year to 2015 and the month to 6 to get the same result.
Figure 4-3. Comparing metrics to target values in a chart
If the black and white example from a printed book does not make the point (and you are not reading this in a color e-book) then you can always display the file _SalesToBudget.rdl from the example solution. This way you should get the effect of displaying the sales over budget in one color and the sales under budget in another color.
This approach requires a little work on the source data, as a simple calculation will be required to do the following:
Moreover, to make it clearer when there is a shortfall and when the target has been exceeded, the source data will include a flag that will indicate the state of the second data series (shortfall or excess) that can then be used to set the color of the category for each data point.
The source SQL not only return the sales and budget figures, but will also apply the business logic that is required to set the chart column colors. You can find this code (wrapped in a stored procedure) in the CarSales_Reports database as Code.pr_WD_CountrySalesToBudgetRatio.
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
IF OBJECT_ID('tempdb..#Tmp_MyData') IS NOT NULL DROP TABLE tempdb..#Tmp_MyData
CREATE TABLE #Tmp_MyData
(
CountryName VARCHAR(50) COLLATE DATABASE_DEFAULT
,Sales MONEY
,Budget MONEY
,SalesChartValue MONEY -- The first data series
,BudgetChartValue MONEY -- The second data series
,Indicator TINYINT
)
-- Sales Data
INSERT INTO #Tmp_MyData (CountryName, Sales)
SELECT
CASE
WHEN CountryName IN ('France','Germany','Switzerland','United Kingdom','USA') THEN CountryName
ELSE 'Other'
END AS CountryName
,SUM(SalePrice)
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY
CASE
WHEN CountryName IN ('France','Germany','Switzerland','United Kingdom','USA') THEN CountryName
ELSE 'Other'
END
-- Budget Data
;
WITH Budget_CTE
AS
(
SELECT
BudgetDetail AS CountryName
,SUM(BudgetValue) AS BudgetValue
FROM Reference.Budget
WHERE Year = @ReportingYear
AND Month <= @ReportingMonth
AND BudgetElement = 'Countries'
GROUP BY BudgetDetail
)
UPDATE Tmp
SET Tmp.Budget = CTE.BudgetValue
FROM Budget_CTE CTE
INNER JOIN #Tmp_MyData Tmp
ON CTE.CountryName = Tmp.CountryName
-- Calculations
UPDATE #Tmp_MyData
SET SalesChartValue =
CASE
WHEN Sales < Budget THEN Sales
ELSE Budget
END
,BudgetChartValue =
CASE
WHEN Sales < Budget THEN Budget - Sales
ELSE Sales - Budget
END
,Indicator =
CASE
WHEN Sales < Budget THEN 1
ELSE 2
END
SELECT CountryName, SalesChartValue, BudgetChartValue, Indicator from #Tmp_MyData
Running this T-SQL gives the output shown in Figure 4-4.
Figure 4-4. Comparing metrics to target values and adding an indicator
How the Code Works
Here you are using a single stored procedure to output all the data that you need for the chart. As the two data series that are used in the chart are calculated, and not taken directly from the source data, you use a temporary table to store the sales and budget data first. Then a simple calculation sets the values of the two data series. You use the indicator value to set the column color: 1 means that you are under budget, 2 means that you are over budget. Note that the SalesChartValue and BudgetChartValue figures are not the raw data for sales and budget.
Let’s see how to apply this data to your chart to get the result you desire. As you just saw many of the essential techniques in some detail in the previous chart, I will be a little more succinct when describing any repeated elements.
=Iif(Fields!Indicator.Value=1,"Tomato","Khaki")
=Iif(Fields!Indicator.Value=1,"Khaki","LightBlue")
Section | Property | Value |
---|---|---|
Axis Options | Always include zero | Checked |
Labels | Disable auto-fit | Selected |
Label rotation angle (degrees) | 0 | |
Label Font | Font | Arial |
Size | 9 point | |
Color | Dim 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 | Dim gray | |
Minor Tick Marks | Hide minor tick marks | Checked |
Line | Line color | Dim gray |
The key to this chart is in the work done by the source SQL. This code essentially sets the second series to display the over- or underachievement of the sales targets. Then SSRS applies colors to the columns to indicate much more visually whether the target has been attained or undershot.
Note Some graphic artists object strongly to presenting axis elements at an angle, as you have done in the last two examples. I am not saying who is right or wrong, merely showing how it can be done and the effect that it produces. The choice is yours.
One trick that can help your audience appreciate the essence of a simple data set (and assuming that you are presenting it as a bar, column or even pie chart) is to order the data in the series. This is particularly useful if you are displaying the top or bottom elements in a set, as it makes the hierarchy of data (such as sales, for instance) really stand out. An example of such a chart is shown in Figure 4-5. The data shown is for June 2015 again.
Figure 4-5. Ordering the series in a chart
As this is a simple example, the data is also extremely simple to produce. It uses the following code snippet, which is in the stored procedure Code.pr_MonthlyCarSalesCount, to return the car sales for the year to date:
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
SELECT Make
,COUNT(Make) AS NbSales
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
This T-SQL returns the output shown in Figure 4-6.
Figure 4-6. Monthly unit sales by make
This simple snippet merely calculates the unit sales for makes of car for the selected month in the chosen year.
This chart requires a few tweaks to be applied to make it look effective, so here is how to build it.
That is your chart finished. It should look like Figure 4-5.
I have only the following few points to make here:
Bar charts have been around since charts first appeared on PCs (some readers may even remember them gaining popularity with Lotus 1-2-3). Their sheer ubiquity can have a detrimental effect, because readers are so used to bar charts that they do not make any conscious or unconscious attempt to interpret them.
So you, the BI developer, may need to assist the user. One way to do this is to add a little pizazz to a bar chart so that the differences in the data series stand out more. Superimposing bars and tweaking their respective widths is one way to do this.
As this chart is, as the case with so many BI charts, a comparison of two metrics, you will reuse an existing dataset from earlier in the chapter. This dataset is Code.pr_MonthlyCarSalesWithTarget. You will use it to present another way of using charts to compare sales with targets. An example is given in Figure 4-7. This example also uses the data for June 2015.
Figure 4-7. Superposed bar charts
As the source data for this chart is virtually identical to that used in the previous example, I will not show it here. Feel free to look at it in the CarSales_Reports database if you really want a closer look.
Now that you have seen what you are aiming for, here is how to create it.
That is all that you need to do to create the chart.
You may have a couple of questions as to why you carried out some of the steps in this process. I am not suggesting that all of the aesthetic choices are necessary, but I took the opportunity to add some further effects to your SSRS BI armory. Anyway, the points I want to make are the following:
You are free to disagree with these choices, and to reinstate or alter any elements that you choose.
This chart could potentially be extended in a couple of ways.
Much business intelligence consists of comparing values and being alerted to anomalies. One chart type that lends itself easily to this kind of analysis is a radar chart. This kind of chart is great when you have a few (say between four and eight) data points that are on the same scale. It is instantly evident if any data point varies compared to the others.
As it is easier to understand the techniques if you can visualize the result, an example of a radar chart is shown in Figure 4-8. What is important to remember is that each data point is essentially a percentage (so that disparate figures have been reduced to a similar scale). In this case, the percentage is the over- or under-shoot compared to the budget.
Figure 4-8. A radar chart
For this example, the data is also extremely simple to produce. It uses the following code snippet, which is in the stored procedure Code.pr_WD_RatioAnalysis, to return the car sales for the year to date (June 2015 in this example):
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
SELECT
((1/(SELECT BudgetValue FROM Reference.Budget WHERE [Year] = @ReportingYear AND BudgetElement = 'KeyRatio' AND BudgetDetail = 'CostPrice')) * SUM(CostPrice)) / SUM(SalePrice) AS Ratio, 'CostPrice' AS RatioName
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
UNION
SELECT
((1/(SELECT BudgetValue FROM Reference.Budget WHERE [Year] = @ReportingYear AND BudgetElement = 'KeyRatio' AND BudgetDetail = 'TotalDiscount')) * SUM(TotalDiscount)) / SUM(SalePrice), 'TotalDiscount'
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
UNION
SELECT
((1/(SELECT BudgetValue FROM Reference.Budget WHERE [Year] = @ReportingYear AND BudgetElement = 'KeyRatio' AND BudgetDetail = 'DeliveryCharge')) * SUM(DeliveryCharge)) / SUM(SalePrice), 'DeliveryCharge'
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
UNION
SELECT
((1/(SELECT BudgetValue FROM Reference.Budget WHERE [Year] = @ReportingYear AND BudgetElement = 'KeyRatio' AND BudgetDetail = 'LabourCost')) * SUM(LaborCost)) / SUM(SalePrice), 'LaborCost'
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
UNION
SELECT
((1/(SELECT BudgetValue FROM Reference.Budget WHERE [Year] = @ReportingYear AND BudgetElement = 'KeyRatio' AND BudgetDetail = 'SpareParts')) * SUM(SpareParts)) / SUM(SalePrice), 'SpareParts'
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
This T-SQL returns the data shown in Figure 4-9.
Figure 4-9. Key ratios expressed as a percentage of target
This chart requires five different sales metrics to be returned from the CarSalesData view and then expressed as a percentage of the corresponding budgetary figure. In this case, I preferred (for no real reason other than to show that it is possible) to use UNION statements to assemble the data set.
Now that you have your source data, you can build the chart to display it.
The main point to note for this chart is that this is a classic example of a chart where most of the work is done when preparing the data. If the data is right, then creating the chart is a piece of cake. As you require an output table of five elements, the easiest way to deliver this is using a UNION query where each record is customized to return the required ratio.
Here are two hints for this example.
Bubble charts seem to be in vogue at the moment. This is probably because, if properly constructed, they can display four data series and how they relate to each other. An example of such a chart is shown in Figure 4-10. This chart displays sales and profit by make of car by age range. One data series displays the make on the X (horizontal) axis. The second data series shows how (on the Y or vertical axis) the sales relate to the age of the car. The third data series shows the bubble size for each intersection of sales and age range. Finally, the fourth data series (the color) indicates the percentage profit.
Figure 4-10. A bubble chart
The data for this chart is produced by the following code snippet, which is in the stored procedure Code.pr_WD_MarginByAgeAndMake. The values returned are used as follows:
The code for this chart (for June 2015) is
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
SELECT
Make
,COUNT(SalePrice) AS NbSales
,CASE
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN -5 AND 0 THEN '-5-0'
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0 AND 0.2 THEN '0-20%'
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0.2 AND 0.4 THEN '20-40%'
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0.4 AND 0.6 THEN '40-60%'
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0.6 AND 0.8 THEN '60-80%'
ELSE '>80%'
END AS PercentageProfit
,CASE
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN -5 AND 0 THEN 1
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0 AND 0.2 THEN 2
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0.2 AND 0.4 THEN 3
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0.4 AND 0.6 THEN 4
WHEN (SUM(SalePrice) - (SUM(CostPrice) + SUM(TotalDiscount) + SUM(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost))) / SUM(SalePrice) BETWEEN 0.6 AND 0.8 THEN 5
ELSE 6
END AS ProfitOrder
,DATEDIFF(yy, Registration_Date, GETDATE()) AS VehicleAge
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make, DATEDIFF(yy, Registration_Date, GETDATE())
ORDER BY Make
This T-SQL returns several rows of data, a few of which are displayed in Figure 4-11.
Figure 4-11. The data for a bubble chart
How the Code Works
Here the data is the count of the number of sales for the selected year up to and including the selected month. However, the code groups the number of sales per make according the whole number of years representing the age of the car. It then calculates the percentage profit of each age range using a hard-coded setting to produce five profit ranges.
The data may seem more complex than it really is. All that the CASE statements do is to calculate profit in discreet ranges to avoid a plethora of different values, and then add a sort order from smallest to largest, which is used by the legend to provide visual coherence by ordering the ranges from smallest to largest, rather than in alphabetical order.
Now that you have your source data you can build the chart to display it.
Section | Property | Value |
---|---|---|
Series Data | Bubble Size | [Sum(NbSales)] |
Category Field | [Make] | |
Markers | Marker Type | Circle |
Marker Size | 30 point | |
Marker Color | Automatic |
You can now preview your bubble chart. If all has gone well, it will look like Figure 4-10, above.
I have only a three short points to make about what you have done here.
Waterfall Charts
A waterfall chart is perfect when you need to isolate the various data elements that make up a whole, such as the determination of profit for a sale, for instance. A waterfall chart will only display one data series, essentially, but you can display multiple waterfall charts in a multiple-chart structure if you need to compare and contrast several elements.
An example of a set of waterfall charts is shown in Figure 4-12, using the data for June 2015. In the example, you will only be creating a single chart, but the source data allows you subsequently to alter the filter on the chart to use it as part of a composite visualization.
Figure 4-12. A waterfall chart
The code for this dataset can be found in the stored procedure Code.pr_WaterfallChartOfProfitByMake in the CarSales_Reports database. It is as follows (for June 2015 again; a pattern is emerging here):
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
DECLARE @MaxValUE BIGINT
;
WITH Mx_CTE
AS
(
SELECT
Code.fn_ScaleSparse(MAX(SalePrice)) AS MaxValue
FROM
(
SELECT SUM(SalePrice) AS SalePrice, Make
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
) Mx
)
SELECT @MaxValue = MaxValue FROM Mx_CTE
SELECT
Make
,Sum(SalePrice) AS Result
,'Sales' AS Element
,1 AS SortOrder
,'Blue' AS ColumnColor
,'Lower' AS ChartElement
,@MaxValue AS MaxValue
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
UNION
SELECT
Make
,Sum(CostPrice) + SUM(TotalDiscount) AS Result
,'Direct Costs' AS Element
,2 AS SortOrder
,'Red' AS ColumnColor
,'Upper' AS ChartElement
,@MaxValue AS MaxValue
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
UNION
SELECT
Make
,Sum(SalePrice) - (Sum(CostPrice) + SUM(TotalDiscount)) AS Result
,'Direct Costs' AS Element
,2 AS SortOrder
,'white' AS ColumnColor
,'Lower' AS ChartElement
,@MaxValue AS MaxValue
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
UNION
SELECT
Make
,Sum(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost) AS Result
,'Indirect Costs' AS Element
,3 AS SortOrder
,'Purple' AS ColumnColor
,'Upper' AS ChartElement
,@MaxValue AS MaxValue
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
UNION
SELECT
Make
,(Sum(SalePrice) - (Sum(CostPrice) + SUM(TotalDiscount))) - (Sum(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost)) AS Result
,'Indirect Costs' AS Element
,3 AS SortOrder
,'White' AS ColumnColor
,'Lower' AS ChartElement
,@MaxValue AS MaxValue
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
UNION
SELECT
Make
,Sum(SalePrice) - (Sum(CostPrice) + SUM(TotalDiscount) + Sum(DeliveryCharge) + SUM(SpareParts) + SUM(LaborCost)) AS Result
,'Profit' AS Element
,4 AS SortOrder
,'CornflowerBlue' AS ColumnColor
,'Lower' AS ChartElement
,@MaxValue AS MaxValue
FROM Reports.CarSalesData
WHERE Make IN ('Rolls Royce', 'Jaguar', 'Aston Martin', 'Bentley')
AND ReportingYear = @ReportingYear
AND ReportingMonth <= @ReportingMonth
GROUP BY Make
This T-SQL returns many rows of data. Figure 4-13 shows the data for one make of vehicle.
Figure 4-13. The data for a waterfall chart
This type of chart does not take its data directly from a data source, but needs to apply a little logic first. The arithmetic is not difficult (it is essentially a series of accounting operations) but it requires two records for each column:
The two series are defined in the ChartElement column and are hard-coded. Equally, and to show that it can be done, the color for the series is hard-coded in the T-SQL. To finish, a manually-defined sort order is added. This will be used to present the columns in the correct sequence on the horizontal axis.
This code could be extended, if you wish, to set the colors as variables or as data sourced from another table.
Now that you have your source data, you can build the waterfall chart to display it.
=Fields!ColumnColor.Value
=Fields!Make.Value
=Fields!MaxValue.Value
You should now have an initial waterfall chart that shows how the final profit figure for a make of car is computed.
This chart uses the two data series that were calculated in the T-SQL to produce the waterfall effect. The trick is to set the lower series to appear transparent for the final three columns. This is done by applying the same color as the chart background.
This group of charts really needs to be used as a set. This is why the maximum value is calculated using a CTE and not left to SSRS to decide. This way the same maximum figure is applied to each of the charts. Doing this allows sales for different makes to be compared without multiple vertical scales distorting the picture and making comparison difficult.
The final trick is to force the order of the horizontal axis, again using a hard-coded value set in the source SQL.
Here are several tips for this example.
Sometimes a chart itself is extremely simple. Its value, however, is drawn from the fact that it allows multiple elements to be compared. This is where trellis charts (also known as lattice charts) can be useful.
A trellis chart in SSRS is simply a table that holds a matrix of charts. They are usually between two and four rows and two and five columns. Each chart in the matrix is a copy of the same chart, filtered to display a different record from the source data.
For this type of chart, creating the source data is the easy part (though, as ever, this is important). Most of the attention is spent setting up the table that will hold the multiple charts. It is worth noting that the charts that you use really must be simple, or their density will make the information unreadable, and consequently invalidate the whole purpose of the chart. An example of a trellis chart is shown in Figure 4-14.
Figure 4-14. A trellis chart
The data for this chart is produced by the following code snippet, which is in the stored procedure Code.pr_Trellis:
DECLARE @ReportingYear INT = 2015
SELECT
ReportingMonth
,SUM(TotalDiscount) AS TotalDiscount
,SUM(DeliveryCharge) AS DeliveryCharge
,SUM(SpareParts) AS SpareParts
,SUM(LaborCost) AS LaborCost
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
GROUP BY ReportingMonth
This T-SQL returns twelve rows of data, one for each month, as shown in Figure 4-15.
Figure 4-15. The data for a trellis chart of costs per month
How the Code Works
You have, for once, a code snippet that is mercifully easy to understand. It aggregates a set of metrics from a source view and groups them by month for the selected year.
So, armed with your source data, you can create the trellis chart.
This creates the trellis chart. However, it is missing a legend. This is something that you will learn in Chapter 7.
The key to a successful trellis chart is a clean table structure from the outset. Once you have created a table with the required number of columns and rows, everything else is a copy-and-paste operation once the initial chart has been created.
Once again, as so often with multiple charts and gauges, getting the initial chart right is key. Otherwise, you will be deleting and re-copying and re-pasting over and over again as you make and reapply changes multiple times.
Keep these hints and tips in mind for this example.
As mentioned, one problem that you as a BI developer will face is avoiding the repetition of constantly used chart types. To avoid “chart fatigue” on the part of your users, you should vary chart types if only to waken your public from their slumbers.
Given the general consensus against using pie charts in BI, one alternative is to use pyramid charts. An example of a pyramid chart is shown in Figure 4-16.
Figure 4-16. A pyramid chart
This chart simply shows sales for a selected month (June 2015) by make. It has the added advantage of sorting the makes by sales amount and making the relative percentages clear.
The data for this chart is produced by the following code snippet, which is in the stored procedure Code.pr_WD_PyramidSales_Simple:
DECLARE @ReportingYear INT = 2015
DECLARE @ReportingMonth TINYINT = 6
IF OBJECT_ID('tempdb..#Tmp_Output') IS NOT NULL DROP TABLE tempdb..#Tmp_Output
CREATE TABLE #Tmp_Output (ChartType VARCHAR(25) COLLATE DATABASE_DEFAULT, Make VARCHAR(25) COLLATE DATABASE_DEFAULT, Sales NUMERIC(18,6))
INSERT INTO #Tmp_Output (ChartType, Make, Sales)
SELECT
'MonthlySales'
,Make
,SUM(SalePrice) AS Sales
FROM Reports.CarSalesData
WHERE ReportingYear = @ReportingYear
AND ReportingMonth = @ReportingMonth
GROUP BY Make
SELECT * FROM #Tmp_Output
This T-SQL returns six rows of data, one for each make of car, as shown in Figure 4-17. The ChartType field is not used yet, but you will see it in action in Chapter 7.
Figure 4-17. The data for a trellis chart of costs per month
You have, once again, a code snippet that is mercifully easy to understand. It returns the total sales from a source view for a selected month and year.
Now that you have the data, you can create the pyramid chart.
Section | Property | Value |
---|---|---|
Fill | Fill style | Gradient |
Color | Dim gray | |
Secondary color | White | |
Gradient style | Diagonal right | |
Border | Border type | Raised |
Property | Value |
---|---|
Position | Bottom center |
Font | Arial 7 point bold |
Color | Dim gray |
Layout | Wide table |
Property | Value |
---|---|
Caption | Breakdown by Make |
Color | Gray |
Custom Position | Enabled |
Height: 7.97 | |
Left: 7.07 | |
Top: 2 | |
Width: 80 | |
Font | Arial 8 point bold |
Property | Value |
---|---|
Caption | Current Month |
Color | Dim gray |
DockOffset | -4 |
Position | LeftTop |
Font | Arial 7 point bold |
Section | Property | Value |
---|---|---|
General | Label data | #PERCENT |
Font | Font | Arial Black |
Size | 7 point | |
Color | White | |
Number | Category | Percentage |
Decimal places | 2 |
That is it. You now have a stylish pyramid chart to display sales per make for a specific month.
Setting up a pyramid chart is really very easy, so this one was spiced up with a few stylistic points. Firstly, the titles were tweaked to be positioned closer to the chart area edges than is the default. This automatically lets the chart grow and take up a relatively larger amount of the available space. Then you set the legend so that it does not occupy too much space either, which lets the chart itself grow even more.
A Few Ideas on Using Charts for Business Intelligence
In this chapter, you saw only a very few of the ways that charts can be used to deliver your business intelligence with SSRS. There are many other possibilities, some traditional, some more adventurous, that you can also apply. The trick is to use charts effectively, without trying to impress by using exaggerated stylistic effects, and also without underwhelming the audience through delivering chart types and styles that they have seen too many times before.
So instead of detailing all the potential application of charts, I want to present a few tips on effective BI chart use. This is not a definitive guideline, nor a list of rules to be followed blindly. They are just some ideas that I have applied over time, and that you may find useful.
BI dashboards rarely have the space, and their readers even more rarely the attention span, for complex or even large charts. So a first principle is to reduce the number of elements in a chart so that it is readable. Another idea is to accentuate the information that really matters, so consider using greys for text unless you need to stress something. Then you can put in black the text that needs the audience’s attention. This is better than using large or bold text to attract the reader’s attention: you gain space and avoid multiple confusing and conflicting text styles in a tiny visualization. You could use pastel colors for the less important chart elements, and a primary color for a bar, line, or column that you need your audience to focus on.
I always advise simple chart design in BI reports. This can mean avoiding the temptations of 3-D charts. These are essentially a matter of taste, but they can distract from the information being displayed, and they take up more space to deliver the same information.
Use Multiple Charts of the Same Type
If you have a dense amount of information to deliver, consider creating multiple charts with few data series rather than a single chart with multiple data series. As you will see in Chapter 8, there are ways to add interactivity to an SSRS BI report. Consequently, you can switch between multiple charts on the same report if you do not have the space for several charts to be displayed at once.
Titles take up space. If space is at a premium, see if you can avoid them. This is not always possible, but if you can reduce the size of titles, or even remove them, then do so. As you saw earlier, you can always rotate titles on axes to save space, and you can also set titles to be vertical or pivoted, all of which can save space. These tweaks can add to development time, but the result is nearly always worth it.
If your chart does not need a legend, remove it. The same logic applies to most chart elements. Think about the chart as a whole, and remember that in most cases “less is more.”
If you are afraid of your readers not reacting to the information on a dashboard because the chart type is too classic (once again, possibly because it is one of the overused Excel standard chart types), then consider varying a basic chart type. A few possibilities are given in Table 4-1, though the final choice will inevitably depend on the data and the effect that you want to create.
Table 4-1. Chart Variations
Classic Chart | Alternative |
---|---|
Pie | Donut Pyramid |
Bar | Horizontal Cylinder |
Column | Cylinder |
Line | Radar |
Conclusion
In this chapter, you saw a few ideas on ways to produce charts that suit the more specific requirements of business intelligence reports. These ideas are not the only ones that you can apply, but hopefully they will give you some ideas for your own dashboards and reports. Take these examples and extend and adapt them. Even better, perhaps you now have ideas that were not even touched upon here.
There are also ways to use charts to deliver greater levels of interactivity in a dashboard. Some techniques to enhance the user experience using charts to highlight and slice data are given in Chapter 10. In any case, charts are a fundamental part of BI delivery. I hope that you will enjoy using them and pushing the envelope to deliver some really eye-catching BI.