Design ssrs sparkline report using table

This section describes the steps to design Sparkline report using SSRS table report item.

Create dataset

The following dataset query is used for this Sparkline report.

SELECT Prodcat.EnglishProductCategoryName,
              prodSubcat.EnglishProductSubcategoryName,
              prod.EnglishProductName,
              prod.Color,
              fact.[TotalProductCost],
              fact.SalesAmount,
              fact.TaxAmt,
              fact.[OrderDate],
              fact.[ShipDate]
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

Refer Create Data section and create dataset using the above query. AdventureWorksDW2014 database is used here.

Add sparkline report item

  1. Drag and drop table report item to the design area. Sparkline item in item panel
  2. Assign dataset to the table. Assign dataset to table
  3. Assign EnglishProductSubcategoryName field in the first cell as shown below. Assign value in table cell
  4. Click on Details group in Row Groups pane, now the respective tablix member properties will be listed in the properties panel. Group properties
  5. Click on the Set Groups... button in the properties panel. Now, the Grouping dialog will be opened like below. Group dialog
  6. Click on Add button and select EnglishProductSubcategoryName and click OK. Group data in table
  7. Assign Sales field in the second cell as shown below. Assign value in table cell
  8. Now select the Sales text box and assign the following expression =Sum(Fields!SalesAmount.Value) in the cell. Sum sales value
  9. Drag and drop the sparkline report item into last cell of the table as shown below. Add Sparkline item in table

Now, the report design will look like below.

Basic design of sparkline report

Cannot insert a sparkline into a detail cell. Detail members can only contain the static inner members.

Assign data

  1. Select the cell containing sparkline report item and switch to DATA tab in properties panel. Assign data to chart
  2. Drag and drop TotalProductCost field into Y-Values section as shown below. Add series to sparkline
  3. Drag and drop OrderDate field into Column section as shown below. Add column to sparkline

Now, the report design will look like below.

Basic design of sparkline report

Configure properties

  1. Now, switch to the PROPERTIES tab in the properties panel.
  2. Choose the Sales series in the Choose Series dropdown. Choose series in chart
  3. Click on the edit icon to open series properties. Series properties
  4. Under data label settings category, enable Data Label property checkbox. Chart data label
  5. Format the numbers produced by the Data labels, using Format property. Set the '$'#,0;('$'#,0) as value format property field. Format datalabel value
  6. In the below design background color and font styles are changed in table cells to improvise the report design. Format table design

Refer the Cell Properties to style the table cell.

Report preview

On report preview, the report is showing sales amount progress from start year to till end.

Sparkline RDL report preview

Download the above report design from the link