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

 

Create a Treemap Graph in SQL Server Reporting Services 2016


By:   |   Read Comments (5)   |   Related Tips: More > SQL Server 2016

Attend these FREE MSSQLTips webcasts >> click to register


Problem

At the time of writing, SQL Server 2016 preview (CTP 2.2) has been released and it contains a few exciting changes for SQL Server Reporting Services (SSRS). One of the new additions is a new chart type: the treemap. This tip will give an introduction on how to create a treemap graph.

Solution

Test Data

First of all, we need data for our treemap. In the tip Retrieving file sizes from the file system using Power Query it is explained how you can use Power Query to extract the file sizes of all the files in a folder and its subfolders. The resulting data looks like this:

Sample Data

The data consists of all the files found with their corresponding file size and to which folder hierarchy the file belongs. There are 3 main folders, corresponding for each year I have been writing for MSSQLTips: 2013, 2014 and 2015. Each folder has different subfolders, where each subfolder corresponds with a tip I wrote.

The easiest way to get this data into Reporting Services, is to import the final Excel worksheet into a SQL Server table using the Import wizard. For more information on this wizard, check the tip Simple way to import data into SQL Server.

You can download an Excel workbook with sample data here.

SQL Server 2016 preview

As mentioned earlier, at the time of writing SQL Server 2016 is still in preview (currently CTP 2.2 has been released). This means that the look and feel of Reporting Services and its charts can still change and that functionality might change, disappear or be added.

Another issue is there is for the moment not yet a new version of SSDT-BI (the Visual Studio shell for developing business intelligence solutions) released for SQL Server 2016. You can develop BI solutions for SQL Server 2016 preview using SSDT-BI 2013 (which is actually for SQL Server 2014), but it has only SQL Server 2014 features. So how are we going to test SSRS functionality now? Luckily Report Builder has already been updated and included in the SQL Server 2016 CTP 2.2 release, so in this tip we’ll use Report Builder to create the treemap.

In order to use Report Builder, you need to install SSRS 2016 CTP 2.2 (or later), preferably not on your production machine. Then you navigate to Report Manager - typically the URL has the format http://myserver/Reports - where you can launch Report Builder.

Launch Report Builder

Creating a Treemap

What is a treemap exactly? According to the dataviz catalogue:

Treemaps are an alternative way of visualizing the hierarchical structure of a Tree Diagram while also displaying quantities for each category via area size.

But an example may be more clear:

Treemap example

This example, created by Power BI Desktop, shows the sizes for the different subfolders in three main folders. The bigger the folder size, the bigger the corresponding square in the treemap. You can compare the overall shape of the three main squares of the folders 2013, 2014 and 2015 with each other, but in each folder you can also compare the file sizes of their corresponding subfolders. You can for example, immediately see that the folder BIML_part3 has the biggest size of all the different subfolders. The goals of this tip is to create a similar chart with SSRS 2016 using Report Builder.

First, open Report Builder, create a blank report and add a data source pointing to your SQL Server database. Then add a dataset and include a query to fetch the data needed for the treemap. I used the following query to get the data to the tip level:

Source Query

Report Dataset

Next we need to insert a chart into the report canvas.

Insert chart

The treemap chart type is listed under the Shape types:

Different charts available

After the chart has been added to the canvas, click on it so the Chart Data window opens up. Add the field Size to Values.

Adding the series

Next put Tip on Category Groups and Year on Series Groups.

Added Tip and Year

When you preview the report, you get the following result:

First treemap in SSRS!

Like this, the chart is quite useless because SSRS doesn’t show most of the labels because they are too long (in sharp contrast with the treemap created with Power BI desktop, where most of the labels were visible). An option would be to make the font size smaller for the data labels, but they would have to be really small to fit most of the labels in the chart and this defeats the purpose of having labels at all. Another option is not to display the name of the tip, but rather just the total size in kilobytes. This can be done by right-clicking the chart and deselect Show Data Labels.

Hide Data Labels

The treemap now shows the size instead of the tip name, which is the same as setting the property UseValueAsLabel to true.

Size is now data label

Most of the squares now have a label, although the smaller squares still don’t. If you deselect Show Data Labels one more time, all the labels disappear.

Deselect labels again

Maybe a better option is to show labels for the series (the different years) and the size, and have a tooltip displaying extra information when needed. This can be done by setting the following expression for the tooltip on the series:

Add tooltip expression

This results in the following tooltip:

Treemap with tooltip

After applying some cosmetics - dropping the legend, changing the tile, changing the font color of the labels and choosing different colors for the series groups - the treemap is finished:

Final treemap

Conclusion

Treemaps are a welcome addition to the Reporting Services chart types. The chart is quite easy to configure, but it doesn’t display the labels quite nicely. A work around is to create a tooltip to show you relevant information.

Next Steps
  • Try it out yourself! Download the sample data and create your own treemap.
  • Download Power BI desktop from PowerBI.com and create a treemap over there (you can use the same sample data). Which one do you like best? Let me know in the comments!
  • For more SQL Server 2016, read these other SQL Server 2016 Tips.
  • For more Reporting Services tips: Reporting Services Charts Tips.


Last Update:


signup 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
Related Resources





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, March 13, 2017 - 3:59:59 AM - Koen Verbeeck Back To Top

Hi Hugo,

thanks for reading.
You could use an expression for the labels. Keep in mind though that in the example here, you would use the labels on the series groups. In the expression, you could cut off the text if it exceeds a certain length. Or you could just make it empty and then it's "hidden".

Koen


Saturday, March 11, 2017 - 6:14:28 AM - Hugo Back To Top

Hi Koen,

Thank you for this interesting post.


Do you know if it's possible to hide "big" labels (Years in your example) but still display little values ? I have a treemap with always the same big label displayed and it's really useless to show it.


Wednesday, September 16, 2015 - 4:11:03 PM - Koen Verbeeck Back To Top

Hi Dory,

the SSRS team is aware of the limitations of the labels and last time I heard they were working on it, so they might improve in next CTP releases.


Wednesday, September 16, 2015 - 2:22:00 PM - Dory Owen Back To Top

Very nice, Koen.  It would be helpful if those box labels wordwrapped to avoid truncation, right?

I've been using a default Excel add-in from Microsoft Research to create interactive treemaps.

Been waiting to see treemaps in the Microsoft Excel/BI toolkit for years.  Just need time to play with this.  Thanks for the demo.


Wednesday, September 16, 2015 - 10:05:42 AM - Bala Back To Top

Good one


Learn more about SQL Server tools