Design an SSRS sparkline report using a table in Bold Reports®

To show trends in data over time inside table rows, add a Sparkline report item to an SSRS table in the Bold Reports® Web Report Designer. You create a dataset, add a table and sparkline, assign data, and format the series.

How do I create the dataset?

The Sparkline report in the Bold Reports® Web Report Designer uses the following dataset query:

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 to the Create Data section and create a dataset using the above query. The AdventureWorksDW2014 database is used here.

How do I add the sparkline report item to a table?

You add a sparkline in the Bold Reports® Web Report Designer by placing a table, grouping its rows, and dropping the sparkline into the last cell.

  1. Drag and drop the table report item to the design area. Sparkline item in item panel
  2. Assign the dataset to the table. Assign dataset to table
  3. Assign the EnglishProductSubcategoryName field in the first cell. Assign value in table cell
  4. Click Details group in the Row Groups pane. The tablix member properties are listed in the properties panel. Group properties
  5. Click the Set Groups... button in the properties panel. The Grouping dialog opens, as shown in the following image. Group dialog
  6. Click the Add button, select EnglishProductSubcategoryName, and click OK. Group data in table
  7. Assign the Sales field in the second cell. Assign value in table cell
  8. 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 the last cell of the table. Add Sparkline item in table

The report design now looks like the following image.

Basic design of sparkline report

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

How do I assign data to the sparkline?

You assign data to a sparkline in the Bold Reports® Web Report Designer from the DATA tab of the properties panel.

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

The report design now looks like the following image.

Basic design of sparkline report

How do I configure the sparkline properties?

You configure the sparkline series and data labels in the Bold Reports® Web Report Designer from the PROPERTIES tab.

  1. 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 the edit icon to open series properties. Series properties
  4. Under the data label settings category, enable the Data Label property checkbox. Chart data label
  5. Format the numbers produced by the Data labels using the Format property. Set '$'#,0;('$'#,0) as the value of the format property field. Format datalabel value
  6. Change the background color and font styles of the table cells to improve the report design, as shown in the following design. Format table design

Refer to the Cell Properties to style the table cell.

What does the report preview show?

The sparkline report preview in Bold Reports® shows the sales amount progress from the start year to the end.

Sparkline RDL report preview

Download the sparkline report design (RDL)

Frequently asked questions

Can I insert a sparkline into a detail cell?

No. You cannot insert a sparkline into a detail cell, because detail members can only contain the static inner members.

Which fields does the sample sparkline use?

The sample uses TotalProductCost in the Y-Values section and OrderDate in the Column section.

See also