2 Replies Latest reply: Nov 6, 2012 9:53 AM by Robin Harris RSS

    How to change the values in report level..

    Avinash P
      Hi All,

      I have country dimenssion column in this column haveing more then 500 values.in this I filter 6 countries.
      country names like this
      1.BankUSAgroup-BUG
      2.BankUKGroup-BUG
      3.BankAfricaGroup-BAG
      Like This I want Change this values to USA,UK,Africa.

      Any Help.......

      Thank You,

      Edited by: 958741 on Nov 6, 2012 7:07 AM
        • 1. Re: How to change the values in report level..
          Maqs-Oracle
          If all the column values are prefixed with Bank and suffixed with Group-BUG then you can use the following formula

          TRIM(BOTH FROM REPLACE(REPLACE("COUNTRY_TABLE"."COUNTRY_COLUMN", 'Bank',' '), 'group-BUG', ' ')).

          You can use the same in SQL query.

          Its better to populate the Country dimension with proper values instead of doing this in OBIEE.


          Thanks
          • 2. Re: How to change the values in report level..
            Robin Harris
            If it's only 6 values you need to change (and not the 500!!) you could use a case statement in the report column formula.

            An example of the syntax is:

            CASE ("GL Calendar"."Fiscal Period Number")
            WHEN 1 THEN 'January'
            WHEN 2 THEN 'February'
            ELSE 'Other'
            END

            So here I'm taking the period number and converting into a period name so you can use the syntax and substitute into this your column and values.

            If its 500 values you might need a more technical solution (e.g. add another column to the dimension and change the ETL to populate this, or have a static lookup table you can use)

            Please mark if helpful or answered

            Thanks