Calculating growth rate in Cross tab

[RonAle]

Hello all,

I'm able to do a simple crosstab query with Category and Year as row headers and Quarter as column header. I also added a column that provides Grand total for Year.
Where I'm stuck is trying to compute year-over-year growth for total year and for each Quarter compared to same Quater previous year.

So in theory I would have 10 columns. QTR1, QTR1Growth%, QTR2, QTR2Growth%, QTR3, QTR3Growth%, QTR4,QTR4Growth%, Year Total, YearGrowth%,

Is this possible in a crosstab query??
Ultimately I would like to base a report on this query.
Something like this:

Category 1
QTR 1 QTR 2
Sales Growth% Sales Growth% ......
2003
2004
2005
2006

Category 2
QTR 1 QTR 2
Sales Growth% Sales Growth% ......
2003
2004
2005
2006

Total Category
QTR 1 QTR 2
Sales Growth% Sales Growth% ......
2003
2004
2005
2006


What would be the recommended approach?
Should I try to compute growth rate in the report or should I do it in the query and if yes then how???

Thanks
RonAle

 

[MichaelRed]

placing the calculation of values in the report will both simplify and speed up the query while slowing down the report. using a self join of the XTab query (with appropiate joining) will provide all the values for y to y and q to q values in a single record, simplifying the calculations.




MichaelRed