Two Variables You Should Have In Every Report Studio Report

There is an old joke that tells us that hard work pays in the long run, but that laziness pays off right now. However, in the world of software development, I think that laziness done right can pay off in the long run even better than hard work. Now there are a lot of developers who would prefer to skip the task of documenting a report they’ve developed. Sure it’s lazy, but that’s short term lazy.

I prefer long-term lazy, where I document the report now so that when I go to edit it a year later, I don’t have to spend an hour re-learning all the details of the report design. The first variable I want to talk about allows report documentation right in the report.

ShowDocumentation

Few reports are so simple that new Cognos developers can understand them intuitively. In fact, I suspect that most of your reports take even experienced developers a little time to understand. Sadly, Cognos does not give us a place to document the report, however they do give us the tools necessary to easily create one ourselves.

Report documentation can come in many forms. Maybe you have a large list of items that need to be completed (developer name, date, time, parameters, query descriptions, ,modifications, etc.), or perhaps you just have a bit of information on the design that you really need to remind yourself or another developer, the next time the report is being modified. Either way, you can put this information right in the report and use variable ShowDocumentation to hide it from the report consumers.

Create ShowDocumentation as a string variable with the expression of “0” (zero), and set the values allowed to be either 0 or 1 (I never use boolean variables, but that’s a topic for another time).

Then add text items to your report and set the render variable so that it’s only rendered if ShowDocumentation is “1” (which it never is). If you’re just trying to call out something, you can put the text item right in the report where it will be seen by every developer. You could even color it red, or make it bold, or give it a yellow background (or all of these) if you really need to get their attention. If the text is long, put it in a block with a fixed width (say 800px) and set the render variable on it. Then, any text you put inside will be word-wrapped for the developer, but still hidden to the report consumer.

If you have a lot of documentation , then you could create a report page called “Documentation“with ShowDocumentation of “1” as the render variable. Within that report page you could use tables and blocks to create a form you could fill out. Use small text variables with the render variable, placed in the actual report page (not the Documentation report page) to serve as call-outs that you could then reference in the Documentation page.

Debug

Often, while we are in the process of developing or testing a report we need information that the report consumer should not see. Perhaps it’s some information on the layout, or just numbers that are used in a calculation. To do this the lazy way, you want to be able to add this information to the report once, and be able to turn it on and off at will. That’s where variable Debug comes in.

As with ShowDocumentation, create a text variable called Debug with a hard-coded value of “0” (zero) and possible values of 0 or 1. You can then use Debug either as a render variable or a style variable to hide the items you only want the developer to see.

Then, all you need to do is change the Debug variable’s value to “1” when you are testing so that the items are visible, and then change it back to 0 when you’re ready to put the report back into production.

Here are some examples:

  • In a list, you can add columns used in a calculation for validation. Use the ancestor button to navigate to the column’s List Column object and set the render variable there.
  • If you are having trouble with layout, you can use the style variable to turn on an object’s border when in “debug mode”. I like to use the color fuchsia because it really stands out. Besides, how often do you see fuchsia in a report?
  • Debugging a chart? Add a list using the same data source as the chart with the render variable set to Debug = 1.

Okay, you should only have to use one of these in every report (ShowDocumentation), but I find myself using Debug in a large percentage of the reports I develop.
Have you used something similar? If so, let me know.

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.

Changing a Value Prompt in Report Studio using JavaScript

There have been many occasions where I have needed to change the filters on a report by having the user click on data on the report.  For example if I had a listing of cars, class, and number sold, and the user clicked on “Sedan” under the Class column, I would want to filter the report to only show sedans.

In Cognos 8 Report Studio, I accomplished this by having  a drill-through on the Class column back to the same report, passing the data item into the Class prompt.  This was an imperfect solution for a number of reasons, but it was usually good enough.  However, Cognos 10’s introduction of Business Insight  made this technique a bit less than “good enough”.  If I were to add the report to a Business Insight workspace, clicking on “Sedan” would not update the report I was looking at.  Instead, it would open a new copy of the report in a new window.

So the drill-through technique is no longer an option. My new technique for accomplishing this is to use Javascript to change the prompt. The following technique works with Cognos 10.1.1 Report Studio and I believe it’ll work with Cognos 8 as well. However, remember that with any JavaScript used in Cognos, an update to another version of Cognos (even a minor one) could break it.

Set Up the Prompt

This technique requires that you add the prompt to your report page (regardless of whether or not you have a copy of the prompt on a  prompt page).  I like to put these near the top of the report so the user can immediately see what filters are being applied.  However, you can put it anywhere on the report page that you want. 

  1. Add the prompt to the report page
  2. Before the prompt, add an HTML item with the following HTML code:
    <div id=”VP_TestPrompt”>

    Give the div tag an appropriate ID.  If you have more than one prompt, give each one a unique name.

  3. After the prompt, add an HTML item to close the div tag:
    </div>
  4. After the closing div tag, add a third HTML item with the following function:
    <script type=”text/javascript”>
    function selectPromptByValue(divId, val) {
       var oVPCont = document.getElementById(divId);
       var oVP = oVPCont.getElementsByTagName(“select”);
       var i = oVP[0].options.length;
          while (i) {
          if (oVP[0].options[–i].value==val) {
             oVP[0].options[i].selected=1;
             promptAction(‘finish’);
             i=0;
          }
       }
    }
    </script>

    If you have multiple prompts, you will only need this HTML item once on the report page.  The first parameter tells it which prompt to reference.

Set Up the Link

Now you just need to call the function selectPromptByValue, passing in the name of the div tag (set in step 2 above) and the value you want to pass into the value prompt.
Ther are a couple of ways to do this.  For most reports you could use a hyperlink to call the function (ex: javascript:selectPromptByValue(“VP_TestPrompt”, “A”);).  However, this does not work when running the report from a Business Insight workspace because BI incorrectly assumes that the hyperlink (anchor tag) will be taking you to another web page.  This results in a warning about navigating away from the page.

Instead I use a simple text object and wrap it in another div tag with an onclick event.

  1. Add your text.  To make it look more like a hyperlink, you may wish to underline it and set the color to blue.
  2. Add an HTML item before the text:
     <div style=”cursor:pointer”; onclick=’javascript:selectPromptByValue(“VP_TestPrompt”, “A”);’>

    The first parameter should be the id of the div tag you set before the prompt.  The second parameter is the value in the prompt that you want to select.
    If the link is to be data driven (ex: in a list), then you will need to make the ULR Source Type a report expression and parse it together so that it looks like the above example.

  3. Add an HTML item after the text to close the div tag:
     </div>

Example Report

To see how this technique works, copy the text from this Word document into your clipboard, open Report Studio, and select Open Report from Clipboard.

How To Display Data From a Cognos 8 Report in Google Maps

IBM’s Cognos 8 Report Studio already has built-in maps that allow you to create geographic representations of your data, but they are not without limitations, the most notable being that there are only about a half dozen maps included.   The world-level maps contain every location, but not down to the level of granularity you often need (zip-code level for example).  There are U.S.-only maps that go down to the zip code, but they don’t include U.S. Posessions (ex: Puerto Rico) You can develop custom maps, but the software required to generate the MapInfo files is quite expensive and outside of the range of many project budgets.

One solution is to integrate your reports with Google Maps via the Google Maps API.  The API is capable of marking any location in the world, by a variety of means, including zip-codes, city names, or even latitude and longitude.  The interactivity is about as intuitive as it gets, and you can add markers to highlight specific map points or polygons to outline and color areas of any shape (a state for example)

This article follows-up on the example provided by Rayudu Vecha and Sridhar Munireddy in their developerWorks article; Integrate Google Maps with Cognos 8.  In it, they explain how to call the Google Maps API from within a Cognos 8 Report Studio report and insert hard-coded markers.  Here, I will show you how to expand that to dynamically populate markers on top of the Google Map.

The basic technique is fairly simple.  I take the single HTML Item that Vecha and Munireddy use (enhanced a bit for my particular needs) and then divide it into four separate HTML Items, with one of them nested inside a repeater object to handle an unknown number of rows from a query. Optionally, the third HTML can be embedded inside a singleton to allow it to retrieve from the database a value that will be put into a JavaScript variable.

When the report is run, the four separate HTML items act as a single block of HTML code, but displayed within the Cognos 8 report viewer.

What You’ll Need:
  • A Cognos 8 Business Intelligence server
  • A Google Maps API Key  for the Cognos 8 BI server’s domain (directions for obtaining this are below)
  • Basic development skills for Cognos 8 Report Studio
  • A fundamental understanding of JavaScript
  • A database that contains the latitude and longitute of the locations you wish to mark on the map.

The Steps:
  1. You are going to need a Google Maps API key.

    1. Go to http://code.Google.com/apis/maps/signup.html
    2. Enter the domain name at which you access Cognos 8 (ex: http://cognos.mycompany.com)
    3. Click the Generate API Key button.This should create a key that looks similar to this:

      ABQIAAAAN28cRTOPRc8HwC3fHtsmKhTtWTE_GChODK0XVX0wNep29RbXARRXke_fSEx94NZZnhOisv8S0tZqVg
    4. Copy the API key displayed and save it for future use.
    5. If you can, store this key somewhere in your reporting database.  This will help if you plan to later migrate the report to another server (ex: from development to production)
  2. Open Report Studio and create a new report using the blank template.
  3. From the insertable objects toolbox, add a Repeater object to the body of the report.
  4. Right-click on the new repeater object and select Go To Query.
  5. Add to the query the Data Items you want to display.This data will be used to assemble a Javascript array (in a subsequent step).You will also need to include two columns containing the latitude and longitude of each marker.

  6. If you plan to have more than one row in the query (i.e. more than one marker on the map) you will need a column that contains a comma for all but the first row.  This comma will be used as a separator for the elements in the Javascript array.  To Do this:
    1. Add a Data Item to the query named Row. Set the expression to:

      rank ([Location])

      (replacing “[Location]” with a column you’ll be sorting on)

    2. Add another Data Item named ElementSeparator. Set the expression to:

      IF ( [Row] =  1 ) THEN
      ( ” )
      ELSE
      ( ‘, ‘ )


  7. Return to the page (Page1).
  8. Click on the repeater and edit the Data->Properties.Click check-boxes by each data item that you will want to use.  This will allow you to use them in a report expression.

  9. Change the repeater’s Data->Rows Per Page to 99999(otherwise, Report Studio will try to create a new page for every 20 rows in your repeater)

  10. You may also need to define the Data->Grouping and Sorting of the repeater.
  11. Add an HTML Item immediately to the left of the repeaterThis will contain the start of the Javascript

    1. The source type will be the default: Text
    2. Set the HTML text to the following:
      <html>

      <body>

      <head>

      <title>Static -> Dynamic Google Map with Markers</title>

      </head>

      <style type=”text/css”>

      .message {

      font-size: 80%;

      padding: 2px;

      font-weight: bold;

      text-align: center;

      width: 550px;

      height: 450px;

      background-color: #ffcc00;

      border: 1px solid white;

      position: relative;

      }

      </style>

      <script type=”text/javascript”>

      var locations = [


  12. Add a second HTML Item inside the repeaterThis will be the assembly of the array containing your data

    1. Set the source type to: Report Expression
    2. Set the report expression to the following, modifying it to fit your data:

      [Query1].[ElementSeperator] + ‘{name: ”’ +

      [Query1].[Location] + ”’, url: ”’ + [Query1].[URL] +  ”’, headcount: ‘ + number2string ( [Query1].[Headcount] ) + ‘, hires: ‘ + number2string ( [Query1].[Hires] ) + ‘, terminations: ‘ + number2string ( [Query1].[Terminations] ) + ‘, promotions: ‘ + number2string ( [Query1].[Promotions] ) + ‘, lat: ‘ + number2string ( [Query1].[Latitude] ) + ‘, lng: ‘ + number2string ( [Query1].[Longitude] ) + ‘} ‘

      The resulting data would look something like this (line breaks and indentation added for readability):

      {
      name: ‘Location 1’,
      url: ‘http://url.for.location.1&#8217;,
      headcount: 123,
      hires: 1,
      terminations: 2,
      promotions: 3,
      lat: 38.88944,
      lng: -77.035341

      } ,

      {

      name: ‘Location 2’,

      url: ‘http://url.for.location.2&#8217;,

      headcount: 456,

      hires: 7,

      terminations: 8,

      promotions: 9,

      lat: 43.878996,


      lng: -103.459811

      }



  13. Immediately to the right of the repeater, add a third HTML Item
    (Note: if you stored the API Key in your database, add a Singleton with a query that retrieves it and put the HTML Item inside the singleton, and use a report expression to define the text)

    1. The source type will be the default: Text
    2. Set the HTML text to the following:

      ];

      var strMapKey = ‘

      ABQIAAAAN28cRTOPRc8HwC3fHtsmKhTtWTE_GChODK0XVX0wNep29RbXARRXke_fSEx94NZZnhOisv8S0tZqVg‘;

      (substituting your own API Key)

  14. Add a fourth HTML Item immediately to the right of the third one.This will contain the end of (and the bulk of) the Javascript

    1. The source type will be the default: Text
    2. Set the HTML Text to the following:

      var map = null;

      var bounds = null;

      var currentMarker = null;

      var mapDiv = null;

      var containerDiv = null;

      var clickedX = 0;

      var clickedY = 0;

      var isLoaded = false;

      /**

      * Called after script is asynchronously loaded in.

      * Creates the GMap2, GMarker objects and performs actions according to

      * what the user did to trigger the map load (search, zoom, click etc).

      */

      function loadMap() {

      if (GBrowserIsCompatible()) {

      mapDiv.style.background = ‘#fff’;

      mapDiv.style.cursor = ”;

      map = new GMap2(mapDiv, {logoPassive: true});

      bounds = new GLatLngBounds();

      for (var i = 0; i < locations.length; i++) {

      bounds.extend(new GLatLng(locations[i].lat, locations[i].lng));

      }

      var latSpan = bounds.toSpan().lat();

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      map.addControl(new GLargeMapControl());

      //map.setZoom(2);

      var mapControl = new GMapTypeControl();

      map.addControl(mapControl);

      var newBounds = map.getBounds();

      var newLatSpan = newBounds.toSpan().lat();

      if (latSpan/newLatSpan > .90) { map.zoomOut(); }

      for (var i = 0; i < locations.length; i++) {

      var marker = createMarker(i);

      var latlng = marker.getLatLng();

      var pixel = map.fromLatLngToDivPixel(latlng);

      if (Math.abs(pixel.x -clickedX) < 12 && Math.abs(pixel.y -clickedY) < 20){

      //GEvent.trigger(marker, ‘click’);

      }

      map.addOverlay(marker);

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      }  } }

      function zoomToAll() {

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      }

      /**

      * Creates a marker for the given location.

      * @param {Number} ind

      * @return {GMarker}

      */

      function createMarker(ind) {

      var value= 103037;

      var location = locations[ind];

      var marker = new GMarker(new GLatLng(location.lat, location.lng));

      /**

      * When the user clicks on the marker while zoomed out, it zooms in (in case there is

      * another marker nearby that would not be visable when zoomed out)

      * When they click on the marker while zoomed in, it opens an Info Window that displays

      * details about the location

      */

      GEvent.addListener(marker, ‘click’, function() {

      marker.html = [‘<b>’, location.name, ‘</b>’,

      ‘<ul>’,

      ‘<li>Headcount: ‘, location.headcount, ‘</li>’,

      ‘<li>New Hires: ‘, location.hires, ‘</li>’,

      ‘<li>Terminations: ‘, location.terminations, ‘</li>’,

      ‘<li>Promotions: ‘, location.promotions, ‘</li>’,

      ‘</ul>’,

      ‘<a  href=”‘ + location.url + ‘”  target=”_blank “> Detail Listing</a>’].join(”);

      currentMarker = marker;

      if ( map.getZoom() <= 12 ) {

      map.setCenter( marker.getLatLng(), 15 );

      }

      else {

      marker.openInfoWindowHtml(marker.html);

      }

      });

      return marker;

      }

      /**

      * Formats location info into a URL-friendly version for maps url.

      * @param {Object} location

      * @return {String}

      */

      function formatAddressForMaps(location) {

      var address = location.street + ‘ ‘ + location.city + ‘ ‘ + location.state + ‘ ‘

      + location.zip;

      return escape(address.replace(‘ ‘, ‘+’));

      }

      function _cel(elementType, id) {

      var element = document.createElement(elementType);

      element.id = id;

      return element;

      }

      /**

      * Loads in the Maps API script. This is called after some sort of user interaction.

      * The script loads asynchronously and calls loadMap once it’s in.

      */

      function loadScript() {

      if (!isLoaded) {

      isLoaded = true;

      var div = document.createElement(‘div’);

      div.className = ‘message’;

      div.innerHTML = ‘Loading…’;

      div.style.left = (800/2 – 53) + ‘px’;

      div.style.top = 800/2 + ‘px’;

      mapDiv.appendChild(div);

      var script = document.createElement(‘script’);

      script.type = ‘text/javascript’;

      script.src = ‘http://maps.google.com/maps?file=api&v=2&#8217; +

      ‘&async=2&callback=loadMap&key=’ + strMapKey;

      document.body.appendChild(script);

      }

      }

      /**

      * Sets up the gadget by setting CSS and click events.

      */

      function loadMapGadget() {

      containerDiv = document.getElementById(‘container’);

      mapDiv = document.getElementById(‘map’);

      mapDiv.onclick = function (e) {

      clickedX = (window.event && window.event.offsetX) || e.clientX;

      clickedY = (window.event && window.event.offsetY) || e.clientY;

      loadScript();

      };

      loadScript();

      mapDiv.style.cursor = ‘pointer’;

      var urlString = [‘http://maps.google.com/staticmap?markers=’%5D;

      var markerString = [];

      for (var i = 0; i < locations.length; i++) {

      markerString.push(locations[i].lat + ‘,’ + locations[i].lng + ‘,red’);

      }

      urlString.push(markerString.join(‘|’));

      urlString.push(‘&size=800×600’);

      urlString.push(‘&key= ‘ + strMapKey);

      mapDiv.style.background = ‘url(\” + urlString.join(”) + ‘\’)’;

      }

      </script>

      </head>

      <body onload=”loadMapGadget();”>

      <div id=”container”>

      <div id=”map” style=”width: 800; height: 600; overflow:hidden”></div>

      </div>

      </body>

      </html>



  15. Save and test the report.

Conclusion:

Integrating Cognos 8 Report Studio data into Google Maps might not be quite as straight-forward as using the built in maps, but it’s not too difficult and the results are sure to impress your report consumers.  This is also a great starting point for learning to integrate Cognos 8 with other web services.