Design ssrs data bar report using table

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

Create dataset

The following dataset query is used for this Data bar report.

SELECT PC.Name AS ProdCat, PS.Name AS SubCat, DATEPART(yy, SOH.OrderDate) AS OrderYear, 'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales
FROM Production.ProductSubcategory PS INNER JOIN
Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN
Production.Product P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

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

Add data bar report item

  1. Drag and drop table report item to the design area. Data bar item in item panel

  2. Assign dataset to the table. Assign dataset to table

  3. Design a simple table design with two columns like below. Table report design

  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 SubCat and click OK. Group data in table

  7. Now select the Sales text box and assign the following expression =Sum(Fields!Sales.Value) in the cell. Sum sales value

  8. Format the numbers produced by the Sales field, using Format property. Set the '$'#,0;('$'#,0) as value format property field. Format textbox value

  9. Add a column to the right and name it as Sales Indicator. Add column in table

  10. Drag and drop the data bar report item into last cell of the table. Add data bar report item

    Now, the report design will look like below.

    Basic design of data bar report

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

Assign data

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

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 in 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 difference in sales between each product category will be displayed like below,

Data bar RDL report preview

Download the above report design from the link