asked on
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.
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.
Crystal ReportsMicrosoft DynamicsDB Reporting Tools
Last Comment
James0628
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.
https://www.experts-exchange.com/questions/26293091/RMS-report-filter-Last-Month.html
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.
James
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.
https://www.experts-exchange.com/questions/26293091/RMS-report-filter-Last-Month.html
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.
James
ASKER
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
I'm still lost, hoping a kind soul is able to help me.
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
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.
mlmcc
We can help you with Crystal but the RMS tool would take some research.
mlmcc
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
mlmcc