Creating a Report From Two Queries Using the Same Files with Different Joins

[jaymoore1756]

I have two reports that draws data from the same two files - The equipment master master and the GL transaction file. The first report shows a standard rate, actual hours and standard cost (rate x hours) charged to each equipment. The second report shows the actual cost charged to the each piece of equipment. The joins are different for each report and setup in framework manager. Both reports run and each report captures the required information. Now I want to create a report that calculates a new standard rate - Actual cost / Actual hours. The issue is the joins - report one uses a join based on equip # and resource id that does not exist report 2. Report 2 uses a join based on subsystem,batch, sheet and sequence that does not exist in report 1 because thaey are seperate entries.

The issue is how to join the the two processes together. Any help is appreciated

[blom0344]

Why not build 2 submodels to accomodate the different reports? You can define more than one model query subject on an imported definition.  The drawback is that you more or less build your model to suit a specific report.

What is the common object to tie the datasets together?  Cognos allows joining datasets locally, so you may still get what you need without remodelling.. 

[jaymoore1756]

I have never done this before " What is the common object to tie the datasets together?  Cognos allows joining datasets locally, so you may still get what you need without remodelling..  " Can you give me an example. Thanks

The common object is equipment number an equipment type

[blom0344]

Basically it is a matter of defining 2 queries within report studio and then using a local join to combine the fetched datasets. This generates a third query as a resultset of no. 1 and no. 2
When you are in query panel you can drag the join into the query canvas and drag query 1 and query 2 to the right of the join icon.  When joining this way you can even set cardinalities if so required.
The cognos engine will handle joining the sets (not as efficiently as a database) so the resultsets from both queries should be relatively small.  Equipment type would be the common object type to use for joining the sets..

[jaymoore1756]

Thanks .... issue resolved