Joins (Inner Join, Right, Left Joins) with Merge Dimensions

[Former Member]

In WebI XI 3.1

1) How do I enforce an inner join using merged dimensions?

2) How do I enforce a right outer join using merged dimensions?

3) How do I enforce a left outer join using merged dimensions?

Thank you

[Former Member]

Yes there are ways to enforce Combinations at report level.

"Mergerd dimensions act as Inner Joins" however you can enforce Left outer Join/Right Outer join in a single block by using for example Query1.STATE or, Right outer join by replacing STATE with

Query2.STATE

Try the combination of following in the Table :-

STATE <For Inner Join>

Query1.STATE <Left Outer Join>

Query2.STATE <Right Outer Join>

Use Block's/Table's property to show blank rows too to get the effect of outer joins.

Check "Show rows with empty measure values"

Thanks,

Prabhat Kumar

[Former Member]

Thank you for your reply. However, merged dimensions, in the reports I am building, are creating unions.

So if Query1.STATE values are Texas, New York and Washington and Query2.STATE values are Texas, Maine, and Ohio, my merged State dimension - let's call it STATE_Merged contains the values of Texas, New York, Washington, Maine and Ohio. Whereas that may be helpful, I also want an option for Business Objects WebI XI 3.1 to just give me the equal values, that is, the values where Query1.STATE and Query2.STATE are equal, or in this case, Texas. That's not happening.

Can you provide any further help, please?

[Former Member]

1. Instead of adding a secound Query. Go and click "Add a combined Query" Icon in query panel. I will show two similar Combiled queries .Then double click the UNION to change it to INTERSECTION. You will get the result Texas.

2. you can have the above Query as a Third query for your conveniences as Query3 (This might include two sub queries).

(ISSUES above: Here you may have to choose similar number of Objects which will write its value unto one Object i.e. the object choosen in first Query. AND Performance could be a shoe_stopper AND We cannot do a

If(Query1.STATE=Query2.STATE;1;0) with filter on 1 as it will consider the data grains of both queries.)

3. HARD CODE the SQL Query to have Intersection/Inner Join encoded in your SQL itself.

4. Go by MEASURE Side. Use a function like

   =If (IsError(ToNumber([Query 1].[Sales revenue]));[Query 1].[Sales revenue];1)   

This will give you 1 where there is "DISCONTINUED" . You can filter by using Report Filter.

Use the following and filter 0.

=If (IsError(ToNumber([Query 1].[Sales revenue])) And IsError(ToNumber([Query 2].[Sales revenue])) ;0;1)

Regards,

Prabhat Kumar