Search results

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

Drag and drop table report item to the design area.
Assign dataset to the table.
Design a simple table design with two columns like below.
Click on Details group in Row Groups pane, now the respective tablix member properties will be listed in the properties panel.
Click on the Set Groups... button in the properties panel. Now, the Grouping dialog will be opened like below.
Click on Add button and select SubCat and click OK.
Now select the Sales text box and assign the following expression =Sum(Fields!Sales.Value) in the cell.
Format the numbers produced by the Sales field, using Format property. Set the '$'#,0;('$'#,0) as value format property field.
Add a column to the right and name it as Sales Indicator.
Drag and drop the data bar report item into last cell of the table.

Now, the report design will look like below.

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

Assign data

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

Configure properties

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