How can I append two tables in a WebI query?

[Former Member]

I have one table that has parent tickets and one table that has work orders, which are sometimes, but not always, children of tickets.

I need to combine all of these into one query "table" so that I can sort/group/tally the aggregate of all work orders and tickets for a month.

The names of the fields differ, ie:

Incident table:

Incident #, date open, date close, group, subject

Work order table:

HD# [this is incident #], date open, date close, group, subject

I also need to know how to join on the incident #.

I'm thinking this will be easiest in the SQL.

Many thanks!

Leslie

.

[former_member189638]

Isn't it more easy to join the Incident table and Work Order table in the Universe with Cardinality 1:N and with outer join to Work Order Table?

[Former Member]

That was my original thought, but I'm fighting with the SQL.

I want to do the join on incident and work order numbers

If I have the fields:

incidentnumber from incident

workordernumber from work orders

incidentnmber from work orders

Can you help me formulate the join?

This will be hugely appreciated, and thanks so much everyone for your helpful comments! 

[Former Member]

Is there any primary foreign key relationship between these two tables?? If there is then you just need to join these two table in the universe end..

[former_member189638]

May I know where you are using the SQL?? This needs to be done at the Universe with a simple drag and drop.. no need for any SQL coding.

[Former Member]

Answering both questions.

Ideally I would like to see:

Incident 22556

Incident 24352

and Work Order

34797

38473

Appear like:

Incident

22556

24352

34797

38473

Some Incidents have work orders associated with them but not all

Some Work orders have incidents associated with the but again not all

I pressed the sql button and was able to edit from there.

I am coming from Crystal Reports and need to convert my reports to WebI, so am not all that familiar with WebI yet.

Many thanks!!

Leslie

[Former Member]

No, The tables are very similar, one contains incidents and one contains work orders, I just want to dump the work orders at the end of the incidents.

[Former Member]

Can you explain further how I can do this with drag and drop? Thanks!

[Former Member]

If these are based on two queries then use merged dimension on the common objects of both the queries..

[gleo_SRAM]

Can you merge the characteristics in WebI Leslie?

With regards

Gill

[Former Member]

Work order table also has field work order no.

[Former Member]

Hi Leslie,

If these are two tables present in your universe, than easiest way will be two join the two tables in universe and create objects from those tables.

You can use these created objects in the webi report.

If these are coming from two different queries in the report than you can make use of merge dimension concept..

Details of using merge dimension in webi are in below link:

http://scn.sap.com/docs/DOC-49941

Thanks

Gaurav

[Former Member]

Thank you I will try the second suggestion, but very curious about the steps for the first.

Many thanks!!

Leslie

[Former Member]

I have sucessfully merged, but I have a [beginner - sorry!] problem.

All of my selected fields from the first query appeared in my report, however,

Only the merged column from the second queryappeared in the report, how can I add the other columns?

[Former Member]

Hi Leslie,

This unmerged column from the other query can not be directly included in the block.

Try below workaround:

Create a detail variable for those columns which are not merged from second query and associate that to the merged dimension.

For eg.

If query 1 has columns a,b,c,d  and query 2 has a,e,f and all dimensions types, than you can use in a single block merged dimension a with either e,f or a with b,c,d.

The workaround to include e and f column from query 2 in block having a,b,c,d would be to create detail variable for the column e and column f and associate these two variables to the merge dimension a and use these detail variable instead of original columns in the block.

Let us know, if this is working.

Thanks

Gaurav

[Former Member]

Hi Leslie

You need to create a variable for each of the dimensions in the second query.

When you define the variable you need to set the Qualification to "Detail" and the Associated Dimension to the merged dimension.

You can then drag the variable into your report block.