Using Sparklines & Data Bars to Visualize Data in SQL Server Reporting Services Reports

SQL Server Reporting Services 2008 R2 introduced many features in the area of graphical display of information. According to MSDN, there were three new features introduced in SQL Server 2008 R2:  maps, covered in a previous blog post, sparklines and data bars and indicators. In this second of three articles on data visualization, we will explore the sparklines and data bars features as we build reports using Dynamics GP data.

Setting the Stage

The old expression is, “A picture is worth a thousand words,” meaning people can get a lot of information from a picture. Some people prefer raw data, but the beauty of pictures is they help many of us understand the data much more quickly.

Sparklines and data bars are two ways you can add pizzazz to your reports and help readers visually grasp what the data holds. Instead of just showing numbers by period, you can show charts in your table demonstrating how well the salespeople are doing.

Sparklines

A sparkline is a graphical representation of data over time. For example, you may want to show sales trend of sales over a year. The difference between a sparkline and a regular graph is that a sparkline is placed inside a table, whereas a graph stands alone.

Here is a table showing sales by month:

We want to introduce a sparkline and remove the months of sales to succinctly illustrate how the salespeople are doing.

First, delete the matrix displaying the numbers by month and replaced it with a simple table grouped by salesperson.

The next step is to add a sparkline from the toolbar; the column will be named “Performance for the Year.”

Click on Sparkline and then in the textbox for the footer. When we click in the box, a number of choices appear.

We’re going to select the line type.

We then have to add some properties to the sparkline.

These properties tell the report to graph the subtotal using the SaleMonth column.

When running the report, the system creates a point for each sale month the salesperson has data.

Notice from the original chart that Nancy B. had four months of sales, and the last month was a large increase. Compare that data to the report above, which shows fairly flat sales and then an increase in the last data point.

Notice how the sparkline tells a story for each salesperson.

Data Bars

Sparklines are just one way to visualize data. Data bars provide another way. A data bar is a way to indicate a single value at a time for each person. For example, what if you want to display a bar showing the value of the subtotal for each salesperson so you can visually compare total sales?

We’re going to start with the same report above, showing each salesperson and the total sales.

On the toolbar, select Data Bar.

Click in the textbox. In this case, I’ve created a blank textbox to hold the data bar.

The end result looks like the image below.

Another way to display the data is with the number in the same cell as shown below.

This view may be a little cluttered for your taste, but the great thing about the system is you can choose either method.

We’ve just scratched the surface on adding visualizations using sparklines and data bars to your SSRS reports. In just a few steps, you can add these visual elements to your reports. Feel free to experiment using Report Builder or Visual Studio.

For more information, please contact our Microsoft Dynamics GP Support Center.