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

# Problem with Sort settings and RSUM function in 11.1.1.5

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?
• ###### 1. Re: Problem with Sort settings and RSUM function in 11.1.1.5
Hi,
Can u try copying the xml from advanced tab of 1.1.3 version to 1.1.5 version and check for results.
Or
Try recreating rsum again in 115.

Seems xml corrupt issue.
• ###### 2. Re: Problem with Sort settings and RSUM function in 11.1.1.5
The texts in "SQL Issued" in 11.1.1.3 and 11.1.15 are equal! In 11.1.1.3:

SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8 FROM (
SELECT s_0 s_0, s_1 s_1, s_2 s_2, s_3 s_3, s_4 s_4, s_5 s_5, s_6 s_6, s_7 s_7, s_8 s_8
FROM (
SELECT
0 s_0,
"DB"."Ship"."Year" s_1,
"DB"."Ship"."Month" s_2,
"DB"."Ship"."Brand" s_3,
SORTKEY("DB"."Ship"."Month") s_4,
"DB"."Ship"."Amount" s_5,
CAST ("DB"."Ship"."Amount" as DOUBLE)/SUM("Ship"."Amount" by "DB"."Ship"."Year","DB"."Ship"."Month")*100 s_6,
RSUM(CAST("DB"."Ship"."Amount" as DOUBLE)/(SUM("Ship"."Amount" by "DB"."Ship"."Year","DB"."Ship"."Month"))*100 by "DB"."Ship"."Year","DB"."Ship"."Month") s_7,
SUM("DB"."Ship"."Amount" by "DB"."Ship"."Year","DB"."Ship"."Month") s_8
FROM "DB"
ORDER BY 1, 2, 3, 6
) q0
) djm ORDER BY 1, 2, 3, 6

and in 11.1.1.5

SELECT s_0, s_1, s_2, s_3, s_4, s_5, s_6, s_7, s_8 FROM (
SELECT
0 s_0,
"DB"."Ship"."Year" s_1,
"DB"."Ship"."Month" s_2,
"DB"."Ship"."Brand" s_3,
SORTKEY("DB"."Ship"."Month") s_4,
"DB"."Ship"."Amount" s_5,
CAST ("DB"."Ship"."Amount" as DOUBLE)/SUM("Ship"."Amount" by "DB"."Ship"."Year","DB"."Ship"."Month")*100 s_6,
RSUM(CAST("DB"."Ship"."Amount" as DOUBLE)/(SUM("Ship"."Amount" by "DB"."Ship"."Year","DB"."Ship"."Month"))*100 by "DB"."Ship"."Year","DB"."Ship"."Month") s_7,
SUM("DB"."Ship"."Amount" by "DB"."Ship"."Year","DB"."Ship"."Month") s_8
FROM "DB"
) djm ORDER BY 1, 2, 3, 6

I can't understand what is the problem!
• ###### 3. Re: Problem with Sort settings and RSUM function in 11.1.1.5
See the order by in last of both query. In 115 its missing. Copy the xml or sql from 113 and it will work fine
• ###### 4. Re: Problem with Sort settings and RSUM function in 11.1.1.5
I can copy "Analysis XML" from 113 to 115. But I can't copy "SQL Issued" - this section is not editable! If I copy only "Analysis XML" - there is no effect!
• ###### 5. Re: Problem with Sort settings and RSUM function in 11.1.1.5
Err ..ok. Then how did u copy the above SQL ? You should be able to copy paste the SQL. Just try re-login.

Also, u urself can see the diff in sql generated...so just try to create the same report as u have in 113 if copy/paste is not working for u.
• ###### 6. Re: Problem with Sort settings and RSUM function in 11.1.1.5
I can copy text from "SQL Issued", but I can't edit text in this field. I can edit text in field "Analysis XML". I copy text from 113 to 115 and then press "Apply XML" button. Text in "SQL Issued" is changed, but the text different than in 113.

I tried to recreate analysis in 115 without copy, but get the same result - sorting by Year, Month, Amount doesn't work!
• ###### 7. Re: Problem with Sort settings and RSUM function in 11.1.1.5
We are having the same problem, have you found any solution? I noticed the SQL was different too, but couldn't find any way to change it.
• ###### 8. Re: Problem with Sort settings and RSUM function in 11.1.1.5
Do you have any excluded (not hidden) columns in your view? I found that RSUM/RCOUNT/RMIN/RMAX in 11.1.1.5 do not behave as it did in 11.1.1.3 when there are excluded columns (and sorts, in some cases).

If you have any excluded columns, try adding them to the view and making them hidden instead, that appeared to make it work correctly for me (or at least as it did in 11.1.1.3), although it may not look the same but at least the values might be right.

Edited by: user9034165 on Sep 23, 2011 7:30 AM
• ###### 9. Re: Problem with Sort settings and RSUM function in 11.1.1.5
I also had the same problem.

It is right, that the hiding all columns returnes the right rusult. But you wrote that the report not look the same.
So this isn't a possible solution for the problem.

Do somebody have on other way to return the correct value?

Thx!
• ###### 10. Re: Problem with Sort settings and RSUM function in 11.1.1.5
I raised an SR yesterday, so will post what they come back with. The only "workaround" I've found is to create separate reports for each view to ensure there are no excluded columns, but it would be a nightmare to maintain.
• ###### 11. Re: Problem with Sort settings and RSUM function in 11.1.1.5
Likely cause of issue (excluding columns can cause list to be re-sorted)

Bug 13258530: OBI 11G - RSUM VALUE NOT TOTALLING CORRECTLY AFTER SORTING COLUMN
• ###### 12. Re: Problem with Sort settings and RSUM function in 11.1.1.5
Hi!
The Oracle-Support wrote me the following text to my SR:

"I found two bugs which correspond to what you observe. One was for excluded columns affecting the RSUM and the other was that sorting a column can affect the RSUM. The two bugs are being worked on under a merged bug 13325511. I don't currently have an ETA for this bug fix.

For now, instead of putting columns in the excluded section of a report, include them in the report and set the visibility property for the columns to hidden so that end users do not see them. Also remove any sorting if that is affecting the results.

Regards"

It seems that the workaround are
- create separate reports for each view
OR
- use the "Display as running sum"-option in the pivot-table-element.

Have an nice weekend!
Tobias
• ###### 13. Re: Problem with Sort settings and RSUM function in 11.1.1.5
Heard from Oracle Support, there is a patch for this issue. Have not applied it yet.

Patch 13611078 TRACKING BUG FOR 11.1.1.5.0 BP2 PATCHSET (BP1 BUG 13562882 + NEW BUG FIXES)
which includes fix for Bug 13393602:RSUM NOT CALCULATING CORRECTLY WHEN THERE ARE EXCLUDED COLUMNS