SQL Server - Multi-level grouping drill down SSRS report

Asked By Paul

20-Jan-10 10:15 PM

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
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

20-Jan-10 10:46 PM

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
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.