Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: date range - current day to last friday Post Reply Post New Topic
Page  of 2 Next >>
Author Message
ITSpecialist
Newbie
Newbie
Avatar

Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
Quote ITSpecialist Replybullet 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 IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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 IP Logged
ITSpecialist
Newbie
Newbie
Avatar

Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
Quote ITSpecialist Replybullet 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 IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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 IP Logged
ITSpecialist
Newbie
Newbie
Avatar

Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
Quote ITSpecialist Replybullet 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 IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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 IP Logged
ITSpecialist
Newbie
Newbie
Avatar

Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
Quote ITSpecialist Replybullet 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 IP Logged
ITSpecialist
Newbie
Newbie
Avatar

Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
Quote ITSpecialist Replybullet 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 IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 28 Dec 2007 at 10:33am
Please explain in more detail.  What daterange?
IP IP Logged
ITSpecialist
Newbie
Newbie
Avatar

Joined: 27 Dec 2007
Online Status: Offline
Posts: 9
Quote ITSpecialist Replybullet 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 IP Logged
Page  of 2 Next >>
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