I run a lot of reports off of BOE scheduler. Most of them have to have date ranges entered. Instead of using specific dates and scheduling the reports multiple times, I like to use what I call interpreted date codes. These are codes like:
t = today
t-1 = yesterday
t+1=tomorrow
w = current week
m = current month
mb = beginning of current month
me = end of current month
mb-1 = beginning of previous month
mb+1 = beginning of next month
me-1 = end of previous month
etc.
That way I just enter a parameter date code and the report always interprets the code into the correct date.
The formula for this is:
// Accepts a date as a string in mm/dd/yyyy, yyyy-mm-dd, or
// relative (t-45) format. Returns a standard Crystal date.
local numberVar NRange;
local numberVar NOperator;
local stringVar SInterval;
local stringVar STemp;
local numberVar NPos;
if IsDate({?EndDate}) then Date({?EndDate}) //If proper Crystal date
was entered, don't change
else
(
if
InStr({?EndDate},"+")>0 then NOperator:=1 //Plus or minus?
else NOperator:=-1;
STemp:=Replace({?EndDate},"+","-"); //Convert + to -, for
easier string manipulation
NPos:=Instr(STemp,"-");
If
NumericText(Mid(STemp,NPos+1)) then
//Extract and make sure valid number
NRange:=toNumber((Mid(STemp,NPos+1)))
else NRange:=0;
if NPos>0 then
SInterval:=Left(STemp,NPos) //Extract first part. Ex.
"T" or "W"
else SInterval:=STemp;
if
Left(SInterval,1)="T" or SInterval="TODAY" then //Dates relative to Today
CDate(DateAdd("d",(NOperator*NRange),DataDate))
else if
Left(SInterval,1)="W" or SInterval="WEEK" then //Dates relative to this week
CDate(DateAdd("ww",(NOperator*NRange),DataDate))
else if
Left(SInterval,2)="MB" then //Dates relative to beginning of this month
CDate(DateAdd("m",(NOperator*NRange),
DateValue(DatePart("yyyy",DataDate),
DatePart("m",DataDate), 1)))
else if
Left(SInterval,2)="ME" then //Dates relative to
end of this month
DateSerial(year(DataDate), month(DataDate) + (NOperator*NRange) + 1, 1 -
1)
else if
Left(SInterval,1)="M" or SInterval="MONTH" then //Dates relative to this month
CDate(DateAdd("m",(NOperator*NRange),DataDate))
else if
Left(SInterval,2)="YB" then //Dates relative to
beginning of this year
CDate(DateAdd("yyyy",(NOperator*NRange),DateValue(DatePart("yyyy",DataDate),
1, 1)))
else if
Left(SInterval,2)="YE" then //Dates relative to end of this year
CDate(DateAdd("yyyy",(NOperator*NRange),DateValue(DatePart("yyyy",DataDate),
12, 31)))
else if
Left(SInterval,1)="Y" or SInterval="YEAR" then //Dates relative to this year
CDate(DateAdd("yyyy",(NOperator*NRange),DataDate))
else if
Left(SInterval,1)="Q" or SInterval="QUARTER" then //Dates relative to this quarter
CDate(DateAdd("q",(NOperator*NRange),DataDate))
else
// Error trap
DataDate
)
So you create parameters called EndDate and StartDate making them strings. I usually enter in the list of basic values like the ones I have above.
Then you use the above code to create two formulas: InterpretedEndDate and InterpretedStartDate.
You use the InterpretedStartDate and InterpretedEndDate to do your record selections.
I also have created a SQL function that does the same thing only this allows you to pass a date code to a SQL stored procedure and to have the stored procedure interpret the date code. If interested, I can send it to you.
Hope this helps someone.
IP Logged
