Impromptu and Excel

[TErnst]

Hello,
how can I connect an Excel-Table with my Impromptu catalogue, so that I can run a report over both and after this I can model the .iqd - file in Transformer. I think I should define a join between the Excel-Table and one of the tables in the catalogue, but I don't know how.

Thanks

Thorsten Ernst

 

[nagrajm]

With only the available information, I would say that there is no way you can join an excel sheet/table within an impromptu catalog. The only other object that Cognos supports in a catalog, is their generic hotfile. You can bring in and join a hotfile with other tables within your catalog.

One way out would be to create a separate catalog for the excel sheet using ODBC connections and then create an impropmtu report out of it by including all the columns from the excel sheet. Save this report as a hotfile (.ims).

Bring this hotfile into the other catalog (one mentioned in your post). Create the appropriate joins.

Using joins with hotfiles would have performance issues as the joins are implemented internally within Cognos after all the rows from the database have been retrived. But this is one way of creating reports from two different data sources.

I am not quite sure how an IQD created out of this query would behave in a model. I haven't done any model with a hotfile in it. But I remember some documentation on it. I have to look it up. In the meanwhile you could try what I have suggested above.

 

[nagrajm]

Hey Thorsten...I would advice that you join this forum as a member in order to enjoy it's full array of benefits.

 

[quatermass]

Hi Thorsten,

If you are going to use ODBC to create a new catalog for the Excel spreadsheet, might I suggest (if possible) saving the spreadsheet to csv files.

Every time I have tried to use the Excel ODBC drivers since Impromptu v4, I have had problems with missing/inconsistent data.

Saving the data as a csv file still allows users to use Excel, but I have found text ODBC drivers to be much more reliable than the Excel ones.


Kevin **************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[griffindm]

Kevin (quartermass),

Great signature!

Thorsten,

Both suggestions are good. I would recommend the csv solution for use with Transformer.

HTH,

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

http://www.decisionsupportgroup.com

 

[nagrajm]

Actually, transformer(any version) does not support the use of hotfile as a data source in IQD. You will have to find a way to use the excel sheet directly as a data source for your transformer model. As others have suggested above, it is better to convert it to a CSV file.