Prev: SQL 2005 PDF Export
Next: Can you expand/collapse columns based on a reportitem/field values
From: Paul on 20 Jan 2010 22:15
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


From: Paul on 20 Jan 2010 22:46
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.


"Paul" <paul_mak(a)shaw.ca> wrote in message
news:%23qCECgkmKHA.5612(a)TK2MSFTNGP05.phx.gbl...
>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
>


 | 
Pages: 1
Prev: SQL 2005 PDF Export
Next: Can you expand/collapse columns based on a reportitem/field values