OBIEE 11g Sort Pivot Prompt

Question

I have created a query that selects user base data from two different weeks, uses a MSUM to work out the difference between the two weeks and then create a projection of base size across different verticals based on the net change.

This requires the use of a pivot table with prompts to display just the data from the most recent financial week (in format YYYY-MM), however, every time a new week rolls around, it resets the ordering in the pivot prompt to show the least recent week, which makes the calculations redundant.

I can't re-order the weeks in the base data, as the MSUM calc requires a specific order to be used across multiple dimensions.

Whilst this is very easily fixed by the end user each time by changing the drop down, or by the support team by editing the pivot table and changing the prompt before saving, (which then persists until the next week), it is either going to be a poor customer experience, or extra work for the support group.

Is there a method that I'm missing to create a sort on the pivot prompt options from within the pivot table options?

The equation follows this kind of logic...

"Metrics"."Base Size" + (
    (
        (
            "Metrics"."Base Size" - (
                MSUM ("Metrics"."Base Size", 2) - "Metrics"."Base Size"
            )
        ) / [days in time period]
    ) * 365
)

jackohug jackohug · Accepted Answer · 2016-04-26T09:52:22

OBI will order the data as defined by the sort order in the RPD, but ascending is probably the best choice for it at that level.

In your case you could put the Analysis on a dashboard and use a dashboard prompt instead. For that you have the ability, in the options, to change the "Choice List Options" to SQL Results. This should put in a default query, to which you could add an ORDER BY clause. You can also set that to default to the most recent/current period no matter the sort order of the column.

SELECT      "Date"."Financial Week"
FROM        "My Subject Area"
ORDER BY    "Date"."Financial Week" DESC

Instead of using the MSUM() function, you may also be better to use one of the built in time-series functions that can get the value of a previous period for you, without having to rely on any ordering. Have a look into the Ago() function to get the previous period.

OBIEE 11g Sort Pivot Prompt

1 Answers