Sparkline in SSRS

In this article, we will show you how to create Sparkline in SSRS with an example. As most of the followers are asking about the sparklines in the table report – We will show you the steps involved in adding SSRS Sparklines to Table Report and Matrix report.

For this SSRS Sparklines example, we are going to write the Custom Query against the SQL Server Data Source, and the query that we are going to use for this report is:

-- SQL Query that we used in Sparkline
SELECT Prodcat.EnglishProductCategoryName,
FROM dbo.DimProduct as prod 
   INNER JOIN  dbo.DimProductSubcategory AS prodSubcat ON 
               prod.ProductSubcategoryKey = prodSubcat.ProductSubcategoryKey 
   INNER JOIN  dbo.DimProductCategory AS Prodcat ON 
                prodSubcat.ProductCategoryKey = Prodcat.ProductCategoryKey 
   INNER JOIN  dbo.FactInternetSales AS fact ON 
                 fact.ProductKey = prod.ProductKey

Create Sparkline in SSRS Example

This example shows you the step by step approach to create a Sparkline in SSRS and how to add it to Table report and Matrix report?. The below screenshot will show you the Data Source and Dataset that we used for this SSRS report.

Data Source and DataSet

To demonstrate the steps involved in adding the Sparkline in SSRS Report, We are going to use the previously generated report that we shown below. Please refer to Add Headers and footers to Report article to understand the steps involved in adding page headers and footers.

Blank Report

Add Sparkline to SSRS Table Report

In this example, we are going to create a Table report, and then add a sparkline inside that SSRS table. We can add a table to report design by dragging the table from the Toolbox to Design space. Or, right-click on the report designer to open the context menu. Please select the Insert -> Table option from it.

Insert Table Report

As you can see, we added the English Product Subcategory Name as the first column, and also removed the third column from the table. I suggest you to refer Create a New Table Report to understand the steps involved in creating a table report.

Delete Extra Columns

Let me do some formatting to change the look of a table report. Please refer to the Format Table Report to understand the available formatting options.

Format the Table Report

Next, right-click on the Textbox that contains the Product Subcategory Name will open the context menu. Please select the Row Group, and then Group Properties.

Row Groups for Sparklines

Once you select the Group Properties option, a new window will open, as shown below. Please click on the Add button to add Group Expression.

Sparkline Group Properties

Next, select the Grouping column. Here it is English Product Sub-category Name

Sparkline Group Category

Next, let me add the Sparkline to the Empty column.

There are multiple ways to add a Sparkline in SSRS. First, right-click on the Empty Column. From the context menu, please select the Insert -> Sparkline option.

Insert Sparkline into a Table Report

Or, by dragging Sparkline from the SSRS Toolbox to TextBox

Add Sparkline From Toolbox

Either way, it will open the following window to select the sparkline type. Here, we are choosing the Line type.

Select Sparkline Type

Now you can see the sparkline inside the table report in SSRS.

Sparkline Inside a Table Report

Click on the empty space around the sparkline will open the Chart Data window. Here we want to see the Total Product cost by Order Date Trend. So, Drag and drop the Total Product cost column from dataset to chart data values, Order date to category group.

Edit Sparkline Chart Data

Let me show you the SSRS Sparkline report preview.

View Sparkline in Table Report Preview

Add Sparkline to SSRS Matrix Report

In this example, we are going to create a Matrix report, and then add a ssrs sparkline inside that Matrix.

We can add a Matrix by dragging the Matrix report item from the Toolbox to Design space. Or we can right-click on the report designer, and select the Insert -> Matrix option.

Insert Sparkline in Matrix

As you can see, we added the English Product Subcategory Name as the Row Group and Sales Amount. I suggest you to refer Create a New Matrix Report article to understand the steps involved in creating a Matrix report.

Matrix Design

Let me show you the report preview.

Matrix Preview

Next, let me add a new column outside the Group.

Add Sparkline Column to Matrix

Next, we are inserting the SSRS Sparkline inside the newly created column

Insert Sparkline into the Matrix

This time we are using the Area Chart as Sparkline Type

Area. Chart as a Sparkline

Last, we added the Sales Amount column as the chart data values, Order Date as a category group.

Area Chart as the Sparkline Data

Let me show you the SSRS sparkline report preview.

Sparkline in SSRS Preview

As you can see from the above screenshot, we successfully added the Sparkline to Matrix report in SSRS.

Let me remove the sparkline column and add a new column inside the Row Group.

Remove Sparkline from Matrix

Add Sales Amount, and Order Date as the sparkline fields

Change or Update Sparkline Column Data

Let me show you the sparkline report preview.

Updated Sparkline in Matrix Preview

Add one more metric value i.e., Total Product Cost

Add Multiple Sparklines in a Matrix Report

Well, our SSRS Sparkline is displaying both the Sales Amount and Total Product Cost.

Multiple Sparklines in SSRS Matrix Report Preview

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.