Duplicate Data in Impromptu Report .imr 1

[hla9803]

Hello,

I am a starter of Cognos. I began with create a simple catalog from SQL database, with one table only (no any join): sales invoice report and tried to create a impromptu report. The problem is report display duplicate rows (or triple)for each actual invoice.

Please help me a advice.

Thanks and Kind regards,

hla9803

 

[blom0344]

The invoice may consist of 1 or more invoice lines. If you do not assign an aggregate to the actual values, then you will get all line data returned.
If you want a summary of values for each invoise, then you need to apply an aggregate.

Ties Blom

 

[hla9803]

I would like to explain more my problem. The problem is report display wrong:

When data be taken from one table only (invoice table), it's correct
Invoice No. A1 Product B Customer C
Invoice No. AB Product B1 Customer C1

but report displayed wrong, two or three time per each invoices, when take data (customer name) from another table (customer table)

Invoice No. A1 Product B Customer C Customer Name
Invoice No. A1 Product B Customer C Customer Name

Invoice No. AB Product B1 Customer C1 Customer Name
Invoice No. AB Product B1 Customer C1 Customer Name
Invoice No. AB Product B1 Customer C1 Customer Name

I think the problem relation to join between Invoice table and Customer table. I create a equijoin here, with the coulumn is customer code. The problem is still happen.

Please advice me a solution.

Kind regards,

Have a nice weekend.

 

[blom0344]

If your Customer table is a slowly changing dimension object, then there may be more than 1 record for each customer, hence the customercode is not unique. This would result in multiple rows returned for each invoice.
Check wether you need to filter the customer table for the active records.

By the way, the join type has nothing to do with this. It is a matter of cardinality between the tables, which in you case should be 1:1 or n:1. (fact <--> dimension)
At the moment the cardinality is probably 1:n or even m:n

Ties Blom

 

[hla9803]

Thanks for your fast reply.

I found the way to filter duplicated data. The problem is solved.

Thanks and Kind regards,

Have a very nice weekend.

 

[aviation001]

hello all;

first time cognos user; trying to run a report with employee date to yield results all employees with pay transactions between certain dates got that part right; the result shows multiple entries for some employees; i would like to sort/filter the report to remove the duplicate entries with the understanding that i still want all affected employees represented on the report with the latest date.

how do is specify a filter/query to show latest date entry only for all employees.

thanks

 

[blom0344]

Depends on the tool you are using. If you are using Cognos 8 Report Studio than create a rank item and use this as a filter..

Ties Blom