Monday, July 27, 2015

SSRS – Combine Chart Report

In this article, we will learn how to combine two different charts within each other and represent as a single chart report. Integration a line-chart into a column-chart makes our SSRS report more elegant and insightful to display the meaningful data.

For example; sales admin wants to see the performance of their products in the different regions such as France, England and USA etc. within two different views in a single chart as we did in the excel.  To meet the requirement, Chart Graph reports are the best option to represent the sales data to state or cities level.

We are going to assume that we have Sales Summary data table in our database having the following information –
CountryName
SalesYear
TotalSales
TotalTax
Germany
2008
1981811.89
172758.34
Germany
2008
2981811.89
272758.34
Germany
2008
2581811.89
342758.34
France
2008
3298789.76
306620.72
France
2008
4298789.76
346620.72
France
2008
1298789.76
106620.72
France
2008
1598789.76
166620.72
United Kingdom
2008
2487391.50
220058.34
United States
2008
1181754.12
113381.69
United States
2008
1323769.09
128255.76
United States
2008
1059022.59
101832.09
Virgin Islands, U.S.
2008
1181754.12
113381.69


We need to display the above information in our SSRS report and Report header should be like as “Regional - Sales Summary Report” as shown below:

To create Combined Chart report, we will follow the following steps as shown below:
1) In our existing report server project, we will add a new report as “Regional - Sales Summary Report” as shown below:


2) Now, we need to create a dataset for the report as shown below:


3) Add Dataset will launch Dataset properties window. We need to set the name as SalesSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:

SELECT
      [CountryRegionName]
      ,[SalesYear]
      ,[TotalSales]
      ,[TotalTax]
FROM [dbo].[SalesSummary]
  




4) After click on the OK button, a dataset has been added in the report data also as shown below:


5) Now right click on the report, click on Chart from Insert as shown below:


6) From Chart Type window, choose Column Chart and click OK button as shown below:


7) Now change the Chart Title as “Regional – Sales Summary” and set the dataset to the column chart in the Chart properties window as shown below:


8) Now, we need to set the Total Sales Amount for Y-Axis and Country Region Name for Category Groups to the column chart as shown below:



9) Click on the preview and the report will be look like as shown below-



10) Now, we need to add another chart type (Line Chart) to the report. For this we need to add another value (Total Sales value again) as shown below:



11) Now right click on the second added TotalSales values and click on the Chart Type as shown below:



12) Now, we need to choose the Line Chart as the secondary chart as shown below:


13) Click OK button and preview of the report as shown below:


This completes the tutorial section of the marge column and line chart into a single chart report.

Other Drill Reports in SSRS
1) To learn the Row Drill Down Report, Click here.
2) To learn the Row Drill Through Report, Click here.
3) To learn the Column based Drill Down Report, Click here.
4) To learn the Chart based Drill Down Report, Click here.

How can you create your first sub report. Click here.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands