I have a report with amount and year column. Both of them come from different query subjects. My amount column is repeating for each year
Emp No Amount Year
--------------------------------------------
300 30,000 2006
30,000 2008
30,000 2009
30,000 2011
301 20,000 2008
-----------------------------------------------
Total 140,000
My Report displays like below since I grouped amount field
Emp No Amount Year
--------------------------------------------
300 30,000 2006
2008
2009
2011
301 20,000 2008
-----------------------------------------------
Total 140,000 -- > I need the correct total here which is 30,000+20,000 = 50,000
How can I do this ? Please suggest.
Thank you.
Hey Harry,
You have a common problem, where the values will all repeat if there is a single column with a new value ... which makes aggregating more fun ! I would solve this problem in SQL (surely others will chime in promptly with an equally effective Cognos solution:-) )
Say you have these 3 values in your employee table in a SQL Server database, I would query the table like this:
SELECT A.[Emp No], B.[Amount_Total], A.[Year]
FROM Employee A
INNER JOIN (
SELECT [EMP No], SUM([Amount]) as Amount_Total
FROM Employee
GROUP BY [EMP No]
) B ON (A.[Emp No]=B.[Emp No])
Basically ... you get the totals you want in the derived table and then join back to the original table to return all the fields you want.
good luck ! ;)
Dave, thank you for your reply.
I solved this using running-count.
if(running-count([Amount] for [EmpNO]) = 1)
then ([Amount])
else (null)
Source: http://www-01.ibm.com/support/docview.wss?uid=swg21340990