Problem with Sort settings and RSUM function in 11.1.1.5
852989 Aug 1, 2011 7:05 AMHello, 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?
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?