use select expert to only show data for Saturdays and Sundays

[rmcguire80]

Im trying to run a report which only displays data for Saturdays and Sundays during 2012, the field that I would use would be an actual date field displaying for example December 8, 2012

Is there a way to include all Saturdays and Sundays for 2012 to only run the report for those days of the week?

 

[IanWaterman]

Try

Year(yourdatefield) = 2012
and dayofweek(yourdatefield) in [1,7]

Dayofweek returns 1 for sunday and 7 for saturday

Ian

 

[rmcguire80]

can this be put in the select expert so I would just put

Year(yourdatefield) = 2012
and dayofweek(yourdatefield) in [1,7]

 

[rmcguire80]

when I put that in the select expert the report will not run...the only fields I have displaying are what is the SHIPMENTNUMBER and the PICKUPDATE (the date that the shipment nubmer is being picked up)

 

[CoSpringsGuy]

Just to clarify Ians post... yourfield is supposed to be the field from your database.. so your selection formula would read


Year({PICKUPDATE}) = 2012
and dayofweek({PICKUPDATE}) in [1,7]

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[rmcguire80]

that didn't seem to work, when I run my report it times out....I just have one table SHIPMENTS and two fields

RECNO and PICKUPDATE to display those records which are on each Saturday and Sunday for the date range I specify

 

[CoSpringsGuy]

My guess is pickupdate is not an indexed field and the there is a lot of data in the table?

Can you use an indexed field from the database to slim down the number of records which come back? For example if there was an orderdate field that was indexed and you only really needed to see those orders after October first


{Orderdate} >= date(2012,10,1)
and
(
Year({PICKUPDATE}) = 2012
and dayofweek({PICKUPDATE}) in [1,7]
)

Thats the only reason I could think that the report is timing out ..
are you linked to any other tables?
What database are you reporting from?



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[IanWaterman]

The table is obviously very and a CoSpringguy says the datefield is probably not indexed also the Select formula will not parse to the database in the SQL.

I would change report to use a command. If you are using SQL sever command would be something like

select * from
yourshipmenttable
where year(pickupdate) = 2012
and DATEPART(dw, pickupdate) in (1,7)

Ian