9 Replies Latest reply: May 30, 2012 2:08 AM by 913195 RSS

    Custom Sorting columns in OBIEE 11g answers

    913195
      Hi All,

      I have an analysis developed in OBIEE 11g where data source is essbase.
      I have columns 'Scenarios (Actual, Actual vs plan , Plan, etc).
      When I run the report, I get columns Actuals, Actual vs plan, and then plan, if select those members.
      But as per the PnL reporting, it should be Atcual, Plan, and then Actul vs plan.
      My query is , how to change the sequesnce , as it seems the column scenario is sorted as ascending..
      I want to sort it so that plan comes in the middle of Actual , and Actual vs Plan values..

      Is it possible to achieve custom sorting by using any functions/formaulas in Analysis or by modifying the Essbase hirarchy?
      I was trying to using Binning, but could not do it. If any one knows how to achieve this, please let me know.

      Thanks in Advance
      Santosh
        • 1. Re: Custom Sorting columns in OBIEE 11g answers
          user248025
          Hi,

          Just pull Scenario columns once again into criteria and rename as Scenario_ID then edit formula and write a case condition like below

          case when Scenario='Atcual' then '1'
          when when Scenario='Actual vs plan' then '2'
          when Scenario='Plan' then '3' else
          END

          Include this column in your report view and do the sorting for Scenario_ID columns then hide that colum.

          Thanks
          Deva
          • 2. Re: Custom Sorting columns in OBIEE 11g answers
            obiee_kid
            You can try using Sort Order in the RPD.

            Here's an example:
            http://oraclebizint.wordpress.com/2008/04/28/oracle-bi-ee-101332-handling-sort-order-in-hyperion-essbase-931-evaluate-and-mdx/
            • 3. Re: Custom Sorting columns in OBIEE 11g answers
              913195
              Hi Deva,

              Thanks for your inputs for I am still not able to solve this. When I ran the report I got following error (pasting initial few lines as the error log is quite long)

              State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 42015] Cannot function ship the following expression: AggrExternal(SampleGL_Flattened.Net Revenue by [ SampleGL_Flattened.Gen2,Time Periods, SampleGL_Flattened.Gen2,Time Periods - Member Key, SampleGL_Flattened.Gen2,Scenarios, SampleGL_Flattened.Gen2,Scenarios - Member Key, case when SampleGL_Flattened.Gen2,Scenarios = 'Actual' then '1' when SampleGL_Flattened.Gen2,Scenarios = 'Plan' then '2' when SampleGL_Flattened.Gen2,Scenarios = 'Actual v Plan Variance' then '3' else SampleGL_Flattened.Gen2,Scenarios end ] ). (HY000)
              SQL Issued: SET VARIABLE PREFERRED_CURRENCY='USD';SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8, s_9, s_10, s_11, s_12, s_13, s_14, s_15, s_16, s_17, s_18, s_19, s_20, s_21, s_22, s_23, s_24, s_25, s_26, s_27, s_28, s_29, s_30, s_31, s_32, s_33, s_34 FROM ( SELECT 0 s_0, CASE WHEN "G - Sample Essbase GL"."Scenarios"."Scenarios" ='Actual' THEN '1' WHEN "Scenarios"."Scenarios" ='Plan' THEN '2' WHEN "Scenarios"."Scenarios" ='Actual v Plan Variance' THEN '3' ELSE "Scenarios"."Scenarios" END s_1, CAST(NULL AS VARCHAR(1)) s_2, CAST(NULL AS VARCHAR(1)) s_3, CAST(NULL AS VARCHAR(1)) s_4, CAST(NULL AS VARCHAR(1)) s_5, CAST(NULL AS VARCHAR(1)) s_6, "G - Sample Essbase GL"."Scenarios"."Scenarios" s_7, "G - Sample Essbase GL"."Time Periods"."Total Year" s_8, CAST(NULL AS INTEGER) s_9, CAST(NULL AS INTEGER) s_10, CAST(NULL AS INTEGER) s_11, CAST(NULL AS VARCHAR(1)) s_12, CAST(NULL AS VARCHAR(1)) s_13, CAST(NULL AS VARCHAR(1)) s_14, CAST(NULL AS VARCHAR(1)) s_15, CAST(NULL AS VARCHAR(1)) s_16, IDOF("G - Sample Essbase GL"."Time Periods"."Time Periods"."Year") s_17, CAST(NULL AS INTEGER) s_18, CAST(NULL AS INTEGER) s_19,


              Can u please throw some light on why the error was thrown and how to resolve this. I cant modify the RPD as this is an example rpd referred by many other dashboards and reports and I would like to avoid any adverse impact by chaning the RPD. I would prefer to achieve this custom sorting at report level.
              Please help.

              Regards
              Santosh
              • 4. Re: Custom Sorting columns in OBIEE 11g answers
                Satya Ranki Reddy
                Hi,

                Remove single quote in your case statement.

                case when scenarios='x' then 1 when scenarios='y' then 2 end

                Note: please select ascending order option then hide this column in your report.

                Hope this help's

                Thanks,
                Satya
                • 5. Re: Custom Sorting columns in OBIEE 11g answers
                  Satya Ranki Reddy
                  Hi,

                  Remove single quote in your case statement.

                  case when scenarios='x' then 1 when scenarios='y' then 2 end

                  Note: please select ascending order option then hide this column in your report.

                  Hope this help's

                  Thanks,
                  Satya
                  • 6. Re: Custom Sorting columns in OBIEE 11g answers
                    913195
                    Hi Satya,,

                    I did exactlhy what u suggested. Still getting error as below

                    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 42015] Cannot function ship the following expression: AggrExternal(SampleGL_Flattened.Net Revenue by [ SampleGL_Flattened.Gen2,Time Periods, SampleGL_Flattened.Gen2,Time Periods - Member Key, SampleGL_Flattened.Gen2,Scenarios, SampleGL_Flattened.Gen2,Scenarios - Member Key, case when SampleGL_Flattened.Gen2,Scenarios = 'Actual' then 1 when SampleGL_Flattened.Gen2,Scenarios = 'Plan' then 2 when SampleGL_Flattened.Gen2,Scenarios = 'Actual v Plan Variance' then 3 else SampleGL_Flattened.Gen2,Scenarios end ] ). (HY000)

                    My case statement looks like following;

                    CASE WHEN "Scenarios"."Scenarios" = 'Actual' THEN 1 WHEN "Scenarios"."Scenarios" = 'Plan' THEN 2 WHEN "Scenarios"."Scenarios" = 'Actual v Plan Variance' THEN 3 ELSE "Scenarios"."Scenarios" END

                    There are other values in the scenario hirarchy but I have not inculded them in the case statement as I wanted to test with few members first , and then add rest of the other members...

                    Kindly let me know wht wrong I am doing,

                    Thanks a ton for your inputs...

                    Warm Regards
                    Santosh
                    • 7. Re: Custom Sorting columns in OBIEE 11g answers
                      Dhar
                      Hi Santosh,

                      Could you check the 'Aggregation rule' set for this column in the report? I guess it is 'default' and hence it is trying to push the calculation back to Essbase where I see as not needed.

                      I guess you could change the aggregation rule to 'Sum/None' in the report and see if this works.

                      Hope this helps.

                      Thank you,
                      Dhar
                      • 8. Re: Custom Sorting columns in OBIEE 11g answers
                        913195
                        Hi,

                        I tried everything which is suggested in this thread.
                        I tried to do this sorting on a normal attribute column , in which case it works using binning .. but for a scenario column which is a dimension in essbase databse, it does not seem to work.

                        I suspect , if there is any limitation for implemeting such custom sorting on a essbase dimension and on its members or levels.
                        No matter what I did, it does not work and throws an error pasted earliear.

                        Anyone have any idea, if this ever could be done on essbase dimension.? If any one has done this in the past, please help me to achieve this.
                        You may try this on a example provided by oracle on an analysis report based on G - Sample Essbase GL subject area in 11g and let me know if this really can be achieved or not.

                        Need help.

                        Thanks and Regards
                        Santosh
                        • 9. Re: Custom Sorting columns in OBIEE 11g answers
                          913195
                          Hi All,

                          I am exlaining the situation today again after confirming the member values for scenario dimension in rpd.

                          In Admin tool, by opening Oracle sampleApp rpd, in physical layer, when I right click on Scenario dimension and click on View members..
                          I see following members in the order specified.

                          1. Actual
                          2. Plan
                          3. Forecast
                          4. What-if
                          5. Actual & Forecast Outrun
                          6. Actual & What-if Outrun
                          7. Actual V Plan Variance
                          8. Forecast Outrun V Plan
                          9. What-if Outrun V Plan


                          But in OBIEE answers, when I open respecive subject area ( G- Sample Essbase GL), and open the scenario folder, I see above members appearing in alphabetically sorted order as below

                          Actual
                          Actual & Forecast Outrun
                          Actual & What-if Outrun
                          Actual V Plan Variance
                          Forecast
                          Forecast Outrun V Plan
                          Plan
                          What-if
                          What-if Outrun V Plan

                          (why the sequence is changed to alphabetically sorted when memvers in essbase database are not that way?)

                          I created one analysis using Scenario columns Actual, plan and Actual V Plan Variance. In the report it is required to show the values in following
                          sequence

                          Actual Plan      Actual V Plan Variance
                          1000     2500     1500


                          But in My analyses, I see the as below     

                          Actual Actual V Plan Variance     Plan     
                          1000     1500          2500


                          I tried to use case statement, and tried to change the order so that data is in order expected (Actual, Plan Actual V plan variance) but it did not work.

                          Just to make sure , I tried to use case statement on a non-essbase column, and I am able to sort and display the values , but the same does not work
                          on essbase members.     

                          Any one has any idea why this is happening?

                          One more thing which should be noticed is that, the sequence of members is different (in alphbetical order) in the subject area vs
                          the sequence of members in the RPD. Can sequence of members be changed in presentation layer of rpd compared to seuqnee in the physical layer?

                          Anyone have faced this challange in the past.? Please throw some light on this challange.

                          Thanks in Advance
                          Santosh