Tutorial Gateway

Column Chart in SSRS

by

SSRS Column charts are good for comparisons. By seeing the Column Chart one can understand the business without any further concerns.

For example, compare product Sales of this year with the previous year, Monthly / Yearly performance of an employee, customer orders compared to last year or last month or last week etc.

In this article, we will show you, How to create and configure the Column chart in SSRS with an example

Below screenshot will show you the Data Source and Dataset we used for this SSRS Column Chart Report.

COLUMN CHART IN SSRS 2014

SQL Command we used in the above screenshot is:

-- SQL Query for the SSRS Column Chart
SELECT PivotTable.SalesTerritoryCountry, 
       [2010] AS [Year 2010],
       [2011] AS [Year 2011], 
       [2012] AS [Year 2012], 
       [2013] AS [Year 2013], 
       [2014] AS [Year 2014]
FROM (
      SELECT Ter.SalesTerritoryCountry
	    ,Dt.CalendarYear 
	    ,Fact.SalesAmount 
      FROM DimSalesTerritory AS Ter
	INNER JOIN
           FactInternetSales AS Fact ON 
	       Ter.SalesTerritoryKey = Fact.SalesTerritoryKey
	INNER JOIN
	   DimDate AS Dt ON
	       Dt.DateKey = Fact.OrderDateKey 
    ) AS SourceTable

PIVOT (
     SUM (SourceTable.SalesAmount) FOR 
	  SourceTable.CalendarYear IN
	   ([2010],[2011],[2012],[2013],[2014] )
      ) AS PivotTable

Create a Column Chart in SSRS

When you drag and Drop the Chart from Toolbox to Design space, a new window called Select Chart Type will be opened to select the required chart from the available once. In this example, we are selecting Simple Column Chart as shown in below screenshot.

COLUMN CHART IN SSRS 1

Once you click on Ok button, Column chart will be displayed in the design region with dummy data as shown below.

COLUMN CHART IN SSRS 2

Click on the empty space around the SSRS Column Chart will open the Chart Data window

  • Values: Any Numeric (Metric) value such as sales amount, Tax, Total Sales, Customer count etc. All these values will be aggregated using an aggregate function (Sum, Count etc) because we are grouping them with the category group items.
  • Category Group: Please specify the Column name on which you want to partition your Column Chart.

COLUMN CHART IN SSRS 3

In this example, we will create a Column chart in SSRS for the Year 2011 and 2012 Sales by country. So, Drag and drop the Year 2011 and 2012 column from dataset to chart data values and Country Name in category group.

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

COLUMN CHART IN SSRS 4

Click on the Preview button to see the SSRS column chart report preview.

COLUMN CHART IN SSRS 5

If you observe the above screenshot, It is providing perfect result but we are unable to identify the exact amount of Sales for each country. To resolve these situations, We have to use the Data Labels.

In order to add Data labels to column chart in SSRS, Right click on the Blue bars (representing 2011 Year). Then, select the Show Data Labels option from the context menu to show the values.
COLUMN CHART IN SSRS 6

Second, Right click on the Yellow bars (representing 2012 Year). Next, select Show Data Labels option from the context menu to show the values.

Click on the Preview button to see the Column Chart in SSRS report preview.

COLUMN CHART IN SSRS 7

Please refer Formatting Column-Chart in SSRS to understand the steps involved in formatting the above-specified SSRS column chart with a practical example.

Thank you For Visiting Our Blog