Multi-level grouping drill down SSRS report

[Paul]

I have the following dataset:

col_group col_province col_health_region col_city_region col_hospital
col_total col_median col_25_percentile col_75_percentile
province BC 661 65 53 75
health_region BC east 499 56 45 73
city_region BC east city_east_1 143 69 105 89
hospital BC east city_east_1 hospital_east_11 58 54 58 65
hospital BC east city_east_1 hospital_east_12 85 15 47 24
city_region BC east city_east_2 214 74 111 85
hospital BC east city_east_2 hospital_east_2 214 74 111 85
city_region BC east city_east_3 72 79 88 78
hospital BC east city_east_3 hospital_east_31 27 58 14 23
hospital BC east city_east_3 hospital_east_32 45 21 74 55
city_region BC east city_east_4 70 127 84 140
hospital BC east city_east_4 hospital_east_41 48 72 42 66
hospital BC east city_east_4 hospital_east_42 22 55 42 74
health_region BC south 162 53 47 51
city_region BC south city_south_1 39 55 35 57
hospital BC south city_south_1 hospital_south_11 14 11 62 66
hospital BC south city_south_1 hospital_south_12 25 74 47 44
city_region BC south city_south_2 36 65 77 65
hospital BC south city_south_2 hospital_south_2 36 65 77 65
city_region BC south city_south_3 44 37 42 48
hospital BC south city_south_3 hospital_south_31 15 22 34 52
hospital BC south city_south_3 hospital_south_32 29 45 74 34
city_region BC south city_south_4 43 47 65 72
BC south city_south_4 hospital_south_41 43 47 65 72



I want to create a multi-level drill down report with the highest level as
"province" then "health_regiion" and then "city_region" and finally the
"hospital". When the report first open, it will show a single row of data
which is the "province" with the col_total, col_median, col_25_percentile
and col_75_percentile. Then user can click on the "province" cell to toggle
the next level data which is the health_region and it will shows two rows of
data "east" and "south" with the col_total,........col_75_percentile etc.
And so on user can drill down to the "hospital" level. I want to show the
data the way it is at the dataset (ie no aggregation). Can it be done on a
single standard table report with four level groupings? I believe that the
trick is on how to define the grouping on each of the grouping level. I have
spent the last two days on it without success. Please can someone make my
day. Thanks

[Paul]

Just realize that the dataset I posted is not very clear. The dataset has 9
columns with the prefix "col_". Each row in the dataset has the four numeric
values populated for the col_total, col_median, col_25_percentile and
col_75_percentile. The col_group shows the grouping of that row, eg
col_group with "province" will show "BC" for the col_province and null value
for the col_health_region, col_city and col_hospital. as for the col_group
with col_group "health_region" it will show "BC" for the col_province" and
"east" or "south" for the col_health_region and null value for teh
col_city_region and col_hospital. And so on for the col_city_region showing
all the possible values except null value for the col_hospital. The
col_group "hospital" will show values for all columns. Hope it is clear.
Thanks.

Post by Paul
col_group col_province col_health_region col_city_region col_hospital
col_total col_median col_25_percentile col_75_percentile
province BC 661 65 53 75
health_region BC east 499 56 45 73
city_region BC east city_east_1 143 69 105 89
hospital BC east city_east_1 hospital_east_11 58 54 58 65
hospital BC east city_east_1 hospital_east_12 85 15 47 24
city_region BC east city_east_2 214 74 111 85
hospital BC east city_east_2 hospital_east_2 214 74 111 85
city_region BC east city_east_3 72 79 88 78
hospital BC east city_east_3 hospital_east_31 27 58 14 23
hospital BC east city_east_3 hospital_east_32 45 21 74 55
city_region BC east city_east_4 70 127 84 140
hospital BC east city_east_4 hospital_east_41 48 72 42 66
hospital BC east city_east_4 hospital_east_42 22 55 42 74
health_region BC south 162 53 47 51
city_region BC south city_south_1 39 55 35 57
hospital BC south city_south_1 hospital_south_11 14 11 62 66
hospital BC south city_south_1 hospital_south_12 25 74 47 44
city_region BC south city_south_2 36 65 77 65
hospital BC south city_south_2 hospital_south_2 36 65 77 65
city_region BC south city_south_3 44 37 42 48
hospital BC south city_south_3 hospital_south_31 15 22 34 52
hospital BC south city_south_3 hospital_south_32 29 45 74 34
city_region BC south city_south_4 43 47 65 72
BC south city_south_4 hospital_south_41 43 47 65 72
I want to create a multi-level drill down report with the highest level as
"province" then "health_regiion" and then "city_region" and finally the
"hospital". When the report first open, it will show a single row of data
which is the "province" with the col_total, col_median, col_25_percentile
and col_75_percentile. Then user can click on the "province" cell to
toggle the next level data which is the health_region and it will shows
two rows of data "east" and "south" with the
col_total,........col_75_percentile etc. And so on user can drill down to
the "hospital" level. I want to show the data the way it is at the dataset
(ie no aggregation). Can it be done on a single standard table report with
four level groupings? I believe that the trick is on how to define the
grouping on each of the grouping level. I have spent the last two days on
it without success. Please can someone make my day. Thanks

[m***@gmail.com]

Paul,

Why don't you post a screenshot of the steps you followed? Explaining the way you did is more confusing than the problem itself.