Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Changing Sort Order in Dashboard Prompts

Posted by Venkatakrishnan J on February 14, 2008

Another common question that generally comes up is how do we change the sort order in Dashboard prompts. Generally, sort order is determined by the sorting column which you have specified in the repository. If you do not have any alternative sorting column specified in the repository then the default alphabetic sort is done on non-numeral columns and numeric sort is done on numeric columns. If you are not sure what i mean, lets create a simple dashboard prompt on the column REGION_NAME which does not have any sort order specified in the Business Model Layer.

      

      

      

As you see above, the dashboard prompt by default sorts it alphabetically. Now, how do we make it to sort in the descending order. In order to do this, go to the dashboard prompt. Convert the Show Results from “All Values” to “SQL Results”. Then enter the following SQL.

SELECT GEOGRAPHY.REGION_NAME FROM SH2 ORDER BY GEOGRAPHY.REGION_NAME desc

This will automatically sort the results in the descending order.

      

      

Now, the next question would be how do we define our own sort order. In order to do this, you would need another column with one to one mapping with the REGION_NAME. Any change in granularity will result in duplication. In our example, lets try sorting the same by REGION_ID. Edit the dashboard prompt and enter the below SQL

SELECT GEOGRAPHY.REGION_NAME FROM SH2 ORDER BY GEOGRAPHY.REGION_ID

This will again automatically sort the results based on another column of the same granularity.

      

      

The above can be useful in quite a few situations when you dont have a sort order specified in the repository.

Advertisements

One Response to “Oracle BI EE 10.1.3.3/2 – Changing Sort Order in Dashboard Prompts”

  1. srini said

    Hi Venkat

    Thanks for the article. I am in need of changing sort order dynamically.

    That is, I have set a few columns as the default sorting columns when the report loads. But when the user searches by entering a value in a particular prompt (which was empty when the report loaded), I want to use different columns for sorting. IS this possible?

    Thanks again

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: