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 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 example
Below screenshot will show you the, Data Source and Dataset we used for this Column Chart Report.
SQL Command we used in the above screenshot is:
T-SQL QUERY
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- 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 |
Creating 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 required chart from the available once. In this example, we are selecting Simple Column Chart as shown in below screenshot.
Once you click on Ok button, Column chart will be displayed in design region with dummy data as shown below.
Click on the empty space around the 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 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 you Column Chart.
In this example, we will create Column chart for 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.
Click on the Preview button to see the report preview.
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, Right click on the Blue bars (representing 2011 Year) and select the Show Data Labels option from the context menu to show the values.
Second, Right click on the Yellow bars (representing 2012 Year) and select the 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.
Please refer Formatting Column Chart in SSRS to understand the steps involved in formatting the above specified column chart with practical example.
Thank you For Visiting Our Blog
Leave a Reply