Query Studio: Cognos does not Summarize Totals Properly

[rockys1]

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)

Code: [Select]

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:

Code: [Select]

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:

Code: [Select]

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


Code: [Select]

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

[blom0344]

I may be missing the point, but why assign a -1 value in a totalization in the first place?

[Lynn]

In addition to Blom's comment I suggest that the total function in your calculation is problematic. The scope of the total is generally defined by a "for" clause and could vary from report to report. Perhaps you can remove the function in the expression and just set the aggregate property to total.