How to Create A Lollipop Chart in SQL Server Reporting Services

By:   |   Comments   |   Related: > Reporting Services Charts


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?


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;
     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


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

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