My question maybe more design related, than BusinessObjects specific, but was wondering how others might be handling similar situations with BusinessObjects.
We have several files that contain information related to our orders that users wish to analyze. We have built s “demo” universe for them, but a problem occurs when there are “measures” on the header & detail.
We have several parts per order, but the Freight & Labor are for the entire order.
We can join the tables fine, but the problem occurs when the user want to know total order value (sum of parts + freight & labor) along with what specific parts were ordered.
We have looked for ways to “allocate” the header amounts to the detail, but that doesn’t work well when the user wants to include only select parts & know the relationship between the part cost & freight.
We have seen that problem also and have a crude workaround. We haven’t spent much time on it and just really did the first thing that came to mind so you may get a much better idea but here goes.
In this situation we would create an object in the report (you could probably do it on the designer end too but we haven’t tried it) similar to this:
sum(freight)/count(part #)
So let’s say you have 5 parts… The total freight will be $75.00 (15 x 5) but the division will divide it by 5 and take it back down to $15.00.
We can join the tables fine, but the problem occurs when the user want to
know total order value (sum of parts + freight & labor) along with what
specific parts were ordered.
You are not the first to bump in to this problem, in fact it is one of the oldest problems since the invention of SQL. When there are two tables with a one (T1) to many (T2) relationship and both contain measures, then basic SQL leaves us with a problem: T1.col1,sum(T1.col2),sum(T2.col1) group by T1.col1 gives back results by summing after creating joined single rows for each row in T2, say: T1.col1val1,T1.col2val1,T2.col1val1
T1.col1val1,T1.col2val1,T2.col1val2
T1.col1val1,T1.col2val1,T2.col1val3
T1.col1val2,T1.col2val2,T2.col1val4
The results:
T1.col1val1,3T1.col2val1,(T2.col1val1+T2.col1val2+T2col1val3) T1.col1val2,T1.col2val2,T2.col1val4
In which 3T1.col2val1 is not wat we want.
You could go for the solution sum(T1.col2)/count(distinct T1.rowid) (the universe version of another suggestion on the list). However the most elegant (and fastest) solution I have seen sofar is based on entirely different approach:
sum(distinct T1.col2)
To simply use this however would cause accidently like values to get counted only once too.
The solution to that again is making the values of the various T1 records unique by adding a very small value based on the primary key (if numeric) or rowid (converted to numeric). After summing this small value gets truncated by rounding. So use:
round(sum(distinct (T1.col2+(T1.PK/e20)),number of decimals you need) if T1.PK is numeric or
round(sum(distinct
(T1.col2+(replace(’.’,rowidtochar(T1.rowid)/e20)),number of decimals you need) if it is not.
Needless to say that the universe is the place to put this into your measures coming from T1.
Success!
Marianne Wagt-van Loenen
IDETA, the Netherlands
Will Business Objects support the following design with the following report requirements,
or do I need to force all these fact tables into an abstract fact that unions the rows?
Our datamart is designed to allow accountants to analyze their transactions (Journal Entries)
against their related Deal transactions. A deal transaction can have many JE Slivers (Accruals
and Invoice debits and credits). A JE sliver can represent many deal transactions.
The 2 fact tables joined across a common dimension.
Journal Entry Sliver Fact
JE ID Sliver ID Entry Type Tran Source Grp Debit/Credit Ind Account Amount
I have a situation where I had to include the alias to get around a loop.
Let’s say I have a table “Facility” and I created an alias “aliasFacility” and there is another table “Provider” that makes use of a field (Facility Name) from “Facility” table. But as soon as I try to add Provider Name (From Provider class) and facility Name (From Facility) then the report doesn’t show the result. But if I use Facility Name (From aliasFacility) then it returns the result.
My question is I don’t want to confuse end users with multiple Facility Names. Is there a way where we can define 1 Facility Name under “Facility” class and use it in the reports?
Any kind of help is appreciated.
Shally
__________________________________________________ Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com
I am using contexts but there are 2 loops which wouldn’t resolve unless I added two alias.
Now I have “Facility” as a class which works fine with 4 out of 5 classes. And the for the one that it doesn’t work is “Event” class. So I had to add “Facility” sub class (from the alias) under “Event”. The results are okay if I use the subclass. But I am sure it is going to confuse the users that they can use “Facility” with the rest of the classes but not with the “Event”.
Am I missing anything?
Thanks
Shally
__________________________________________________ Do You Yahoo!?
Make a great connection at Yahoo! Personals. http://personals.yahoo.com