How do you write a sumproduct function in Cognos

[v2k4u]

How do you write a sumproduct function in Cognos when you are trying to replicate the formula in excel to Cognos Crosstab Report?
SELECT TO_DATE(TO_CHAR(a.row_date,'mm/dd/yyyy'),'mm/dd/yyyy') AS dt
        , CASE WHEN a.split = 42129 THEN 'Direct Check Enhancement' ELSE c.description END category
        , c.inbound_ind
        , SUM(a.acdcalls) + SUM(a.abandoned) AS offered
        , SUM(a.abandoned) AS abandoned
        , ROUND(SUM(a.acdtime+a.acwtime)/NULLIF(SUM(a.acdcalls),0),0) AS handle_time
        , SUM(a.calls_out) AS calls_out
        , ROUND(SUM(a.out_time)/NULLIF(SUM(a.calls_out),0),0) AS aht_out
        , SUM(availtime) AS availtime
        , SUM(stafftime) AS stafftime
        , SUM(auxtime) AS auxtime
        , NULL AS ans_time
        , SUM(a.acdcalls) AS acdcalls
        , SUM(a.acceptable) AS acceptable
        , ROUND(SUM(a.anstime)/NULLIF(SUM(a.acdcalls),0),0) AS asa_time
        , TO_NUMBER(TO_CHAR(b.date_key,'mm')) AS month
        , CASE WHEN b.date_key BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE-1,-1)) AND LAST_DAY(SYSDATE-1) THEN 1 ELSE 0 END AS current_month
        , d.yyyymm AS yyyymm_weekly
        , CASE WHEN a.split = 42129 THEN 'TAMPA' ELSE c.site END site
        , d.week_nbr AS week_nbr_weekly
FROM (     SELECT 'SPLIT' AS source
                , site
                , row_date
                , split
                , abncalls AS abandoned
                , CASE WHEN site = 'Fannin' THEN outflowcalls
                            ELSE acdcalls END AS acdcalls
                , acwincalls AS ext_call_in
                , acwoutcalls + auxoutcalls AS ext_call_out
                , acwoutcalls AS acwoutcalls
                , auxoutcalls AS auxoutcalls
                , holdtime/60 AS holdtime
                , holdcalls AS holdcalls
                , transferred AS transout
                , acd
                , acdtime
                , acwtime
                , anstime
                , acwoutcalls + auxoutcalls AS calls_out
                , auxouttime + acwouttime AS out_time
                , i_availtime AS availtime
                , i_stafftime AS stafftime
                , i_auxtime AS auxtime
                , acceptable
            FROM mis.mis_local_cms_dsplit
            UNION ALL

            SELECT 'VDN' AS source
                , site
                , row_date
                , TO_NUMBER(vdn) AS split
                , abncalls AS abandoned
                , CASE WHEN site = 'Fannin' THEN outflowcalls
                            ELSE acdcalls END AS acdcalls
                , NULL AS ext_call_in
                , NULL AS ext_call_out
                , NULL AS acwoutcalls
                , NULL AS auxoutcalls
                , NULL AS holdtime
                , NULL AS holdcalls
                , NULL AS transout
                , 2 AS acd
                , acdtime
                , acwtime
                , NULL AS anstime
                , NULL AS calls_out
                , NULL AS out_time
                , NULL AS availtime
                , NULL AS stafftime
                , NULL AS auxtime
                , acceptable
            FROM mis.mis_local_cms_dvdn
        WHERE vdn <> 42129
        UNION
        SELECT 'VDN' AS source
                , a.site
                , row_date
                , TO_NUMBER(a.vdn) AS split
                , abncalls AS abandoned
                , acdcalls
                , NULL AS ext_call_in
                , NULL AS ext_call_out
                , NULL AS acwoutcalls
                , NULL AS auxoutcalls
                , NULL AS holdtime
                , NULL AS holdcalls
                , NULL AS transout
                , acd
                , NULL acdtime
                , NULL acwtime
                , NULL anstime
                , NULL AS calls_out
                , NULL AS out_time
                , NULL AS availtime
                , NULL AS stafftime
                , NULL AS auxtime
                , acceptable
            FROM  mis.mis_local_cms_dvdn a
        WHERE site =  'Tampa' and vdn = 42129 and (skill1=231 OR skill2=231 OR skill3=231)
            ) a
JOIN dms.date_table b ON a.row_date = b.date_key 
LEFT JOIN mis_stage.acd_reporting_lookup c
    ON (UPPER(a.site) = c.site
        AND a.split = c.split
        AND a.row_date BETWEEN c.date_active_from AND c.date_active_to
        AND a.acd = NVL(c.acd,a.acd)
        AND a.source = c.type
        )
JOIN (        SELECT
        a.date_key
        ,CASE   WHEN NVL(b.rank,5) = 1 and CASE   WHEN b.rank = 1 then b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) THEN 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN TO_CHAR(ADD_MONTHS(date_key,-1),'yyyymm')
                WHEN NVL(b.rank,5) = 5 AND CASE   WHEN b.rank = 1 then b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) THEN 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN TO_CHAR(ADD_MONTHS(date_key,1),'yyyymm')
                ELSE TO_CHAR(date_key,'yyyymm')
        END AS yyyymm
        ,CASE   WHEN NVL(b.rank,5) = 1 and CASE   WHEN b.rank = 1 then b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) then 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN 5
                WHEN NVL(b.rank,5) = 5 and CASE   WHEN b.rank = 1 THEN b.dte-TRUNC(a.date_key,'MONTH') + 1
                WHEN b.rank IN (2,3,4,5) then 7
                ELSE LAST_DAY(a.date_key) - MAX(b.dte) OVER (PARTITION BY TO_CHAR(a.date_key,'YYYYMM'))
        END <=3 THEN 1
                ELSE NVL(b.rank,5)
        END AS week_nbr
        FROM dms.date_table a
        LEFT JOIN
                (
                SELECT TO_CHAR(date_key,'YYYYMM') YYYYMM,
                    date_key as dte,
                    day_of_month,
                    RANK() OVER (PARTITION BY month_of_year, year_number ORDER BY day_of_month ) as rank
                FROM dms.date_table
                WHERE day_of_week = 'FRIDAY'
                    AND date_key > TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-6)))
                )b
            ON b.dte-a.date_key  BETWEEN 0 AND 6 AND TO_CHAR(a.date_key,'YYYYMM') = b.YYYYMM
        WHERE a.date_key BETWEEN TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-6))) AND TRUNC(SYSDATE)

     ) d ON b.date_key = d.date_key
WHERE row_date BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE-1,-6)) AND LAST_DAY(SYSDATE-1)
    AND (c.reporting_desc IN ('INBOUND_COLL') OR  a.split = 42129)
GROUP BY row_date
    , CASE WHEN a.split = 42129 THEN 'Direct Check Enhancement' ELSE c.description END
    , CASE WHEN a.split = 42129 THEN 'TAMPA' ELSE c.site END
    , c.inbound_ind
    , TO_NUMBER(TO_CHAR(b.date_key,'mm'))
    , CASE WHEN b.date_key BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE-1,-1)) AND LAST_DAY(SYSDATE-1) THEN 1 ELSE 0 END
    , d.yyyymm
    , d.week_nbr
ORDER BY a.row_date, category
is the SQL that i am using for pulling a crosstab report and the value that i want to calculate as Sumproduct is based on Sum of Offered and Sum of Abandoned.

Please Refer to the attached document with tabs Tampa,Phoenix and Manila. Inb. Unit Aban. Rate is the one which i am trying to replicate in the Crosstab Report. Any help is greatly appreciated.