SSRS – Chart based Drill-down Report

A picture is worth a thousand words – especially when business is trying to find relationships and understand in their data, which could include thousands or even millions of variables and representation of data visualization through the chart reports become very urgent to take the right action for the business. A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will provide the additional details of that particular category. 

One of the biggest challenges for business users is deciding which visual should be used to best represent the information. Better understanding of the business requirements, chart reports play a very important role to create dynamic Dashboard reports. 

For example; sales admin wants to see the performance of their products in the different regions such as France, England and USA etc. and he is also interested to see the performance of the products in the states of any specific country.  

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 –

StateCode	StateName	CountryCode	CountryName	SalesYear	TotalSales	TotalTax
BY	Bayern	DE	Germany	2008	1981811.89	172758.34
HE	Hessen	DE	Germany	2008	2981811.89	272758.34
HH	Hamburg	DE	Germany	2008	2581811.89	342758.34
FR	France	FR	France	2008	3298789.76	306620.72
1	Ain	FR	France	2008	4298789.76	346620.72
2	Aisne	FR	France	2008	1298789.76	106620.72
3	Allier	FR	France	2008	1598789.76	166620.72
ENG	England	GB	United Kingdom	2008	2487391.50	220058.34
AL	Alabama	US	United States	2008	1181754.12	113381.69
AR	Arkansas	US	United States	2008	1323769.09	128255.76
DE	Delaware	US	United States	2008	1059022.59	101832.09
VI	Virgin Islands	VI	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”. Whenever user clicks on the country category column then it will drill the chart graph and show the state sales summary of that particular country category as shown below:

After click on the country France column then Report will show the following states sales information in the France country- 

We will apply the following things to the graph report–

• Add country parameter to the report and set ALL as default value for the graph report means to display all countries on the graph. This value will be changed automatically whenever drill down apply to the report.
• We need to set the dynamic X- axis title for various drill-down based on the report parameter.
• We need to set the axis title dynamically to set countries name and states name based on the report parameter.
• The same report will call again whenever drill request will send to server.  

To create Graph Drill Down 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:

1    

SELECT [StateProvinceCode]       ,[StateProvinceName]       ,[CountryRegionCode]       ,[CountryRegionName]       ,[SalesYear]       ,[TotalSales]       ,[TotalTax] FROM [dbo].[SalesSummary] Where (@Country = 'ALL' OR [CountryRegionCode]=@Country )

  

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

5) Click on the Country parameter and set the default value “ALL” as shown below:

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

7) From Chart Type window, choose 3-D Clustered Column Chart and click OK button as shown below:

 

8) 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:

9) Now, we need to set the Tax sales amount and Total sales amount to the column chart. For this, click on the chart legend as shown below:

10) We need to set values in chart data which are work for Y-axis data representation. Choose Total Tax and Totals Sales as shown below: 

11) To set the values in the X-axis, we need to fill the values in Chart Data in Category groups as shown below:

12) We need to set the dynamic X-axis category labels. Right click in Chart Data’s Category Groups as shown below:

13) In Category Group Properties window, In the label, we need to set the conditional expression based on the report parameter. 

  

=IIF(Parameters!Country.Value="ALL", Fields!CountryRegionName.Value, Fields!StateProvinceName.Value)

14) Now click on OK button and view the preview of the report as shown below-

15) Now, we need to set the dynamic X - Axis Title as shown below which is also based on the country report parameter:

16) In the Axis Title Properties, we need to set the Title Text such as following condition as shown below: 

=IIF(Parameters!Country.Value="ALL", "Sales in countries" , "Sales in "+Fields!CountryRegionName.Value + "'s cities") + " in year " + Convert.ToString(Fields!SalesYear.Value)

17) Click on the OK button and need to set the Y-axis title also as shown below-

  

18) Now click on OK button and view the preview of the report as shown below-

19) In the chart report, we need to set the column data labels now. For this, right click on the columns and click on the Show Data Labels as shown below: (do this for both columns)

  

20) Now, we need to set the drill down option on the column. For this, right click on the column (do this for both columns) and click on the Series Properties as shown below:

21) In the Series Properties window, we need to do the following things-

a. Choose the page Action,

b. Select Go to Report radio button,

c. Specify the current report name,

d. Add parameter country and fill the value as [CountryRegionCode] from the values list 

as shown below:

22) Click on the OK button and preview the report as shown below - 

After click on the country France column then Report will show the following states sales information in the France country- 

This completes the tutorial section of the simple chart based drill 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.

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