Welcome to the new CogKnowHow.com!


If you have trouble logging in, reset your password via the login window
by clicking the 'Forgot Password' link.

Query Studio: Cognos does not Summarize Totals Properly


#1

Hi,
In a Query Studio Package, I have a Query Subject that calculates the age of a particular item. To simplify here, there is a Query Subject (QS_Age_Calc) at Business Layer which goes like this (in FM)

QS_Age_Calc (total (case when a=x then -1 when a=y then 0 when a=z then 1 else 0 end ))

However, when QS_Age_Calc = -1, then we have to show it as 0 in the report which we are able to show successfully with another case statement which goes like this:

QS_Age (Case when QS_Age_Calc < 0 then 0 else QS_Age_Calc end )

The problem is that though we are able to successfully able to show it at the adhoc report, the summary (at the bottom of the report) does not work properly, for eg, The Report output is:
Item | Age
Item 1 | 0
Item 2 | 15
Item 3 | 0
Summary| 14
Here, Summary is shown as 14 at the report level but it should be 15.
Also, QS_Age_Calc for Item 1 = -1, but we are displaying it as 0 because of QS_Age.
QS_Age_Calc for Item 2 = 15, and, QS_Age_Calc for Item 3 = 0.
The cognos here behaves some weird when it comes to summarize the all items. Can anyone please suggest here what to do. Its urgent as we are just stuck up hereā€¦

Below are the SQLs so genarated:

--COGNOS SQL
SELECT ITEM_FACT.ITEM_ID as Item ID,
 XSUM (case when (XSUM (case 
                     when (ITEM_FACT.START_DATE = DATE_DIMN.CAL_DT) then (-1) 
 when (DATE_DIMN.CAL_DT > current_date) then 0 
 when DATE_DIMN.BUSINESS_DAY_FLAG = 'Y' then 1
 else 0
 end for ITEM_FACT.ITEM_ID )
 < 0) then 0
 else XSUM (case 
                     when (ITEM_FACT.START_DATE = DATE_DIMN.CAL_DT) then (-1) 
 when (DATE_DIMN.CAL_DT > current_date) then 0 
 when DATE_DIMN.BUSINESS_DAY_FLAG = 'Y' then 1
 else 0
 end for ITEM_FACT.ITEM_ID )
 
 end at ITEM_FACT.ITEM_ID for ITEM_FACT.ITEM_ID ) as Item_Age
FROM START_DATEITEM_FACT ITEM_FACT,
 START_DATEAHWT_CAL_DIM DATE_DIMN
WHERE ((DATE_DIMN.CAL_DT <= current_date)
 AND (DATE_DIMN.CAL_DT >= ITEM_FACT.START_DATE))
GROUP BY ITEM_FACT.ITEM_ID
-- NATIVE SQL
SELECT T0.C0 Item ID,
 first_value(T0.C1) over (partition by T0.C0) Item_Age
FROM (SELECT ITEM_FACT.ITEM_ID C0,
 case 
 when sum(case 
                     when (ITEM_FACT.START_DATE = DATE_DIMN.CAL_DT) then (-1) 
 when (DATE_DIMN.CAL_DT > current_date) then 0 
 when DATE_DIMN.BUSINESS_DAY_FLAG = 'Y' then 1
 else 0 
 end ) < 0 then 0 
 else sum(case 
                     when (ITEM_FACT.START_DATE = DATE_DIMN.CAL_DT) then (-1) 
 when (DATE_DIMN.CAL_DT > current_date) then 0 
 when DATE_DIMN.BUSINESS_DAY_FLAG = 'Y' then 1
 else 0
 end ) 
 end C1
 FROM ITEM_FACT ITEM_FACT,
 AHWT_CAL_DIM DATE_DIMN
 WHERE DATE_DIMN.CAL_DT <= current_date
 AND DATE_DIMN.CAL_DT >= ITEM_FACT.START_DATE
 GROUP BY ITEM_FACT.ITEM_ID) T0 FOR FETCH ONLY

Thanks in advance
Rocky