Checkbox in BIP

Since BI Publisher is integrated with MS Office, you can use some features of Word to add extra functionality into your reports. This topic will handle about the usage of checkboxes in your RTF-template. An example below..

We will create an checkbox which status is dependent on a value in the xml. First, we have to create a SQL-query to get our value. Next step is to design a template to add our new functionality and as finishing touch, we upload it to BI Publisher and test it out.


SELECT 1 as checkbox_ind FROM dual


&lt?xml version="1.0" encoding="UTF-8" ?&gt

To add an form element into our template, we have to do first some steps to get our element.

Open MS Word 2007 and go to ‘Word Options’ under the Office-logo. There appears a new window where you can set the option for a developer tab.

Check box ‘Show Developer tab in the Ribbon’. Click ‘OK’ and you will see a new tab into your Word-page.

Follow next step to insert a new form element, in our case a checkbox:

The selected form element appears on your page. We are almost there, we have to add the BI Publisher functionality to get our checkbox work.

Select your checkbox and right click on the icon. You see now the properties of this element. You can set the default value of the checkbox. Choose ‘Add Help Text’ and specify your condition on this element.

Extra information about condition: &lt?CHECKBOX_IND=1?&gt
This consists of two element. First element, CHECKBOX_IND, is xml-tag in your xml-file. Second value is the comparising value that has to be met when returning true. If everything is done, click in both dialog boxes on ‘OK’.

At this moment, we have to prepare BI Publisher to understand our checkbox functionality. We have to add a new font. Log in to BI Publisher and go to ‘Admin’ tab.

Choose ‘Font Mappings’ by ‘Runtime Configuration’. Then ‘Add Font Mapping’.

Properties for this font:

Note: If ‘Arial Unicode MS’ –font is not available on the server. You have first to upload this into directory ../jdk/jre/lib/fonts. Restart your OC4J! Now you can select your font-type.

Last step before we upload our template is configure our reports to use this font. Go back to Admin tab and choose ‘Runtime Configuration’ -> Properties. Edit following setting under RTF-template.

Characters used for checkbox: Arial Unicode MS;9746;9744.

Note: Value 9746 stands for checked checkbox and value 9744 stands for unchecked checkbox.

All configuration is done, the final step is to upload our template into BI Publisher and run our report. You see that your checkbox is checked or unchecked dependent on your value of xml-tag ‘CHECKBOX_IND’.

If you use the xml-tag &lt?CHECKBOX_IND=1?&gt in your template, it would return the value true or false. This can also be used if needed.

Practical examples:


Data templates in BIP

There is so much to talk about but what I haven’t discuss right now is extra functionality in Data Templates. Features that you can use to allow more complexity into your templates.

One of the advantages of data templates is the use of Report Triggers. These triggers will handle a specific sequence. So, the sequence of execution seems to be:

  • Before Report Trigger
  • Data Query (SQL statement)
  • After Report Trigger
  • Output Post Processor

Another thing, maybe not so important for all of you, is that you specify extra options into your data templates to debug better your report or to get more information about a report. These elements are called properties of a data template.

A data template consists of following elements:

&ltdataTemplate name="&ltname&gt" description="&ltdesc&gt"
   dataSourceRef="&ltdatasource&gt" Version="1.0"&gt
&ltproperty name="showControls" value="false"/&gt
&ltproperty name="online" value="true"/&gt
&ltproperty name="parameterColumns" value="3"/&gt
&ltproperty name="openLinkInNewWindow" value="true"/&gt
&ltsqlStatement name="Q1"&gt
&lt![CDATA[select ID from dual]]&gt
&ltgroup name="ROW" source="Q1"&gt
&ltelement name="ID" value="ID"/&gt

The default properties are present in bold. You find a list below that you can use to extend your report properties.

include_parameters – indicates whether to include parameters in the output.
Valid values:

  • True (default)
  • False

include_null_Element – indicates whether to remove or keep the null elements in the output.
Valid values:

  • True (default)
  • False

xml_tag_case – allows you to set the case for the output XML element names.
Valid values are:

  • Upper (default)
  • Lower
  • As_are (The case will follow the definition in the dataStructure section)

db_fetch_size – sets the number of rows fetched at a time through the JDBC connection. The default value is 500.

scalable_mode – sets the data engine to execute in scalable mode. This is required when processing a large volume of data.
Valid values:

  • On
  • Off (default)

include_rowsettag – allows you to include or exclude the Rowset Tag from the output.
Valid values:

  • True (default)
  • False

debug_mode – turns debug mode on or off.
Valid values:

  • On
  • Off (default)

Example of Use:
&ltproperty name=” include_parameters ” value=” false “/&gt

RPE-02062: Itemtype cannot be dropped as it has a running process.

When deploying a process flow using the control center of OWB (11r1), following error is raised:

Error: RPE-02062: ItemType PKG cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor.
INFORMATIONALRPE-02071: Deployment has been aborted due to a previously reported critial error.

I used following steps to solve the issue:

Step1: Find the running processes:

SQL*Plus: Release – Production on Tue Jul 1 10:46:10 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter user-name: owf_mgr@DWHD
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

OWF_MGR> SELECT item_type, item_key, begin_date, end_date, activity_status
2 FROM wf_item_activity_statuses
3 WHERE activity_status’COMPLETE’
4 AND item_type = ‘PKG';

ITEM_TYP···ITEM_KEY·································BEGIN_DAT··· END_DATE···ACTIVITY
PKG·········WB_IK_20080627_200542_108294···27-JUN-08····················· NOTIFIED


Step 2: Remove the active process

2 WF_ENGINE.ABORTPROCESS(‘PKG’, ‘WB_IK_20080627_200542_108294′);
3 END;
4 /

PL/SQL procedure successfully completed.

OWF_MGR> commit;

Commit complete.


Step 3 Redeploy the process flow

From the logs: