Record Selection for Current Week

[GCL2007]

I'm trying to create an Overdue Sales Order Backlog report. The report will print all Orders that are late shipping to customer. I have a PromiseDate field on each Sales Order, so I typically would want to report all orders with PromiseDate<today. However, in this report I want to include all orders that have a PRomiseDate of sometime this current week (Sunday to Saturday). Any suggestions on how I could do this?

 

[lbass]

datepart("ww",{table.Promisedate}) = datepart("ww",currentdate) and
year({table.Promisedate}) = year(currentdate)

Better yet, create SQL expressions using the week() and year() functions for both {table.promisedate} and what might be shown in the function list as currdate() or now(), as in:

[{%wkcurr}]
{fn week({fn currdate()})}

[{%wkprom}]
{fn week(table.`promisedate`)}

Then use a record selection formula like this:

{%wkcurr} = {%wkprom} and
{%yrcurr} = {%yrprom}

The advantage of the SQL expressions is in report speed.

-LB

 

[CR85user]

Another approach is a little more simplistic:

//Selection criteria
{table.date} > (currentdate - dayofweek(currentdate))

This would leave crystal to perform the op though rather than passing to the db - so it is a question of performance and preference really.

If records in the millions pass to db - otherwise if performance is ok with the above then go for it.

(I tried on several thousand didn't see too much impact).

'J

CR8.5 / CRXI - Discovering the impossible