Dynamic Headings in a crosstab query

[Harry]

I have a sales report containing [PartNumber], [TransactionDate], [Qty],
[CostCenter], [UnitofMeasure], [TransactionAmount] with data from the last
12 months. The table is linked to a report created in Crystal Report.
Creating a crosstab to sum [Qty] by month[TransactionDate] is no problem.
[CostCenter], [UnitofMeasure], [TransactionAmount] become Row Headings. But I
would like the column heading to read Jul '06 (This Month -12), Aug '06(This
Month -11), Sep '06(This Month -10), ... Jun '07(This Month -1).
). And change dynamically when the report changes. Is this possible?

 

[KARL DEWEY]

Not exactly what you asked for but try this --
PIVOT Format([TransactionDate], "yyyy mm") & "(This month - "
&DateDiff("m",[TransactionDate],Date())&")";

 

[Harry]

Thank you Karl. That gets me closer. I must apologies, the (This Month -1)
was for clarification. I did not need it to display so I took it out.

Not exactly what you asked for but try this --
PIVOT Format([TransactionDate], "yyyy mm") & "(This month - "
&DateDiff("m",[TransactionDate],Date())&")";
--
KARL DEWEY
Build a little - Test a little

I have a sales report containing [PartNumber], [TransactionDate], [Qty],
[CostCenter], [UnitofMeasure], [TransactionAmount] with data from the last
12 months. The table is linked to a report created in Crystal Report.
Creating a crosstab to sum [Qty] by month[TransactionDate] is no problem.
[CostCenter], [UnitofMeasure], [TransactionAmount] become Row Headings. But I
would like the column heading to read Jul '06 (This Month -12), Aug '06(This
Month -11), Sep '06(This Month -10), ... Jun '07(This Month -1).
). And change dynamically when the report changes. Is this possible?