Tutorial Gateway

Data Bars in SSRS Matrix Reports

by

In real-time, we mostly use the Data Bars in Matrix reports. In this article we will show you, How to create and configure the Data Bars in SSRS Matrix Reports.

TIP: Please refer Data Bars article to understand the configuration of Data Bars in Table Reports with example.

We are going to use below shown matrix report to configure the Data Bars. Please refer SSRS Matrix Report, Data Source and Dataset articles to understand the creation of Data Source, Dataset and Matrix report. If you observe the below screenshot, It was a Drill Down Matrix report with Row Total, Column Total and Grand Total (Pink Color Column) columns.

Data Bars in SSRS Matrix Reports
SQL Command we used as the Dataset is:

SELECT Geo.[EnglishCountryRegionName] AS [Country]
      ,Geo.[StateProvinceName] AS [State]
      ,Geo.[City]
      ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]  
      ,Cust.EnglishEducation AS Education 
      ,Cust.EnglishOccupation AS Occupation
      ,Cust.[Gender]
      ,SUM(Cust.YearlyIncome) AS YearlyIncome
      ,SUM(Fact.SalesAmount)AS SalesAmount 
FROM  DimCustomer AS Cust
  INNER JOIN
      FactInternetSales AS Fact ON 
   Cust.CustomerKey = Fact.CustomerKey
  INNER JOIN
     [DimGeography] AS Geo ON
   Cust.GeographyKey = Geo.GeographyKey
 GROUP BY Geo.[EnglishCountryRegionName]
         ,Geo.[StateProvinceName] 
	 ,Geo.[City]
	 ,Cust.FirstName
         ,Cust.LastName  
         ,Cust.EnglishEducation 
         ,Cust.EnglishOccupation 
	 ,[Gender]
  ORDER BY [Country]

Data Bars in SSRS Matrix Reports

In this example, We will create and add the Data Bar on the right hand side of the Total Column. First, Let us create an Empty column by right clicking on the Total column and select the Insert Column option and then select Right option as shown below.

Data Bar in SSRS Matrix Report 2

Now, Drag and drop the Drop the Data Bars from Toolbox to Empty column. When you drag and Drop the Data Bars from Toolbox to Empty column, a new window called Select Data Bar Type will be opened to select required Data Bar or Data Column from the available once. In this example, we are selecting Simple Data Bar as shown in below screenshot.

Data Bar in SSRS Matrix Report 3

NOTE: Please be careful while adding Data Bars in Matrix Report. If you place the Data Bars at Total Row then Reporting Server will display the data bar for Totals not for States.

Click Ok to add Data Bar to newly created empty cell. Now, we have to configure the values of a Data Bars. First, Click on the Blue bar from the below screenshot will open the Chart Data window. In this example, we want to show the Sales Amount in Data Bars so, Please drag and drop or select the Sales Amount as the Chart Value.

Data Bar in SSRS Matrix Report 4

NOTE: Please be careful when dragging the columns to values region, if you mishandled or dropped in design area will end up in mess.

Click on the Preview button to see the report preview.

Data Bar in SSRS Matrix Report 5

If you observe the above screenshot, It is providing perfect result but we are unable to identify the value represented by the Data Bar (Sales Amount in each state). To resolve these situations, We have to use the Data Labels.

First, Right click on the Blue bars and select the Show Data Labels option from the context menu to show the values.

Data Bar in SSRS Matrix Report 6

Here, we Formatted the Numbers produced by the Data labels. Lets click on Preview tab to see the Report preview.

Data Bar in SSRS Matrix Report 7

Thank you For Visiting Our Blog