Not Enough Records from a Universe

[Smithsc]

Hi there,
I've created a simple universe extracting data from a SQL database.
The universe consists of a Company table and a Opportunity table. I've linked the 2 tables together so that the cardinality says:
Each Company has zero of more Opportunity.
Each Opportunity has one and only one company

If I create a Webi report that has 2 fields in it (Company Name & Opportunity Description) I get 31 records showing when there are actually 36 on the database.
The problem is that a few of the records have the same Company name and description and the report is not showing them seperately. If I include the opportunity ID field in my report I suddenly get 36 records.

I've looked at the sqlsrv.prm file and made sure the outerjoin parameter says ANSI_92 and the parameter is set to yes but this doesn't make any difference.

Am I missing something obvious?

Many thanks for any help.

 

[nimmy99]

Did you check the duplicate records?

 

[Smithsc]

The retrieve duplicate rows option in the webi report is checked. Is there a similar option in the universe that I've missed?

 

[blom0344]

Apart from the technical side, why would you want to return duplicates on the combination of a Name and a Description?

Ties Blom

 

[Smithsc]

These records are not duplicates. It's just that the Name and Description fields are the same. I want the ensure that the correct number of records are shown if a user selects just these fields

 

[blom0344]

If no facts are specified in the query, then every BI-tool (not just Business Objects) is designed to show the distinct set of combinations. There are usual overrides to prevent this, but my question was a functional one.
A user shouldn't be interested in the number of rows returned from a query, just in the correctness of the data displayed. Once you work with aggregated facts then the number of rows returned by the set is academic.

Ties Blom

 

[Smithsc]

What are the usual overrides?

 

[skrandel]

There is a report level setting, "Avoid Duplicate Row Aggregation" that you need to check in order to display all rows even if they have the same values.

This setting has to be done on each block.

Steve Krandel
Intuit

 

[Smithsc]

I'm being really stupid here but I can't seem to find the Avoid Duplicate Row Aggregation setting. Where would I find it?

 

[skrandel]

For a DeskI report, it's part of the Format Block dialog.

For a WebI report, you have to select your block. On the properties tab, under Display, it's the first option.

I assume your on XI R2.

If you're on 6.5, I do not believe there is a WebI option for this.


Steve Krandel
Intuit

 

[blom0344]

I have no experience with XI R2, but with 6.5 and earlier releases it was in the Format Table GUI

Ties Blom

 

[Smithsc]

I still can't find it......
I'm still using XI R1. Would this make a difference.
Just to confirm - is this option in the universe or the webi report?

 

[skrandel]

Yes, it would make a difference. XI R1 (lousy software) did not have this ability as it was really just WebI 6.5 shoe-horned into a new architecture.

You're going to have to bring back another item in your query to make each row unique. I believe oracle has a rownum or recordnum function that will do the trick.

Steve Krandel
Intuit

 

[Smithsc]

Oh well. I think I need to get my IT dept to upgrade me. Thanks for all your help.

 

[skrandel]

You definitely have a problem as there was no DeskI in XI r1.

Steve Krandel
Intuit