Conditional sum - how to do that

[manita]

I'm not really a Cognos "pro", so I hope my question is an easy one here.

I need to make a list of some of the financial information for our department. Every bookrow has 4 attributes. Sometimes people realise later that one of the attributes is incorrect and needs to be corrected.
The program doesn't allow to change the data, it has to be corrected negative and then the correct data can be added. This results in 3 different bookrows, one of them being correct. In my list, I only want to see the correct data.

I thought the best way to do that is to make a sum of the rows that have these things in common:
the 4 attributes, the invoice number and the booking kind. Then I want to see only the rows where that sum is not 0. I tested that in Excel and it works.

The problem is that I have no idea how to make that sum in Cognos. Can anyone help me? If you know a different way to get the wanted result, I will be happy too.

Example of a correction: attrib 4 needs to be changed from null to D.

First booking:
Invoice number 1
Booking kind: Creditors
Attrib 1: A
Attrib 2: B
Attrib 3: C
Attrib 4: (null)
Amount: 100

Correction 1:
Invoice number 1
Booking kind: Creditors
Attrib 1: A
Attrib 2: B
Attrib 3: C
Attrib 4: (null)
Amount: -100

Correction 2:
Invoice number 1
Booking kind: Creditors
Attrib 1: A
Attrib 2: B
Attrib 3: C
Attrib 4: D
Amount: 100

I only want to see the information of correction 2 in this case, the list needs to leave the first 2 out.

[Lynn]

Presumably this is a relational source?

You could try creating a detail filter with an expression something like this:

Code Select Expand


total ( [Amount] for [Invoice number], [Booking kind], [Attrib 1], [Attrib 2], [Attrib 3], [Attrib 4] ) <> 0


You might need to set application property to "after auto aggregation". This may perform poorly if your data volumes are very large. Of course you could also be filtering out legitimate data if there is a possibility that a zero value entry can exist.

If this is in a data warehouse environment then I'd say some ETL work could be done to simplify this for reporting purposes.

[manita]

Thank you very much, it works perfectly!