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 ONLYThanks in advance
Rocky