How to Create Dumbbell Plots in SQL Server Reporting Services

By:   |   Comments   |   Related: > Reporting Services Development


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?


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]
VALUES   (2015,0.30,0.70)

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:

    ,Competitor  = 'CompetitorA'
    ,MarketShare = [CompetitorA]
FROM [dbo].[MarketShare]
    ,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
       ,Competitor  = 'CompetitorA'
       ,MarketShare = [CompetitorA]
       ,[Sign]      = SIGN([CompetitorB] - [CompetitorA])
    FROM [dbo].[MarketShare]
       ,Competitor  = 'CompetitorB'
       ,MarketShare = [CompetitorB]
       ,[Sign]      = NULL
    FROM [dbo].[MarketShare]
   ,LowValue        = IIF(Competitor = 'CompetitorB'
                            , NULL
                            ,MIN([MarketShare]) OVER(PARTITION BY [Year])
   ,HighValue       = IIF(Competitor = 'CompetitorB'
                            , NULL
                            ,MAX([MarketShare]) OVER(PARTITION BY [Year])
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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms