dynamic header for each quarter

[dzavad]

Hi,
I have manual cross-tab report that calculates orders by quarter.
This cross-tab is dynamic, meaning user only inputs End Date and report will show 36 month data back from the Ending date. Basically 3 year span.
After each year it needs to be extra column with the total:
Example headers for each row:
Q1 Q2 Q3 Q4 total Q1 Q2 Q3 Q4 total Q1 Q2 Q3 Q4 total

I didn’t get into the calculation formulas yet. I am stuck with the dynamic headers. It would be easier without Totals column to come up with the formula that I did:
Example of header formula:
numberVar QMonth:=Month(CDate(DateAdd ("q",-2 ,{?Ending Date} )));

if QMonth in [1,2,3] then "Q1-"+Right(totext(Year(CDate(DateAdd ("q",-2 ,{?Ending Date} )) ),0,""),2 )
else if QMonth in [4,5,6] then "Q2-"+Right(totext(Year(CDate(DateAdd ("q",-2 ,{?Ending Date} )) ),0,""),2 )
else if QMonth in [7,8,9]then "Q3-"+Right(totext(Year(CDate(DateAdd ("q",-2 ,{?Ending Date} )) ),0,""),2 )
else if QMonth in [10,11,12] then"Q4-"+Right(totext(Year(CDate(DateAdd ("q",-2 ,{?Ending Date} )) ),0,""),2 )

This works if I just change the numbers (-n) for each column heading.
But I need to add "Total" heading and that where I am stuck.
I need help on figuring out how to make Total column header dynamic.
Example:
if user input 7/3/2009 as the Ending Date, my report will start (right to left)from "Q3-09" header , so it will look like this:
Q4-06 Q1-07 Q2-07 Q3-07 Q4-07 Q1-08 Q2-08 Q3-08 Q4-08 Q1-09 Q2-09 Q3-09
How can I add dynamic totals in-between the years knowing that position for Total will change depending on the Ending date parameter like so:
Ending Date=7/3/2009
A) Q4-06 total Q1-07 Q2-07 Q3-07 Q4-07 total Q1-08 Q2-08 Q3-08 Q4-08 total Q1-09 Q2-09 Q3-09 total

Ending Date=2/3/2009
B)Q2-06 Q3-06 Q4-06 total Q1-07 Q2-07 Q3-07 Q4-07 Total Q1-08 Q2-08 Q3-08 Q4-08 total Q1-09 total



Thank you

 

[lbass]

If you add a column on your date field on change of year as your first column, you will automatically get a Total column.

-LB

 

[dzavad]

Hi lbass,
Thank you for your reply.
I am not sure I understand what you mean.
I don’t use crystal cross-tab. It a manual crosstab I have an issue with. The complexity of the overall report does not allowed me to use Cross-tab. I have to build manual one. And I am not sure how to add the code for Totals. the calculations for each Quarter works fine.

Thanks again

-DZ

 

[lbass]

Sorry, I didn't read your post carefully enough.

The simplest solution might be to have four header sections with the four possible layouts of your formulas with different positions for the total. Then you would conditionally suppress the sections depending upon the quarter of {?EndingDate}.

-LB