0 Replies Latest reply: Dec 28, 2012 8:06 AM by 970365 RSS

    Presentation service(Answer)-->Agg rule

    970365
      Hi
      I set agg rule in "Ave" in edit column formula.and values is

      (SUM("FACT - AGENT TIME STATISTICS"."STAFFED TIME") / 60) / (COUNT(DISTINCT "FACT - AGENT TIME STATISTICS"."DATE ID") * "FACT - AGENT TIME STATISTICS"."INTERVAL TYPE")

      I run the report.My SQl query is

      -------------------- Logical Request (before navigation):

      RqList
      DIM_TIME_DIMENSION_MASTER.30 Mins Interval as c1 GB,
      DIM_TEAM_MAPPING_MST.TEAM_NAME as c2 GB,
      (sum(FACT_AGENT_TIME_STATISTICS.STAFFED_TIME by [ DIM_TEAM_MAPPING_MST.TEAM_NAME, DIM_TIME_DIMENSION_MASTER.30 Mins Interval] ) / nullif( 60 , 0)) / nullif( FACT_AGENT_TIME_STATISTICS.INTERVAL * count(distinct FACT_AGENT_TIME_STATISTICS.FKEY by [ DIM_TEAM_MAPPING_MST.TEAM_NAME, DIM_TIME_DIMENSION_MASTER.30 Mins Interval] ) , 0) as c3 GB
      DetailFilter: DIM_TIME_DIMENSION_MASTER.30 Mins Interval = '08:30'
      OrderBy: c1 asc, c2 asc


      -------------------- Sending query to database named CSG_NEW_MODEL (id: <<7433235>>):

      select distinct D2.c3 as c1,
      D2.c4 as c2,
      (D2.c1 / nullif( 60, 0)) / nullif( D1.c1 * D2.c2, 0) as c3
      from
      (select distinct T402.INTERVAL as c1
      from
      SCBT_R_TIME_DIMENSION_MASTER T646 /* DIM_TIME_DIMENSION_MASTER */ ,
      SCBT_AGENT_TIME_STATISTICS T402 /* FACT_AGENT_TIME_STATISTICS */
      where ( T402.FKEY = T646.DATE_ID and substr(TO_CHAR(( CAST(T646.FULL_DATE as DATE) + ( case when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 0 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 15 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 30 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 45 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 60 end / 1440 ) ),'MM/DD/YYYY HH24:MI') , 12 , 15) = '08:30' )
      ) D1,
      (select sum(T402.STAFFED_TIME) as c1,
      count(distinct T402.FKEY) as c2,
      substr(TO_CHAR(( CAST(T646.FULL_DATE as DATE) + ( case when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 0 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 15 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 30 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 45 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 60 end / 1440 ) ),'MM/DD/YYYY HH24:MI') , 12 , 15) as c3,
      T631.TEAM_NAME as c4
      from
      SCBT_R_TEAM_MAPPING_MST T631 /* DIM_TEAM_MAPPING_MST */ ,
      SCBT_R_TIME_DIMENSION_MASTER T646 /* DIM_TIME_DIMENSION_MASTER */ ,
      SCBT_AGENT_TIME_STATISTICS T402 /* FACT_AGENT_TIME_STATISTICS */
      where ( T402.FKEY = T646.DATE_ID and T402.BANK_ID = T631.BANK_ID and substr(TO_CHAR(( CAST(T646.FULL_DATE as DATE) + ( case when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 0 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 15 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 30 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 45 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 60 end / 1440 ) ),'MM/DD/YYYY HH24:MI') , 12 , 15) = '08:30' )
      group by T631.TEAM_NAME, substr(TO_CHAR(( CAST(T646.FULL_DATE as DATE) + ( case when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 0 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 15 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 30 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 30 when TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') = 45 then TO_NUMBER(TO_CHAR(T646.FULL_DATE, 'mi'), '99') - 60 end / 1440 ) ),'MM/DD/YYYY HH24:MI') , 12 , 15)
      ) D2
      order by c1, c2

      -----------------

      Could you please let me know How can i find "Avg" fn is working or not.In this query i am not able to see any AVG calculation.

      If set AVG agg fun in this column it will work or not?.I dont know why set ave fun in my report.I am new to my project.I am not aware about requirment.
      In 10g this report working fine but when i move to 11g.It showing wrong values(data mismatch 10g report and 11g report).How will fix.

      Thanks
      Gram