Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a box plot graph in SQL Server Reporting Services


By:   |   Read Comments (4)   |   Related Tips: > Reporting Services Charts

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I have a data set about durations that I want to plot in a graph. I need to display the minimum, maximum, average and other statistical information in a convenient way. How can I do this using SQL Server Reporting Services (SSRS)?

Solution

An efficient way to display different statistical properties is a box plot. It allows us to display the following statistical information in a condensed representation:

  • Maximum value
  • Minimum value
  • Average
  • Median (which is the 50th percentile)
  • Percentiles (typically 25th, the bottom of the box and 75th, the top of the box)
  • Possible outliers

Using a box plot, you can quickly find out if there are outliers, if the data is tightly grouped or if it is rather skewed.

An example:

Box plot example

In Reporting Services, there are different graph types available that allow us to create this type of graph. The two most important ones are the candlestick and the boxplot itself of course. Both can be found in the Range category.

Range graph types

The tip Distribution Analysis using a SQL Server Reporting Services Box Plot Chart explains in great detail how to make a box plot using the candlestick graph and shows some customization tricks, such as adding outliers to the graph. This tip will focus on the box plot type, which was used to build the example above.

Set-up a Box Plot in SSRS

We will use the same data as in the tip about formatting durations in SSAS, which was about tracking durations for several customer representatives and the handling of service tickets. For your convenience, hereís the entire script to set-up the test data.

CREATE TABLE dbo.DimRepresentative(
  SK_Representative INT NOT NULL
 ,Name    VARCHAR(50) NOT NULL
 CONSTRAINT [PK_DimRepresentative] PRIMARY KEY CLUSTERED 
 (
  SK_Representative ASC
 )
);
GO

INSERT INTO dbo.DimRepresentative(SK_Representative,Name)
VALUES  (1,'Bruce')
  ,(2,'Selena')
  ,(3,'Gordon')
  ,(4,'Harvey');

CREATE TABLE dbo.FactCustomerService
 (SK_CustomerServiceFact INT IDENTITY(1,1) NOT NULL
 ,SK_Representative  INT NULL
 ,TicketNumber   UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() -- degenerate dimension
 ,TicketTimestamp  DATETIME2(3) NOT NULL
 ,Duration    INT NOT NULL);

INSERT INTO dbo.FactCustomerService(SK_Representative,TicketTimestamp,Duration)
SELECT
  SK_Representative = FLOOR(RAND()*4) + 1 -- random assign a duration to a customer representative
 ,TicketTimestamp = DATEADD(DAY,RAND()*100,'2014-01-01')
 ,Duration   = RAND()*100000;
GO 100

CREATE VIEW dbo.CustomerServiceFact_Cube
AS
(
 SELECT
   SK_CustomerServiceFact
  ,SK_Representative
  ,TicketNumber
  ,TicketTimestamp
  ,TicketMonth = CONCAT('2014, ',DATENAME(MONTH,TicketTimestamp)) -- concat is SQL 2012+ only
  ,MonthOrder  = MONTH(TicketTimestamp) -- used for sorting in the report/cube
  ,Duration  = Duration / 86400.0
 FROM dbo.FactCustomerService
);
GO

Creating the Box Plot Graph in Visual Studio

Open up a new report in Visual Studio. Add a connection to the database holding the test data and create a new dataset. The following query is used to retrieve all the statistical information from the database:

SELECT DISTINCT
  Representative = r.Name
 ,MaxDuration = MAX([Duration]) OVER (PARTITION BY r.Name) / 86400.0
 ,MinDuration = MIN([Duration]) OVER (PARTITION BY r.Name) / 86400.0
 ,MeanDuration = AVG([Duration]) OVER (PARTITION BY r.Name) / 86400.0
 ,Percentile25 = (PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Duration) OVER (PARTITION BY r.Name)) / 86400.0
 ,Percentile75 = (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Duration) OVER (PARTITION BY r.Name)) / 86400.0
 ,MedianDuration = (PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY Duration) OVER (PARTITION BY r.Name)) / 86400.0
FROM [dbo].[FactCustomerService] c
JOIN [dbo].DimRepresentative  r ON c.SK_Representative = r.SK_Representative;

Note that this query will only work using SQL Server 2012 or above, because it uses the analytical windowing function PERCENTILE_CONT introduced in SQL Server 2012 to calculate the percentiles and the median. The query returns the results in decimal time. The maximum, minimum and average can be calculated starting from SQL Server 2005, because those are not actual windowing functions using windows (defined by RANGE or ROWS); they are normal aggregates using the OVER clause. The difference is described in this blog post.

The dataset looks like this:

The SSRS dataset

Add a new graph to the reporting canvas and select the boxplot graph type.

Fresh inserted box plot

Click somewhere in the chart area so that the Chart Data form pops up. Click on the green plus icon next to Values and choose MaxDuration.

Step 1 of creating the graph

This will insert a new series called MaxDuration. If you want, you can change the name in the properties window.

Step 2 of creating the graph

Right-click on the series and choose Series Properties... There you can see MaxDuration has been configured as the high whisker. Now we can setup all the other properties of the box plot using the other measures. Leave category and tooltip empty.

Step 3 of creating the graph

Now add the Representative column as a category group to the chart.

Step 4 of creating the graph

Finally we need to clean up the layout a bit: give the chart and the y-axis proper titles, drop the legend and the x-axis title and perhaps make the axis and gridlines less prominent. When we preview the report, we have a box plot like this one:

Step 5 of creating the graph

Customizing the Box Plot in Reporting Services

Unfortunately, there arenít many options to customize this box plot. You can change the color of the box and the thickness of the lines, but thatís about it. Luckily SSRS is smart enough to change the color of the median and mean lines depending on the color of the box. For example, they turn white/grey against a darker box color.

Not many options to customize a box plot

But suppose I want to choose the color of those lines myself. Or that I want to change the thickness of that line. In that case we have to use a small work around. Remove the median and the mean from the series and click the plus icon in the Chart Data window again to add a new series. Choose MeanDuration from the list. This will add a new series with MeanDuration configured as the high whisker.

Added a new series

Right-click the series and change the chart type to Candlestick.

Changing the chart type

Right-click the series again and go to the properties. Set the open and close value to MeanDuration and leave low and high empty. Change the color in the Fill pane and the border thickness in the Border pane to your liking.

Configure the candlestick series

Repeat the same process for MedianDuration. When the report is run, the box plot looks like this:

The final box plot

Because the open and close values for the candlestick series are populated with the same values, they are reduced to single lines. Due to this trick, we can change color and line weight of the median and mean lines in the box plot.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, March 25, 2016 - 11:47:42 AM - Nate Mills Back To Top

 Thanks very much for this!  It was super helpful.  For me, though, I had to do something else with the SQL since my database is set up with 2008 compatibility:

With core as (

SELECT *

FROM theDB.dbo.theTable)

 

SELECT  core.[Category],

COUNT([Measurable]) Count,

MIN([Measurable]) minTime,

MAX([Measurable]) maxTime,

AVG([Measurable]) meanTime,

med.medianTime,

CASE WHEN pct25.pct25th IS NULL THEN MIN([Measurable]) ELSE pct25.pct25th END pct25th,

CASE WHEN pct75.pct75th IS NULL THEN MAX([Measurable]) ELSE pct75.pct75th END pct75th

FROM core LEFT JOIN

 (SELECT

[Category],

AVG([Measurable]) medianTime

FROM (

SELECT

[Category],

[Measurable],

RowDesc,

ROW_NUMBER() OVER (PARTITION BY [Category] ORDER BY [Measurable] ASC, RowDesc DESC) AS RowAsc

FROM (

SELECT

[Category],

[Measurable],

ROW_NUMBER() OVER (PARTITION BY [Category] ORDER BY [Measurable] DESC) AS RowDesc

FROM core) xx

) x

WHERE

RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

GROUP BY [Category]) med ON core.[Category] = med.[Category] LEFT JOIN

(SELECT [Category], Min(subq.[Measurable]) pct25th 

FROM

(SELECT TOP 75 PERCENT [Category], [Measurable] 

FROM core

GROUP BY [Category], [Measurable]

ORDER BY [Measurable] DESC) subq

GROUP BY [Category]) pct25 ON core.[Category] = pct25.[Category] LEFT JOIN

(SELECT [Category], Min(subq.[Measurable]) pct75th

FROM

(SELECT TOP 25 PERCENT [Category], [Measurable] 

FROM core

GROUP BY [Category], [Measurable]

ORDER BY [Measurable] DESC) subq

GROUP BY [Category]) pct75 ON core.[Category] = pct75.[Category]

GROUP BY core.[Category],

med.medianTime,

pct25.pct25th,

pct75.pct75th

HAVING (COUNT([Measurable]) > 0)

 

This produces the final values, so the box plot mappings don't need to be "sums", they can just be the direct values.

Hopefully this helps some other folks, too!


Tuesday, May 19, 2015 - 5:34:22 AM - Koen Verbeeck Back To Top

Hi Siva,

I guess if all the values are 0, then the box will be right on the axis, right?


Thursday, May 14, 2015 - 9:53:35 AM - Siva Back To Top

Hi Koen,

 

Really a great article which healed my pains of Box Plot, because I couldn't find any documentation on msdn, but luckily got your blog here. The Step by Step explanation could help even the beginner like me. 

 

question:

in my Result Set, I have Null values which was replaced by zero, for some business reason, but I am unable to find the Boxes in the chart for which the Min, Max, Median, High & Low Box values are all same = zero. 

is there any way, we can show the empty boxes.. (No I Guess..)

any quick replies would help me a lot. 

 

Thanks,

Siva


Monday, November 03, 2014 - 5:08:34 PM - Jacque Back To Top

Great tip, thanks.


Learn more about SQL Server tools