Hi, I have a report that I want to send via the scheduler. The report holds data of several years. Tricky thing is when it refreshes its should always the data off month-1. I am a ‘simple’ user, no admin or dev, so I cannot change anything the my Universe. I thought I could create some sort of variable in my report and use a filter. Any ideas please ?
Thank you
renwin (BOB member since 2014-10-06)
Create a dimension variable using the functions Currentdate() and relativedate(), and use that as a filter for your report.
mikeil (BOB member since 2015-02-18)
thank you for the suggestion ,but could give an example
should I then create a variable like daysbetween (CURDATE;RELATIVE DATE) or how can I set a filter so that I always on refresh have the data of month-1?
renwin (BOB member since 2014-10-06)
Try something like
=if formatdate([yourtrandate];“yyMM”)=formatdate(relativedate(currentdate();-20);“yyMM”) then 1 else 0
which will work within the first 20 days of the following month (is that OK?).
Then filter in your report when the value is 1.
mikeil (BOB member since 2015-02-18)
The big problem with that is that it will bring back all data forever.
Best solution is via the universe. Are there any predefined conditions available? If not, talk to your universe designer to explain your requirement.
Hi Mikeil, your solution seems to work. Many thanks !!
Of course I agree with Mark P, but as said, to adapt the universe we must submit a request, wait for the acceptance, find someone who can /will … all the benefits of working in a ‘large’ company 
thanks guys.
renwin (BOB member since 2014-10-06)
As soon as you’ve got unworkable volumes, get the request submitted; at least then you’ve got a “real” problem for them to deal with.
. . . and while they’re doing that, they could easily give you a few more useful dates: first day of previous month, last day of previous month, first day of rolling 3 months, first day of calendar year, last day of calendar year, first day of financial year, last day of financial year etc. etc.
mikeil (BOB member since 2015-02-18)
Very true.
Refer them to this thread if they don’t know what to do: