SEO

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

In article <1113572121.026196.313660@l41g2000cwc.googlegroups .com>,
Nabil (nsuleman@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

Thank you.

Gert van der Kooij wrote:
> In article <1113572121.026196.313660@l41g2000cwc.googlegroups .com>,
> Nabil (nsuleman@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

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

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.