How to Get Auto-Submit in a Cognos Multi-Select Prompt

This week, we had a report that we wanted to function like an Active Report, but we couldn’t use Active Reports because the report prompts would have resulted in hundreds of thousands of charts being generated. So we were forced to use Report Studio.

To make it look like an Active Report, we would have the drop-down value prompts auto-submit, so each change in the prompt would re-submit the report and change the chart. However auto-submit does not work for multi-select value prompts, so we had to get creative. The solution was to wrap the multi-select value prompt in a div tag with an onmouseup event. To implement this, add an HTML item before the multi-select value prompt and set it’s source to this:

<div id="cbPrompt1" onmouseup=" setTimeout(function(){   var inputs =getElementById('cbPrompt1').getElementsByTagName('INPUT');   for (var i =0; inputs.length > i; i++) {     inputs[i].disabled=true;   }   promptAction( 'reprompt' ); }, 50); ">

Then add an HTML item after the prompt to close the div tag:

<div>

That’s all there is to it! When the user clicks on an option they want to select the mouse-up is triggered.  This first disables the prompts to prevent users who click very fast from selecting another option (which would be ignored), and then it triggers a re-prompt..
Of course, only use this technique on reports that run quickly, otherwise it could be very frustrating for your users.

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 <> 0 — 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.