Docufy: Document Generation for APEX made easy!

APEX is well known for his RAD solution and the no-extra cost factor: APEX makes it possible to build on time and within budget your database application for the web. But an application is more then building pages for CRUD operations. Very often you also need to generate well looking documents. And this is standard not possible with APEX. A pity!  Within the Oracle world, Bi Publisher is by far the best solution. But from the pricing point of view, it does not really fit in the ‘cheap’ philosophy of APEX. Further, you can also look for Open Source solutions like Jasper Reports and Birt. Both have a community version that you may use for free.

Nevertheless, we decided to build our own tool. During the presentation at APEX World of last week, organized by OgH, we explained how we came to this decision. On different projects at the Flemish Government we always had the business requirement to generate output for MS-Word (.docx). We first tried to solve this requirement with Jasper Reports. This was fine for simple letters. But the end-user also wanted to change some of those documents after generation … and that was not so easy to do with the output created by Jasper.

So the idea for an own “document generator for Oracle” was born. The main objective was trying to bring some of the better features of other tools together in one solution:

  • Separation of data retrieval and document layout.  The layout is template driven: your template is a MS Word document (.docx). This leaves the possibility open to involve business users in the design and build of the desired output.
  • Simple, easy and light: we want the solution to be as much as possible in the database, in PL/SQL, with no extra software on a middle-tier
  • Metadata driven and nice user interface to manage those data
  • Integration in your application via a PL/SQL API. This application may be developed in the technology of your choice. We use it initially in APEX projects, but it can be integrated in any tool that can do a PL/SQL call (Oracle Forms, any java solution, Formspider, …)

Our custom solution evolved well: we already used it successfully in different projects. Therefor, we decided to make it available for the world and packaged the solution in a tool with the name Docufy. You can find more information on the Docufy-site or you may always send a mail to info@docufy.be.

With Docufy,
we proceed on the APEX philosophy,
there where APEX it self stops:
easy and quick generation of operational documents.

BI Publisher caching results problem

If you are experiencing behavior that indicates that your report is being cached and you can’t refresh it then it could be that a reporting setting is indicating that it can cache your result set.

To check this setting go to “Edit Report” and look at the bottom in the list of options. You will find a check-box with the label “Enable document cache”. Unchecking this check-box will probably solve your problem.

By default, all Oracle Enterprise Repository reports uses Document Caching to reduce database roundtrips on the generation of report data. While the data is cached, the rendered report may not always show the most recent activities in Oracle Enterprise Repository. Setting the cache expiration changes depending on how frequently BI Publisher needs to refresh the data for the second and subsequent rendering of a report.

It is possible to change the default cache expiration (30 minutes) under Admin -> Server configuration -> Cache Section.

More information can be found in the BI Publisher.

BI Publisher showing TOPLINK-7001 error on Scheduled Reports

When you have the next error in BI Publisher Scheduled Reports:


Exception [TOPLINK-7001] oracle.toplink.exceptions.ValidationExceptionException Description: You must login to the ServerSession before acquiring ClientSessions.

You might want to check if the tablespace containing the BI Publisher schema has enough space. There is no procedure to physically remove scheduled reports once they have been run. You can manually delete records from the database (bipub schema) but don’t forget to reclaim the lob size. If you delete records containing LOB’s the size doesn’t automatically free up.

A way to do this manually by using the next command:
ALTER TABLE “table” MODIFY LOB (“column”) (SHRINK SPACE);
* command available from 10gR2

Delete history from the table ‘XMLP_SCHED_OUTPUT’ and then run the above command for all the lob columns: “XML_DATA”, “MESSAGE” ,”STATUS_DETAIL” and “DOCUMENT_DATA” .

Once you have done that, restart BI Publisher using Enterprise Manager and the error will be gone.

Tips & Tricks in BIP

I have worked with BIP for a few months and I discover some tips & tricks around developing reports. If you develop too fast or you are unattended to some stuff, you would see some of following errors. I will give you a list where you have to be aware of.

Tips & Tricks 1:

If you create a new datasource and you type a new SQL-query, ended with a semicolon.

For example: SELECT * FROM dual ;

You will get an error message:


When you click on ‘Error Detail’, it returns: ORA-00911: invalid character.

Solution for this problem is that you remove your semicolon at the end and render your report again.

Tips & Tricks 2:

If you create a new template name without uploading a RTF-template first. This means that there is no RTF-template available under ‘layouts’. After you created a template name, you will upload your RTF-template into BIP.

In the picture below, you can see that drop-down box for ‘template’ is empty.

Overview Report:


You go back to tab ‘Layouts’ and you upload your RTF-template. Without returning to template name, you run your report and you will get following error.

Error message:

Error detail: Attribute missing in : ‘url’

Solution, you have to go back to edit your report. Go to your template name and assign your RTF-template that you have uploaded into BIP.

The reason why he gives this error is simple. You create a new template name but you didn’t assign a RTF-template to it. Because this was uploaded after you create a new template name. BIP will not automatically assign new RTF-template to your template name. That’s why you see this error. You have to do it manually.

Unless, if you first upload your RTF-template and then create a new template name. Now, he will take first RTF-template in the list and assign it to your template name. You change it if you want.

Next two tips & tricks is dependent on which version of BIP you’re working on. It seems that the problems in 10.1.3.3 were solved or better, they have a workaround to avoid these problems.

Tips & Tricks 3:

This first problem I met, is that you always need to select a ‘default data source’ if you create a LOV (List Of Values). So I will explain the situation.

When you create a parameter in BIP, it is also possible to attach an LOV to this item. The LOV can be used to choose values dependent on tables in your schema. So if you create a new LOV, you have to choose explicitly a connection data source like ‘HR’ or something else you defined by JDBC Connection in the Admin tab.

You may not select ‘Default Data Source’ as connection because this will render following error:

Error detail: Parameter name: p_dual Can not establish database connection(null)

The reason why you would see this message is because the LOV will be build before there was a default data source connection. So you have to define explicitly your data source connection in 10.1.3.2.

I noticed that the problem is fixed in 10.1.3.3 and you don’t have to select explicitly your connection but you can select it as ‘Default Data Source’.

Tips & Tricks 4:

This topic will handle about more data sources concatenated. If you create more than one data source and you want them concatenated with unique row names.

A short explanation of what I have done. I created two data sources:

  • Name of 1ste datasource = DS_1
  • Name of 2nd datasource = DS 2

Note: In second data source is no ‘underscore’ presented but a space!

I changed the option to ‘Concatenated SQL Data Source’ as Main Data Set on the Data Model Tab. And checked ‘Make row names unique’.

When you run the report, You will get an error message like this:

Error detail: ‘=’ missing in attribute.

The problem is that it is not allowed to use a space in the data source name. So you can change this one or you can obtain to uncheck ‘Make row names unique’. I always choose for the first solution. Also to avoid problems later by the creation of my RTF-template.

Note: If you use a font that is not recognized on the server (where BIP is installed), BIP will use the default font type of Helvetica.

Cross Tab in BIP

The columns of a cross-tab report are data dependent. At design-time you do not know how many columns will be reported, or what the appropriate column headings will be. Moreover, if the columns should break onto a second page, you need to be able to define the row label columns to repeat onto subsequent pages.

You can use the built-in function of Oracle BI Word Plugin to create an Cross Tab. If you want more or less functionality, you have to edit your existing cross tab. So you have to know what every tag means or how to realize.. The following example shows how to design a simple cross-tab report.

This example uses the following XML sample:


&ltROWSET&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt
&ltYEAR&gt2008&lt/YEAR&gt
&ltQUARTER&gtQ1&lt/QUARTER&gt
&ltSALES&gt3000&lt/SALES&gt
&lt/RESULTS&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt
&ltYEAR&gt2008&lt/YEAR&gt
&ltQUARTER&gtQ2&lt/QUARTER&gt
&ltSALES&gt2400&lt/SALES&gt
&lt/RESULTS&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt
&ltYEAR&gt2007&lt/YEAR&gt
&ltQUARTER&gtQ1&lt/QUARTER&gt
&ltSALES&gt1200&lt/SALES&gt
&lt/RESULTS&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt
&ltYEAR&gt2007&lt/YEAR&gt
&ltQUARTER&gtQ2&lt/QUARTER&gt
&ltSALES&gt3400&lt/SALES&gt
&lt/RESULTS&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtAdministration&lt/DEPARTMENT&gt
&ltYEAR&gt2006&lt/YEAR&gt

&lt/RESULTS&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtHuman Resources&lt/DEPARTMENT&gt

&lt/RESULTS&gt
&ltRESULTS&gt
&ltDEPARTMENT&gtIT&lt/DEPARTMENT&gt

&lt/RESULTS&gt
&lt/ROWSET&gt

From this XML we will generate a report that shows each department and totals the sales by year as shown in the following figure:


The template to generate this report is shown in the following figure. The form field entries are shown in the subsequent table.


The form fields in the template have the following values:


Note: only the first row uses the @column context to determine the number of columns for the table. All remaining rows need to use the @cell context to create the table cells for the column.

Additional Information:

Defining Columns to Repeat Across Pages:
If your table columns expand horizontally across more than one page, you can define how many row heading columns you want to repeat on every page. Use the following syntax to specify the number of columns to repeat:

&lt?horizontal-break-table:number?&gt

where number is the number of columns (starting from the left) to repeat.

Note that this functionality is supported for PDF output only..

Regrouping the XML Data:
The RTF template supports the XSL 2.0 for-each-group standard that allows you to regroup XML data into hierarchies that are not present in the original data. With this feature, your template does not have to follow the hierarchy of the source XML file. You are therefore no longer limited by the structure of your data source.

Using the Context Commands:
To support this requirement, BI Publisher provides a set of context commands that allow you to define the context (or placement) of the processing instructions. For example, using context commands, you can:

  • Specify an if statement in a table to refer to a cell, a row, a column or the whole table.
  • Specify a for-each loop to repeat either the current data or the complete section (to create new headers and footers and restart the page numbering).
  • Define a variable in the current loop or at the beginning of the document.

BI Publisher supports the following context types:

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.