I have a BusinessObjects 4.1 report which returns data for a whole year and is grouped by an dimension object called Month-Year which represents the date in a character, MON-YY format.
There is a tab to this report which only displays information for the last three months. Currently I manually adjust a filter on this tab to display the only the last three months returned in the query. I am trying to create a dynamic filter which will automatically show the last three months returned so I do not have to change it every time I refresh the report with new data. I have tried several things in the Format…Filters…Define… area, but have not figured out a way to do this yet. Will I even be able to do this with a character representation of the date?
Jennifer Temple wrote
"I have a BusinessObjects 4.1 report which returns data for a whole year and
is grouped by an dimension object called Month-Year which represents the date in a character, MON-YY format.
There is a tab to this report which only displays information for the last three months. Currently I manually adjust a filter on this tab to display the only the last three months returned in the query."
One way to do this, probably not the best way, is to turn your character date into a date object. Then the filter can be applied using the date, the date - 1, and the date -2.
I have a BusinessObjects 4.1 report which is grouped by an dimension object called Month-Year which represents the date in a character, MON-YY format.
I am trying to create a dynamic filter which will automatically show the last three months returned so I do not have to change it every time I refresh the report with new data. Will I even be able to do this with a character representation of the date?
Jennifer,
Not sure if this totally addresses your problem or not, but… you could create a variable for “Quarter” and group the dates along that lines.
=If SubStr(,1,3) InList (“JAN”,“FEB”,“MAR”) Then “1Q” Else If SubStr(,1,3) InList (“APR”,“MAY”,“JUN”) Then “2Q” Else etc etc
You could then have one report tab per Quarter and filter based on that variable.
Hope this helps!
Shawn Leven
Southwestern Bell Telephone
SBC Communications SL7699@SBC.COM
I have a BusinessObjects 4.1 report which is grouped by an dimension
object called Month-Year which represents the date in a character, MON-YY
format.
I am trying to create a dynamic filter which will automatically show the
last three months returned so I do not have to change it every time I
refresh the report with new data. Will I even be able to
do this with a character representation of the date?
__________________________________________________ Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger. http://im.yahoo.com
We are using BO 4.1.5 on an Oracle database. When we have created universes with dynamic filters, we found the easiest way was to compare the date in question i.e. order date with the truncated system date (‘MM’) and then compare the order date with the truncated system date minus the amount of months you require. This can be created within the universe structure itself and therefore, removing the need to adjust your report
I.e. order_date < trunc(sysdate,‘MM’) AND order_date >= trunc(trunc(trunc(trunc(sysdate,‘MM’)-1,‘MM’)-1,‘MM’)-1,‘MM’)
This can be modified to suit whatever amount of months you require.
Hope this helps
Dave
I have a BusinessObjects 4.1 report which returns data for a whole year and is grouped by an dimension object called Month-Year which represents the date in a character, MON-YY format.
There is a tab to this report which only displays information for the last three months. Currently I manually adjust a filter on this tab to display the only the last three months returned in the query. I am trying to create a dynamic filter which will automatically show the last three months returned so I do not have to change it every time I refresh the report with new data. I have tried several things in the Format…Filters…Define… area, but have not figured out a way to do this yet. Will I even be able to do this with a character representation of the date?
Jennifer - You’re on the right track with Format -> Filters. Click the Add button and pick the object you want to filter. Then click Define and define a formula as follows:
= >= CurrentDate() - 90
I did some minimal testing and it worked (meaning it brought back all records where the date object was within the last 90 days)
George Baranowski
QuadraMed
I have a BusinessObjects 4.1 report which returns data for a whole year and is grouped by an dimension object called Month-Year which represents the date in a character, MON-YY format.
There is a tab to this report which only displays information for the last three months. Currently I manually adjust a filter on this tab to display the only the last three months returned in the query. I am trying to create a dynamic filter which will automatically show the last three months returned so I do not have to change it every time I refresh the report with new data. I have tried several things in the Format…Filters…Define… area, but have not figured out a way to do this yet. Will I even be able to do this with a character representation of the date?