Replace Fields in Cognos Reports


Overview

In the case where Cognos folder names or field names get renamed, any Cognos report that contains those fields will break. Cognos will tell you that the report is broken and will also indicate the field that has been broken. Here are some ways that you can use to fix the broken report.

Critical Concepts

Example of the error:

Screenshot: Error generated whend fields break, "identifier not found"

Steps to Take

Option 1 – Drag and Drop in the Display

  1. Make a copy of the report before making changes. 
  2. From the page display, drag and drop the new field into the display.
    Screenshot: Drag and drop field into display on right.
  1. Delete the old field.
    Screenshot: Pointing to old field on report.
    4. Validate that all the broken fields have been replaced.
    Screenshot: Check validate report from the dropdown menu.

Option 2 – Drop and Replace in the Query

  1. Make a copy of the report before making changes.
  2. From the bread crumbs navigation under the edit icon, select the carrot menu between Report and Pages. Select the affected query.
    Screenshot: Query under carrot menu between Reports and Pages
  3. Double click the affected field or filter
    Screenshot: Field or filter double-clicked brings up new menu
  4. The Expression Definition area and Name of the field will need to be updated. 
    1. Use the package navigation to the left of the Expression Definition to find the new field. 
    2. Erase the current Expression Definition and then drag the new field into the Expression Definition.
    3. Manually update the Name of the field.
      Screenshot: Data item expression, expression definition screen.
  5. If the change is small you can also manually type in the change to the Expression Definition.  
  6. Validate that all the broken fields have been replaced.

Option 3 – Find and Replace in SQL

  1. For reports that reuse the old field many times in several queries this method can be used to do a bulk find and replace.
  2. Make a copy of the report before attempting this method.
  3. Double click on the old field name and copy the full field name, you’ll need it for later.
    Screenshot: Expression definition
  4. Click on the cogwheel in the upper right hand corner, select Copy report to clipboard.
    Screenshot: Copy report to clipboard under settings
  5. Select the text and copy (Ctrl+c)
    Screenshot: Copy report
  6. Paste text to a notepad and search for the full field name within it. 
    Screenshot: Search for full field name in copied report text
  7. Replace with the new field full name. 
    1. You’ll need to bring the new field into the display or into a new query in order to find the full field name.
    2. This will not change the name of the field in your report, just where the report is pointing at.
  8. Select all of the text in the Notepad and copy (Ctrl+C).
  9. In the Cognos report, click on the cogwheel and select Open report from clipboard.
    Screenshot: Open report from clipboard under settings
  10. Validate that all the broken fields have been replaced.
  11. This method can be problematic given the complexity of the SQL code.