Changing table names in a Cognos report... 1

[BluByU]

I have a Cognos catalog that I have added 3 new tables to. These tables have the exact same layout as 3 exsitng tables in the database, but contain older, archived data. What I would like to do, is to point some existing Cognos Impromptu reports to these 3 new tables without re-writing the reports. When I go into the Query\Profile to edit the SQL statement, I get the error message: "This report cannot be translated into a single database-only query". I cannot edit the SQL statement at all.

Is there any simple way to do this without creating a whole new report? I even tried editing the .imr report file, but Impromptu complains that the file was created by another version and won't let me load the file. Any help is greatly appreciated.

 

[GilMerc]

Blubyu,

Do you need again your old report?
If Yes, you can replace the report field by calculation field who's take a old field or a new field depending on a prompt value.

If Not, you can replace old field by a nes field.

Gilles.

 

[griffindm]

Blu,

When I have had this situation, my approach has been to add the new tables to a copy of the catalog with the OLD table names, but under a new ownership schema. They then replace the old tables. Do this by creating a copy of your existing catalog and change the owner schema for those tables to be the new one. With this done your old reports should run without any modification if you open the new catalog copy first. Remember to close the report without saving it, or any subsequent use will point to the new catalog.

Hope this helps.

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

 

[GilMerc]

Dave,

I never try this approach but it's very intersting. I will keep in mind this for a future.

Gilles.

 

[BluByU]

Thanks for the replies. One detail I forgot to mention in my first post was that these 3 new tables also have a different name. "HIST_" is appended as a prefix to these tables. That's why I was trying to change the SQL statement with the edit function. It looks like I get that error message I mentioned if the report refers to even one additional table as a lookup. If a report uses one and only one table, then I'm able to edit the SQL. Strange...

 

[GilMerc]

BluByU,

If we follow Dave's approach, the name of table will be rename in new catalog then take it a same name of your old table and the report doesn't see the difference.

To do this, you just to rename a table name in definition.

Gilles.