12-month rolling Cross Tab Query

[JasTa84]

Hello,

I have a cross tab query with state as a row header and the first day of the month as a column header. This covers 3 years. As a value I have a cost.

Instead of showing the cost for each month (ie March 2009 only) I would like to see the 12 month rolling total under each month (ie Total cost April 2008 - March 2009 for my March 09 value.)

Can anyone help me do this? Thanks in advance

 

[sportsguy]

I do not use a cross tab query for my reports. I use a plain query, with some VB code. First, i use a year month field of long integer, with the year as the first four digits, and the month as the fifth and sixth digit. 201001 is January, 2010, etc. and for now, call the field YYYYMM

Then using that as the period field, i build a query that the latest month is entered, say 200912, December, 2009 and the query's where criteria is where <=[Enter Current Period] and >[Enter Current Period]-100.

So then i have a piece of VB code which calculates a YYYYMM given the start month and how many months prior do you want to select.

Function PriorYYYYMM(anyYYYYMM as long, priorperiod as long) as long

PriorYYYYMM = anyYYYYMM - priorperiod

end if

Then for the first period on the report, the query formula is

A:Iif([YYYYMM]=PriorYYYYMM([Enter Current Period],12), anyValue, 0)
B:Iif([YYYYMM]=PriorYYYYMM([Enter Current Period],11), anyValue, 0)
....
L:Iif([YYYYMM]=PriorYYYYMM([Enter Current Period],0), anyValue, 0)

The report is static, Field A is always 12 months behind the [Enter Current Period], Field B is always 11 months behind, etc.

Works like a champ. . . i hate reports with cross tab queries. . .

sportsguy

 

[JasTa84]

Thanks Sportsguy. I've never used VB code in the query builder, could you post a more detailed example of how this can be done?

If I wanted to use the cross-tab format, is there a way to use Dsum to calculate the 12-month rolling total?

Thanks