creating a report from more than one .imr report

[billywizz]

hi
i have a small problem and do not know if cognos impromptu can handle this ??
i have a set of imr reports all the same field stucture which i would like to put together as one imr report.
it's like appending in access datababes.the only way i know is by saving each file as a .dbf file importing it into access and then append each set into one new set.using access.
this is long way around.and access will not allow update if linked database so i have to import it.
is there something in cognos which can append imr results ??
I have tried the help info but no joy.
regards
pam

 

[esselinj]

You can use a UNION ALL command in the SQL box of Impromptu. The disadvantage of this solution is that you have to edit the SQL it cannot be done manually. Via the UNION command you connect different queries into one report


Jack

 

[BruceReed]

We have many reports like this as our erp system breaks all data into "live" and "history" files & we need both sides.

I have not found a good solution for this. I did not even try this in Impromtu, but I "merged" all my reports in transformer, then open the resulting cube in PP. If I needed to look at the detailed data, I drilled back into the correct imr report. This lets have the best of both worlds, Summary information, as well as the details behind.

If you still want to "join tables" I would export one into access, then append a 2nd Impromtu report to this. Impromtu can read MS access files.This works great if one of your files is static.

Good luck

Bruce Reed

PS Transformer is VERY picky on spelling item is not the same as Item, so both reports have have the exact same field names.

 

[debdas]

Hi Bruce Reed,

Though I am a new user may my suggestion works you...

Steps :
1. Create different .imr reports
2. Close all reports ** NB: Don't close catelog
3. Choose menu Catelog/Tables
4. Click on Hotfiles and Browse where your .imr reports are located
5. Click the .imr report files and click the button "Add"
Similarly Add other .imr report files.
6. Now click ok to close
7. Which will open "Join" window
8. here you can do join or sql joins and save it to another .imr report file

Note that don't save it as Hotfiles as Hotfiles are not supported by transformers

Best of Luck

Debabrata Das

 

[esselinj]

Addind IMR-reports in you catalog like running hotfiles will not work in my opinion because Impromptu will handle each report as a seperate table with seperate columns. So you can add extra colomns but the only to add rows from different datasources is either using a UNION ALL SQL command or use an external tool/ utility (you could find plenty on the net) to append different files. In my opinion this also something which could be handled by CognosScript

Cheers

Jack

 

[DrSimon]

I'm confused by the initial question billywizz. I always like to try the Occam's Razor approach to start off. So the question is - why can't you write a single report that does the job of the set of reports?

 

[BruceReed]

Debdas & esselinj
I have learned something from this thread. I am a tranformer guru & not an impromptu expert. Using union sounds like a cool idea to merge similar reports into one. I'll give it a try & drill back from Powerplay into one impromtu report.

However in my enviroment I still would be stuck with 8 other queries "paste together", after the union join, and these other queries are very dissimilar to the sales history tables. These other queries add dimensions to my cube table, and some of them are from impromtu, some are from Excel, some are from Access.

Smile everyone there is only 4 more days to the weekend!

BR