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. You can watch a demo in our YouTube Channel -

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.