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

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

How to Create Dumbbell Plots in SQL Server Reporting Services


By:   |   Read Comments   |   Related Tips: > Reporting Services Development

Problem

I’d like to create a dumbbell plot in SQL Server Reporting Services (SSRS), but the chart type is not available there. How can I create such a visualization in SSRS using the built-in tools?

Solution

In this tip, we’ll show you how you can create a dumbbell plot using SQL Server Data Tools (SSDT) and the built-in visualization types. A dumbbell plot is a combination of a range chart and a point chart. It typically visualizes a couple of points in time, depicting different categories against each other, for example two political parties, man versus women, two competitors and so on. We would like to create the following visualization, showing market share of two competitors over the different years:

final plot

The dumbbell plot is not an available chart type in SSRS, so we’ll need to improvise.

Creating the Dumbbell Plot in SSRS

Test Data

The first step is to get data to show on the plot. With the following SQL statement, you can create a table and populate it with sample data:

CREATE TABLE [dbo].[MarketShare]
(
    [ID]          INT           IDENTITY(1,1) NOT NULL
   ,[Year]        INT           NOT NULL
   ,[CompetitorA] NUMERIC(3, 2) NOT NULL
   ,[CompetitorB] NUMERIC(3, 2) NOT NULL
);
 
INSERT INTO [dbo].[MarketShare]
(
    [Year]
   ,[CompetitorA]
   ,[CompetitorB]
)
VALUES   (2015,0.30,0.70)
        ,(2016,0.45,0.55)
        ,(2017,0.60,0.40)
        ,(2018,0.40,0.60);

The data has the following format:

sample data

However, to get this data on the chart, we’ll need to reformat it. A better form would be if the data is unpivoted. With the following query we can get the data in the desired format:

SELECT
    [Year]
    ,Competitor  = 'CompetitorA'
    ,MarketShare = [CompetitorA]
FROM [dbo].[MarketShare]
UNION ALL
SELECT
    [Year]
    ,Competitor  = 'CompetitorB'
    ,MarketShare = [CompetitorB]
FROM [dbo].[MarketShare];	

The result set:

sample  data unpivot

Since we only have two competitors, the query works fine using UNION ALL. However, if multiple competitors are considered, it might be a better option to use the UNPIVOT operator.

Creating the Dumbbell Plot

Create a new SSRS report, a connection to your database. Add a data set with the query from the previous section. Add a chart unto the canvas by right-clicking it and selecting Chart from the context menu.

insert chart

Choose a Line Chart from the various options:

line chart

Click on the chart and specify MarketShare as the value, Year as the Category and Competitor as the series.

configure chart data

We now have the following line chart:

line chart

We now need to turn this line chart into a point plot. Right-click on the series and go to its properties.

series properties

First, we’re going to show the points by enabling the markers in the Markers pane. Choose Circle as the marker type and set the size to 8 points.

configure marker

Next, we’re going to hide the lines themselves. Go to the Border pane and set the Line Style to None.

hide lines

We now have the following visualization:

point plot

The next step is to add the lines between each two points. Before we can do this, we need some extra data: the start and end point for each line. Using window functions, we can adjust the query like this to find the high and low value for each year:

WITH CTE_MarketShare AS
(
    SELECT
        [Year]
       ,Competitor  = 'CompetitorA'
       ,MarketShare = [CompetitorA]
       ,[Sign]      = SIGN([CompetitorB] - [CompetitorA])
    FROM [dbo].[MarketShare]
    UNION ALL
    SELECT
        [Year]
       ,Competitor  = 'CompetitorB'
       ,MarketShare = [CompetitorB]
       ,[Sign]      = NULL
    FROM [dbo].[MarketShare]
)
SELECT
    [Year]
   ,[Competitor]
   ,[MarketShare]
   ,LowValue        = IIF(Competitor = 'CompetitorB'
                            , NULL
                            ,MIN([MarketShare]) OVER(PARTITION BY [Year])
                         )
   ,HighValue       = IIF(Competitor = 'CompetitorB'
                            , NULL
                            ,MAX([MarketShare]) OVER(PARTITION BY [Year])
                         )
   ,[Sign]
FROM [CTE_MarketShare];	

You can read all about window functions in the window functions tutorial. Since we only need to draw the line once, we calculate it only for CompetitorA and set it to NULL for CompetitorB. The result set:

sample data with high and low value

Let’s add this data to the chart. In the chart data, click on the green plus icon next to Values. Select LowValue (or HighValue) from the context menu.

add extra data to the chart

This new value series will default to extra lines on the chart. Click on the arrow next to LowValue and choose Change Chart Type… from the menu.

change chart type

The visualization option that comes closes to “a floating line” is the Range Column. Select this option.

change to range column

In the chart data, we need to specify the low and high point of the column. These are the high and low value from the query.

configure high and low value

We now have the following chart:

points with range columns

We’re getting close. There’s no option in the editors though to specify the width of the column. However, there is one property buried deep in the property menu: MaxPixelPointWidth.

setting the width of the column

When we set this to two, we get the desired effect of the dumbbell plot:

dumbbell before formatting

Formatting the Dumbbell Plot

As a finishing touch, we’re going to format the chart so it confirms more to best practices. First, we’re going to hide the legend for the range columns. Click on the series and in the properties, go to the Legend pane. Simply disable the legend there.

disable legend

Some minor changes:

  • Add a chart title and make the font size a bit bigger
  • Format the vertical axis as a percentage

When you look closely to the chart, you can see the lines are drawn over the points:

line over points

To get rid of this effect, the line needs to be drawn “before” the points are drawn. Simply change the order of the values in the chart data, so the range column appear first:

switch values

We now have the following chart:

almost finished chart

As the cherry on the pie, let’s change the margins of the horizontal axis so less white space is used. Right-click on the horizontal axis and go to its properties. In the Axis Options pane, set the side margins to Disabled.

disable side margins

The dumbbell plot is now finished:

finished dumbbell plot
Next Steps


Last Update:


next webcast 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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools