Editing Cognos Report Studio Reports in a Text Editor

One technique for editing Report Studio reports that is too often overlooked is editing the report’s query data items in your favorite text editor (mine is Notepad ++ Portable). This is made possible because Report Studio reports are stored in XML which is fundamentally a text format.

For most of the editing you do, the Report Studio tool is the best way to do it. It keeps all the XML properly formatted and free of structural errors (report design errors are your responsibility). However, occasionally I find the need to make bulk changes, particularly in report queries, which Report Studio does not do well.

Before I proceed further, I must warn you that editing a report in a text editor is something you must do with great caution. It is very easy to introduce errors into the XML that will invalidate your report. Making backups of your reports before editing is always a good idea, but it is even more crucial to do so before performing any changes outside of the Report Studio web application.

Just to be sure I’m clear about this… BACK UP YOUR REPORT FIRST, BACK UP YOUR REPORT FIRST, BACK UP YOUR REPORT FIRST, BACK UP YOUR REPORT FIRST!

Getting the Data Items To and From Your Text Editor

There are two ways to copying a report into a text editor and back out. You can do the whole report, which works in all browsers supported by Report Studio, or you can do specific items in a report, which currently only works with Microsoft IE (internet Explorer).

Whole Report

The XML for an entire report can be quite large and if you are not familiar with XML, it can seem very complicated. This can make it quite difficult to locate the specific data items you want to edit. For this reason, I avoid working with the whole report, but if you don’t have IE (working on a Mac for example), this may be your only option.

For the whole report, there are two menu items you can use:

  • Tools -> Copy Report to Clipboard
    This will copy the full XML of the report into the operating system clipboard. Then you just need to paste it into your text editor.
  • Tools -> Open Report from Clipboard
    From your text editor, select the entire report contents and copy it, then use this option in Report Studio to open the modified report from the clipboard.

One of the risks of using this method is that when you save the report, if you save over an existing copy of the report, you will re-create the report with a new internal report id. This could cause you a big problem if you have drill-throughs to that report, because it will break those links. To work around this problem, save your report to a temporary folder and then in Cognos Connection, copy the report and paste it into the original folder. This will overwrite the report but retain the original report id.

Specific Query Data Items

For specific data items, make sure you open the report in an IE browser and confirm that Report Studio is using the operating system clipboard (in Tools -> Options -> Advanced (tab)). Then, simply cut (ctrl-x) the query items from Report Studio and paste them into your text editor. When you are done editing, you copy the text from your text editor and paste them back into your report query. You may also need to reposition the items in the Report Studio Query Explorer after you have pasted them.

Editing the Query Data Items

From here on, we’ll be discussing editing specific data items. The techniques are the same if you are working with the whole report, but as said before, the XML is much larger and too complex, to use as an example.

Here is an example of the XML from a pair of data items. The XML has been reformatted (addition of line feeds and indentation) and highlighting added for readability.

<RSClipboardFragment version="2.0">
    <dataItem name="Base URL">
       <expression>[versionConstants].[Base URL]</expression>
    </dataItem>
    <dataItem name="Base Gateway">
       <expression>[versionConstants].[Base Gateway]</expression>
    </dataItem>
 </RSClipboardFragment>

The highlighted portion (green and yellow) show the XML, which you should not change unless you are familiar with both XML and the XML schema that Cognos uses.

Between each of the expression opening and closing tags (<expression> and </expression> respectively) are the data item expressions that you would find in Report Studio by double clicking on the data items in a query. These are the areas that to which we want to limit our editing.

Let’s say I needed to change the sub-query of these two items from “versionConstants” to “chapterConstants”. In my text editor, I could do a search and replace, searching for “[versionConstants]” and replacing it with “[chapterConstants]”. Please note that it is important to provide sufficient context to the search term so that you don’t accidentally make changes you didn’t intend. For example, if I had just searched for the word “version”, it would have also modified the first line of the XML and made it invalid, so I wouldn’t be able to paste it back into my report.

Of course, this wouldn’t be worth the effort if I were just making this change to 2 data items, but what if I were changing 20 data items, or 100? That is when this technique becomes practical.

Summary

It’s also worth noting that editing in a text editor can work with more than just query data items. In fact it works with just about any object type in Report Studio (variables, pages, text items, lists, images, etc.), but most other items are more complicated to change and require an understanding of XML, HTML, and the Cognos XML schema.

This technique requires a bit of practice, but once you’ve mastered it, you’ll be bulk-editing Cognos reports like a ninja.

Advertisements

Turn a Report Studio Text Prompt into a Table (Pivot a parameter)

I recently had a requirement where I needed to allow the report users to paste a number of values into a prompt, all at once.  Not only did the values need to be used in a filter, but they also had to be validated against the database.  The idea was that if they had a value that was invalid, that they could be notified before running the report so that they could correct the invalid value.

The technique for accomplishing this was relatively straightforward; Create a query in the report using the (manual) SQL object, and then use macro functions to parse the parameter and append the appropriate SQL to accomplish a union of each value.
This macro/SQL assumes a space delimiter (in the split() function), but could be easily modified to accommodate other delimiters.
# 'SELECT ' + join( ''' AS ColumnName FROM DUAL UNION ALL SELECT ''', 
split(' ', prompt('Parameter1'))) + ' AS ColumnName FROM DUAL' #

(In this case, I used the Oracle system table Dual, which always has exactly one row.  If you’re not on Oracle, you may need to find another single-row table.)

What this does is takes a prompt such as this one:
Value1 Value2 Value3 Value4

and creates a SQL statement that looks like this:

SELECT 'Value1' as ColumnName FROM DUAL
UNION ALL
SELECT 'Value2' as ColumnName FROM DUAL
UNION ALL
SELECT 'Value3' as ColumnName FROM DUAL
UNION ALL
SELECT 'Value4' as ColumnName FROM DUAL
The query can then be joined to other queries with an outer join to show the values that are not valid.

A List Prompt From an Actual List

A colleague recently used this technique on my current project, so I want to make sure that credit was given where credit is due.

From Paul’s Cognos Blog: Checkbox List Prompt