Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: Date formula (Weekends only) Post Reply Post New Topic
Author Message
brent21090
Newbie
Newbie


Joined: 20 Apr 2009
Online Status: Offline
Posts: 31
Quote brent21090 Replybullet Topic: Date formula (Weekends only)
    Posted: 17 Jul 2013 at 8:57am
I need help with a date formula I can use to return data reported only during the weekend for a time period of a year.
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 18 Jul 2013 at 6:36am
if you are just using crystal (no stored procs) and are returning lots of dates and only want weekends...

what I would do is in the record selection formula (Report/Selection Formula/Record) I would place a condition like:
DayOfWeek({table.dateField}) = 7 OR //Saturday
DayOfWeek({table.dateField}) = 1    //Sunday


HTH
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 18 Jul 2013 at 8:59am
If you don't have a lot of data to process, then lockwelle's suggestion should work fine.  However, because of the use of the Crystal "DayOfWeek" formula, the selection conditions will not be pushed to the database.  Instead, Crystal will pull all of the data into memory and filter it there, which can significantly impact the speed of the report.
 
Something you can do go get around this if you're just connecting to tables in the report (as opposed to a command, universe, or BEx query) would be to create a SQL Expression that converts the date to the day of the week and then use that in your selection criteria.
 
For SQL Server, your SQL Expression would look something like this:
 
DatePart(dw, MyDB.dbo.MyTable.MyDateField)
 
In Oracle, it would look like this:
 
To_Number(To_Char(MyTable.MyDateField, 'D'))
 
-Dell
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 18 Jul 2013 at 10:18am
Yeah, I wasn't too enamored of my idea, just because it does pull in all of the data, which I hate.

Usually, I would create a stored proc and use that to filter the data, and then pull or push the data to report
IP IP Logged
brent21090
Newbie
Newbie


Joined: 20 Apr 2009
Online Status: Offline
Posts: 31
Quote brent21090 Replybullet Posted: 18 Jul 2013 at 10:47am
Thank you very much- I will try the SQL Expression.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.017 seconds.