How to set FilterHilim to LastweekStart + x amount of days - RMS report

I'd like to make it so that whenever I open up employees timeclock .qrp report for the week...  that upon opening the report it will auto set the date ranges to last Sunday through Saturday.

Currently I have it filter last Sunday to this Sunday.

Begin Filter
   FieldName = "TimeClock.TimeIn"
   FilterOp = reportfilteropBetween
   FilterLoLim = "<LastWeekStart>"
   FilterHilim = "<WeekStart>"
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

I've tried <LastWeekStart>+6 and that would not work either.

Thanks in advance for anyone willing to read through the post.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What reporting tool are you using?

Disclaimer: I don't know anything about RMS, other than what I've just read in some search results.

 Looking at this old question here on EE, there doesn't seem to be a special name for "end of last week".  Or, at least, there apparently wasn't one back then.

 I guess one option might be to move the selection criteria into SelCriteria, as described in that post.  That way you have more control over the dates.

 But if you can add another filter, how about this?

 Add a filter where FilterLoLim and FilterHilim are both "<WeekStart>", and set FilterNegated to True.  I am _guessing_ that setting FilterNegated to True reverses the test.  The idea is that your original filter includes dates through the start of this week, but then the second filter excludes the start of this week.

 I suppose the whole filter would be:

Begin Filter
   FieldName = "TimeClock.TimeIn"
   FilterOp = reportfilteropBetween
   FilterLoLim = "<WeekStart>"
   FilterHilim = "<WeekStart>"
   FilterNegated = True
   FilterConnector = reportfilterbooleanconAND
End Filter

 I don't know about FilterConnector.  AND seems the obvious choice, but it may depend on where this new filter is positioned.

antman1437Author Commented:
mlmcc I'm using RMS reports or crystal reports.

Appreciate the answer James. Trying FilterNegated did not work for me received an error.

As for the information in the other post...I don't know enough code to change the SelCriteria to what I'm trying to accomplish from scratch:
"([Transaction].Time >= DATEADD(month, - 1, CONVERT(datetime, CONVERT(varchar(8), GETDATE(), 120) + '01', 120))) AND ([Transaction].Time < CONVERT(datetime,
                      CONVERT(varchar(8), GETDATE(), 120) + '01', 120))"

I tried what the post said in removing all filters and then copying that info inside Selcriteria to see if it would work first. Got an error reading report file at "line #19 CONVERT(varchar(9),GETDATE(), 120)+'01',120))"

I'm still lost, hoping a kind soul is able to help me.
Which are you using?  RMS and Crystal are two completely different reporting tools.

We can help you with Crystal but the RMS tool would take some research.

What error did you get when you tried the FilterNegated filter?  I did some more looking around and it does seem that I was right about FilterNegated = True meaning "reverse the test".

 I also saw that there are other values for FilterOp.  There is presumably a reportfilteropEqual, or something similar, which would really be more appropriate for this test (since you don't really need a range).  And then FilterLoLim and FilterHilim would presumably be replaced by something else.

 FWIW, one suggestion someone had for creating a "negated" filter was to create the filter the usual way, so you could see what it looked like, and just copy that when you add the filter manually, but change FilterNegated to True.

 However, if TimeClock.TimeIn is a datetime value, not just a date, that would complicate things.  I was trying to create a filter like TimeClock.TimeIn <> 03/22/15, but I hadn't considered the time.  If one or both values include the time, that test won't work unless both values have the same time.

 The idea could still work though.  If there is a FilterOp for "less than" (eg. reportfilteropLessThan), and if <WeekStart> gives you a time of midnight, then you'd just need to add a filter for TimeIn < WeekStart.  FilterNegated would be False on that one.

 OTOH, if you want to try SelCriteria instead, I'm pretty sure I read that you can have both (filters and SelCriteria).  So, I'd start by keeping your old filter(s), and just putting the new test in SelCriteria.  If that works, it would be a lot simpler than trying to convert your old filter(s) to SQL.

 Try this:

SelCriteria="TimeClock.TimeIn < CONVERT (datetime, CONVERT (varchar(10), DATEADD (day, 1 - DATEPART (weekday, GETDATE()), GETDATE()), 101))"

 I use DATEPART to get the current day of the week.  if you subtract that many days from today, you get the last day of last week, so I use DATEADD to subtract 1 less than that, to get the first day of this week.  Then I use CONVERT to convert that datetime to a string that contains only the date (removing the time), and then convert that string back to a datetime.  That gives you midnight on the first day of this week, and I look for TimeIn values before (<) that.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.