0 Replies Latest reply: Jan 5, 2013 7:37 AM by 970365 RSS

    Report Sum vs REPORT_AGGREGATE

    970365
      Hi
      I am using 10g and 11g.Could you please explain diff between Report Sum vs REPORT_AGGREGATE in 11g.

      My OBIEE10g SQL

      SELECT "MAP - ACTIVITY LIST"."ORDERING ID" saw_0,
      "MAP - ACTIVITY LIST"."ACTIVITY NAME" saw_1,
      "MAP - ACTIVITY LIST"."VOLUME DEFINITION" saw_2,
      "MAP - ACTIVITY LIST"."STANDARD ACTIVITY RATE (SAR)" saw_3,
      SUM("FACT- AGENT FF VOLUME"."FF VOLUME") / COUNT(DISTINCT "FACT- AGENT FF VOLUME".BUSINESS_DATE BY "FACT- AGENT FF VOLUME"."COUNTRY CODE", "DIMENSION - LOCATION".DEPARTMENT) saw_4,
      SUM("FACT- AGENT FF VOLUME"."FF VOLUME" / CAST("FACT- AGENT FF VOLUME"."STANDARD ACTIVITY RATE (SAR)" AS DOUBLE)) / COUNT(DISTINCT "FACT- AGENT FF VOLUME".BUSINESS_DATE BY "FACT- AGENT FF VOLUME"."COUNTRY CODE", "DIMENSION - LOCATION".DEPARTMENT) saw_5 FROM "Team Performance"

      OBIEE11g Query
      SELECT
      0 s_0,
      "Team Performance"."MAP - ACTIVITY LIST"."ACTIVITY NAME" s_1,
      "Team Performance"."MAP - ACTIVITY LIST"."ORDERING ID" s_2,
      "Team Performance"."MAP - ACTIVITY LIST"."STANDARD ACTIVITY RATE (SAR)" s_3,
      "Team Performance"."MAP - ACTIVITY LIST"."VOLUME DEFINITION" s_4,

      REPORT_AGGREGATE(SUM("Team Performance"."FACT- AGENT FF VOLUME"."FF VOLUME")/COUNT(DISTINCT "Team Performance"."FACT- AGENT FF VOLUME"."BUSINESS_DATE" BY "Team Performance"."FACT- AGENT FF VOLUME"."COUNTRY CODE","Team Performance"."DIMENSION - LOCATION"."DEPARTMENT") BY ) s_5,

      SUM("Team Performance"."FACT- AGENT FF VOLUME"."FF VOLUME")/COUNT(DISTINCT "Team Performance"."FACT- AGENT FF VOLUME"."BUSINESS_DATE" BY "Team Performance"."FACT- AGENT FF VOLUME"."COUNTRY CODE","Team Performance"."DIMENSION - LOCATION"."DEPARTMENT") s_6,

      1.Now my question both query functinality same or diff?
      i have same report in 10g then migrated 11g,but return mismatch data between 2 environment.

      If i put Report_Sum instead report_Aggregation then matching fine.
      2.so what is diff report sum and report_aggregation?

      3.So is the same way i need to change all the reports after migrating or ?

      could please reply above question?

      Thanks
      Gram