Categories
Calculations truncating in reports after upgrade to 12c
We are in the process of trying to upgrage from 11g (11.1.1.9.0) to 12c (12.2.1.3.0) and we are seeing some pretty big problems with our existing reports. Many of the numbers in our calculations are no longer rounding to whatever precision we select in the formatting of that number, they are now just truncating the numbers after 2 decimal spots.
I've tried to put together a very simple and easy example:
11g version: 11.1.1.9.0
12c version: 12.2.1.3.0
Database version: SQL Server 2008 R2
OS version: Windows Server 2016 Standard
I have a report that simply shows 2 attributes (YearWeek & SubmissionDesc), and 1 calculation (CollectedRev). I built the report in 11g (which shows the results correctly) and then put the XML into 12c (which shows the results incorrectly).
Here is the formula for 'Snap Rev %' in the report above:
Notice how 12c just truncates after 2 decimal spots
I have attached the XML for the report out of each environment.
Below is how the query gets generated in 11g vs how the query is generated in 12c:
********************************************************
--11.1.1.9.0
WITH
SAWITH0 AS (select sum(T346632."CollectedRev") as c1,
sum(case when T346625."SnapIndicator" = 'Y' then T346632."CollectedRev" end ) as c2,
T346626."YearWeek" as c3,
T346642."SubmissionDesc" as c4
from
"ExOneDataMart"."EODM1"."DimPermSubmission" T346642 /* Dim_DimPermSubmission */ ,
"ExOneDataMart"."EODM1"."DimPermSubYrWk" T346626 /* Dim_DimPermSubYrWk */ ,
"ExOneDataMart"."EODM1"."DimPermProduct" T346625 /* Dim_DimPermProduct */ ,
"ExOneDataMart"."EODM1"."FactPermRevSnpsht" T346632 /* Fact_FactPermRevSnpsht */
where ( T346625."DimPermProductID" = T346632."DimPermProductID" and T346626."DimDateID" = T346632."DimNewMoneyWeekID" and T346626."YearWeek" = 201821 and T346632."DimPermSubmissionID" = T346642."DimPermSubmissionID" and T346642."SubmissionDesc" = 'CAIR RBC est - no spread adj. Wk 21' and (T346625."PermProdBusName" = 'PRBus1' or T346625."PermProdBusName" = 'PRBus2' or T346625."PermProdBusName" = 'PRBus3' or T346625."PermProdBusName" = 'PRBus4' or T346625."PermProdBusName" = 'PRBus5') )
group by T346626."YearWeek", T346642."SubmissionDesc")
select 0 as c1,
D1.c3 as c2,
D1.c4 as c3,
sum(D1.c2) over () / nullif( sum(D1.c1) over () , 0) * 100 as c4,
0 as c5,
D1.c2 as c6,
D1.c1 as c7
from
SAWITH0 D1
********************************************************
********************************************************
--12.2.1.3.0
WITH
SAWITH0 AS (select sum(T335462."CollectedRev") as c1,
sum(case when T335455."SnapIndicator" = 'Y' then T335462."CollectedRev" end ) as c2,
T335456."YearWeek" as c3,
T346642."SubmissionDesc" as c4
from
"ExOneDataMart"."EODM1"."DimPermSubmission" T335472 /* Dim_DimPermSubmission */ ,
"ExOneDataMart"."EODM1"."DimPermSubYrWk" T335456 /* Dim_DimPermSubYrWk */ ,
"ExOneDataMart"."EODM1"."DimPermProduct" T335455 /* Dim_DimPermProduct */ ,
"ExOneDataMart"."EODM1"."FactPermRevSnpsht" T335462 /* Fact_FactPermRevSnpsht */
where ( T335455."DimPermProductID" = T335462."DimPermProductID" and T335456."DimDateID" = T335462."DimNewMoneyWeekID" and T335456."YearWeek" = 201821 and T335462."DimPermSubmissionID" = T335472."DimPermSubmissionID" and T346642."SubmissionDesc" = 'CAIR RBC est - no spread adj. Wk 21' and (T335455."PermProdBusName" = 'PRBus1' or T335455."PermProdBusName" = 'PRBus2' or T335455."PermProdBusName" = 'PRBus3' or T335455."PermProdBusName" = 'PRBus4' or T335455."PermProdBusName" = 'PRBus5') )
group by T335456."YearWeek", T346642."SubmissionDesc")
select distinct 0 as c1,
D1.c3 as c2,
D1.c4 as c3,
sum(D1.c2) over () / nullif( sum(D1.c1) over () , 0) * 100 as c4,
sum(D1.c2) over () / nullif( sum(D1.c1) over () , 0) * 100 as c5
from
SAWITH0 D1
********************************************************
The really weird thing is that when I run either of these queries SQL Server Management Server, my calculation comes back as 36.56.
HOWEVER, the report in 11g actually shows correct results of 36.5693174722. But that very same report shows 36.56000000 in 12c.
What could be causing this to happen? And what might I do to remedy this situation?
Message was edited by: Chris Arnold
Answers
-
Check if the datatypes of the figures that you use to calculate the percentages, are these integers?
If so change them to numeric/double.
Alternatively/additionally you can use Cast as Double in your calculation to make sure them outcome will be a decimal number.
See also: https://gerardnico.com/dat/obiee/int
0 -
Beaten by Martin. Watch out for integer divisions. 1 / 3 = 0 !
0 -
Some very odd things here though...
The measure I am using ("Perm Rev Snapshot"."Collected Rev") in my calculation is DOUBLE already. I would think that no matter the calculation, as long as all measures in the calc are the same datatype, then it would work itself out. This obviously isn't the case in 12c. The deeper I dive into this, the stranger it gets to me.
Just to reiterate, the calc in 11g (without casting anything) returns the following answer:
36.5693174722
So, I went into the formula in 12c and started doing some casting. Here is what I'm seeing:
***************
(
(FILTER(SUM("Perm Rev Snapshot"."Collected Rev") USING ("Perm Product"."Snap Indicator" = 'Y')))
/
SUM("Perm Rev Snapshot"."Collected Rev")
)* CAST(100 as DOUBLE)
This formula comes out to 36.5599975586
***************
***************
(
CAST((FILTER(SUM("Perm Rev Snapshot"."Collected Rev") USING ("Perm Product"."Snap Indicator" = 'Y'))) as DOUBLE)
/
CAST(SUM("Perm Rev Snapshot"."Collected Rev") as DOUBLE)
)*100
This formula comes out to 36.5693168640
****************
****************
(
CAST((FILTER(SUM("Perm Rev Snapshot"."Collected Rev") USING ("Perm Product"."Snap Indicator" = 'Y'))) as DOUBLE)
/
CAST(SUM("Perm Rev Snapshot"."Collected Rev") as DOUBLE)
)* CAST(100 as DOUBLE)
This formula comes out to 36.5693168640
*****************
****************
(
(FILTER(SUM(CAST("Perm Rev Snapshot"."Collected Rev" as DOUBLE)) USING ("Perm Product"."Snap Indicator" = 'Y')))
/
SUM(CAST("Perm Rev Snapshot"."Collected Rev" as DOUBLE))
)*100
This formula comes out to 36.5693154172
****************
****************
( CAST(
(FILTER(SUM("Perm Rev Snapshot"."Collected Rev") USING ("Perm Product"."Snap Indicator" = 'Y')))
/ SUM("Perm Rev Snapshot"."Collected Rev")
as DOUBLE)
)*100
This formula comes out to 36.5599975586
****************
So as you can see, every combination I try (casting as DOUBLES), I get a different answer almost everytime. And none of them come out to what 11g shows.
When I do the calculation in Excel, I can see that the answer given by 11g is the correct one.
574258188.19 / 1570327881.08 = 36.5693174722
I feel like I might be losing my mind here. There is something very simple that I must be overlooking.
0 -
My first question would be: Why that calc in the front-end and not the analysis to make sure you always have the correct result and data type?
0 -
@ Christian Berg
Well, the calculation that I have been showing in this thread is just for example -- for the sake of being able to display the issue.
In reality, we are seeing this in almost every place that we do any sort of division in 12c.
And the problem being that many of these instances (where this type of division is being done) are calculations that are only used once or twice in reports built by the business authors - and those calcs are only useful to their specific business area. Trying to build the calc in the RPD (or in the database) for every one of these would get crazy. We would end up with possibly hundreds of columns being shown in the Presentation Layer that would each only be used in 1 or 2 places -- those columns meaning very little to the majority of the people that see them. Does that make sense?
Do you think that this is something weird in the way that 12c communicates with our version of SQL Server? Like maybe the two don't play well together?
0 -
OK...so I kept looking at this calculation formula and something came to me. I realized the SUM function was being used in the calculation when it probably did not need to be (being that the default aggregation for "Perm Rev Snapshot"."Collected Rev" is already set to SUM in the RPD). There was definitely some redundancy there -- it was doing a SUM of a SUM. While I still am not sure why this should matter, I decided to simplify the calculation and remove the SUM from the formula. This seemed to correct the problem. Here is the calculation that I used:
(
FILTER("Perm Rev Snapshot"."Collected Rev" USING "Perm Product"."Snap Indicator" = 'Y')
/
"Perm Rev Snapshot"."Collected Rev"
)*100
This formula correctly returned: 36.5693174722
So...I am not totally sure where this gets me. I will need to further dive into all of the reports that are having this issue and see if the reports are all using the SUM function when unnecessary.
I am still left with wondering...why was this NOT a problem in 11g? And why using the extra SUM function caused the answer to be wrong? I want to understand this, and I'll keep looking at it and try to make sense of it.
In the mean time, I think I've taken up enough of your time on this. Thank you guys for taking the time to look at this with me. I am going to go ahead and mark this thread closed. Feel free to chime back in if you have any extra insight.
Thank you!
0 -
Chris Arnold wrote:I am still left with wondering...why was this NOT a problem in 11g? And why using the extra SUM function caused the answer to be wrong? I want to understand this, and I'll keep looking at it and try to make sense of it.
11 accepted more....let's say "fuzzy configuration" and was generally less draconian in the interpretation of things. Luckily 12 forces people to develop at least a *bit* more properly.
And the SUM is pretty much unnecessary to begin with when things are correct inside the RPD. All these front-end calcs you find in most customer catalogs are most of the time unnecessary and complicate things.
0