Grand Total Sorting in Descending order in Pivot view- OBIEE 11g

NOTE - Do not forget the second part of this article as you will find the solution and not the workaround. Thanks to Renu Prasad Gurudev.

Hello friends - Good evening. 
 I came upon one requirement where Grand Total was to sort in descending order in pivot view.

Let us create a simple report.

Look at the last column - The Grand Total column ( Pivot view - Columns section - Total - After )
Grand Total is not in order. Neither in ascending nor in descending order.
There is no option to sort - Sort arrows are missing.

Let us try some other workarounds. 


Create a report as shown below. In the criteria - 2 extra columns
1) Grand Total By Product Catagory
2)Rank on Grand Total By Product Catagory

Now I am placing the Rank column the ROWS section of the Pivot view.


From the pivot view I am going to remove AMOUNT_SOLD_TOTAL column from the measures.


And I am selecting the Grand Total- After option from Column Section of the view.

No luck - The moment you bring Amount_Sold_Rank column in the ROWS section - it losses its order and you DO NOT find the arrows either. So this workaround is not working.


Let us try another workaround. 
Ok.
In my case the Product Categories are less in number and hence I can manually give a rank to them.
Have a look at report below.


From the AMOUNT_SOLD_TOTAL shown in the reports above I came to know which Product Category has the highest Total and which one has the lowest - I have noted it down and accordingly I will be ranking them.

Go to Pivot view. Place the PRODUCT_CATEGORY_RANK in the ROWS section and HIDE it.

In my case AMOUNT_SOLD_TOTAL does not change overnight for a particular product category. Definitely I will have to monitor the AMOUNT_SOLD_TOTAL on a weekly basis and may have to modify the hard coded rank column AMOUNT_SOLD_RANK. 


This is something more of a manual work but at the moment it has solved my purpose.


-----------------------------------------------------------------------------------------------------------------------


Part II : The logic what I have provided in the post shown above is a workaround and it has been corrected by RENU PRASAD GURUDEV and as per the steps he has provided to me - I am correcting my logic. Thanks to him. This would be useful for future reference.


Create the report as shown above - 

In the report we have manually created a grand total column AMOUNT_SOLD_TOTAL and do not forget to select the check box "Treat as an attribute column"

Open the report in PIVOT view - and place the column AMOUNT_SOLD_TOTAL IN THE ROWS SECTION. SORT THIS COLUMN IN DESCENDING ORDER. AFTER THAT HIDE THAT COLUMN. IN THE COLUMN SECTION WE HAVE PLACED YEAR COLUMN AND APPLIED GRAND TOTAL - AFTER OPTION.

Thank you.