Similar Records Counted Once

[karkia]

If I do an equivalent query in Oracle, I get a result like-
Product Cost
------- ----
p1 10
p1 10
p1 20

But,in BO, the records returned are-
Product Cost
------- ----
p1 10 (only one of these is returned)
p1 20
The properties are set to return duplicate records. Where did I mess?

 

[blom0344]

No, you are mixing SQL up with the reporting functionality.

Business Objects is primarily meant for rolling up measures against dimensions. That is the way one wants a reporting/ analysis tool to behave.

This way BO will automatically recalculate the summary of measures when you add or remove dimensions. The standard setting within reporting is to show distinct rows (which accounts for the fact that you are missing something)

If you want to switch , you will need to activate the 'avoid duplicate rows aggregation' in the table's properties. (general tab)

I do not want to sound patronizing, but I think you need to get familiar what it's use should be. It is not comparable to a query tool like TOAD that will only return the SQL resultset. With BO the fun starts after that phase

Ties Blom
Information analyst

 

[karkia]

Hey Blom,
If you read my posting clearly, you would notice that I had mentioned that I CHECKED to retrieve duplicate rows in the Data Properties tab. BTW, I am using BO WebI XI r.1. I understand your comments, but that is what is intended of my reports since I need to make a count of all records that have been in the inventory for specified number of days. So, duplicate records (if any) are required in this case.
Further, is activating 'avoid duplicate rows aggregation' in the table's properties (general tab), the same as checking the 'retrieve duplicate records' in Properties tab of table? If so, why is it still not returning the duplicate records. And yes, the same query in Oracle returns all records.

 

[karkia]

I guess I will just pull in a unique field to solve my problem.

 

[karkia]

Oops, that would cause a problem since the report has to be cross-tab. And the unique field will just be an extra field.

 

[karkia]

I guess counting the unique field helped. But I needed to clear the contents of the unique field, so that it doesn't show in the cross-tab report. But I can't remove the row or column for that field. I guess i will have to live with that.

 

[blom0344]

Well, as stated before WebI XI is not something most BO-ers will be familiar with.
The fact that a WEBI query fetches a distinct resultset seems a ludicrous default setting, especially since full client is set to the opposite.
Are you sure that this is not a configuration setting specific for your installation?



Ties Blom
Information analyst

 

[karkia]

I am happy with the unique field forcing all records to appear. Like I said earlier, I have to hide the field since it is not desired in the report. What baffles me though is, the purpose of the data property 'retrieve duplicate records'. Why is it that checking/unchecking it retrieves only distinct records? Blom, it could be a matter of configuration on the server. As far as I can see, no such preferences are there in the client. But, my job is done..and boy, do i care!!!

 

[skrandel]

You're making a big assumption. What makes you think it's not retrieving duplicate records? These are dimensions. Dimensions rollup so that only unique ones are visible. In the BO Client there is a setting that allows you to show them no matter what.

WebI does not have this feature.

Steve Krandel
Symantec