Why 2 reports are behaving differently when calculation is same for both

Sangeeta Pandey

Hello Experts,

I am wondering why my 2 different DV reports is behaving differently , though I used every thing same (like calculation, data etc):

Report 1:

Here I see value under each brand is sum for all brands:

76698.16+97528.71+38 =174,264.87

However if I generate same report for other dimension product (than brand), in exactly same way then value is showing per product than sum for all products:

This is surprise for me because I used same calculation, same options (#Values(Slice), Color), but just for 2 different dimension (brand in first report, product in 2nd report)

Kindly advise.

Thanks,

Gianni Ceresa

There is no magic in DV: everything is a query that retrieve some data.

Look at the generated LSQL, look at the generated real SQL and adjust accordingly to fix your error.

As it's based on your source and you don't say a word about it, where it is from and how it got there, nothing else can be said.

[Deleted User]

+1 to Gianni. What's your source? How is the logic of aggregation modeled?

Also: Those aren't "reports". Those are visualizations in side of the canvas of a DV project.

A "report" means BI Publisher. An "analysis" means classic analytics.

Sangeeta Pandey

https://community.oracle.com/tech/apps-infra/discussion/comment/16806080#Comment_16806080

Thanks Gianni, Kindly advise if below is the right place to fetch underneath sql:

At this place I can see ,sql for both visualizations are similar :

Visualization 1:

SELECT 

"Claim Line Coverage"."Brand"."Brand Code" s_1, 

CAST(NULL AS DOUBLE) s_2, 

FILTER("Claim Line Coverage"."Claim Line Coverages"."Amount" 

USING "Claim Line Coverage"."Process Type"."Process Type"='Claim' 

and "Claim Line Coverage"."Claim Line Coverages"."Action"='C') s_3 

FROM "Claim Line Coverage" 

Visualization 2:

 SELECT

"Claim Line Coverage"."Product"."Product" s_1,

CAST(NULL AS DOUBLE) s_2,

FILTER("Claim Line Coverage"."Claim Line Coverages"."Amount" 

USING "Claim Line Coverage"."Process Type"."Process Type"='Claim' 

and "Claim Line Coverage"."Claim Line Coverages"."Action"='C') s_3

FROM "Claim Line Coverage"

Thanks,

Gianni Ceresa

That's LSQL, now look at the physical SQL generated by those LSQL statements. Because 2 very similar LSQL can have very different physical SQL generated (that's the real power of Oracle Analytics with the RPD).