13 Replies Latest reply: Apr 17, 2012 8:18 AM by user9034165 RSS

    Problem with Sort settings and RSUM function in 11.1.1.5

    852989
      Hello, everyone! I have two Oracle BI Servers (11.1.1.3 и 11.1.1.5 versions).

      In 11.1.1.3 I have a fact table, which has fields: Year, Month, Brand, Amount. I need to create an analysis which contains columns:
      - [Year]
      - [Month]
      - [Brand]
      - [Amount] (by Year, Month and Brand)
      - [Total amount for all Brands] (by Year and Month) = SUM("Amount" by "Year", "Month")
      - [K1] = [Amount]/[Total amount for all Brands]*100 = ("Amount")/SUM("Amount" by "Year", "Month")*100
      - Running sum for [K1] (by Year, Month) = RSUM(("Amount")/SUM("Amount" by "Year", "Month")*100 by "Year", "Month").

      For correct calculation I set sort order (in "Selected Columns" section) for columns Year (1), Month (2), Amount (3) and get results as follows:
      2011.....January.....Brand08.....1272..........9930539......0,01%......0,01%
      2011.....January.....Brand06.....13618........9930539......0,14%......0,15%
      2011.....January.....Brand04.....93024........9930539......0,94%......1,09%
      2011.....January.....Brand10.....97512........9930539......0,98%......2,07%
      2011.....January.....Brand07.....126720......9930539     ......1,28%......3,34%
      2011.....January.....Brand09.....141984......9930539     ......1,43%......4,77%
      2011.....January.....Brand01.....149760......9930539     ......1,51%......6,28%
      2011.....January.....Brand02.....192576......9930539     ......1,94%......8,22%
      2011.....January.....Brand03.....290061......9930539......2,92%......11,14%
      2011.....January.....Brand05.....4216695....9930539......42,46%.....53,60%
      2011.....January.....Brand11.....4607317....9930539......46,40%.....100,00%
      TOTAL..................................9930539...9930539.......100,00%...100,00%

      Everything is fine.

      But when I launch the same analysis on 11.1.1.5, I get incorrect results:
      2011.....January.....Brand08.....1272..........9930539......0,01%......51,19%
      2011.....January.....Brand06.....13618........9930539......0,14%......49,90%
      2011.....January.....Brand04.....93024........9930539......0,94%......7,30%
      2011.....January.....Brand10.....97512........9930539......0,98%......53,60%
      2011.....January.....Brand07.....126720......9930539     ......1,28%......51,18%
      2011.....January.....Brand09.....141984......9930539     ......1,43%......52,62%
      2011.....January.....Brand01.....149760......9930539     ......1,51%......1,51%
      2011.....January.....Brand02.....192576......9930539     ......1,94%......3,45%
      2011.....January.....Brand03.....290061......9930539......2,92%......6,37%
      2011.....January.....Brand05.....4216695....9930539......42,46%.....49,77%
      2011.....January.....Brand11.....4607317....9930539......46,40%.....100,00%
      TOTAL..................................9930539...9930539.......100,00%...800,00%

      I.e., on 11.1.1.3 my sort settings take effect (sorting by year, month, amount value), but in 11.1.1.5 sort settings don't take any effect (sorting by default - year, month, brand name) and RSUM get incorrect results.

      Does anyone know how to solve this problem?