Displaying date in Report Header --CR9

[JoyCR9]

In Crystal reports 9, I have the following in my "Show SQL Query"

<snip>
("SERVICE_CALL"."CREATE_DT">={ts '2004-06-19 00:00:00'} AND "SERVICE_CALL"."CREATE_DT"<{ts '2004-07-31 00:00:00'})
<snip>

Everytime I run my report (daily) I have a different date range automatically entered from the following formula:

select currentdate
case Date (2004, 07, 28 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 06, 19) to Date (2004, 07, 30)
case Date (2004, 08, 28 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 07, 31) to Date (2004, 08, 27)
case Date (2004, 09, 18 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 08, 28) to Date (2004, 09, 17)
case Date (2004, 10, 30 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 09, 18) to Date (2004, 10, 29)
case Date (2004, 11, 27 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 10, 30) to Date (2004, 11, 26)
case Date (2004, 12, 31 ): {SERVICE_CALL.CREATE_DT} in Date (2004, 11, 27) to Date (2004, 12, 31)
Default: False

When the above case is TRUE, the report runs.

How do I get the correct date range to automatically print at the top of the report? IE: June 16, 2004 - July 30, 2004

 

[lbass]

Create a second formula:

select currentdate
case Date (2004, 07, 28 ): "June 19, 2004 - July 30, 2004"
case Date (2004, 08, 28 ): "July 31, 2004 - August 27, 2004"

(add in other cases here)

Default: ""

-LB

 

[JoyCR9]

Thanks a bunch.

 

[bdreed35]

Another solution that might make it a bit simple to maintain, would be to break the selection out into 2 variables.

Create 1 formula that returns the Begin Date:
@Begin Date

select currentdate
case Date (2004, 07, 28 ): Date (2004, 06, 19)
case Date (2004, 08, 28 ): Date (2004, 07, 31)
case Date (2004, 09, 18 ): Date (2004, 08, 28)
case Date (2004, 10, 30 ): Date (2004, 09, 18)
case Date (2004, 11, 27 ): Date (2004, 10, 30)
case Date (2004, 12, 31 ): Date (2004, 11, 27)
Default:  Date (1900,1,1)

Create a second formula for the End Date of the range:
@End Date

select currentdate
case Date (2004, 07, 28 ): Date (2004, 07, 30)
case Date (2004, 08, 28 ): Date (2004, 08, 27)
case Date (2004, 09, 18 ): Date (2004, 09, 17)
case Date (2004, 10, 30 ): Date (2004, 10, 29)
case Date (2004, 11, 27 ): Date (2004, 11, 26)
case Date (2004, 12, 31 ): Date (2004, 12, 31)
Default:  Date (1900,1,1)

Change your Record Selection to this:

(if {@Begin Date} <> Date(1900,1,1) then 
    {Orders.Ship Date} in {@Begin Date} to {@End Date}
else if {@Begin Date} = Date(1900,1,1) then
    False
)

Now, create the formula for displaying the first and last day of the range:

totext({@Begin Date},"MMMM d, yyyy") & " - " & totext({@End Date},"MMMM d, yyyy")

This may end up being a tad easier to maintain since it looks like you have to keep building additional cases for the future.

~Brian