BIRT: Combining results from two tables or cubes

MAbdelghani

Hello,

My report scenario, I have two tables getting data from same database but can't use same sql statement and need to check

if the first column in each table is the same, then put them in the third table with their accumulated costs.

The below screenshot explains my scenario clearly.

_

Karen Weir

Hi @MAbdelghani

Have you resolved the scenario, or is this still open for discussion?

MAbdelghani

Hi Karen,

No, I have not resolve this scenario and yes still open for discussion but sadly no body comment about my post.

Do you have any information about a resolution for my issue?

Karen Weir

Unfortunately, I can't add anything here - I searched the documentation and the Knowledge Base, but have not found relevant resources for you. It looks like this topic hasn't been raised in the My Support forum yet either https://forums.opentext.com/forums/support/categories/magellan-bi-reporting. While we wait for the community to review, I'll check internally.

Virgil Dodson

Hi @MAbdelghani

You can use the Join data set to accomplish this. First create a data set for each of your tables just like normal. Then create a Join data set. For this example, you want to make sure to select a Full Outer join on name between your two tables. You also need to create two Computed columns in your Join data set.

One for Name (data type: String, analysis type: Attribute)

if (row["Table 1::Name"] == null)

 row["Table 2::Name"]

 else

 row["Table 1::Name"]

...and one for Total Cost (data type: Integer, analysis type: Measure)

expression: row["Table 1::cost"] + row["Table 2::cost"]

This should give you the results below

MAbdelghani

Thanks a lot @Karen Weir, am going to check the solution provided by @Virgil. Thanks @Virgil, appreciate your help.

MAbdelghani

Thanks a lot @Virgil, it works fine as you mentioned.

Really, appreciate your help ?️?️