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 Expand

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 Expand

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 Expand


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


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