Automatic Date Range Selection

[mwilliams]

I have a report that runs daily, M-F.  From Tuesday-Friday, I only need to pull the previous days data.  On Monday, I need to pull the weekend data, 3 days(Sat, Sun, Mon morning).  I can configure the report to pull one date range or the other.  However, I want to report to automatically determine the date range.

The following statement works every time by itself.  I can change the -1 to -3 or whatever at will.
[SQL1].[DocTimestamp]>_add_days(current_date,-1)

But when I try to put that inside an IF statement, it fails. 
IF (_day_of_week(current_date,1)=1) THEN ([SQL1].[DocTimestamp]>_add_days(current_date,-3))
My understanding is the above statement says "If the current day is Monday, then grab the last 3 days of info"

Effectively, I want to report to determine if it's a weekday or a weekend, and run the date range accordingly.

Help.

[cognostechie]

Instead of using the IF statement, try using this in the filter:

( _day_of_week(current_date,1) = 1  AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3)  )

                                  OR

( _day_of_week(current_date,1) <> 1  AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)  )

It is always safer to use <= also in the Date range filter limiting the data to current date. In case there is future data, that will be avoided.

[mwilliams]

That works great! 

I had to modify the parentheses, though, to get it to work.  The corrected version is:

( _day_of_week(current_date,1) = 1  AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3))) OR
( _day_of_week(current_date,1) <> 1  AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)))

I then ultimately wanted to catch Sunday in there also, so I added and extra check:

((( _day_of_week(current_date,1) = 1) OR ( _day_of_week(current_date,1) = 7))  AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3))) OR
((( _day_of_week(current_date,1) <> 1)  OR ( _day_of_week(current_date,1) <> 7)) AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)))

Thank you, sir!  One more tool in the arsenal against Chaos!

[Grim]

Might want to use code blocks to show these better.

Code Select Expand

((( _day_of_week(current_date,1) = 1) OR ( _day_of_week(current_date,1) = 7))  AND ([SQL1].[DocTimestamp] > _add_days(current_date,-3))) OR
((( _day_of_week(current_date,1) <> 1)  OR ( _day_of_week(current_date,1) <> 7)) AND ([SQL1].[DocTimestamp] > _add_days(current_date,-1)))