Global Variable and MTD

[mirogak]

Hi,

Platform: BO CR XI R2 (Version 11.5) and Oracle 8i

I am trying to create a MTD report. I can't use CR's "MonthToDate" feature because our fiscal Calendar Year is set up differently than the "normal" calendar year. For example, our months don't begin at the 1st of every month but rather the 27th or 28th of the previous month. This month is tracked in a field called PERIOD , in the format "200612", meaning 2006 December.

That's why rather than using a record selection filter such as
{TABLE.INVOICE_POSTED_DATE} is in the period MonthToDate ,
instead I want to use a filter such as
{TABLE.PERIOD} = Year (currentDate) & Month (currentDate),
The problem with this filter is that it won't work when the the month (PERIOD) changes on the last few days of the actual month (after the 27th or the 28th)

So I wrote a formula to determine the correct month to filter by, using a global variable:

Global StringVar tempMonth;

if {TABLE.PERIOD} = Year (currentDate) & Month (currentDate) then
tempMonth := Year (currentDate) & Month (currentDate)
else if {TABLE.PERIOD} = Year (currentDate) & Month (currentDate) + 1 then
tempMonth := Year (currentDate) & Month (currentDate) + 1;


Now, that I have the correct month identified using a global variable , I want to modify my selection formula as:

{RECASTED_ALL.PERIOD} = tempMonth

But when I run this filter, I get an error message in the above selection statement saying that CR expects a string, date, number, char, etc instead of the tempMonth . My question is that "tempMonth" is already defined as a Global String variable; why does it not accept/recognize it?

We should be able to define global variables anywhere we want and use them everywhere else in the report, right?

Any ideas whats going on here?

L8R
mirogak

 

[lbass]

Where did you define the variable? The definition belongs in the record selection formula area, along with your final statement.

-LB

 

[synapsevampire]

I think you want to reconsider your archiecture here.

And you haven't defined the proper month here, you're instead telling Crystal NOT to filter the data on the database, instead it should return ALL rows, and then set a variable based on what was returned, which doesn't do anything anyway, except set a variable.

You don't filter data by fetching data {table.period} and then expect it to filter itself prior to being fetched...

So you make loose statements such as the "rather the 27th or the 28th..."

You need to give us facts about when each month starts on each day.

As for your syntax, the error is because you state that you are using a string variable, and then try to populate it with numerics.

Convert the values, as in:

totext(Year (currentDate),0,"") & totext(Month (currentDate),0,"")

But again, you logic doesn't hold up, and it won't pass to the database.

What you should have is something like:

if month(currentdate) in [1,2,3,4]
and
day(currentdate) > 26 then
{table.period} = totext(year(currentdate),0,"") & totext(month(currentdate),0,"")
else
...

I won't even bother elaborating on this at this point, as you need to define the rules.

-k

 

[mirogak]

lbass and synapsevampire !!!

Thanks for your inputs.

lbass , to your point, you're correct, previously i had defined the global variable as a "stand alone" formula and I was then calling it in my record selection formula. That didn't work perhaps because the global variable formula wasn't "executed" at that point in time. But when I incorporated the definition of the global variable into the selection formula, it worked.
Thanks,

synapsevampire, I am not fetching the entire dataset, that's why I had the record selection formula {RECASTED_ALL.PERIOD} = tempMonth . I guess the problem is in the fact that I am calling the "tempMonth" global variable which hadn't been executed at that point in time.

As for the misleading statement "the 27th or 28th of the previous month" I should have stated that every fiscal month is defined in the beginning of the year. Some of our fiscal months begin on the 26th, some on the 27th, and some on the 28th of the preceding month.

For example, if TABLE.INVOICE_POSTED_DATE is 11/28/2006, the "actual" month is 11, but our "fiscal" month TABLE.PERIOD would have 12 in it.

That's why, in order to get a "MTD" picture I have to use the TABLE.POST_PERIOD in my record selection. And I can't just say {TABLE.POST_PERIOD} = Month(currentDate) for obvious reason. I have to do some calculations as mentioned above, right?

L8S,
mirogak