Combining two reports

[JRandallC]

Is there a way to combine the data in two reports into one? In this case, both reports have field names that are identical, but each report comes from a different source. I want to combine the two into a third report as if they had come from the same source. Thanks for any help.

Randall

 

[griffindm]

No can do directly in Impromptu, unless you do a very convoluted double outer-join syntax, which I am NOT going to go into here. You can, however, create a UNION view in your database that automatically does the same thing. The syntax is

CREATE VIEW viewname AS
SELECT COL1, COL2, COL3 FROM TABLE 1
WHERE CONDITIONS
UNION
SELECT COL1, COL2, COL3 FROM TABLE 2
WHERE CONDITIONS;

The key points to remember are

1) the columns must be the same type, i.e text, number, date, etc.

2) the union automatically returns a DISTINCT result, so if the two SELECTS return data with exactly the same information, only one of the rows would be included in the view. To avoid this include some column in both that guarantees each will be unique.

Hope this helps,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[Shek]

Does it come from a different database. If not then it can be done. Store each report as a hotfile (ims) and then the new report will have thse 2 ims' as the source. Of course you need to refresh the ims to that the data is current( ims stores the data from the DB onto your local machine).

This refresh of the ims can be done using a macro. Want to have that. Mail Sharad_gangwar@infosys.com

 

[kinetic123]

Using Datasets might help

kinetic123