{"id":858,"date":"2014-11-21T09:36:06","date_gmt":"2014-11-21T03:36:06","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=858"},"modified":"2015-07-09T17:00:50","modified_gmt":"2015-07-09T11:00:50","slug":"box-plot-graph-in-sql-server-reporting-services","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/","title":{"rendered":"Box plot graph in SQL Server Reporting Services"},"content":{"rendered":"<h3><strong>Scenario<\/strong><\/h3>\n<p>You\u00a0have a data set about durations that you want to plot it in a graph. You need to display the minimum, maximum, average and other statistical information in a convenient way.<\/p>\n<p>How can you do this using SQL Server Reporting Services (SSRS)?<\/p>\n<p><strong>Solution<\/strong><\/p>\n<p>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:<!--more--><\/p>\n<ul>\n<li>Maximum value<\/li>\n<li>Minimum value<\/li>\n<li>Average<\/li>\n<li>Median (which is the 50<sup>th<\/sup> percentile)<\/li>\n<li>Percentiles (typically 25<sup>th<\/sup>, the bottom of the box and 75<sup>th<\/sup>, the top of the box)<\/li>\n<li>Possible outliers<\/li>\n<\/ul>\n<p>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.<\/p>\n<p>An example:<\/p>\n<p>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 <em>candlestick <\/em>and the <em>boxplot<\/em> itself of course. Both can be found in the <em>Range<\/em> category.<\/p>\n<p><strong>Set-up a Box Plot in SSRS<\/strong><\/p>\n<p>For your convenience, here\u2019s the entire script to set-up the test data.<\/p>\n<pre>CREATE TABLE dbo.DimRepresentative( SK_Representative INT NOT NULL ,Name\u00a0\u00a0\u00a0 VARCHAR(50) NOT NULL CONSTRAINT [PK_DimRepresentative] PRIMARY KEY CLUSTERED\u00a0(\u00a0 SK_Representative ASC ));<\/pre>\n<pre>GO<\/pre>\n<pre>INSERT INTO dbo.DimRepresentative(SK_Representative,Name)\nVALUES\u00a0 (1,'Bruce')\u00a0 ,(2,'Selena')\u00a0 ,(3,'Gordon') ,(4,'Harvey');<\/pre>\n<pre>CREATE TABLE dbo.FactCustomerService (SK_CustomerServiceFact INT IDENTITY(1,1) NOT NULL ,SK_Representative\u00a0 INT NULL ,TicketNumber\u00a0\u00a0 UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() \n ,TicketTimestamp\u00a0 DATETIME2(3) NOT NULL ,Duration\u00a0\u00a0\u00a0 INT NOT NULL);<\/pre>\n<pre>INSERT INTO dbo.FactCustomerService(SK_Representative,TicketTimestamp,Duration) SELECT\u00a0 SK_Representative = FLOOR(RAND()*4) + 1  ,TicketTimestamp = DATEADD(DAY,RAND()*100,'2014-01-01') ,Duration\u00a0\u00a0 = RAND()*100000;\nGO 100<\/pre>\n<pre>CREATE VIEW dbo.CustomerServiceFact_Cube \nAS(  SELECT\u00a0\u00a0 SK_CustomerServiceFact\u00a0 ,SK_Representative\u00a0 ,TicketNumber\u00a0 ,TicketTimestamp\u00a0 ,TicketMonth = CONCAT('2014, ',DATENAME(MONTH,TicketTimestamp)) ,MonthOrder\u00a0 = MONTH(TicketTimestamp) ,Duration\u00a0 = Duration \/ 86400.0 FROM dbo.FactCustomerService);<\/pre>\n<pre>GO<\/pre>\n<h2><strong>Creating the Box Plot Graph in Visual Studio<\/strong><\/h2>\n<p>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:<\/p>\n<pre>SELECT DISTINCT\u00a0 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\nJOIN [dbo].DimRepresentative\u00a0 r ON c.SK_Representative = r.SK_Representative;<\/pre>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>Add a new graph to the reporting canvas and select the boxplot graph type.<\/p>\n<p>Click somewhere in the chart area so that the <em>Chart Data<\/em> form pops up. Click on the green plus icon next to <em>Values<\/em> and choose MaxDuration.<\/p>\n<p>This will insert a new series called MaxDuration. If you want, you can change the name in the properties window.<\/p>\n<p>Right-click on the series and choose <em>Series Properties&#8230;<\/em> There you can see MaxDuration has been configured as the high whisker.<\/p>\n<p>Now we can setup all the other properties of the box plot using the other measures. Leave category and tooltip empty.<\/p>\n<p>Now add the <em>Representative<\/em> column as a category group to the chart.<\/p>\n<p>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.<\/p>\n<h2><strong>Customizing the Box Plot in Reporting Services<\/strong><\/h2>\n<p>Unfortunately, there aren\u2019t many options to customize this box plot. You can change the color of the box and the thickness of the lines, but that\u2019s 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.<\/p>\n<p>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 <em>MeanDuration<\/em> from the list. This will add a new series with MeanDuration configured as the high whisker.<\/p>\n<p>Right-click the series and change the chart type to <em>Candlestick<\/em>.<\/p>\n<p>Right-click the series again and go to the properties. Set the <em>open<\/em> and <em>close<\/em> value to MeanDuration and leave<em> low<\/em> and <em>high<\/em> empty. Change the color in the <em>Fill<\/em> pane and the border thickness in the <em>Border<\/em> pane to your liking.<\/p>\n<p>Repeat the same process for MedianDuration.<\/p>\n<p>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.<\/p>\n<p><strong>Thanks for reading this article,<\/strong><\/p>\n<p><strong>Next steps :<\/strong><\/p>\n<ol>\n<li><strong>Share this with your colleagues because Sharing\u00a0 is Learning<\/strong><\/li>\n<li><strong>Comment below if you need any assistance<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: right;\"><em><strong>Powered by <a href=\"http:\/\/www.codereview.co\">CodeReview &#8211; Let&#8217;s make it Better!<\/a><\/strong><\/em><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-858\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-858\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-858\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-858\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-858-69d73eab3beb9' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=858&amp;origin=dotnetsql.info&amp;obj_id=76243027-858-69d73eab3beb9' data-name='like-post-frame-76243027-858-69d73eab3beb9'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Scenario You\u00a0have a data set about durations that you want to plot it in a graph. You need to display the minimum, maximum, average and other statistical information in a convenient way. How can you do this using SQL Server Reporting Services (SSRS)? Solution An efficient way to display different statistical properties is a box &hellip; <a href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Box plot graph in SQL Server Reporting Services<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-858\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-858\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-858\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-858\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/box-plot-graph-in-sql-server-reporting-services\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-858-69d73eab3c434' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=858&amp;origin=dotnetsql.info&amp;obj_id=76243027-858-69d73eab3c434' data-name='like-post-frame-76243027-858-69d73eab3c434'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[25],"tags":[284,286,285,287,97,288,40],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/858"}],"collection":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/comments?post=858"}],"version-history":[{"count":5,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/858\/revisions"}],"predecessor-version":[{"id":1007,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/858\/revisions\/1007"}],"wp:attachment":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/media?parent=858"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/categories?post=858"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/tags?post=858"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}