SOLVED : Distinct values for amount - Please Help

[HDA]

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.

[TheCognosDave]

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 !  ;)

[HDA]

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