We have projects, Project Spnosors and Revenues.
If a project has multiple sponsors, the revenue in BO report is multiplied by the number of sponsors involved in that project.
I can count the number of sponsors in the report and divide the revenue by that number to get the correct results.
Is there any better solution for this situation?
mkumar (BOB member since 2002-08-26)
Sounds like a typical fan trap, which can be resolved at the universe level using aliases and contexts. Please, ask your universe designer for details.
Andreas 
(BOB member since 2002-06-20)
We had also faced such an problem when the formula for a meaure object in the universe was aggregated as sum
Ex : if amt is the object then instead of having the formula as amount we were having it as sum(amount).
B’cos of this the data generated was wrong
You can check if this is the problem you are having.
Akshay
akshay 
(BOB member since 2004-02-05)
IMHO - every measure object should have a mandatory SQL Group By function associated with it.
As a rule of thumb: If the measure does not have a SQL Group By function it should not be a measure in the first place.
Andreas (BOB member since 2002-06-20)
I do have one to Many relations.
My tables have following relationship.
Sponsor - > Project - > RevenueI have one to Many relation from Sponsor -> Project
and one to Many relation from Project - > Revenue.
However I have measures defined only on Revenue Table.
I don’t have any measure in Sponsor or Project table.
Is it still a Fan Trap?
mkumar (BOB member since 2002-08-26)
Did you not initially state that one project can have 1 or many Sponsors?
Please, try a search on BOB, suggest keyword: fan trap
Or speak to your universe designer (he/she should know).
Or look at Business Objects official Tips & Tricks section: “Recognizing and Resolving Chasm and Fan Traps when Designing Universes”
Andreas (BOB member since 2002-06-20)
Hold on, there is some mistake from my side.
I mixed two scenarios I am working on.
I will reply shortly.
sorry
mkumar (BOB member since 2002-08-26)
Here is how it is:
Sponsor <- Company -> Project -> Revenue
One company has many projects
Projects have revenues.
Sponsors are associated with Companies. There can be multiple sponsors for a project.
Does that make sense?
mkumar (BOB member since 2002-08-26)
Please, read my previous post - you suffer from a classical unresolved fan trap.
Andreas (BOB member since 2002-06-20)
It turned out to be a Chasm trap instead.
It was caused by multiple many to one relations converting at company table.
----------------< Sponsor
|
|
|
Company --< Project -< Revenue
Thanks.
mkumar (BOB member since 2002-08-26)
Apologies to Andreas, but I think that you have, actually, a chasm trap not a fan trap. You gave the following diagram:
Sponsor >- Company -< Project -< RevenueWhere -< represents a one - many relationship, correct? You have multiple sponsors for a company, and multiple projects (and revenues) for a company. You have a chasm trap between Sponsor and Revenue. For that reason, in this relationship, you cannot show revenues by sponsor without doubling (or tripling or more) the results. Why?
Because revenues are assocated with a project.
Projects are associated with a company.
A project is not associated with a sponsor in any way, and therefore there is no way to properly calculate revenues by sponsor. It just won’t work because there is no relationship.
If you really need to report revenues by sponsors, I believe your only choice is to divide the revenues by the number of sponsors, thereby allocating (in an even fashion) the revenues by sponsor.
So if you have 2 sponsors for a company, and a total of $100 revenue for that company, each sponsor would get credit for $50. Giving you a total of $100.
[Edit - Bah, you beat me to the post by 2 minutes. 
I took too long to type the answer, I guess.]
Dave Rathbun 
(BOB member since 2002-06-06)
Thanks for the detailed analysis Dave.
We had been asking our users to divide the revenue by number of sponsors till now.
But they are not very comfortable with this idea.
I have suggested them one more work around.
Create separate queries for fetching sponsor and revenue with Company as a common dimension in both queries.
Then they can apply filters at the report level to show the reqd data.
This does fetch a lot of data in report but there are not many solutions for this situation.
Otherwise we will have to create contexts in universe.
mkumar (BOB member since 2002-08-26)
Which in most cases would be the preferred solution.
Andreas 
(BOB member since 2002-06-20)
If I create contexts in Universe, still I have to filter out the Nulls from the report. Is it how it works?
If yes, then this is same as creating two separate data providers.
and only help contexts provide is to automatically create two separate queries.
mkumar (BOB member since 2002-08-26)
Only? That is a big fat PLUS, because Business Objects avoids incorrectly multiplying your measures, and you are not forced to manually create separate data providers.
Contexts can be a huge plus regarding usability 
Andreas (BOB member since 2002-06-20)
Does that mean you have the Multiple SQL statements for each context button checked in your universes? Most of our universes do not have that button checked, and I have frequently built measures with the aggregate function set to None in order to get the numbers to not multiply. Are those two things connected?
Erin Aasted (BOB member since 2002-08-15)
Yes, "Multiple SQL statements for each context " should be checked.
Aggregate function as in SQL Group By function or as in projection within a report?
Andreas (BOB member since 2002-06-20)
I’m talking about the Properties tab when you build an object. It says:
Choose how this measure will be projected when aggregated
Then you pick a function and I have had to pick None to get the measure to stop multiplying when I am using tables that are at different levels of aggregation in a query.
Most of our universes have contexts and most (maybe all) of our universes do not have the Multiple SQL statements for each context button checked. This causes us to manually put in multiple data providers to get around the Incompatible Objects problem.
Do you always check that button? Why?
P.S. Andreas: Did you just become a moderator? Congratulations!
Erin Aasted (BOB member since 2002-08-15)
Well, you all might have your reasons for this, but I do check it (as default), because I want Business Objects to automatically generate multiple SQL statements instead of the end-user having to manually create separate data providers.
Andreas (BOB member since 2002-06-20)