CrossTab Grouping -- Daily, Weekly, Mothly 1

[skurkal]

I have a parameter driven report that asks a user if they want -- daily (rolling 8 days), weekly (rolling 8 weeks) or mothly (rolling 8 months) worth of data. Based on the parameter entered the Crosstab should group by days, weeks or months.

Data retrived from the database is by days.

e.g.
daily should show
05/13 05/14 05/15 .... 05/21
A 10 0 20 15
B 5 10 15 2

Weekly should show
03/26 04/02 04/09 .... 05/14
A 20 0 25 15
B 10 15 25 4

Monthly shold show
09/2006 10/2006 11/2006 .... 04/2007
A 100 0 200 150
B 50 110 151 200

I can put the same crosstab in subsections a,b,c one for daily, one for weekly and one for monthly and supress the ones not selected. But I was wondering if there was a way to write a formula so I can have only one crosstab.

Thanks,
Sumitra.

 

[lbass]

Create a formula like this:

select {?period}
case "daily" : {table.date}
case "weekly" : {table.date}-dayofweek({table.date})+1
case "monthly" : {table.date}-day({table.date})+1

Use this formula for your column field.

-LB

 

[skurkal]

Thanks! this worked perfectly. Never would have thought of doing it this way. Learnt something new. Thanks!