Creating Crosstab with drilldown

[Nabil]

I have to create a report with drill down and I can only think of
crosstab
report to achieve this task. However 2 things I am lacking, first is I
dont know how to enable drill-down on cross tab and secondly how to get
the report look like below...

This is how I would like to see the output...

Date China Brazil USA
Jan 58,999.00 61,258.00 71,244.00
Jan 76,078.67

--------------------------------------------------------------

And then upon drilling into USA...

Date NY CA
Jan 71,244.00
Feb 76,078.67

-------------------------------------------------
This is how the table structure looks

Date Country Region City Amount
Jan China Hong Kong Christchurch $58,999.00
Jan Brazil Rio Rio Grande $61,258.00
Jan USA NY Buffalo $71,244.00
Jan USA CA Los Angeles $76,078.67

How can I create a report like this one with drill-down so I can
drilldown all the way till City. I would really appreciate if anyone
could help me out in this. I was thinking of crosstab but if anyone
have a better solution I am open to suggestions.
Thank you for your help.

- Nabil

[Gert van der Kooij]

In article <11**********************@l41g2000cwc.googlegroups .com>,
Nabil (ns******@gmail.com) says...

How can I create a report like this one with drill-down so I can
drilldown all the way till City. I would really appreciate if anyone
could help me out in this. I was thinking of crosstab but if anyone
have a better solution I am open to suggestions.
Thank you for your help.

- Nabil

In the DB2 Cookbook from Graeme Birchall you can find some examples.
You can find it at:
http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

Check the grouping set examples at page 191.

Kind regards, Gert

[Nabil]

Thank you.

Gert van der Kooij wrote:

In article <11**********************@l41g2000cwc.googlegroups .com>,
Nabil (ns******@gmail.com) says...

How can I create a report like this one with drill-down so I can
drilldown all the way till City. I would really appreciate if anyone could help me out in this. I was thinking of crosstab but if anyone
have a better solution I am open to suggestions.
Thank you for your help.

- Nabil

In the DB2 Cookbook from Graeme Birchall you can find some examples.
You can find it at:
http://ourworld.compuserve.com/homep...l/HTM_COOK.HTM

Check the grouping set examples at page 191.

Kind regards, Gert

[Zining Fu via DBMonster.com]

Try this:

select date, sum(ch_amt), sum(br_amt), sum(us_amt)
from (
select
date,
case when country = 'China' then amount else 0 end ch_amt,
case when country = 'Brazil' then amount else 0 end br_amt,
case when country = 'USA' then amount else 0 end us_amt
from <your_tbl_nm>
) tmp

[Nabil]

Thank you very much. This really helps. I did however figure out a way
to get around with drilldown in crosstab with the use of hyperlinks.