Sort on LOV with access restriction

scheduling XI Scheduler
1

Hello everybody,

Scenario:

Ver: BOXI r3 (going to turn to r4)

We apply row restrictions on a Time dimension table.

The table structure is:

TABLE DIM_TIME:
COLUMN Branch VARCHAR,
COLUMN Period VARCHAR, (formatted description date)
COLUMN Execution_Date DATE (same as Period but “date” datatype)

We apply the row restriction in the Universe on the Branch field.

The “Period” description format can change (depending on the kind of period, end of month, quarter, etc…).
It can be “DD Month YYYY” or “Month YYYY” or “Qrt YYYY”.

We use the Period in a prompt in the reports.
We can’t sort the LOV by Period of course. (“April 2015” would be shown before “January 2015” and so on…
We need to sort on the Execution_Date and show the period in the LOV.

The question is:

  1. If we use a custom subquery on the LOV to show the Period sorted on the execution_Date NO row restriction is applied.
  2. If we sort on the Execution_Date by using the BO “Manage Sorts” in the LOV, NO DISTINCT clause is applied in the LOV query

Any suggestions are welcome.

Tks
Andrea

amaestri :netherlands: (BOB member since 2009-01-16)