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.

SQL-query:

SELECT 1 as checkbox_ind FROM dual

XML-file:


&lt?xml version="1.0" encoding="UTF-8" ?&gt
&ltROWSET&gt
&ltROW&gt
&ltCHECKBOX_IND&gt1&lt/CHECKBOX_IND&gt
&lt/ROW&gt
&lt/ROWSET&gt

RTF-template:
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’.

Additional:
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
&ltproperties&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
&lt/properties&gt
&ltparameters&gt
&lt/parameters&gt
&ltdataQuery&gt
&ltsqlStatement name="Q1"&gt
&lt![CDATA[select ID from dual]]&gt
&lt/sqlStatement&gt
&lt/dataQuery&gt
&ltdataStructure&gt
&ltgroup name="ROW" source="Q1"&gt
&ltelement name="ID" value="ID"/&gt
&lt/group&gt
&lt/dataStructure&gt
&lt/dataTemplate&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

Oracle BI – Dashboard Prompts & BI Publisher

Situation:
One of the common questions that generally come up with regard to BI EE and BI Publisher is ‘how does one pass parameters to BI Publisher reports in Dashboards?’. One of the solution is to create a BI Publisher Report using HR-scheme as data source. Define a parameter for the BI Publisher report and then match this parameter name while assigning the presentation variable for the dashboard prompt.

Solution:
This method is generally the most used since most of the BI Publisher reports would have database as the data source.

We create a simple BI Publisher report with data coming in from HR-schema of the database.

Report-details:

  • Default Data Source: HR
  • Parameters per line: 1
  • Show Controls: unchecked

Overview BI Publisher Report:


First we will create a new data model, called DS_HR. SQL query will be:

Now add a parameter to the report. In our case, we will have ‘pDept’ as the parameter name.


Create a new RTF-template with following fields:


At least add a template to our report. Upload the template you’ve created before and create a new template name ‘TemplateHR’.


Save Report and test your BI Publisher report to be sure everything is OK.
Then go to the BI Answers and create a dashboard prompt on ‘department name’.

Note: The name of the presentation variable must exactly match that of BI Publisher parameter name.

We set default value to ‘IT’ and assign presentation variable to ‘pDept’.


Include this dashboard prompt and the BI Publisher report into the dashboards.


Final result in Oracle BI Dashboard:


When you checked ‘show controls’ in the BI Publisher report:


With this option enabled, you can choose different templates and output formats.


Column Aliases in BI Publisher


Problem:

If you create a dataset and build your SQL-query by Query Builder or type it manually, you have to watch out that you do not use column aliases like ‘FEED’.

See example below:

SQL-query:

select BI_TABLE.FEED as FEED,
      BI_TABLE.FEEDBACK as FEEDBACK,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:

Internet Explorer seems to perform a strict XML validation. He gives you following error:


Solution:

Replace all column aliases with names like ‘FEED’ and your problem is solved. See solution below:

SQL-query:

select BI_TABLE.FEED as COLUMN_01,
      BI_TABLE.FEEDBACK as COLUMN_02,
      BI_TABLE.APPLICATION_ID as APPLICATION_ID,
      BI_TABLE.NAME as TYPE_NAME,
      BI_TABLE.DESCRIPTION as TYPE_DESCRIPTION
from HR. BI_TABLE;

XML-file:


Note: You can use ‘FEED’ as name for database tables but remember, not for column aliases!!!

Oracle BI Publisher Date-type

You can only format dates when they are in the canonical date format. This format looks like: YYYY-MM-DDTHH24:MI:SS.

You have two options:
1. adapt the XML generation process to generate the date in the canonical format and use the format date functions of BI Publisher in the template.

2. adapt the XML generation process to generate the date in the format of the client (example: DD-MM-YYYY) and print this directly on your report.


Solution 1:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

Note: XML-Date is in the canonical format and can be imported directly in the template. Example below is based on a ‘sysdate’ and stored in a field called ‘FULL_DATE’.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Properties:

Load XML into MS Word (with Oracle BI Publisher Desktop). Import the field ‘FULL_DATE’ and edit the properties. We can choose different format-types in the drop-down box. Choose one for example: ‘d-MMM-yy’ or type your own format-type like ‘dd-mm-yyyy’.

Note: Field ‘FULL_DATE’ is a canonical format.

Results in Word:

Solution 2:

First, we create a new dataset. We give it a name and select SQL Query as Type. Then choose a ‘Data Source’ and check ‘Cache Result’.

We build a query by Query Builder in BI Publisher or type it manually. See below to our ‘Data Set’ -query.

DS_Query:

select BI_DATE.FULL_DATE as FULL_DATE,
   to_char(BI_DATE.FULL_DATE) as CHAR_DATE,
   to_char(BI_DATE.FULL_DATE,’dd-mm-yyyy’) as FORMAT_DATE,
   to_char(BI_DATE.DAY_DATE,’DD’) as DAY_DATE,
   to_char(BI_DATE.MONTH_DATE,’MM’) as MONTH_DATE,
   to_char(BI_DATE.YEAR_DATE,’YYYY’) as YEAR_DATE
from HRM.BI_DATE BI_DATE

After we create a dataset, we generate an XML to build our RTF-template.

XML-type:

&lt?xml version=”1.0″ encoding=”UTF-8″ ?&gt
&ltROWSET&gt
   &ltROW&gt
      &ltFULL_DATE&gt2008-02-19T13:24:37.000+01:00&lt/FULL_DATE&gt
      &ltCHAR_DATE&gt19-FEB-08&lt/CHAR_DATE&gt
      &ltFORMAT_DATE&gt19-02-2008&lt/FORMAT_DATE&gt
      &ltDAY_DATE&gt19&lt/DAY_DATE&gt
      &ltMONTH_DATE&gt02&lt/MONTH_DATE&gt
      &ltYEAR_DATE&gt2008&lt/YEAR_DATE&gt
   &lt/ROW&gt
&lt/ROWSET&gt

Load XML into MS Word (with Oracle BI Publisher Desktop). Because we have set our date types in the query, we just can import the available fields without editing the properties.

Results in Word:

Solution 3:

XML-Date is not a canonical date format. Second solution for this problem is similar to solution 2. Except that we don’t edit the SQL-query and generate XML.

Load XML into MS Word (with Oracle BI Publisher Desktop). Import necessary fields and edit properties.

Example field ‘Full Date’:

Choose ‘Word Properties’:

Then ‘Add Help Text…’

Type following statement by ‘Help Key (F1)’: &lt?format-date: FULL_DATE;’dd-MMM-yyyy’;’Europe/Brussels’?&gt

Note: ‘Europe/Brussels’ is the time-zone of your region.

And click ‘OK’ -> ‘OK’.

Disadvantage: you have to apply this solution to every field on the report. If you do it on query-level, you can import fields without editing them in the template.

Oracle Open World – Keynote Charles Phillips

This morning a Keynote was given by Charles Phillips, President of Oracle Corporation. In this keynote Charles Phillips gave us an insight on the vision of Oracle and the strategy in marketting this vision throughout it’s customers, partners and of course software offering.

Each Challenge was tackled by the different Oracle Offerings there are today.

CEO Challenge:
The CEO of a company needs to integrate different kinds of applications and once in the boardroom he needs to be able to point all noses in the same direction, the direction of ROI.

This Integration-challenge can be tackled by the new Application Integration Architecture offering of Oracle. Through a demo we saw how this challenge was tackled using Webcenter dashboard as a centralized view-point, JDeveloper as the integrated Development Environment and BPA which was used for the full round-tripping of business analysis and development.

Using this Oracle Technology offering, the AIA, the CEO can integrate his existing Siebel, SAP, Financials or CRM applications in a unified, uniform and more-over standardized way.

CFO Challenge:
The Chief Financial Officer has other concerns such as governance and risk management to keep in mind throughout the different processes in his/her company. How can Oracle offer a solution to this common problem, faced within every company that has a diversity of policies and rules to adhere to.

The Oracle Governance & Risk Competence Manager:
Through this manager the CFO has a one-stop view to control the different policies which are defined throughout the organisation. If a certain rule is being violated, the Manager will show up a red light and in 1 step the CFO can change/adapt the policy rules so the company’s back on track.
This manager uses a centralized content repository and enforces the policies at runtime using the Active Apps Policies Suite.


Another Security-stack being offerd by Oracle entails the Database Vault and Audit Vault to secure as well data as audit data in an Integrated Security Stack.


Engineering & Manufacturing Manager:
The engineering and manufacturing manager has a lot of challenges as well, such as keeping insight on the product stock and having the ability bto manage the entire product lifecycle without any delay.

Oracle offers ‘Oracle Agile PLM’ to tackle all of these requirements, the Oracle Product Lifecycle Management Technology Stack.


In the demo we saw that the manager had a centralized view on all the needed data using Webcenter dashboard and a Shared Content System.
The webcenter dashboard could be hooked up with third-party legacy applications through adapters and uses XML standards based messaging for this purpose.

Operations Challenge, Gaining Actionable Insight:
The operations challenge every administrator, manager is facing today: act when necessary!
If a glitch has occured during a business process lifecycle, a product is out of stock, an order hasn’t been processed correctly, if a given manager hasn’t updated his tasks-lists, in other words when a bottleneck is being introduced inside the company … we need to be able to act!



Through the usage of Enterprise Performance Management Workspace, BI Enterprise Edition and the Action Framework we can act upon these glitches immediatly.

Through the Enterprise Performance Management Workspace, we have a real-time view on the business processes in our company, and act when necessary.

The Action Framework gives us the possibility to interate transactions, historical data and tasks so were able to act correctly given we have all the needed data to provide with an accurate view on our current business.

Through the usage of Packaged BI Applications (Financial Package, Operations Package, CRM Package), the customer can purchase thed package which is measured for his/her company.

How can we manage these heterogenous environments?
Enterprise Manager Services Dashboard gives us the capability of monitoring and administrating the different services which are running inside our business.
The EMSD gives a follow-up on as well the business as the system metrics in our organisation.

Through this EMSD we can enable Real Application Clustering at runtime, without downtime.