Print Previous Day (default) 1

[tj007]

Hello, I know this is simple, but I cant figure it out. I have created a report with crystal 8.0. The report has a datetime parameter field that prompts the user to enter a specific date. It works perfectly. However, we all know that some people are lazy. Is there a way to have the report generate records for the previuos or last day from the current day or date?

Thanks

 

[rosemaryl]

Dear tjoo7

You should be able to do:

{yourdatetimefield} in (currentdate -1)

and generates the following in the SQL Query for if run for today:

WHERE
Table."DateTimeField" >= {ts '2003-07-16 00:00:00.00'} AND
Table.&quot;DateTimeField&quot; < {ts '2003-07-17 00:00:00.00'}

Hope that helps,
ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rhinok]

For previous business day, use the following:

{yourtable.yourdatefield} =
Switch
(
DayOfWeek(CurrentDate) = 1,(CurrentDate - 2),
DayOfWeek(CurrentDate) = 2,(CurrentDate - 3),
DayOfWeek(CurrentDate) In 3 To 7,(CurrentDate - 1)
)

This also passes data to the db. I ran the report tonight (07/17) so data was returned for 07/16:

WHERE
v_view.&quot;createdate&quot; >= {ts '2003-07-16 00:00:00.00'} AND
v_view.&quot;createdate&quot; <= {ts '2003-07-16 23:59:59.00'}

 

[rosemaryl]

Dear Rhinook,

Very nice!* The switch is one of my favorites.

I just took tj007 to his statement of yesterday... you took the extra step to last business day.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rhinok]

Thanks Rosemary!

TJ007, if you look at the 'WHERE' clause generated by the examples Rosemary and I provided, you'll notice that the date ranges are different:

WHERE
Table.&quot;DateTimeField&quot; >= {ts '2003-07-16 00:00:00.00'} AND
Table.&quot;DateTimeField&quot; < {ts '2003-07-17 00:00:00.00'}

VS.


WHERE
v_view.&quot;createdate&quot; >= {ts '2003-07-16 00:00:00.00'} AND
v_view.&quot;createdate&quot; <= {ts '2003-07-16 23:59:59.00'}

The difference is caused by date conversion. Rosemary's report leaves DateTime values as Datetime. My report converts DateTime to Date (File|Report Options). Either way, you should get the same results. I generally convert all DateTimes to Date unless I explicitly need the time data...

 

[rosemaryl]

Dear Rhinok,

Yes, I always need to work with DateTime values in my reports since I deal a lot with call aging etc for Service Centers and rarely need just the dates.

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.