How to Set an Active Report Variable in JavaScript

Yesterday I was asked if it was possible to set an Active Report variable in a Cognos Active Reports using JavaScript and the short answer was “No”. I have not yet found a way to do so directly. If you know how to do this, please share! However, changing A/R prompts is another matter entirely and since you can change the prompt, you can have the prompt set the variable.

I successfully did this a couple of years ago, and realize that I should have shared it. I guess better late than never. Here’s a report example that shows you how it’s done:
Alter an Active Report Variable.xml
Once you open the report in Cognos Report Studio, you’ll need to change the package and the data source in the SQL query object. The SQL’s written for Oracle, but you can alter it for your own database.

The report has several elements. First is the image tag that is necessary to implement the functions and the JavaScript that must run when the report first opens. The use of the image tag is explained in greater detail in my post Creating JavaScript Functions in Active Reports. This contains three functions:

  • getElementByARId – This function, along with some start-up code at the end of the onload event allows us to quickly find objects we need to reference. An Alternative to getElementById for Active Reports will describe this in full.
  • fireMouseDown – Takes a node in a radio button and simulates a click on that node. The three major browsers all need to do this differently.
  • selectRBListValue – Finds the radio button group you want to affect (list), searches for the node you want to select (node), and attempts to click on that node. Since the timing of the creation of the many elements in an Active Report can be tricky, it attempts to click on the node four times.

The other elements are:

  • A div tag (and closing tag) surrounding a button. This describes what to do when the button is clicked.
  • A data drop-down list to show the results of the button being clicked.
  • A div tag surrounding a data radio button group that we will use to manipulate the variable. This tag and its contents are hidden from the user’s view.

It looks like a lot, but once you understand how to implement it, this technique is fairly simple to deploy. It may have taken a long time for me to give this to you, but on the bright side, I can say that it has been tested thoroughly almost every business day for the last two years.

Advertisements

Finding Schedules in Cognos That Run Too Frequently

Sometimes, a task seems too easy to bother with reading the manual.  We’ve all done it.  The steps appear to be obvious, so why look for guidance?  In our organization, one of those tasks is scheduling Cognos reports.  We provide training documentation and even videos on how to do this, but Cognos does make it reasonably simple.

And yet, we have a handful of users who don’t understand the By Day tab or the Daily Frequency sections of the schedule, so we occasionally end up with reports that are run every hour or even every minute.  Of course this creates an unnecessary load on Cognos and on the reporting databases.

When listing schedules, Cognos does not provide filters for  frequency or daily frequency (recurrence), so finding these wasteful schedules can be tedious.  The solution, as it often does, lies in querying the content store.  The following SQL will list any active schedules that run more than once a day, or that have a recurrence (every X minutes or hours) on the days it runs.  It’s in Oracle SQL, but should be simple enough to convert to your own content store’s SQL.

SELECT
       N2.NAME AS Owner_Name,
       N.NAME AS Schedule_Name,
       O.Created ,
       O.Modified ,
       OP2.Active ,
       OP2.StartDate ,
       OP2.EndDate ,
       DECODE ( OP2.TYPE,
               1, ‘By Month (Day of Month)’,
               2, ‘By Month(Day of Week Instance)’,
               4, ‘By Week’,
               5, ‘By Year(Day of Month)’,
               6, ‘By Year(Day of Week of Month Instance)’,
               7, ‘By Trigger’,
               9, ‘By Day Recurring’,
               10, ‘By Month (Day of Month) Recurring’,
               11, ‘By Week Recurring’,
               12, ‘By Month(Day of Week Instance) Recurring’,
               13, ‘By Year(Day of Month) Recurring’,
               14, ‘By Year(Day of Week of Month Instance) Recurring’
          ) AS Schedule_Type ,
       OP2.EveryNPeriods ,
       CASE
          WHEN OP2.TYPE IN ( 0, 9) AND OP2.DailyPeriod = 0 THEN ‘Every Day’
          WHEN OP2.TYPE IN ( 0, 9) AND OP2.DailyPeriod = 1 THEN ‘Every Hour’
          WHEN OP2.TYPE IN ( 0, 9) AND OP2.DailyPeriod = 2 THEN ‘Every Minute’
          ELSE NULL
       END AS DailySchedule,
       CASE
          WHEN OP2.TYPE IN ( 9,
                            11,
                            12,
                            14)
                THEN OP2. IntraRecurInterval
          ELSE NULL
       END AS Recurrence
  FROM CMOBJECTS O
       INNER JOIN CMOBJECTS PO1 ON O.PCMID = PO1.CMID
       INNER JOIN CMOBJNAMES N ON PO1.CMID = N.CMID
       INNER JOIN CMOBJPROPS2 OP2 ON O.CMID = OP2.CMID
       INNER JOIN CMREFNOORD2 REF ON O.CMID = REF.CMID
       INNER JOIN CMOBJNAMES N2 ON REF.REFCMID = N2.CMID
 WHERE    
       N2.Name LIKE ‘%yournamespace%’  — Something that identifies the organization’s namespace
       AND O.ClassId = 39 — Schedules only
       AND OP2.Active <> — Ignore disabled schedules
       AND NVL ( OP2.EndDate, SYSDATE) >= SYSDATE — Ignore schedules that have expired
       AND
       (  
            (
                    OP2.TYPE = 0  — Daily Schedule
                    AND
                    NVL ( OP2.DailyPeriod , 0) <> 0 — Frequency is not per day (is per hour or minute)
            — Only schedules that run more than once a day
            OR
            (
                    OP2.TYPE IN ( 9,10 ,11, 12,13 ,14) — Any schedule with a recurrence (minute or hour)
            — Or schedules that recur on the day they are run
        );

 

One thing to look out for:  In order to control what time the schedule runs, some of our users set an hourly occurrence, but only within a specific hour of the day.  The technique is effective, and they gave this some thought , so I don’t want to discourage them.

To make this work, I had to determine the possible values for CMOBJPROPS2.TYPE (see the DECODE statement).  I think I found them all but there are gaps in the values (3 & 8).  They may be obsolete, or just values that require a combination of frequency parameters that I couldn’t guess.  If you figure out what those values represent, please let me know.

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.

Finally – RAVE Visualizations

I’m finally diving into the customization of RAVE Visualizations in Cognos 10. I don’t expect to have anything truly insightful in the near future, but if you’re wanting to get started, this article provides an excellent overview of the fundamentals.

Prevent Page Advance on AutoSubmit in Report Studio Prompt Pages

Recently we had a Report Studio prompt page with a number of prompts including some radio buttons. When a radio button was selected, the prompt page needed to be automatically refreshed. The problem is that Auto-Submit would advance to the report page.

The solution was to add a hidden text box prompt with “Require” set to “Yes” and then to replace the Next and Finish buttons with Javascript that would set the value of the required prompt before performing the Next or Finish function.

To accomplish this we needed 4 HTML items, a text box prompt with Required=Yes, and a fifth HTML item:
Prevent Page Advance on AutoSubmit in Report Studio Prompt Pages Fig01

 

Here’s the Javascript:

Next button
<div class="clsPromptComponent" pt="btn">
<BUTTON 
  class="bp" 
  type="button"
  onclick="getElementById('divPage1Complete').getElementsByTagName('input')[1].value=1; 
    promptButtonNext();"
  onmouseover="this.className = 'bp bph'"
  onmouseout="this.className = 'bp'"
>Next
</BUTTON>
</div>
Finish Button
<div class="clsPromptComponent" pt="btn">
<BUTTON 
  class="bp" 
  type="button"  
  onclick="getElementById('divPage1Complete').getElementsByTagName('input')[1].value=1; 
    promptButtonFinish(); " 
  onmouseover="this.className = 'bp bph'" 
  onmouseout="this.className = 'bp'" 
>Finish
</BUTTON>
</div>
Reset
<script>
  while( !alert) {}
  setTimeout( function() {
    document.getElementById('divPage1Complete').getElementsByTagName('input')[1].value="";
  } , 1000)
</script>
(the text box prompt goes here)
Prompt Opening Div
<div id="divPage1Complete" style="display:none;">
Prompt Closing Div
</div>

This way, the report will not leave this page until the user clicks the custom Next or Finish button.

A Technique to Audit Cognos Active Reports

Cognos Active Reports can be great, but one huge drawback is that the viewing of these reports does not show up in the Cognos audit database.  For environments where you need to justify your existence, this can be a serious problem.

While you can’t get the Active Report to show up in the audit database, you can certainly have a Report Studio report show up.  So all you need to do is embed the execution of a dummy Report Studio report in the Active Report.  Of course this doesn’t work if the user takes the Active Report offline, or if they are running it from a Cognos Mobile app, but for our environment this covered 98% of the Active Report executions.

The first thing you do is create the dummy report.  The content for the report can be anything, as the user will not see it.  Just keep it simple (perhaps just a  text item describing it’s purpose),  so that it will not affect the performance of the Active report.  We chose to give the report a name identical to the Active Report, but in a different folder.  Another option is  to prefix or suffix the name with the word “Audit” (ex: “My Report – Audit”).

Next, go into the properties of the new dummy report and click “View the search path, ID and URL” and copy the default URL Action.  Paste this into your favorite text editor because you’re going to make a couple of changes:

  1. Replace every ampersand (“&”) in the URL with “&amp;”
  2. Change “&run.prompt=true” at the end to “&run.prompt=false”

Finally, add an HTML item to your Active Report with the following HTML:

<i frame
     style="visibility:hidden;display:none; border:0; overflow:hidden;"
     seamless="seamless"
     allowTransparency="true"
     width="10"
     height="10"
     src="EncodedReportGoesHere"
></i frame>

You’ll need to remove the space between “i” and “frame”. Believe it or not, WordPress won’t let me post that word.
Replace “EncodedReportGoesHere” with the dummy report URL you edited.

Save the Active Report and you’re done.  Now just watch the audit tables after the Active Report is run and you’ll see the dummy report logged in there.