Duplication of Rows

[G-MAN]

I'm fairly new to Cognos and need help please.  I have been using crystal reports primarily.

I'm creating a report and  my data will duplicate.  The problem is if there is more than one row in the collateral data field and it will duplicate the account number. This will cause my balances to be off.

Account Number         Balance         Collateral
123456789               $10,000          123 Main Street
123456789               $10,000          Business Assets

In crystal report I would pull in a sub report by linking an account number though parameters.  The main report would allow me to modify the size of the sub report.  This allowed me to only display one row if their was multiple rows in a table.

I have tried to bring in the collateral section of the report by isolating them in a list and cross tab.  This has stopped the duplicated balances however, I need to only display row 1.  I used singleton with no luck.  I tried to add RowNumber() <1 Boolean formula into the style variable. 

It is possible to filter out the duplicates however, I need to group every field. The end results of this is data that is no longer presentable.


Does anyone have any ideas?

[sixajd]

Does the collateral have an associated ID number for each record? If it does, and you don't mind which address pulls in to your report, you can create a new filter like this -

[Collateral Code] = minimum([Collateral Code] for [Account Number]) which will only pull in the lowest numbered collateral code, and associated collateral address, for each account.

If you don't have any sort of collateral code or ID tied to the address, you can still try what I mentioned above but using [Collateral] = minimum([Collateral for [Account Number]) and it it may work that way as well.