How to add a sparkline to a report in SSRS

Introduction: This blog post is written by my friend and colleague Mickie Stamm. Mickie is a fabulous Dexterity, SQL and application developer and is one of the great minds behind GP Reports Viewer.

By Mickie Stamm:

In the last GP Reports Viewer newsletter I wrote about using rectangles as containers in SQL Server Reporting Services reports. In this post I would like to demonstrate another example of using rectangles in conjunction with something that I think adds a lot of punch to reports – sparklines. Sparklines were introduced with SSRS 2008 R2, however you can simulate them in 2008 and 2005 by using a regular line chart and removing a lot of the surrounding elements like the chart title, legend, category and value axes, etc.

Sparklines are great little charts that help you to see the trend of your data over a period of time. They are useful in a table, matrix or list because you can view them together and compare them to more easily see which rows in your data are performing better than the others.

The steps below will walk you through building an SSRS report with a sparkline in the 2008 R2 Business Intelligence Development Studio (BIDS).

Our report is going to use the following query to get data from a Dynamics GP company database:

SELECT DATENAME(m, SH.DOCDATE) Month_Name, MONTH(SH.DOCDATE) as Month_Number,
       SD.ITEMNMBR Item_Number, COALESCE(I.ITEMDESC, SD.ITEMDESC) Item_Description,
       SUM(CASE SD.SOPTYPE
          WHEN 3 THEN SD.XTNDPRCE
          WHEN 4 THEN SD.XTNDPRCE*-1
          END) Sales
FROM SOP30200 SH
INNER JOIN
     SOP30300 SD
     ON SD.SOPNUMBE = SH.SOPNUMBE
     AND SD.SOPTYPE = SH.SOPTYPE
LEFT OUTER JOIN
     IV00101 I
     ON I.ITEMNMBR = SD.ITEMNMBR
WHERE SH.VOIDSTTS = 0
     AND SH.SOPTYPE IN (3,4)
     AND SD.XTNDPRCE <> 0
     AND SD.ITEMNMBR not like 'XXXXXXXXXXXXXXX%'
     AND YEAR(SH.DOCDATE) = @year
GROUP BY DATENAME(m, SH.DOCDATE), MONTH(SH.DOCDATE), SD.ITEMNMBR,
         COALESCE(I.ITEMDESC, SD.ITEMDESC)

To create the report:

1. Start by right-clicking on your report project and choose Add New Report to start the Report Wizard: 
2. Select an existing shared data source or enter in new datasource information and click Next.
3. Enter in the query string above on the following screen and click Next.
4. For the report type on the next screen choose the radio button for Matrix.
5. Design the Matrix as follows: 
6. Choose a style for the matrix on the following screen (I chose the Corporate theme) and click Finish.
7. Give your report a Name (I chose Sales by Item) and click Finish.
8. Run the report and your results should look like this: 
9. Make the columns for Item Number and Item Description wider.
10. Click on the textbox for Sum(Sales) and then right-click and choose Add Total > Column. 
11. Click on the Month_Name column header to select the column and then right-click and choose Insert Column > Outside Group – Right. 
12. Click on the Month_Name column header to select the column and then right-click and choose Delete Columns.  Choose Yes when prompted if you want to delete columns and associated groups.
13. Click on the textbox in the header row of the new column you added in Step 11 and then right-click and choose Expression…and enter the following expression:  

     =Parameters!year.Value & " Sales" 

14. Click on the textbox in the details row of the new column you added in Step 11 and then right-click and choose Insert > Rectangle. 
15. Next, right-click on the rectangle you added in the last step and choose Insert > Sparkline.
16. Choose a chart type (I chose the default column chart type) and click OK.
17. Set your chart Values to be Sales and Category Groups to be Month_Number. 
18. Make the chart wider and shorter and adjust the row height to be shorter as well so that it looks something like this: 
19. Now, when you run the report you will notice that the sparkline chart doesn’t look quite right if the items don’t have sales in certain months. To resolve this issue, go back to design mode, right-click on your sparkline and choose Horizontal Axis Properties… and mark the checkbox for Align axes in: and select the name of your matrix (in my case it is the default name matrix1) from the dropdownlist and click OK.
20. This will make the bars line up in the right places horizontally, however the next issue we run into is that the vertical maximum is the same height in each row no matter what the sales amount is. To resolve this issue, go back to design mode, right-click on your sparkline and this time choose Vertical Axis Properties… and mark the checkbox for Align axes in: and select the name of your matrix (in my case it is the default name matrix1) from the dropdownlist. Set the Minimum value to 0 (zero) and click OK.
21. To add tooltips for the individual bars of the sparkline, in design mode click on one of the bar columns of the Sales Chart Series and enter in the following expression the under ToolTip property: 

     =Fields!Month_Name.Value & " : " & Format(Fields!Sales.Value,"C2") 

Those are the basics to setting up a sparkline embedded in a rectangle in your SSRS report. You can explore the other chart properties and tweak things like colors, markers, etc. and even provide actions such as jumping to a detail report for a given line item. The report created with the steps above can be downloaded here.

For more GP Reports Viewer news and Dynamics GP reporting tips, sign up for the GP Reports Viewer newsletter. For additional SQL code, take a look at the GP Reports page on this blog.