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

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

How to Create A Lollipop Chart in SQL Server Reporting Services


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

Problem

I have created a bar chart in SQL Server Reporting Services (SSRS), but the chart looks crowded. I can’t seem to find a good alternative. Is there a possibility to make the chart more lightweight using the built-in tools?

Solution

In many cases, a bar chart or a column chart is the best way to present categorical data. However, sometimes the visualization can become a bit cluttered or aggressive when all the values are high, resulting in long bars throughout the chart. Let’s look at an example:

bar chart example

This bar chart displays a fictional satisfaction score for a set of subcategories. However, since all of the data is in the range between 80 and 100%, all of the bars extend to the outer right portion of the graph, cluttering the bar chart. This makes the visual quite heavy to look at, even though the bar chart is typically the most optimal visualization to display this kind of data.

A line chart might prove a viable alternative for some sets of data, but it typically implies a trend over time, which is something we’d like to avoid with this data set. When looking at the following line chart – using the same data – it becomes clear why the line chart is a bad idea. The peaks give the illusion of a certain “cyclical trend”, while there is none. Furthermore, the labels have now moved from the Y-axis to the X-axis, making them harder to read.

line chart example

The solution to this problem is to use a slight variation of the bar chart: the lollipop chart. This visualization slims the bars down to a single line with a dot at the end to mark the value. In this tip, we’ll create the following lollipop chart:

final lollipop chart

As you can see, the visualization is now less visually aggressive. There’s only one minor drawback: the center of the circle identifies the value of the subcategory, but it can be difficult to judge the exact position of the center. This makes the lollipop chart a bit less accurate than the actual bar chart.

Creating a Lollipop Chart

Test Data

In this tip, we’ll use the AdventureWorks data warehouse to fetch the names of the subcategories. You can download this sample database from Github. With the following query, we’ll generate a random satisfaction score for each subcategory in the range of 80 and 100%.

DECLARE @Upper numeric(2,1) = 1;
DECLARE @Lower numeric(2,1) = 0.8;
 
SELECT
     Subcategory        = [EnglishProductSubcategoryName]
    ,SatisfactionScore  = ((@Upper - @Lower) * RAND(CONVERT(VARBINARY, NEWID()))) + @Lower
FROM  [AdventureWorksDW2017].[dbo].[DimProductSubcategory]
WHERE [ProductCategoryKey] = 2;	

The RAND() function generates a random number between 0 and 1. Using the @Upper and @Lower variables, we can force the result into a specific range of values.

  • If RAND() returns 0, @Lower is returned.
  • If RAND() returns 1, (1 – 0.8) * 1 + 0.8 returns 1, the value for @Upper.
  • All other values are situated between @Upper and @Lower.

As an argument to the RAND() function, CONVERT(VARBINARY,NEWID()) is passed as a seed. If we don’t do this, RAND() will return the same random number for each row:

incorrect random number generation

The NEWID() function will guarantee a new seed for every, resulting in a unique random number for each row:

correct random number generation

Keep in mind that since we’re using random numbers as sample data, your charts might look a bit different than the ones in this tip.

Creating a Bar Chart

The first step of creating a lollipop chart is creating the original bar chart. Using the query from the previous section, add a connection and a dataset in a blank report. Right-click on the canvas and choose the insert a chart. From the menu, choose the bar chart visualization:

choose chart type

Make the chart a bit bigger and configure the chart data as follows:

chart data

When we preview the chart, we can see most of the labels are missing:

missing labels

We can force them to show up in the axis properties. Right-click on the vertical axis and choose Vertical Axis Properties.

set interval options for the axis

In the Axis options pane, change the axis interval from Auto to 1. Click OK to close the menu. We’re making three additional formatting changes:

  • Delete the legend at the top. There’s only one measure, so there’s no need to display a legend.
  • Give the chart a proper title.
  • Format the X-axis to show percentages.

The bar chart now looks like this:

bar chart example

Transforming the bar chart into a lollipop chart

Now it’s time to create the lollipop chart. Right-click on a bar and choose Series Properties. Here we’re going to add the marker. Go to the Markers pane and set the Marker type from None to Circle. Change the size to at least 6pt.

add marker

Click OK to close the properties menu. The next step is to reduce the width of the bars. This setting cannot be found in the series properties menu. Select a bar and hit F4 to display the properties pane. In the CustomAttributes section (under General), you can find the MaxPixelPointWidth property. This property controls the width of the bars. Change it from 0 to 3.

change bar width

The bar chart is now transformed into a lollipop chart:

final lollipop chart

Conclusion

Using a couple of minor tweaks, we have transformed a standard bar chart into a lollipop chart. The advantages of the lollipop chart is that they are less visually cluttered due to the smaller widths of the bars. However, it’s slightly less accurate since the center of the marker indicates the actual value (not the outer right point of the circle), which is now a bit harder to locate on the horizontal axis. You can mitigate this issue by displaying data labels.

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