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

Now, the report design will look like below.

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.
2. Drag and drop TotalProductCost field into Y-Values section as shown below.
3. Drag and drop OrderDate field into Column section as shown below.

Now, the report design will look like below.

Configure properties

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

Download the above report design from the link