sqlcognoscognos-8cognos-bicognos-10

How do I create a summary data item in Cognos Report Studio?

I am working on a Cognos Report Studio Version 10 where I have to convert SQL code into Data Items. I have all the Columns required in the package to drag as Data Items in the query but I am stuck where there's calculated fields. This is one of them

NET_ORIG_AMT derived from (ORIG_ AMT) – (PARTICIPATION_ORIG_AMT)

ORIG_AMT was derived from a simple IF THEN ELSE function and could be easily created as a calculated data item. But PARTICIPATION_ORIG_AMT is coming from this SQL code:

SELECT          LEAD_ACCT, 
                PART_FLAG, 
                SUM (ORIG_AMT) AS PARTICIPATION_ORIG_AMT,                                                                                                   
FROM            TableName
GROUP BY        LEAD_ACCT, PART_FLAG
HAVING          PART_FLAG = 'Y'

How do I create a Data Item for PARTICIPATION_ORIG_AMT?

Pl note: I have LEAD_ACCT, PART_FLAG fields as calculated fields in the Query.

Solution

  • Can you help me understand how to write SUM (ORIG_AMT) AS PARTICIPATION_ORIG_AMT Group by LEAD_ACCT, PART_FLAG in Cognos Report Studio?

    The equivalent in a Cognos expression would be:

    total([ORIG_AMT] for [LEAD_ACCT],[PART_FLAG])