BIRT: Combining results from two tables or cubes

MAbdelghani
MAbdelghani
edited February 11, 2022 in Analytics #1

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.

image.png


_

Tagged:

Comments

  • Karen Weir
    Karen Weir E Community Administrator
    #2

    Hi @MAbdelghani

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

  • MAbdelghani
    MAbdelghani
    #3

    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
    Karen Weir E Community Administrator
    #4

    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
    Virgil Dodson E Community Administrator
    edited April 13, 2021 #5

    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

    join data set.jpg


    Warning No formatter is installed for the format ipb
  • MAbdelghani
    MAbdelghani
    #6

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

  • MAbdelghani
    MAbdelghani
    #7

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

    Really, appreciate your help ?️?️

Categories

image
OpenText CE Products
Magellan Text Mining
Docs & APIs