Search This Blog

Drill down two columns in a single column in SSRS

Drill down two columns in a single column in SSRS

As an active user of various forums and groups someone had a query regarding SSRS Report Design Output.I found the question interesting so I am writing a post of it in my website.
Below is the question asked by user?
I have a table with country names,state names and population.
There should be an expanded or collapsed (i.e drill down) on country Names. So, when I collapsed the countries Name it should show sum of population country wise when I expand on country Names it should show population state wise, with the exception that when we expand country name it should open state Names under country name column not in a new column as below screenshot.

Requirement Screenshot.

Issue Solution -If you want to try the same use the below script the below script to create the table

CREATE TABLE [dbo].[country](
       [countries] [nvarchar](50) NOT NULL,
       [states] [nvarchar](20) NULL,
       [popvalue] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'ind', N'M.P', 210025)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'ind', N'U.P', 3005605)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'ind', N'A.P', 24563)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'AUS', N'MEL', 23695)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'AUS', N'SYD', 36985)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'AUS', N'MEL', 21478)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'usa', N'L.A', 96325)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'usa', N'L.V', 31568)
GO
INSERT [dbo].[country] ([countries], [states], [popvalue]) VALUES (N'usa', N'TEXAZ', 456328)
GO



Next create dataset in your Report calling the above table.

 1-Add three columns countries state and Population in your dataset

2-Create a Parent Group-Group by Country

 3- Add Country fields above States

 4- Delete the Column generated after creating Parent Group

 5-Delete the country column also.
 
6- Set the visibility of Details to be Hidden and set the Display can be toggled with item-Select countries text box which you added above state as below screenshot..

Design Screenshot


Once you are done preview the Report .

 Countries and State Name will come in a single column as required screenshot.

Note: Drilldown two columns in a single column is little complicated process .So, feel free to ask me if you have same requirement like this I will be happy to help you.