Hello - I have been playing with trying to do an offset of sorts when we are using dates in the advanced search feature. What I am trying to do is say if an order was entered older than 20 days than show me those orders. I can do this by manually changing the date every time I log into the report, but I was hoping there is a way to get this date filter to change dynamically.
Nick Deccio
I am fairly certain this was discussed in another thread on the forum, the answer was quite technical if I recall correctly.
I ended up figuring out a work around. I went into the SQL on the backend and added sysdate and another instance of the the entered date. I then used an expression in the DB Designer and subtracted sysdate and enterdt. I then used the expression as a property and converted it to a number and basically filtered by the moving difference between sysdate and in my case the entered date. Definitely not pretty, but effective.
Hi @ndeccio. This is a slightly more complex issue, as from what you’ve already gathered, the value must exist in order to reference/calculate from it. It sounds as though you’ve found a way to do this, but if it helps at all, this may be how I would have approached it.
The easiest way to do as you’re asking is probably to either include an additional calculated field on the data extract (ie SQL query) or add a transform column within the database design in order to generate the required value (assuming you have access to designer - if not you may need to arrange Phocas to undertake such a change for you)
For example within the design you could add a transform column that effectively ran a case statement over the date and evaluated against your criteria. This does assume you are evaluating a date type field, as otherwise you would need to convert before evaluating. You would end up with something along the lines of;
CASE WHEN [DateEntered] < dateadd(d, -20, Getdate()) THEN 1 ELSE 0 END
This would show all records with a date entered greater than 20 days ago as having a 1. Everything newer would be 0.
If however you wanted to evaluate every date so you could reference the age as a property, then you could do the following;
DATEDIFF(day,[DateEntered],getdate())
This could then be mapped to a property and formatted as a number so you could use in calculations,. You should then be able to use this within your search.
I hope this helps!
Regards,
Lee Konrath
That works beautifully. Thank you Lee.
Hi Lee,
Tried using this and the transformed column looks good in the feed, but when I go to build I get this error:
Inner Exception: The conversion of nvarchar data type to a datetime data type resulted in an out-of-range value.
Do I possibly need to convert to datetime prior? I believe my data already already is in that (MM/DD/YYYY) , but I could be mistaken.
Thanks!
Hi @Andrew_Singer. This would likely be as a result of your date field being text (varchar) rather than a date data type. Although it may appear in a date format, it may still be stored as text. If you try converting to date as part of the formula, it should hopefully resolve the error.
So you could try something such as the following for the transform column;
CASE WHEN CAST([DateEntered] as date) < dateadd(d, -20, Getdate()) THEN 1 ELSE 0 END
And then maybe the following for the property if needed;
DATEDIFF(day,cast([DateEntered] as date),getdate())
I hope this helps, but if there’s anymore questions then please let me know!
Regards,
Lee Konrath
This worked perfectly. Thank you Lee!