| Author |
Message |
ITSpecialist
Newbie
Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
|

Topic: date range - current day to last friday Posted: 27 Dec 2007 at 8:00am |
|
Can anyone help?
I have been trying to figure out how to run a report on a date range from CurrentDate to last friday. Our work week is Friday to Thurs and I need to schedule a report to run daily showing the totals from the previous friday to the currentdate without using any dates.
Can someone offer suggestions, and if you need more information, let me know.
Thanks.
|
IP Logged |
|
|
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|

Posted: 27 Dec 2007 at 11:28am |
|
I've done something very similar, to calculate the date of the Sunday just previous to today's date. The trick is to use the DayOfWeek function, which returns the numerical day of the week. Try:
CurrentDate - DayOfWeek(CurrentDate) - 1
Suppose today is Tuesday. The DayOfWeek , therefore, is 3 (since Sunday is 1). CurrentDate - 3 gives you the date of 3 days prior, or Saturday. Subtract another day to get Friday.
Incidentally, I also strongly suggest that you actually use DataDate rather than CurrentDate. DataDate is the date on which the report was last refreshed. This is especially important if you save data with the report, as the report will remain static from day to day, until you hit refresh.
|
IP Logged |
|
ITSpecialist
Newbie
Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
|

Posted: 27 Dec 2007 at 11:46am |
|
I'm not sure this will work. My reports are generated through BO Report Server, so the need for a refresh date isn't necessary.
I need a formula that I can use that will run from the current date to last Friday. I am not understanding how the DayofWeek function will work if I have to manually put in a number (ie. 3). Can you maybe give a little more clarity. This also needs to work with changing the FirstDayofWeek to Friday and not Sunday (which I haven't figured out either).
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|

Posted: 28 Dec 2007 at 5:02am |
|
DayOfWeek is a function in Crystal that returns the day of the week. If you set FirstDayOfWeek to Friday, then the function would return: If the day is Friday, it returns 1 If the day is Saturday, it returns 2 If the day is Sunday, it returns 3 If the day is Monday, it returns 4 If the day is Tuesday, it returns 5 If the day is Wednesday, it returns 6 If the day is Thursday, it returns 7
Effectively, it's a sort of modulo of the day of the year. So, you don't have to manually put in any number. It's just a function of the date. Because the function returns the number of days from the beginning of the week, then subtracting that number of days from the current date takes you back to the beginning of the week. Voila!
You can overload the DayOfWeek function to use Friday as the first day. In this case, your formula would look like:
CurrentDate - DayOfWeek(CurrentDate, crFriday) +1
(You have to do the "+1" bit because the series is 1-based, not 0-based.)
I, personally, still recommend using DataDate, even if you are only ever viewing a report at the same time you are generating the data. I just find that it's cleaner all the way around. But, that's my personal preference, and you can feel free to take it for what it's worth.
|
IP Logged |
|
ITSpecialist
Newbie
Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
|

Posted: 28 Dec 2007 at 5:18am |
|
Can I do this as a selection criteria?
When I use it as a regular formula, all it displays is current date (data date) and the data that is retrieved is every week since sometime in October.
Would I first change FirstDayofWeek and then do the DayofWeek? I also cannot seem to put the formulas in the right location (i.e. in details, as selection criteria or as formula). It needs to be to where I can set a schedule for the report to run on any given day, and the data it retrieves is from the day the report is ran to the previous Friday.
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|

Posted: 28 Dec 2007 at 6:54am |
|
AFAIK, you can't set the FirstDayOfWeek globally for the report. To do that, you have to change it for the system (i.e., the server where your reports sit). You have to specify it in each relevant function.
For what you are doing, you need to go into the Select Expert. Click on the Show Formula button. You will see the formula for selecting the data. In addition to whatever other criteria you have, you need to have a line to restrict the dates that looks like:
{MyReport.MyDate} BETWEEN (CurrentDate - DayOfWeek(CurrentDate,crFriday) + 1) AND CurrentDate
That should give you the records you are looking for.
|
IP Logged |
|
ITSpecialist
Newbie
Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
|

Posted: 28 Dec 2007 at 8:17am |
|
For those reviewing it worked with the following formula:
{table.date} in ((CurrentDate-DayofWeek(CurrentDate,crFriday)+1) to CurrentDate)
Thanks.
|
IP Logged |
|
ITSpecialist
Newbie
Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
|

Posted: 28 Dec 2007 at 8:26am |
|
Next question...
How do I get my daterange to show previous Friday to Current day (or date of report)?
|
IP Logged |
|
Lugh
Senior Member
Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
|

Posted: 28 Dec 2007 at 10:33am |
|
Please explain in more detail. What daterange?
|
IP Logged |
|
ITSpecialist
Newbie
Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
|

Posted: 28 Dec 2007 at 11:04am |
|
I have a text field at the top of my report that is supposed to 'print' the date range of the report. Since I cannot manually put a date each and every time the report is generated, I thought I could use a variation of the same (previous) formula to display the dates used in the report.
When I use the above formula I get a '2' as the first date and current date as the end date. It should say for today 12/28/2007 - 12/28/2007. For Monday it should print 12/28/2007-12/31/2007, etc. The beginning date will change on each new Friday.
|
IP Logged |
|
|
|