Print Page | Close Window

Date formula (Weekends only)

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19780
Printed Date: 02 May 2025 at 2:18pm


Topic: Date formula (Weekends only)
Posted By: brent21090
Subject: Date formula (Weekends only)
Date 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.
 
 


Replies:
Posted By: lockwelle
Date 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

Posted By: hilfy
Date 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


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: lockwelle
Date 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

Posted By: brent21090
Date Posted: 18 Jul 2013 at 10:47am
Thank you very much- I will try the SQL Expression.


Print Page | Close Window