6 Replies Latest reply on Jan 21, 2013 12:04 PM by Christian Berg

    Data Mismatching at analysis level

      Hi Experts,

      I executed the physical query against the database ,it shows me correct result as 4262087106.46,But the same data it doesn't not show in analysis...
      It shows -$2,147,483,648.00 .
      What could be the reason?

        • 1. Re: Data Mismatching at analysis level
          Change the data format in the analytics-->column properties-->data format..

          mark if helpful/correct..

          • 2. Re: Data Mismatching at analysis level
            Hi ,

            So , you took the query from cursor cache and ran the same in DB and the result in OBIEE is different than DB ?
            • 3. Re: Data Mismatching at analysis level

              Already changed.

              • 4. Re: Data Mismatching at analysis level
                Yes Sayak.
                • 5. Re: Data Mismatching at analysis level
                  from the following query
                  SAWITH0 AS (select sum(T18648.BEGIN_BALANCE_DR - T18648.BEGIN_BALANCE_CR) as c1,
                  sum(T18648.PERIOD_NET_DR - T18648.PERIOD_NET_CR) as c2,
                  sum(T18648.PERIOD_NET_DR + T18648.BEGIN_BALANCE_DR - T18648.BEGIN_BALANCE_CR - T18648.PERIOD_NET_CR) as c3,
                  sum(T18648.PERIOD_NET_DR + T18648.BEGIN_BALANCE_DR - T18648.BEGIN_BALANCE_CR - T18648.PERIOD_NET_DR) as c4,
                  T18648.period_year as c5,
                  T18648.ACTUAL_FLAG as c6,
                  T18648.CURRENCY_CODE as c7,
                  T18648.period_name as c8,
                  T11465.SEGMENT3 as c9,
                  T14996.DESCRIPTION as c10,
                  T14996.FLEX_VALUE_SET_ID as c11,
                  T14771.FLEX_VALUE_SET_NAME as c12
                  GL_CODE_COMBINATIONS_KFV T11465 /* DIM_CODE_COMB_KFV_D */ ,
                  (SELECT * FROM FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_NAME LIKE 'Operations Company') T14765,
                  (SELECT * FROM FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_NAME LIKE 'Operations Department') T14768,
                  (SELECT * FROM FND_FLEX_VALUE_SETS WHERE flex_value_set_name = 'Operations Account') T14771,
                  FND_FLEX_VALUES_VL T14850 /* DIM_COMP_FLEX_VALUE_VL */ ,
                  FND_FLEX_VALUES_VL T14923 /* DIM_DEPT_FLEX_VALUE_VL */ ,
                  FND_FLEX_VALUES_VL T14996 /* DIM_ACCT_FLEX_VALUE_VL */ ,
                  (SELECT * FROM GL_BALANCES WHERE LEDGER_ID=1 and period_type='Month' and period_year > 2004) T18648
                  where ( T11465.CODE_COMBINATION_ID = T18648.CODE_COMBINATION_ID and T11465.SEGMENT1 = T14850.FLEX_VALUE and T11465.SEGMENT2 = T14923.FLEX_VALUE and T11465.SEGMENT3 = T14996.FLEX_VALUE and T11465.SEGMENT3='1000' and T14765.FLEX_VALUE_SET_ID = T14850.FLEX_VALUE_SET_ID and T14768.FLEX_VALUE_SET_ID = T14923.FLEX_VALUE_SET_ID and T14771.FLEX_VALUE_SET_ID = T14996.FLEX_VALUE_SET_ID and T18648.ACTUAL_FLAG = 'A' and T18648.CURRENCY_CODE = 'USD' and T18648.period_name = 'Apr-08' and T18648.period_year = 2008 )
                  group by T11465.SEGMENT3, T14771.FLEX_VALUE_SET_NAME, T14996.DESCRIPTION, T14996.FLEX_VALUE_SET_ID, T18648.ACTUAL_FLAG, T18648.CURRENCY_CODE, T18648.period_name, T18648.period_year),
                  SAWITH1 AS (select distinct D1.c8 as c1,
                  D1.c7 as c2,
                  D1.c1 as c3,
                  D1.c2 as c4,
                  D1.c3 as c5,
                  D1.c6 as c6,
                  D1.c5 as c7,
                  D1.c9 as c8,
                  D1.c10 as c9,
                  D1.c11 as c10,
                  D1.c12 as c11
                  SAWITH0 D1
                  where ( D1.c1 <> 0 or D1.c2 <> 0 or D1.c4 <> 0 ) )
                  select D1.c1 as c1,
                  D1.c2 as c2,
                  D1.c3 as c3,
                  D1.c4 as c4,
                  D1.c5 as c5,
                  D1.c6 as c6,
                  D1.c7 as c7,
                  D1.c8 as c8,
                  D1.c9 as c9
                  SAWITH1 D1
                  order by c1, c2, c6, c7, c8, c9

                  i got
                  Apr-08     USD     4262087106.46     39485667.76     4301572774.22     A     2008     1000     Total Assets

                  But report shows different output
                  -$2,147,483,648.00 $39,485,667.00 -$2,147,483,648.00

                  Any idea?

                  • 6. Re: Data Mismatching at analysis level
                    Christian Berg
                    OBI jeopardy: "What is the difference in results display between configuring a physical column as INT vs. configuring it as a DOUBLE?"