Oracle Bi Solutions

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Friday, 1 March 2013

OBIEE 11g - Sorting on the Total in a Pivot

Posted on 11:22 by Unknown

Pivots in OBIEE are a great option of displaying data, especially monthly data.  However, I often run
into a situation where the customer wants me to show trailing 12 months of data, sorted descending
by the total for those 12 months.  Unfortunately, OBIEE doesn't have a sort arrow on the Total.  And, 
creating a column with a total doesn't get me what I need, since it ends up being another metric in the pivot. 
 For example, a customer might want to see this:

Unfortunately, with the limitation of not having a sort button on the total, this is not as simple as one would
 like.  However, there is a way to do this.  What I did in the above example, was create a sorting column. 
 I started out by trying to add a calculation to give myself a SUM of the metric/fact by the Dimension
 ('Utility').  However, if I do this, it shows up as a fact/metric, and it won't let me sort on it.  So, I then tried
 to check the option 'Treat as an attribute column'.  However, this caused performance issues that were 
unacceptable.  So, I finally came to a solution where I created a Rank column, by ranking the sum of the 
metric by the 'Utility' field, and then converted it to an integer (if you convert to a character, it will sort a 
10 before a 2).  I set the option to treat the field as an attribute, and now it works.  Below is the example 
of what I did.

This formula gives me the ability to have a Rank column that I can now put in my pivot and sort on it.
If I want to,
 I can also hide this field, but the downfall of this is that once hidden, none of the other sorts will work, 
since the first sort is on a hidden field.  Below is the formula and a picture of the final result:

CAST(RANK(SUM("Fact Table"."Field" BY "Dimension Table"."Utility")) AS INTEGER)
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in OBIEE | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Upper Function
    In Oracle/PLSQL, the  upper function  converts all letters in the specified string to uppercase. If there are characters in the string that ...
  • [OBIEE11g] - OBIEE Dashboard for Informatica Metadata Reporting
    The metadata that Informatica Power Center 8 retains in its repository can be exposed via OBIEE reports and dashboards. This metadata includ...
  • OBIEE 11g Hide/Show Sections based on Dashboard Prompt
    allow a user’s interaction to hide/show certain sections of a dashboard. In this particular case the user wanted to choose either ‘Quarterly...
  • OBIEE 11g not showing new dashboard in the drop down menu
    When creating New dashboard in  OBIEE 11g, I have faced with issue that dashboard name did not show up in drop down dashboard menu. 1. When ...
  • [ODI] - Frequently Asked Questions (FAQ)
    Here is a list of FAQs about Oracle Data Integrator 1) What is Oracle Data Integrator (ODI)? 2) What is E-LT? 3) What components make up Ora...
  • Data Modeling: Schema Generation Issue with ERwin Data Modeler 7.3
    We are using Computer Associate’s ERwin Data Modeler 7.3 for data modeling. In one of our engagements, we are pushing data model changes to ...
  • Installation Informatica Powercenter 9.1 on Oracle Enterprise Linux 5.6
    Ingredients: Program Version Filename Oracle Express 11G 11.2.0 oracle-xe-11.2.0-0.5.x86_64.rpm SQL Developer 3.0 sqldeveloper-3.0.04.34-1.n...
  • [OBIEE11g] - Creating Dashboard Traversing Through Graph
    The general requirement asked for by customers is that they want to Click on the Main Dashboard Page’s Graph and be transferred to the other...
  • [OBIEE11g] - Dashboard Prompt - "Prompt User"
    Oracle BI 11g which we hadn't seen before, the " Prompt User " operator on a dashboard prompt. I'm not sure exactly when t...
  • OBIEE 11g - Query Limit
    Query limit and number of minutes a query can run per physical layer database connection, follow the below steps. > Login to Repository u...

Categories

  • BI Publisher
  • DAC
  • DataWarehouse
  • Hyperion
  • Informatica
  • OBIEE
  • ODI
  • Oracle Applications EBS 12.1.3
  • Oracle Database
  • PL/SQL
  • SQL
  • Unix/Linux

Blog Archive

  • ▼  2013 (500)
    • ►  November (8)
    • ►  October (1)
    • ►  July (4)
    • ►  June (9)
    • ►  May (15)
    • ►  April (24)
    • ▼  March (43)
      • EBS 12.1.3
      • OBIEE 11g - Weblogic Security Administration
      • Error: OBIEE 11g: Error: "Stream Closed" Server Ex...
      • OBIEE 11g - Oracle Map viewer Installation & Confi...
      • Hyperion - Essbase Production Server crash
      • Hyperion - EPM, Fusion Edition Performance and Tun...
      • Hyperion FAQ'S
      • Hyperion Planning 11.1.2.2 Calculation Manager Is...
      • OBIEE 11g - Hyperion Integration With OBIEE
      • OBIEE 11g - Single Sign On With (EBS)
      • Hyperion Financial Reports – Preview User Point of...
      • OBIEE 11g - Display user name and time of the repo...
      • OBIEE 11g - Getting the custom links on the OBIEE ...
      • OBIEE 11g - supports 2 kinds of clustering Vertica...
      • DAC - Replace Base: Procedure for Upgrading
      • DAC - Simplified Refresh From Base Option
      • DAC - Peer to Peer Merge
      • OBIEE 11g - User Specific UI
      • OBIEE 11g - Multiple values in single column using...
      • OBIEE 11g - Look up Tables in obiee11g
      • DAC - W_DAY_D and Configure the Multi Calendar su...
      • OBIEE 11g - Roll back Selection Security to be Enable
      • OBIEE 11g - XMLP Server Cache Control
      • OBIEE 11g - How to remotely connect to online RPD
      • BI Publisher - 10g to 11g Upgrade Process
      • OBIEE 11g - Deployment of BI Publisher reports
      • OBIEE 11g - Deinstall
      • OBIEE 11g - Uploading/calling PDF file into OBIEE ...
      • OBIEE 11g - Switching Between Multiple Reports
      • OBIEE 11g - Action link saving a report in local d...
      • OBIEE 11g - Disable Caching
      • OBIEE - Executing Stored Procedures/Functions befo...
      • How to call stored procedure from DAC?
      • OBIEE - Selecting a Single Value and Showing a Range
      • OBIEE - Handy Date Session Variables
      • OBIEE 11g - Calculated % always shows up as 0%
      • OBIEE 11g - Using a Dashboard Prompt as a Column S...
      • OBIEE 11g - How to get proper date sorting in graphs
      • OBIEE 11g - Sorting on the Total in a Pivot
      • OBIEE - Google Charts
      • OBIEE11g - Inline Microcharts using Google Charts API
      • OBIEE 11g - Dashboard Background Image or Watermark
      • NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A gen...
    • ►  February (73)
    • ►  January (323)
Powered by Blogger.

About Me

Unknown
View my complete profile