|
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.
-------------
Thanks,
D. Bodell
|