Tuesday, February 7, 2012

Update Report Specification for Multiple Reports without SDK

Business Case:
The business users decided to change the name of a data item in the Cognos package. Unfortunately there were about 20 reports that used this data item, which means the report author has to open each of these reports and repoint the query item to the correct one.

Background:
If you are not familiar about what a Report Specification is and would like to get a better understanding, read this article first: Explaining Report Specification

You may also be interested in learning how to quickly update a single report with multiple queries for data item name changes. Check out this article: Update Report Specification without SDK

Example:
Let's take the first business case I mentioned above.  Suppose that you have 20 reports have the query item, [Billing Address]. On top of this the term "Billing Address" is used in multiple pages and multiple places in the reports (header, column title, foot notes etc). The report user comes to you, the author, and requests you to change all references to Billing Address to Shipping Address, which already exists in the package.

Solution:
We will be using the Report Deployment option in Cognos to do the bulk update. Note that you will need access to Content Administration to be able to use this approach.

Implementation:
In this case, editing the report in report studio to find every instance of [Billing Address] and changing it to [Shipping Address] is a tedious process. It is highly inefficient too. Here is a quick way to change the report.
  • Prepare the Deployment Content:
    • In the "Public Folders", create a folder called "Deployment".
    • Copy all the reports that need to be updated into this folder.
  • Create Export Job:
    • From the "Launch" menu in Cognos Connection, click "IBM Cognos Administration".
    • Select the "Configuration" tab and click on "Content Administration" on the left pane.
    • Create a new "Export" and specify a name of your choice. For our example, let's call it, "Report Spec Update"
    • In the next screen, under "Deployment Method", select the option "Select public folders and directory content" and click "Next".
    • Under "Public folders content" section, click "Add" and add the "Deployment" folder.
    • Select the appropriate options from the "Options" section.
    • Go thorough the rest of the screens like you normally would and enter a name for the archive in the last screen (E.g. report_spec_update).
    • Save and Run the export job once.
  • Update the Specification:
    • Login to the Cognos server (Use remote desktop connection if Windows, SSH if Linux).
    • Navigate to the deployment folder: <cognos_install_dir>/deployment
    • You will find a zip file with the archive name you specified when creating the export (report_spec_update.zip).
    • Copy the zip file to a different folder.
    • Unzip the contents of the zip file. It will produce 3 files: content.xml, exportRecord.xml and package1.xml.
    • The file we are interested is the package1.xml. Open this file in a text editor. 
    • Note: This is the most important step of the entire process. If your cognos server is running on a linux environment, then the editing must be done in the linux server itself, in vi editor. Do not copy the zip file or the xml files to your windows desktop for editing since this screws up the file encoding.
    • Once you have the file open in the text editor,  do a find and replace for the data item name that you are trying to update. For example, replace all occurrences of [Business View].[Customer].[Billing Address] with [Business View].[Customer].[Shipping Address]
    • Save the file package1.xml.
    • Zip the 3 files to a new archive.
    • Copy the zip file back to the Deployment folder in the Cognos server.
  • Create Import Job:
    • Log in to Cognos Connection and navigate to Content Administration.
    • Create a new Import and from the list of archives, select the new zip file that you just created.
    • Run the Import.
    • The reports in your "Deployment" folder will be updated with the changes you made in the XML file.
That's it !!! The same task, if done manually would take hours to complete and you would not be sure if you updated all the places. The steps above might seem like a long process but when I tested this, the entire process took me about 5 minutes.

If you are in a daring mood, you can expand the approach to the entire content store instead of exporting only the Deployment folder. As long as you keep a copy of the zip file you exported, you are fine.

If you have made a mistake during the update, or if you find out that you have changed more reports than necessary, you can always import the original export back again and you will be right where you started.

Remember.. There is more than one way to skin a cat.. Let's do it the easy way.

8 comments:

  1. I am a beginner in cognos and you explained the things like very well that beginner like me can also understand, Thanks for you valuable post......

    ReplyDelete
  2. Hi,

    You could also use the Dynamic Report Spec updater (DRU) utility to to the same thing without having to export the content and edit the XML manually.

    ReplyDelete
  3. Hi,

    I followed the same steps , but it is not working and throwing below error.

    CM-REQ-4139 "Archive Name" is not a valid deployment archive.

    Version : Cognos 10.1.1
    DB : DB2
    Os : Linux

    ReplyDelete
  4. You can also use MotioPI Pro update many reports at once (in several different ways).

    There's a search and replace feature that allows you to update many reports en-masse (and will validate them before / after the change) :

    http://www.motio.com/products/pipro/searchReplace.do

    There's also a property replicator feature which will let you replicate selected properties from one Cognos object (e.g. permissions, package, default action) to many other Cognos objects, for example :

    http://info.motio.com/Blog/bid/76653/Update-the-Default-Action-for-Multiple-Cognos-Reports

    and

    http://info.motio.com/Blog/bid/76653/Update-the-Default-Action-for-Multiple-Cognos-Reports

    There's also a "Templatizer" feature in the upcoming release which will let you replicate things like prompt pages, headers & footers from one report onto many others.

    ReplyDelete
  5. @caffineinduced: Thanks for the comment. I thought I had already mentioned this in the post but now I realize that I didn't. I will add it to my post.

    ReplyDelete
  6. Nice approach Sriram,
    A great way to bulk edit the XML without hitting the (very slow) content store database tables directly, or having to use a third party tool.

    ReplyDelete
  7. Keep posting the good work. Some really helpful information in there. Bookmarked. Nice to see your site. Thanks! medicine shoppe franchise cost

    ReplyDelete
  8. This is impressive and also great information. I personally liked going through your solid points on this topic. Many thanks for creating such excellent material. This is excellent. best 500 dollar mountain bike

    ReplyDelete