Divide 2 Columns in Union Report

Ebin Cherian

Hi All,

Oracle Business Intelligence 12.2.1.1.0.

I am struggling to create a calculated column which perform division on Union.

I need to create a calculated column to perform calculation Amt 1/ Amt 2.

So far result is correct. Now I am going to add a Region column to both analysis and then exclude it in the pivot table layout. When I do that, Division result is not correct.

How can I fix this issue ?

Thanks

Ebin

[Deleted User]

By looking at the query and your models. Including the Region changes the query grain!

Look at the two detailed queries. Look at how "Region" changes things. Look at what data arrives and whatt gets calculated.

This is analytics 101 - you have to get behind it and understand the impact of what you are doing.

Joel

If I understand correctly, you want to divide 2 measures derived from a union report. If so, you add a result column and use the syntax saw_0/saw_1 where the number indicates the position of the column in the analysis - where 0 = position 1 and 1 = position 2 etc

Teresa Modesta

If you had the right answer with the region in can you make region hidden using the format properties to get the right result, or does that yield the wrong level of summarisation?

[Deleted User]

That's my point - it's a conceptual question which may point to insufficient or just completely wrongly done logical modeling.

Ebin Cherian

Hi Joel,

I tried that already, but not getting the desired result.

Ebin Cherian

Hiding the region results in wrong level.

Ebin Cherian

Hi Christian,

Before posting in OTN, I verified the data part. Report total shows the same number with or without region. Only issue is while dividing 2 columns in union report. If I specify the aggregation rule as average for the "division column", then it calculates the average of the division for each record.

Joel

then you need to look at @Christian Berg's response in your thread. Check your model as you're adding in the Region dimension attribute but you don't want to aggregate at the Region level so I'm personally confused with what it is that you're trying to achieve.

Ebin Cherian

Sorry for the confusion... We have a different view within same report where Region is used. For the pivot table view in question, it gives a summary and therefore region is excluded.

Teresa Modesta

If you need a fast fix then you could use the physical sql generated in opaque view or direct database request.

For a longer term fix I would look at the fact to dimension levels set in the LTS