Monday, July 13, 2015

SSRS - Column Based Drill Down Report

A Column Drill Down report is similar to row drill report but main difference is that it’s based on the columns and will provide the additional details of the column summary. 

For example; sales admin wants to see the performance of the each item in last 5 years. If he is also interested to know the performance of the sales person for those items in the same year then this kind of the report will be very helpful to represent the requested information.

We are going to assume that we have an Order Master table in our database having the following information –

Order No
Order Date
Year
Item Name
Sales Person
Qty
Unit Price
Tax Amt
Total Amt
20120180
01/15/2014
2014
TONER F/IP1222
Ryan Arjun
8
113.42
1.52
1,381.00
20120181
02/16/2014
2014
TONER F/IP1222
Will Smith
25
113.42
1.52
4,315.63
20120190
01/16/2014
2014
HEADSET GN2124NC
Ryan Arjun
15
87.11
0.52
679.46
20120191
05/17/2014
2014
HEADSET GN2124NC
Will Smith
5
87.11
0.52
226.49
20120200
01/17/2014
2014
LADDER STACKING KIT
Ryan Arjun
40
125.32
0.95
4,762.16
20120201
03/18/2014
2014
LADDER STACKING KIT
Will Smith
25
125.32
0.95
2,976.35
20120210
01/18/2014
2014
REPL CANON LV-7575
Ryan Arjun
5
275.50
2.50
3,443.75
20120211
03/19/2014
2014
REPL CANON LV-7575
Will Smith
15
275.50
2.50
10,331.25
20120220
03/15/2015
2015
TONER F/IP1222
Ryan Arjun
2
113.42
1.52
345.25
20120221
05/16/2015
2015
TONER F/IP1222
Will Smith
12
113.42
1.52
2,071.50
20120230
03/16/2015
2015
HEADSET GN2124NC
Ryan Arjun
21
87.11
0.52
951.24
20120231
03/17/2015
2015
HEADSET GN2124NC
Will Smith
20
87.11
0.52
905.94
20120231
07/17/2015
2015
LADDER STACKING KIT
Ryan Arjun
15
125.32
0.95
1,785.81
20120232
09/18/2015
2015
LADDER STACKING KIT
Will Smith
10
125.32
0.95
1,190.54
20120232
03/18/2015
2015
REPL CANON LV-7575
Ryan Arjun
8
275.20
2.50
5,504.00

 We need to display this information in our SSRS report and Report header should be like as “Item Wise Sales Summary Report”. Whenever user clicks on the Year then it will drill column and shows Sales Person Performance details in that Year as shown below:

After click on the Year 2014 toggled then Report will show the following information-


To create Column Drill report, we will follow the following steps as shown below:
1) In our existing report server project, we will add a new report as “Item Wise 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 ItemSummary for the Dataset and choose a dataset embedded in my report and choose Query Type Text as shown below:
SELECT [Order No]
      ,[Order Date]
      ,[Year]
      ,[Item Name]
      ,[Sales Person]
      ,[Qty]
      ,[Unit Price]
      ,[Tax Amt]
      ,[Total Amt]
  FROM [dbo].[OrderMaster]
  order by
  [Item Name], [Sales Person]




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



5) Now right click on the report, add table with dataset Name and report header as shown below:



6) Now fill the columns into the report table and do needful formatting also. We need to add column group into the report table as shown below:

7) It will launch the Tablix group, we need to select Year as Group by and checked Add group header and footer as shown below:



8) Now we have the following data into the report as shown below:



9) Click Preview on the report as shown below-


Now, we need to remove details row and add row group to the report as shown below:







10) After doing needful setting to the report, click on the preview button which will show the following report -



11) Now, we need to add column details to report means need to add child column group as shown below:



12) After clicking on child group, Tablix group will open where we need to choose Sales Person and checked Show details data as shown below:


13) Click Ok button, new we have the following report:



14) Fill the data in the column details columns as shown below:



15) Now see the preview of the report as shown below:



16) In Column Groups, Right click on the Sales Person and choose Group Properties as shown below:



17) In the Group Properties window, set the visibility of the group as Hide and Display can be toggled as Year as shown below: 



18) After click OK button, see the preview of the report as shown below:



19) We have column drill down in our report now and need to add yearly total as well as Grand Total in the report. We need to add these totals in the column groups as shown below:


20) After adding the Grand total and Yearly total to the report, we need to done the needful color formatting and setting to the report as shown below-


21) Now preview of the report and you are able to view the report based on the column based as shown below:


After click on the Year 2014 toggled then Report will show the following information- 



This completes the tutorial section of the simple column drill report. 

Other Drill Reports
1) To learn the Row Drill Down Report, Click here.
2) To learn the Row Drill Through Report, Click here.
3) To learn the Chart Drill Down Report, Click here.

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

No comments:

Post a Comment

Popular Posts