Excel or Access to Impromptu Hotfile (.IMS)

[SteveFinance]

Basically I have an Excel spreadsheet that I need to bring into Impromptu. I am working with Impromptu 6 and MS Office 2000. I am savvy enough to work with the file in both Excel and Access and save as or export to whatever. Also, I need to do this without writing to the main database. If anyone has any ideas or if you just do not think it is possible, I would really appreciate the feedback. Thanks.

 

[DoubleD]

You'll have to create a catalog against your file (you can export it into any number of formats), then create a Hotfile. Now you can add your Hotfile to your catalog that contains the database.
Remember that Hotfiles can perform VERY poorly, since it nothing more than a flat file with no indexing.
Depending on your database you could also link to your database tables from Access, then create a single catalog against the Access tables.

Ultimately, the right solution is usually to put the data in the main database if it's that important.

Pain is stress leaving the body.

DoubleD

 

[SteveFinance]

How do I use any of the formats I can export as a catalog? It seems I can only use files with a .CAT extension, which is a format I do not think I can directly export to.

 

[DoubleD]

If your data is already in Access, just create a new catalog that uses the Access Database as its datasource. Now write a report that captures the data you want and save that as an .IMS file.
Now you can add your hotfile (IMS) to the catalog that points to your main database.

Pain is stress leaving the body.

DoubleD

 

[SteveFinance]

I understand how to create a hotfile from a catalog. What I need to know is how to create a catalog from whatever I export from Access.

 

[DoubleD]

The Impromptu documentation has complete coverage of how to create catalogs. You don't need to export the data out of Access, you can create a catalog the uses your Access database as its datasource.

Sorry if I'm not being clear about my directions. Maybe someone else could word it in a different way that would make more sense to you.

Pain is stress leaving the body.

DoubleD

 

[griffindm]

Steve,

What DoubleD is saying is:

1. Create a catalog (if you haven't already) for Impromptu to pull your Access data out. This involves setting up the ODBC link to your Access data, adding the Access ODBC in Impromptu under the menu item Catalog | Databases. Then set the database in the catalog to match whatever you named this logical database to.

2. Create a report to dump the data out. Save this report, and then save it again to a hotfile (under Report | SaveAs). Save it to a location that is accessible to whomever is going to run the final report.

3. Add the hotfile to your existing catalog that references your main database. Do this under the Catalog | Tables screen. Add the hotfile and then add any required joins to make the data integrate with the main database data.

4. Create your final reports, using data from each source.

That about covers it. Get back with any questions.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[SteveFinance]

Thanks for your help. I have set up the ODBC database (it has the file extension .dsn). However, under the menu item catalog, there is no such option as "Databases". The closest thing I have is "Open", which will only allow me to open files with the extension .cat. Again, I am working in Impromptu 6.

 

[GJParker]

Try using the New option under catalog menu. If you don't have this option you must have Impromptu User edition installed. you will require the Admin version to create catalogs.



Gary Parker
MIS Data Analyst
Manchester, England

 

[griffindm]

Gary,

If he doesn't have the 'Databases' option under Catalogs then he is definitely using the User version of Impromptu.

Steve,

As Gary said, you MUST use the Admin version of Impromptu to make these changes.

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com