Rolling 24 months with a manual crosstab report?

[Bennie47250]

Using Crystal 7.0 on a MS SQL database

I believe how I have done this is considered a “Manual Cross Tab”.

I have formulas for each month over a 24 month period. Each month is hard coded with a month and year. Here is an example of one of the formulas.
if {MyTable.Month Key} = 7 and {MyTable.Period Key} = {MyOtherTable.Current Year}
then {MyTable.Qty Shipped} else 0

I then subtotal this formula in Group Footer 1

How can I convert the existing formulas to rewrite them so I can get a rolling 24 months?
For example when the report is ran in January 1 2012, the data would be for the range of Jan 1 2010 to Dec 31 2011
When ran in Feb 2012 the date range would be Feb 1 2010 to Jan 31 2012
When ran in March 2012 the date range would be Mar 1 2010 to Feb 28 2012

Thanks
Bennie

 

[Madawc]

In higher versions of Crystal, this could be done with

DateDiff("m", {your.date}, currentdate)

This would definitely have worked in Crystal 8.5, though as far as I recall, it did not let you build automatic crosstabs from formula fields.

Hope this helps.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Bennie47250]

Unfortunately version 7 does not have the date difference function.

However I did find an old posting and using the formulas suggested in that post.

A formula for each column heading. If you are using calendar month, then it's easy,…

The formula for the current month column heading (Head0)is
Date(Year(CurrentDate),month(Currentdate),1)

The formula for the prior month (Head1) is
local numbervar y:=year(Currentdate);
local numbervar m:=month(CurrentDate)-1;
if m<1 then (m:=m+12; y:=y-1); // have gone back a year
Date(y,m,1)

To see the full tek-tips posting go to

http://www.tek-tips.com/viewthread.cfm?qid=288394