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

Question

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.

score 3 · Accepted Answer · answered May 30 '14 at 20:56

3

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])

answered May 30 '14 at 20:56

Johnsonium

2,005
1
13
15

score 0 · Answer 2 · answered May 21 '14 at 14:29

0

3 choices:

Create a separate query in Cognos specifically for PARTICIPATION_ORIG_AMT. Join this query to your primary query on the appropriate fields and bring this in.
Create a query subject for PARTICIPATION_ORIG_AMT within the model itself, and do the above in RS as described above. Preferred method if you are re-using this data item across reports.
Create a view in SQL which takes care of all of this special logic.

answered May 21 '14 at 14:29

toddsonofodin

513
2
4

Thank you for your response. Please be aware that I don't have access to Framework Manager. Even If I try to create separate query in RS, how can I still translate this code as a data item considering the fact that all the fields in the select statement are calculated fields? – Excited_to_learn May 21 '14 at 14:42
Recreate them in the query or create derived queries where you progressively add more business logic. – toddsonofodin May 21 '14 at 14:55
Sorry not able to understand. I am very new to SQL coding and Cognos too. Anyways thank you for your help. – Excited_to_learn May 21 '14 at 15:06
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? – Excited_to_learn May 21 '14 at 17:53

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

2 Answers2