Business Days

[DKirksgirl]

I have to create a report in which the time periods will be outlined like the following:

Monthly -

See example below:

Jan 14th business day through Feb 13th business day (run on Feb 14th business day for this time period)

So, in a nutshell the report will run monthly on the 14th business day, for the prev month 14th business day through the current month 13th business day

Quarterly -

Works kind of the same way, except

Quarters would be defined like the following.

Jan 14th business day through Apr 13th business day (run on Apr/14th business day for previous Jan 14th business day through Apr 13th business day.

Apr 14th business day through Jul 13th business day (run on Jul/14th business day for previous Apr 14th business day through Jul 13th business day

Jul 14th business day through Oct 13th business day (run on Oct/14th business day for previous Jul 14th business day through Oct 13th business day

Oct 14th business day through Jan 13th business day (run on
Jan 14th business day of the next year for previous year Oct 14th business day through next year Jan 13th business day.

Annual

Defined as Jan 14th business day current year through Jan 13th business day next year (to be run on Jan 14th business day of next year)

I know this is a lot - but I wanted to be thorough. I do wish to exclude holidays.

Thanks in advance for your help.
dkirksgirl@comcast.net

 

[synapsevampire]

Create 2 formulas, @startdate and @enddate to be referenced by the reports reocrd seldction as required:

Here's the code for each criteria for startdate:

// If Month
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,14,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,14,0,0,0)

// If Quarter
If (Month(CurrentDate) = 1) Then
DateTime(Year(CurrentDate),10,14,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-3,14,0,0,0)

// If Annual
DateTime(Year(CurrentDate)-1,1,14,0,0,0)

Here's the code for each for the end dates:

// If Month
DateTime(Year(CurrentDate),Month(CurrentDate),13,0,0,0)

// If Quarter
DateTime(Year(CurrentDate),month(currentdate),13,0,0,0)

// If Annual
DateTime(Year(CurrentDate),1,13,0,0,0)

Now reference them in the record selection formula as required, as in:

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

-k

 

[DKirksgirl]

But will this give me BUSINESS DAYS? or just CALENDAR DAYS?

 

[synapsevampire]

Sorry, my usual solution for Business Days is to create a Periods table, which every database should have anyway, with every day in it, and designations for business days, weekends and holidays.

However you can determine business days in CR too.

Go here:

http://support.crystaldecisions.com/docs

Search for:

cr_business_days_hours.zip

-k

 

[DKirksgirl]

Okay - let me start over. What I would like to do is to &quot;redefine&quot; what a month period is, so that for instance, if I input a parameter month of January, then it would know that the January parameter value means the month range from the 14th BUSINESS DAY of January through the 13th BUSINESS DAY of February. That would be considered the month of January.

And so on,
14th bus day of Feb thru 13th bus day of Mar
14th bus day of Mar thru 13th bus day Apr.....etc

(remember-HOLIDAY ARE ALSO EXCLUDED)

Thanks a bunch!

 

[synapsevampire]

This is definitely a candidate for using the real solution, that being a Periods table with this data pre-defined.

I don't know of a way to redefine what Crystal sees as a month, but you can create your own formula to return a value to be used as a month/quarter number, try something like:

If day({table.date}) >14 then
Month({table.date})
else
and day({table.date}) < 113 then
Month({table.date})-1

Whereas a Quarter is slightly more involved in that you'd specifically state the month and day.

As for excluding holidays, check this:

http://support.crystaldecisions.com/library/kbase/articles/c2005297.asp

Crystal's solution is code intensive and not the best way to work around this problem, fix it at the database level.

-k