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.
How can you create your first sub report. Click here.