Determining Fiscal Period 2

[mstrong]

Hi,
I'm using CR10 to write a report which will be set up as a recurring report, to run on the first day of each fiscal period (using a custom calendar in CE10). I need this report to pull data for the previous fiscal period. I have a table that has dates, fiscal months, years, etc, which I can link to my main table using the date field. What I can't figure out is how to get only the records for the previous period. I thought that maybe it would be a matter of using something like today-1 to get yesterday's date, and determine the period from that, but I can't figure it out.

Mike

 

[Nuffsaid]

Hi,

One approach would be to add your “period” table to the report a second time.
Link this 2nd table to your original period table on your fiscal year and fiscal month fields.
Change your record selection formula to use the 2nd table, for example;

{periodtable2.datefield}=currentdate-1

Due to the way the 2 period tables are joined this will cause all records that fall within the same month and fiscal year as currentdate-1 does to be returned.


Nuffsaid.

 

[mstrong]

That's an interesting idea. I'll give it a shot. Thanks!

 

[synapsevampire]

The table which has dates sounds like the key to this. Often people will use the period table as the main table and then join it to the main table by the date.

These period tables will often have specific fields such as fiscal year, fiscal quarter, etc, so you can use this to determine the periods required in the record selection formula (report->Edit Selection Formula->Record), as in:

{period.quarter} = 1
and
{table.date} = {period.date}

I have also generated drop downs which will use the predefined periods as text to determine the dates to pull, so if the user chose last month, I'd have 2 formulas, startdate and enddate, which woudl derive the required dates:

Use chose "last month"

@startdate:
if {?MyParm} = "Last Month" then
dateserial(year(currentdate),month(currentdate)-1,1)
else
if....

@enddate:
if {?MyParm} = "Last Month" then
dateserial(year(currentdate),month(currentdate),1)-1
else
if....

Now the record selection formula shows:

{table.date} >= @startdate
and
{table.date} <= @enddate

So there's a couple of ways described in which to derive dates from periods requested.

-k

 

[mstrong]

Thanks for both posts. I actually tried the first one and it did the trick for me. Thanks guys!

Mike

 

[Nuffsaid]

We use this approach all the time for reports that require no user interaction and must return records based on current date, or some derivative thereof. By messing with the joins you can pull fiscal days, weeks, months, quarters etc.

Glad it worked out for ya...


Nuffsaid.

 

[synapsevampire]

Purdy slick idea there, *nuffsaid*

-k

 

[Nuffsaid]

Thanks Synaps...

I occasionally "have my moments"

Nuffsaid.