IBM Support

How to create a Distinct Total thus preventing repeating values

Troubleshooting


Problem

Is it possible to create a Distinct Total to prevent repeating values in a report?

Resolving The Problem

Using Part as a grouped Item, and Invoice amt as the field duplicating values as the examples, you would need to do the following:

1. Modify the expression of the Invoice Amount to read: Total ( Distinct [Invoice Amount] for [Part] )
2. Make sure you have your Level Span in the Properties pane of this field set to the grouped Item, in this case Part
3. Make sure the Aggregation of the fact and data in the detail and footer are listed as Total.
4. Once you total the Invoice Amount column, you will get the distinct total.

Another option would be the following:

1. Modify the expression of Invoice Amount to read: If (running-count ([Invoice Amount] for [Part] = 1)) then ([Invoice Amount]) else Null
2. Change the Level Span in the properties Pane to Part.
3. Once you total this new calculated column, you will get the distinct total

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2;10.1.1;10.1;8.4.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21340990