Skip to Content

Outjoin issue or Context issues?

Apr 27, 2017 at 07:33 PM


avatar image


Have a circumstance where the relationship with the data is causing a problem. Meaning that there is an id in the data that is what joins the data to each other. Specifically there is a relationship between PARENT EVENT ID AND AN EVENT ID. The hierarchy is such that you have something like the following:


1 Event ID 1 (Parent Event ID)


Event ID (2) , (1) Parent Event ID 3, 1


4,2 5,2 6, 2 7,3 8,3

For a row which has no parent the event id and the parent event id will be the same. For a child row the parent event id will equal the event id on the parent row.

Now I’ve done this in SQL but can’t put this together with Web Intelligence. I’ve tried to use multiple queries, outer joins, detail objects and have not been able to get the values into one table.

Below is the outcome of the three queries that are strung together. Using the hierarchy shown above.

The cold values above (258685677;258685731) are for “Clinical Nutrition Grid” and “Intake Nutritional Grid”.

Notice below that I’m running the second query from the first based on the hierarchy shown above.

The clinical code above (112146587;1681809363) are for “Malnutrition” and “Underweight”

Again below, notice that the level of the hierarchy is again dropped.

Code values above (258685659;258685665) are for “Evidenced By” and “Related To”.

The outcome I would like is similar to the following.

I want the data such as:

Underweight associated with the related to and evidenced by columns.

Ultimately at the very least, I want malnutrition and underweight to be broght into the bottom table!

One of my assumptions is that I could do this with a detail object but I may not be getting the context correct?

ngqza.jpeg (43.1 kB)
e1bgj.jpeg (15.7 kB)
ffkvp.jpeg (15.3 kB)
agmfr.jpeg (18.2 kB)
sc7ux.jpeg (13.5 kB)
ytsiy.jpeg (11.4 kB)
knil0.jpeg (19.5 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Scott Gilsdorf Apr 27, 2017 at 07:38 PM

The web form is not keeping the layout above to make the Form, Section, Control diagram make sense.

Event ID 1 Parent ID 1

Event ID 2 Parent ID 1 Event ID 3 Parent ID 1

Event ID 4 Parent ID 2, Event ID 5 Parent ID 2, Event ID 6 Parent ID 2, Event ID 7 Parent ID 3, Event 8 Parent ID 3

I hope this helps.

10 |10000 characters needed characters left characters exceeded
Apr 27, 2017 at 07:57 PM

What is the issue if you will create one webi query and include all three query filters in the single query.after that create three different tables at report level and apply your query filters in individual table.

Show 7 Share
10 |10000 characters needed characters left characters exceeded

Amit thanks for your response~! If I understand your thought process, you mean to sit two different tables side by side using one table from q1 and another from q2. The problem with that is the values are not link and so they won't be on the same line/record next to each other. Am I understanding you correctly?

Thanks Scott


yes you are right..i am thinking to go with single query only.

Sorry I was attempting to answer again and lost my page. I need the outcome to look like the following.

Malnutrition | Evidenced By | Result values

Malnutrition | Related To | Result values

Underweight | Evidenced By | Result values

Underweight| Related To | Result values

The outcome above would be associated with one person. The values for Malnutrition/Underweight are the at a level above the values for Evidenced By/Related To based on a Parent Event ID and Event ID relationship.

Parent ID 1 | Event ID 1

Parent ID 2 ( = to Event ID 1) | Event ID 2

Hope that helps. The thing is, if I create this in sql then I just need to outjoin the two different levels and all the data will layout in one wide set of columns. When I try to create the outer join in webi, the fields can't all be brought together into one table. This tells me that either the outer join won't work or I need a context for the variables to have everything in one table is the problem. If it is the context, which I've attempted multiple times, I have not been successful.

Again thanks!


can you share some sample data of each query and final expected output in final.

2 rows are enough for each one and expection .I will try if i can suggest something


Amit, I have shared the data and out come. I'm going to attempt to attach a screen shot.

The outcome should look like previous post.

Underweight | Related To | Recent Illness

Underweight | Evidenced By | BMI o f15, 65% IBW


Amit, I'm still working to get a solution here. Basically I have a hierarchy of parent event id and event id. The data would look something like

Parent event id = 1 and event id = 1 ; this is the original event id.

parent event id = 1 and event id = 2 ; this is the next level of event id.

parent event id = 1 and event id = 3 ; this is another event at the same level as event id = 2.\

parent event id = 2 and event id = 4 ; this is an event id at level 2

I hope that makes sense. Basically I need to associated parent event id = 1 all the way down to event id = 4 and any details that are associated with that event id (4). Is this possible since the relationship is not one to one?

Thanks Scott


I attempted to do this a different way, assuming that my approach wasn't clear. I opened another question called "

How to attain when there is no heirarchy". I hope that makes more sense. Sorry but since it can be done with regualar sql using outer joins, it's got to be possible in webi. Thanks !!!!

Scott Gilsdorf May 03, 2017 at 10:04 AM


Here is a screen shot of the queries

sap.jpg (209.3 kB)
10 |10000 characters needed characters left characters exceeded