Hello,

First some background - I'm converting Hyperion reports to analyses in the presentation layer of OBIEE 12.2.1.2.0.  I do not have access to anything except the presentation layer.

I'm wondering if something like the below SQL example statement can be used anywhere in the presentation layer of OBIEE:

TABLE.COLUMN =

        (SELECT MAX(xx.EFFDT) FROM TABLE.COLUMN xx

        WHERE TABLE.COLUMN_ID = xx.COLUMN_ID

          AND xx.EFFDT <= to_date('09012019','mmddyyyy'))

     AND TABLE.EFF_STATUS = 'A'

I know that I am able to turn a filter into an "Advanced SQL Filter" that allows me to enter a custom where clause but I need to issue a select statement.  If possible I'd also like to be able to set the to_date as a variable so an end user could define a value there.  I also know that in the "Advanced Tab" there is an area to enter "Advanced SQL Clauses".  I don't have the right permission to make changes here but if I could would I be able to issue the statement above and set a variable?  Can this task be completed in the presentation layer and if so where should something like this be completed?  If not in the presentation layer can/should this be completed in one of the other layers? 

Thanks for your time!