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

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Getting Started with Sparklines in SQL Server 2008 R2 Reporting Services


By:   |   Last Updated: 2010-12-28   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | > Reporting Services Charts

Problem

I'm looking at several new visualization features in SQL Server 2008 R2 Reporting Services and the sparkline looks like something that I could really use.  Can you provide an example of how to use this in a report?

Solution

The sparkline is a new visualization that essentially provides a line chart within a single cell in a report.  This enables you to easily see the trend of data values over a period of time.  In this tip I will build the following report to demonstrate sparklines:

new visualization features in SQL Server 2008 R2 Reporting Services

The report uses the following query to retrieve data from the ContosoRetailDW database:

SELECT d.CalendarMonth, c.ChannelName, sum(f.SalesAmount) SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimDate d ON d.DateKey = f.DateKey
JOIN dbo.DimChannel c ON c.ChannelKey = f.ChannelKey
WHERE d.CalendarYear = @CALENDAR_YEAR
GROUP BY d.CalendarYear, d.CalendarMonth, c.ChannelName
ORDER BY c.ChannelName, d.CalendarYear, d.CalendarMonth

The important point to note is that we need multiple data points in order to render the sparkline.  The above query will return the sum of SalesAmount for each month in a particular year for each ChannelName.  When there are sales for a ChannelName in each month of the year there will be 12 rows.  The report will sum all rows and present the total in the Sales Amount column but the sparkline will use the monthly sales total rows to render the line chart.

To build this report launch Report Builder 3 which is available for download on the SQL Server 2008 R2 Feature Pack site.  Click the Insert tab and launch the Matrix Wizard as shown below:

To build this report launch Report Builder 3

The Matrix Wizard walks through setting up a matrix type of report where you can specify grouping on columns, grouping on rows, and what values to summarize.  The wizard asks you to specify (or select) a data source and a dataset; then you get to the Arrange Fields dialog as shown below:

The Matrix Wizard

Drag and drop ChannelName onto the Row groups and SalesAmount onto the Values.  Accept the default values on the remaining steps in the wizard and the report designer will display the matrix.

The last step is to add the sparkline to the report.  Click Sparkline on the Insert tab as shown below:

 Click Sparkline on the Insert tab

Click on the cell in the matrix where you want to add the sparkline.  You will be prompted to select the sparkline type as shown below:

Click on the cell in the matrix

Choose the sparkline type then double click in the cell where you added the sparkline to display the Chart Data as shown below:

display the Chart Data

Click the + then add SalesAmount for the Values.  This completes the report; run the report and you will see the following output:

run the report and you will see the following output
Next Steps
  • Sparklines are a new addition to SQL Server Reporting Services 2008 R2 and provide a nice way to spice up your reports with a bit of visualization.
  • You can download the Contoso Retail DW sample database used in the report from this CodePlex site.  Select ContosoBIdemobak.exe and follow the instructions for installing.
  • You can download the sample report here.
  • You can download Report Builder 3 from the SQL Server 2008 R2 Feature Pack site.


Last Updated: 2010-12-28


next webcast button


next tip button



About the author





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