Tuesday, July 21, 2015

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.

2 comments:

  1. Top of the RDBMS... we can but Top of the Cube, we have generated chart report and whenever click the chart will display the drill down report.... Is this possible top of the CUBE...

    ReplyDelete
  2. What is being passed to the Stored Procedure are the argument values of the parameters. This said if you use a linked server to your OLAP server and an OpenQuery, you can use a generalized MDX query to extract the necessary data AND then filter the data via a standard T-sql predicate.
    Select ..... from OpenQuery("myLinkedServer", 'MDX query goes here') a
    where field1 = argument1 and field2 = argument2
    Do have a look at some of the BI articles on SQLShack dot com

    ReplyDelete

Popular Posts