Tuesday, January 24, 2012

Update Report Specification Without SDK

Business Case:
There were many times, when I wanted to do update the specification of one or more reports. In one instance, a complex report with close to 10 queries needed to be modified because it was using the incorrect revenue field.

In another 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 meant I have to edit each of these reports and repoint the query item to the correct one.

In yet another case, a filter expression in a report with a large number of queries had to be modified.

Report Specification:
In order to keep this article short, I have moved the explanation of Report Specification to a separate post. If you would like to understand what a report specification is, read this article first: Explaining Report Specification

Modifying the Report Specification:
When a report needs to be modified, we usually edit the query or page in Report Studio and make the necessary changes to the layout or report expression. This is fine as long as you have to change just one expression or formatting in one place. But this is not always the easiest way.

Example:
Let's take the first business case I mentioned above.  Suppose that you have a report with 10 queries that have [Billing Address]. On top of this the term "Billing Address" is used in multiple pages and multiple places in the report (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.

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.
  • Open the report in Report Studio
  • From "Tools" menu, click "Copy Report to Clipboard". This will copy the report specification (read, Report XML) to the clipboard.
  • Open a text editor like Notepad or Textpad and paste the XML code.
  • Do a simple "Find and Replace" in your text editor. Find all instances of "Billing Address" and change to "Shipping Address" (don't use the double quotes).
  • Note that since we are looking for the text "Billing Address", you will be changing everything that has "Billing Address" including column titles, report header, query item names etc.
  • If you want to change only the report expressions, add square brackets when you find and replace, "[Billing Address]" to "[Shipping Address]".
  • Now "Select All" in your text editor and "Copy" (CTRL+C).
  • Open a new report studio window and from the Tools menu, select "Open Report from Clipboard".  This will open a new report.
  • You can now validate the report and save it (either overwrite the existing report or save it as a new report).
The entire process will take you about 2 minutes. Remember.. There is more than one way to skin a cat.. Let's do it the easy way.

In the next article, I will talk about how to update the report specifications of multiple reports by altering the report specifications (without the use of SDK).

1 comment:

  1. Thanks for posting some very help techniques.
    Could you please post a java script for dynamically ascending and descending column?
    Thanks

    ReplyDelete