Reporting logged parameter values

During the past few years, I have had - and still have - the privilege to administer one of the largest IBM Cognos BI 10.2.1 configurations in The Netherlands. The production configuration consists of 2 webserver/gateways servers, 10 dispatcher servers and 2 (active/passive) content manager servers. Each day, approximately 80,000 reports are requested and processed. Each month the system serves approx. 6,000 unique users. Substantial growth is anticipated.

One of the security requirements, as demanded by the client, is to log all report and job requests. This is achieved by enabling the out-of-the-box Cognos Audit Logging to a database (Oracle). See the BI Administration and Security Guide, Chapter 5, for more information on this topic.

In addition, my client not only wants to see who has run which report, but also which parameter (or prompt) values were used when running these reports. To enable parameter value logging, we added the Advanced setting “RSVP.PARAMETERS.LOG” value “TRUE” in Cognos Administration for the ReportService and BatchReportService of each requested dispatcher. This setting populates the Audit log table COGIPF_PARAMETER with records containing COGIPF_TARGET_TYPE = 'JobStepDefinition' (for job step request parameter values) or COGIPF_PARAMETER_NAME = 'parameterValues' (for report request parameter values). Note: this setting is not well known or documented! We found and applied the information from this IBM document.

After enabling parameter value logging, for report requests all parameter value information is stored in a CLOB column named COGIPF_PARAMETER_VALUE_BLOB. As you might have guessed, this is XML type information.

Now the challenge is: how to elegantly retrieve this information for presenting in our audit report?

We solved this puzzle by defining some XML functions this native SQL query in Report Studio:

select

       "t"."COGIPF_REQUESTID"       "REQUEST_ID"      ,

       "t"."COGIPF_SUBREQUESTID"    "SUBREQUEST_ID"   ,

       "t"."COGIPF_TARGET_TYPE"     "TARGET_TYPE"     ,

       "t"."COGIPF_PARAMETER_NAME"  "PARAMETER_NAME"  ,

       "t"."COGIPF_PARAMETER_VALUE" "PARAMETER_VALUE" ,

       a.*

from

       COGIPF_PARAMETER "t",

       xmltable(

             xmlnamespaces('http://developer.cognos.com/schemas/bibus/3/' as "bus"),

             '/parameterValues/item/bus:value/item/bus:use'

             passing xmltype(

                    --substitute missing XML by some empty XML to avoid syntax errors

                    case when length(cogipf_parameter_value_blob) > 0

                           then cogipf_parameter_value_blob

                           else to_clob('') end)

             columns

                    naam            varchar (250)   path './../../../bus:name',

                    usevalue        varchar(2500)   path '.' ,

                    displayvalue    varchar(2500)   path './../bus:display'  ) a

Explanation regarding the XML “path” as used in the column definitions above:

• the node “bus:use” is the referral point, as mentioned in '/parameterValues/item/bus:value/item/bus:use'
• column “naam” (Dutch for “name”) is retrieved from three levels up
• column “usevalue” is at the referral level
• column dispayvalue is at the same level, adjacent node

Next step is to feed it to a normal query, here it's named Query2:

Query2 contains the filter on JobStepDefinition and parameterValues (this could alternatively have been defined in the native query as well):

The filter on REQUEST_ID gets its parameter ?parRequestId? passed by an overview report drilling through to this parameter report.

The result is presented in a normal list object, containing the following columns and values per logged parameter:

Using this type of native query was key to extract the information we need in our log reports.

Please let me know in the comments below if this information was of any value to you!

PS Kudos to co-worker Fred Bouwhuis for sorting this technique out!

How to automatically select the first prompt value

In the dashboard application I blogged about in the previous post, I now have added a prompt in the main portal report page showing the current period. When a new year arrives, the users often don't just want to see the few actual details if the new year, but also want to look back into the details of last year. This requirement calls for a prompted list of values.

Here is a screenshot of the actual prompt (red circle). The first value ('2007 t/m januari') is automatically selected. Mind there is NO prompt page! This is the first 'report' page that is presented to the user.



In Cognos it is possible to provide a Default Selection but this contains a list of static values. I cannot use this a each month there is another first value to be selected. So what I've created is a Value Prompt object presenting its values by a query with Use Values and Display Values, and have added an HTML item with a piece of JavaScript that selects the first value. The HTML item is added just after the Value Prompt object.

The JavaScript code in the HTML items looks like this:

<script type="text/javascript">
function init()
{

if

(document.formWarpRequest._oLstChoicesPeriode.options[3].selected == false)

{

if

(document.formWarpRequest._oLstChoicesPeriode.options[2].selected == false)

{

document.formWarpRequest._oLstChoicesPeriode.options[2].selected = true;

listBoxPeriode.autoSubmit();

}

}

}
</script>
<body onLoad=init()>

The first value to be selected in the list is actually the second value in the list object, which JavaScript sees as a list of three values in total. If no value is selected initially (third and second selections are false), the second value is selected by the script.

Tip: Did you know that the Cognos role "Consumers" needs to be given read permission to the Report Studio capability "HTML Items in Report" in order to get HTML item objects working in reports for Consumers? Please mind this and check the Tools > Capabilities in Cognos Connection!