Runtime Sorting in Pivot table report in OBIEE 10g

Brief Summary:

This is a workaround for sorting a pivot table report dynamically based on User selection of sort values. I can only achieve either ascending or descending.

I have a report to show the Customer details for the Top 100 customers (based on Mkt rank) for a particular user. I need to add sort flexibility for the user based on runtime value selection; like Products or Payers.

The report looks like the following for a particular customer

and it is the same format for all the 100 (or less) users as shown below

We have a column called Market Rank and we sorted the report ascending based on that.

Enhancement Requirement:

User needs a flexibility of sorting the Customers based on different Product or Payers. They have the same functionality in some excel tool and preferred the same in OBIEE.

Problem:

Since this is a cross-tab report, we have to use the Pivot table option here and so we do not have any dynamic sorting option here like tables.

Only option is to upgrade the application to 11g version where pivot tables have the sorting capability.

Workaround & Implementation:

I found a workaround to achieve the same (more likely) functionality. With this implementation, user can select any Product or Payer and sort the customers in an ascending order (or descending but not both)

The workaround is simple and it works for me.

What I did is using a Presentation variable in ranking the customers.

In my case I have the same column having both the Product and Payer values. I created a prompt on that and a Presentation variable named ‘Sort’

 

In the report I added a column, named it as “Sort Rank”. The column do a sum of the measure (here TRx) by the customers regardless of Product or Payer and used a rank function. Then I filtered this column with the presentation variable to have the sum of the selected value.

IFNULL(FILTER(RANK(SUM("- Measures".TRx BY "- All Customers"."Customer Code","- All Customers".Customer,"- All Customers"."Customer Address","- All Customers".State))  USING ("- Dimensions"."Sub Group" =  '@{Sort}')), "- Dimensions"."Market Rank")

 

and add sorting order as ‘ascending’

In the pivot, I put this column as the first one so that it took the order precedence.

I created a separate page, added both the report and the prompt. Now, user can select a product (or payer), say Product 1 and the customers will be ranked based on the Product 1. The column will be filtered by the value Product 1 as below

IFNULL(FILTER(RANK(SUM("- Measures".TRx BY "- All Customers"."Customer Code","- All Customers".Customer,"- All Customers"."Customer Address","- All Customers".State))  USING ("- Dimensions"."Sub Group" =  'Product 1')), "- Dimensions"."Market Rank")

Conclusion:

I think it should work where we have two different column values to sort upon. I will try that later and will update the document.

Please feel free to provide your feedback and suggestions.