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.