Rolling Accounting Period Report

[tcimis]

We have a report which we total certain information by accounting period. The report currently has columns for Past, Period 1, Period 2, ... , Period 12, Future. It only prints the current year. We would like the report to roll periods so it prints Past, Current period, Next period, ..., Future.
Example: If sysdate falls in period 6, the report would have Past, Period 6(2002), Period 7(2002), ... , Period 1(2003), ... , Period 5(2003), Future.
We are currently using Crystal 6. Any assistance would be greatly appreciated.

 

[chelseatech]

I've done a few reports like this now and the solution is to write a few formulas.

A formula for each column heading. If you are using calendar month, then it's easy, but I've also worked out the dates for 4,4,5 (see Crystal Clear a few months ago for details on that one).

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)

Then for subsequent columns just change the -1 to -2, -3, etc.... Place these in your headings and format them and Mar-99

The a formula for each column (col0)
if {table.date) >= Year(Head0) and
{table.date} < Currentdate then {table.val} else 0

Formula for the previous month
if {table.date) >= Head1 and
{table.date} < Head0 then {table.val} else 0

The a formula for each column just changing Head1 and Head0 to the next headings. Note each column uses it's own heading as a start value, and the prior heading as a non inclusive end value.

The same approach works for Quarters, LY equivalent month, etc.
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[chelseatech]

A comment on my previous message, you could also use DateAdd to calculate the headings,

tcimis is using CR6 and DateADD is not available in that version. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[tcimis]

Thank you for your assistance. I have the headings working but I'm having problems with the details. I am adding 1 to the header because I want current period plus 11 periods in the future.

Here is the formula for Head0:
Date(Year(CurrentDate),month(CurrentDate),1)

Here is the formula for Head1:
numbervar y:=year(CurrentDate);
numbervar m:=month(CurrentDate)+1;
if m<1 then(m:=m+12; y=y+1);
Date(y,m,1)

Here is the formula for Col0:
if {RCIMG015.SCHED_SHIP_DTTM} >= Year({@Head0}) and
{RCIMG015.SCHED_SHIP_DTTM} < CurrentDate
then {RCIMG015.EXP7_42} else 0

When I run check on this it is telling me &quot;A date is required here&quot;. The Sched_Ship_Dttm includes the date and time. Could this be a problem?

Also I will need the 4,4,5 formula. I didn't understand your message: see Crystal Clear a few months ago. Did a search on clear, nothing came up.

THANKS!

 

[synapsevampire]

Change:

if {RCIMG015.SCHED_SHIP_DTTM} >= Year({@Head0}) and

To:

if year({RCIMG015.SCHED_SHIP_DTTM}) >= Year({@Head0}) and

or

if {RCIMG015.SCHED_SHIP_DTTM} >= date(Year({@Head0},1,1))

replacing 1,1 if need be.


I didn't delve into your logic, but that's part of your problem.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[tcimis]

Thanks! That helped. I now need the formulas for a 4,4,5 accounting calendar. Didn't understand the message see Crystal Clear.

Thanks again for all the assistance.

 

[tcimis]

I have the calendar month report working now. Thanks for all the assistance. I found the Crystal Clear. I'm now just waiting for the next issue which discusses how to use the accounting period for previous years(future years). Hopefully it will also give me some other clues because the select case doesn't appear to work with version 6.

Thanks again!

 

[chelseatech]

You can change the select case to a mutliple if then else, so

Select a
case x: do this
case y: do that
default: somthing

changes to..
if a = x then do this else
if a = y then do that else
something


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[tcimis]

Thanks for all your assistance. I have the report working now for all except one column, future column. The report is:
past, 6/02, 7/02, 8/02, 9/02, 10/02, 11/02, 12/02, 1/03, 2/03, 3/03, 4/03, 5/03, Future. The periods will change since the 6/02 is the current period. The future column should be all records scheduled to ship after the end of the last period on the report, in this example 5/23/03. This is what I was trying but it doesn't work. I think the year is messing me up.

Head11 formula:
numbervar y:=year(CurrentDate);
numbervar m:=month(CurrentDate)+11;
if m>12 then(y:=y+1; m:=m-12);
Date(y,m,1)

Future formula:
If{RCIMG015.SCHED_SHIP_DTTM} >
If month({@Head11}) = 12 then date(year({@Head11}),12,31) else
{@Period} + [0,28,63,91,119,154,182,210,245,273,301] [month({@Head11})]
then {RCIMG015.EXP7_42} else 0

 

[tcimis]

I have the future column working now. Thanks again for all the assistance.