How to pull monthly history when query spans two fiscal years

[cspuckett]

I have a table with financial information with the following (basic) layout: Year, GL Account, January, February, March,...December.

So, for GL Account 40000, you might see:

2007, 40000, $100, $110, $90,....$150
2008, 40000, $105, $120, $95,....$160

The report will show the current (parameter based) period and year (i.e., user enters 2008, period 9) plus the last 11 months.

As you can see, this means the report covers data from Oct-2007 to Sept-2008.

So, the report should look like:

Account Oct07 Nov07 Dec07 Jan08...Sep08
40000 $180 $170 $150 $105 $200

I have tried multiple means to acheive this to no avail. My data is either always 2008 data or 2007 data.

 

[cspuckett]

FYI - I'm getting closer by using the Cross-Tab format. I'll need a couple calculated members.

Right now the report runs for 2008 (Jan-Dec). I still need to figure how to make it parameter based.

I'm familiar with the DateAdd function and think I'm getting close.

 

[dgillz]

What do you mean "parameter based"? If the query already controls the date range what other parameter are you concerned with?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[IanWaterman]

You could of course start by shooting who ever designed the table!

You could change select statement to

YearField >= {yearparam}-1

This will then bring back your two years. Then you will need a hellishly complicated manual cross tab to pick out the required 12 months.

Would be better if the table was restructured to give you a recordset for each month.

So you may be better off building a command to do that

Select Year; gl, jan, 1 as Mth
from table
where year >= {yearparam}-1
Union
Select Year; gl, feb, 2 as Mth
from table
where year >= {yearparam}-1
union
Select Year; gl, mar, 3 as Mth
from table
where year >= {yearparam}-1

etc

Ian

 

[cspuckett]

Yes, I do hate this particular table. And, when I made the update that I have it (partially) working, I switched to the detail transaction table. I was trying to build this off of a consolidated GL table...but, if my performance is acceptable on the detail table, I'll go that route.

The "parameter" part of the equation is that this report will give the user the option to run for a prior month. So, he might want to see the report for January 2009, and the preceding 11 months (even though we are now in March). So, prior to the report running, I prompt for FISCAL YEAR and ACCT PERIOD.

So, yes the query plus parameters should control the data selection. I obviously want to hit as few records as possible for performance reasons, while still getting everything I need.

 

[IanWaterman]

Building the command would keep the recordset to a minimum. Or you could build a command off the transactions tables with the two params you mentioned again reducing the recordset.

Ian

 

[cspuckett]

Thanks everyone. I have it working now.

Based on FISCAL YEAR, I pull FISCAL YEAR and "FISCAL YEAR - 1". I use the period to then further restrict based on a DateAdd date range. Since I have (for example), Year 2009, Period 1...I calculate a date of 2/1/2009.

Then, refine my cross tab dataset with this in the select:
{%PostingDate} >= DateAdd ("m", -12, {@MyCalcDate}) and
{%PostingDate} < {@MyCalcDate}