Hi,
I'm trying to extract data from Oracle Financials, the problem I'm having is I need a filter for all periods in current financial year.
I cant use the transaction date field, I need to use the preiod-date which are all formatted as [red]'JUL-03'[/red].
I currently use an IN clause like so:
[blue]IN ('JUL-03','AUG-03','SEP-03','OCT-03','NOV-03','DEC-03','JAN-04','FEB-04','MAR-04')[/blue]
But I need a way of doing this dynamically (so that I can automate the cube build and forget about it!)
I know the following SQL will produce a suitable lookup:
[blue]select to_char(add_months(sysdate,-rownum),'Mon-YY') as DT
from user_objects
where rownum < 10[/blue]
... I have tried to create an Impromptu report with custom-SQL which works fine, but I cant use the custom report as a lookup.
Any ideas or suggestions welcome.
I'm trying to extract data from Oracle Financials, the problem I'm having is I need a filter for all periods in current financial year.
I cant use the transaction date field, I need to use the preiod-date which are all formatted as [red]'JUL-03'[/red].
I currently use an IN clause like so:
[blue]IN ('JUL-03','AUG-03','SEP-03','OCT-03','NOV-03','DEC-03','JAN-04','FEB-04','MAR-04')[/blue]
But I need a way of doing this dynamically (so that I can automate the cube build and forget about it!)
I know the following SQL will produce a suitable lookup:
[blue]select to_char(add_months(sysdate,-rownum),'Mon-YY') as DT
from user_objects
where rownum < 10[/blue]
... I have tried to create an Impromptu report with custom-SQL which works fine, but I cant use the custom report as a lookup.
Any ideas or suggestions welcome.