Distinct Question

[kodaksmile]

I want to remove duplicate rows from a report - is there a way in Impromptu that I can do the equivilent of select distinct?

 

[griffindm]

Kodak,

Using the 'Eliminate Duplicate Rows' checkbox under the Report | Query | Filter menu dialog will accomplish this.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[kodaksmile]

I saw that but, ran into an issue on a report where it replicated an employee's benefit line 4 times and if I ran a total on it it showed 4 times the actual value even when I used that flag so I was thinking I missed an option somewhere else.

 

[griffindm]

Check the resulting SQL in the Report | Query |Profile dialog and the SQL button. It should show a distinct clause in the SQL. Anytime you are doing a quantitative report and use the distinct clause you run the risk of some serious data integrity issues. Data may be dropped because is is not 'distinct' based on the SQL, even though to be accurate the report needs to have those rows in the report.

Your problem sounds like something different though. Could you post your SQL statement that Impromtpu generates so that we could take a look at it? Also, a little background on what the report is designed to show would help as well.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[stewm]

Have you tried grouping all the items in the report. This will force a SELECT DISTINCT to be generated.

Mark Stewart

Consultants Club Corp.

 

[kodaksmile]

Strangely enough the other systems analyst added distinct count and modified it - went through many errors and then distinct showed up in the list....Don't understand why but, that helped.