Common Header and Footer with Sub Template – Part 3

You have followed my last few posts but somehow it’s not working… Yep, that can be possible and probably the following two settings would be the cause for why it’s not working. Take a look and try that out! 

Set Proxy Server for HTTP Option

If the BI Publisher is running inside the firewall then you need to set your Proxy server to your JVM for the HTTP option to work. Here is an example of how to set that for your deployment with OC4J server.

Edit OC4J Startup File

1. Open the <Oracle_Home>\oc4j\bin\oc4j.cmd file.

2. Locate the following lines starting with ‘set JVMARGS…’:

set JVMARGS=-Djava.library.path=C:\oracle\bi\server\Bin;C:\oracle\bi\web\bin-DSAROOTDIR=C:\oracle\bi -DSADATADIR=C:\oracle\bidata -XX:MaxPermSize=128m -Xmx512m %OC4J_JVM_ARGS%

3. Insert the following string after the 'set JVMARGS=' part of the line:

-Dhttp.proxyHost=<proxy.mycompany.com> -Dhttp.proxyPort=80

4. For example, after inserting the above string, the new 'set JVMARGS=' line should look like the following:

set JVMARGS=-Dhttp.proxyHost=<proxy.mycompany.com> -Dhttp.proxyPort=80 -Djava.library.path=C:\oracle\bi\server\Bin;C:\oracle\bi\web\bin -DSAROOTDIR=C:\oracle\bi -DSADATADIR=C:\oracle\bidata -XX:MaxPermSize=128m -Xmx512m %OC4J_JVM_ARGS%

Restart the server

After editing the command line file you need to restart the OC4J server to apply the proxy server setting.

Enable External References for File Option

When you import the sub-template you need to set ‘Disable external reference’ option to be ‘False’ to allow the main RTF template to be able to reference the Sub-Template file.

1. Open the report and go to Configure page.

2. Change the ‘Disable external reference’ value to ‘False’.

Hope now all of your reports are working fine with your Sub-Template!

Common Header and Footer with Sub Template – Part 2

Construct Reference URI Dynamically

When you reference the Sub-Template from the main template you need to specify an absolute path to the Sub-Template file with either the http or file option in the Import syntax. This is easy when you are developing your reports at your local machine or the development instance. However, sometimes you don’t know where the Sub-Template files will end up being located. For example, your production environment probably will be a different server and have a different directory (or folder) structure. So you don’t want to hard-code the http or file path to the Sub-Template, which you don’t know where it will be at for your deployment. Otherwise, you need to go visit each single RTF template file to update the location path.

There is a limitation that you cannot get a value from the XML data and assign it to the Import syntax. However, you can assign values from BI Publisher’s pre-defined properties or custom properties that you can create both at a server and a report level.

Today, I will go through the steps to make your File or HTTP path dynamic so that the Sub-Template implementation can be independent from any instance and be easier to maintain.

How to Construct URI with Property

Here is the default ‘import’ syntax where you set the absolute path to the Sub-Template file as we saw yesterday.

<?import:http://knishida-lap.us.oracle.com:9704/bip/Header_Sub.rtf?>

Now, you don’t know where the Header_Sub.rtf file will be at a testing or production environment. Or you might need to change the above server location in future. So you want to make the above path dynamic.

This is where the custom property comes in to rescue. I will show you how to set the custom property later, but for now, let’s assume we have a custom property called, ‘HTTPSERVER’, and the value is ‘knishida-lap.us.oracle.com:9704/bip’.

You can use a dollar sign and surround the property name with curly brackets (e.g. ${HTTPSERVER}) and embed it into the path like below.

Example : <?import:http://${HTTPSERVER}/Header_Sub.rtf?>

When a value of the property ‘HTTPSERVER’ is set to ‘knishida-lap.us.oracle.com:9704/bip’ the above ‘import’ syntax will be dynamically constructed as

<?import:http://knishida-lap.us.oracle.com:9704/bip/Header_Sub.rtf?>

at the report generation time.

How to Set Custom Property

Now how to set the custom property?

Here is a list of the step on how to set the custom properties at the BI Publisher server level.

1. Create a custom configuration file, xdo.cfg, if you don’t have it yet, and add the following line.

<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/"> <!-- Properties --> <properties> <property name="xslt.HTTPSERVER">'knishida-lap.us.oracle.com:9704/bip'</property> </properties> </config>

2. Place the xdo.cfg under %BIP_REPOSITORY_HOME%/Admin/Configuration

If your BI Publisher Repository root is ‘C:\OracleBI\xmlp\XMLP’ the xdo.cfg file should be located at

C:\OracleBI\xmlp\XMLP\Admin\Configuration

3. Restart the Application Server (e.g. OC4J) to take the property setting in effect.

Additionally, you can also set this property at the report level. In this case you can locate the xdo.cfg file at the same folder where the BI Publisher report definition file (.xdo) is located.

That’s it, now you are set with the custom property so try testing it!

When you need to deploy your reports to another instance like Testing or Production then you need to make sure that the custom property is set at the server level. Once the property is set then all the reports using the property in the Sub-Template import syntax should work without any hassle!

Lastly, there are some pre-defined properties that you can use in the import syntax. This can be useful especially when you have a different set of Sub-Template files for each language and you want to import an appropriate template based on the user’s session language setting.

Pre-Defined BI Publisher Property List

Here is a list of the BI Publisher’s pre-defined properties.

• _XDOCALENDAR
• _XDOLOCALE
• _XDOTIMEZONE
• _XDODFOVERRIDE
• _XDOCURMASKS
• _XDONFSEPARATORS
• _XDOCHARTTYPE
• _XDOOUTPUTFORMAT
• _XDOSVGFONTEMBED
• _XDOCTX

All the locale related properties values are coming from the user’s preference setting at the BI Publisher Server.

That’s it for today. Happy Thanks Giving!

Common Header and Footer Style with Sub Template

When you develop a report you usually want to have a header and footer section. And when you develop more reports you probably copy and paste the header and footer section from the first report so that all the reports will have a same look and feel with a company logo, report title, reporting date, copy right, etc.

Now you have developed a lot of reports with the same header and footer and you need to change the report title font size or need to change the company logo in the header section, what would you do? Imagine if you have 100 reports already developed! Obviously you don’t want to visit every single report and make such changes in each of the RTF templates.

In order to avoid from visiting each of those hundreds of the reports you wish having just one common header and footer template, in which you can design the header and footer and you can import from all the reports you have. When you need to make such changes for the header and footer only you need to do is to visit only this common header and footer template and make the change. How easy!

This is a very common requirement for any reports development with BI Publisher and BI Publisher addresses this type of requirement with its Sub-Template feature. But somehow it can often be slipped out from the reports design until you hit this maintenance concern mentioned above. I guess that’s because our main focus is how to bring the data into the report and design the layout and formatting for the data when we start the development and we just want to get our report done without taking an account of the long term maintenance perspective.

Also this is not really well documented as a practical and best practice way. There are many information about this common template (called Sub Template in BI Publisher) out there but it’s not really clear as a complete picture of how to implement it and deploy to a real environment.

So here comes the post I’m writing today. I’m writing this using an example of the header and footer, but as you can imagine it doesn’t have to be only for the header and footer. Anything you want to have in a central place and apply to all the reports you can apply this information.

Sub-Template as Common Template

With BI Publisher you can use a Sub-Template feature to create the common template where you can design common objects such as the header and footer. The Sub-Template can be a RTF template or XSL style sheet, which can contain common formatting and layout information, and can be imported from any reports. So for example if you want to have a same header layout then you can design the header in the Sub-Template file and import the header, which you just designed in the Sub-Template, from all of your RTF template files. When you get a new requirement or modification request for the header then you can only modify this one header in the Sub-Template file and no extra work needed.

Common Template Development

Here is a list of the steps how you can implement the common Header and Footer Sub-Template.

Steps:

1. Create a Sub-Template for the Header and Footer
2. Locate the Sub-Template at a Web Server or file system that BI Publisher server can access to
3. Call the Sub-Template from the main templates
4. Call the header or footer section

 

Create a Sub-Template for the Header and Footer

You can create a new RTF template and use a ‘template’ syntax to design the header or footer layout and formatting.

Template Syntax:

<?template:%template_name%?>

There is a starting syntax (<?template….?> and ending sytax (<?end template?>), so you need to design the header or footer layout first then surround it with the starting template and the ending template syntax.

Example:

<?template:header?> Header contents should be here… <?end template?>

 

Locate the sub-template at a common directory (folder), which BI Publisher server can access to

Once you created the sub-template now you can reference it from the main RTF template files. It can be referenced by either File or HTTP option. The File option is to locate the sub-template file on the file system, which means the sub-template needs to be on the same machine where the main template files are. The HTTP option is for the sub-template to be accessed through HTTP so the sub-template needs to be on a web server and it should be accessible from other machines with HTTP.

Call Sub-Template

Import the sub-template from the main templates

First, you need to import the sub-template in the main RTF template. You can import either with File or HTTP option. This import statement needs to be set at the very beginning of the file.

Here is the example:

Example with File reference: <?import:file:/// C:\BIP\SubTemplate\common_template.rtf?> Example with HTTP reference: <?import:http://knishida-lap.us.oracle.com:9704/bip/common_template.rtf?>

Call the header or footer section

Once you imported, now you can use ‘call-template’ syntax to call and embed the header or footer layout and formatting at the header or footer section in the main template.

Example:

<?call-template:header?>

And, that’s it! Now you have a common header and footer template and can apply it to any report you have. Very simple.

I will discuss tomorrow about some best practice on how to deploy and tips. So stay tuned!

Dynamic Date Parameter – Current Date

This is actually really not known trick maybe it is not documented in the user’s guide yet, but BI Publisher supports a ‘dynamic date parameter’.

WHAT???

Ok, so don’t you want to set the default date for the date parameter of the report and make it to be a today’s date or a week before or something ? Or, don’t you want to schedule your report and want it to generate always the last 7 days of the data ?

Yes, here comes the dynamic date parameter!

For example, you can use a current date function (sysdate()) to set a today’s date for your date parameter so that the date will be set always to today’s date depending on when you open the report. So if you open the report today the date would be 11/18/09, if you open tomorrow then it would be 11/19/09.

This would be even more critical when you want to set the date parameter values for your scheduling report. For example, you might want to run a report on every Monday to display the data for the last 7 days. You can’t hard-code any date to the date parameter of the report because then the date range will get too old and invalid. In order to get the last 7 days of the data in SQL world, typically you set a start date to be ‘sysdate – 7’ and an end date to be ‘sysdate’. Yes, you can do the same thing with the BI Publisher’s scheduled report by doing exact the same but setting such parameter values from the scheduler edit window. BI Publisher generate the appropriate dates based on the dynamic date function and pass them to the underlying sql query.

BI Publisher supports not only the current date (sysdate) function but also other types of functions for commonly used dates.

• SYSDATE()   - Today’s date
• FIRST_DAY_OF_MONTH() - First day of the current month
• LAST_DAY_OF_MONTH()  - Last day of the current month
• FIRST_DAY_OF_YEAR()  - First day of the current year
• LAST_DAY_OF_YEAR()   - Last day of the current year

And here is some example…

Set Default Date to Current Date

In order to use the above date functions you need to enclose it with curly bracket and dollar sign such as ‘{$SYSDATE()$}. You can simply type this in the ‘Default Value’ text filed in the report edit window.

You can also have some simple calculation with the date functions. For example, if you want to set the default date to be 7 days before the current date, then you can type ‘{$SYSDATE()-7$}’. Plus (+) and minus (-) are supported for the calculation.

Set Dynamic Date for Scheduled Report

You can also set the date parameter values using the above functions. For example, if you want to schedule a report and want the report to generate the last 7 days of the data, then you can set the From Date to be ‘{$SYSDATE()-7$}’ and To Date to be ‘{$SYSDATE()$}’

Or, if you want to run the report to always return the month-to-date data then you can set the From Date to be ‘{FIRST_DAY_OF_MONTH()}’ and To Date to be ‘{$SYSDATE()$}’.

Just a useful trick if you haven’t known!

JNDI for BI Server – Part 2

This is a follow up for the post I’ve written some time back about the JNDI data source for the Oracle BI Server.

Recently I’ve had a chance to revisit this subject when one of our clients had a performance issue with their BI Publisher reports. The issue was that when they were trying to open a report it takes a long time to get the page with all the parameters. This issue became to be worse when they started testing with multiple users at a same time.

So first, we looked at the report and found it had many parameters which were using SQL queries, some were going against a Oracle database and some were going against BI Server with JDBC. This is a very typical report but can be a cause for the bad report performance.

Every time you run a BI Publisher report it creates a new connection for each query. So if you have 5 SQL base parameters, that means BI Publisher opens 5 connections separately without sharing the connection even when all or some of the queries are going against the same data source.

In order to address this issue we usually recommend to use JNDI data source. You can create a JDBC data source connection pool at the application server level and register it as JNDI, and you can use it from BI Publisher. This way your SQL queries in the reports and parameters can share the same connection through the JDBC connection pool.

Now, what if you need to pass the BI Publisher’s session username to the data source to get authorized or filter the returned data ? You can check the ‘Proxy Authentication’ check box to enable the proxy authentication, which pass the BI Publisher’s session user information to the data source.

However, this, proxy authentication with JNDI for Oracle BI Server, is not supported when the data source is Oracle BI Server with the BI JDBC.

That means, again, when you have 5 parameters, all of which are querying against a same BI Server, BI Publisher needs to open the JDBC connection 5 times! This obviously has significant performance impact especially when more users accessing the same or similar reports.

Well, the good news is, the BI Publisher product development has fixed this issue already by implementing a way that BI Publisher shares the connection for the parameter queries instead of opening and closing for each parameter. This means, you don’t need to use the JNDI connection for the BI Server to improve performance anymore. You can keep using the JDBC connection and set the ‘proxy authentication’ if needed. This fix is available in the latest BI Publisher’s October patchset release, which can be downloaded from our support web site. The patch number is 9017232. 

I would still recommend to use JNDI connection for other reasons as I described in the previous post, but to improve the performance of the parameter values rendering now we don’t need to worry about thanks to this new implementation!

Query is cancelled with BI EE, Why?

Have you ever had a situation where your BI Publisher report, which is using BI Answers as the data source, is returning no result with a message ‘The query was cancelled.’ after around 5 mins?

One of our top BIEE/BI Publisher consultant, Sandeep Bade, encountered this issue at one of his projects and found out why and the solution.

There is a BI Presentation property called, UnaccessedRunningTimeoutMinutes, which controls the time BI server allows the query to run when there is no user interaction. The default value is set to 5 mins and this is why the BI Publisher report querying against BI Server gets cancelled automatically when the query is taking more than 5 mins.

You can set this value to more than 5 mins and restart the BI Presentation Server, now your long running BI Publisher reports should return you the result!

You can add the following line in the instanceconfig.xml and change the value.

e.g.

<UnaccessedRunningTimeoutMinutes>60</UnaccessedRunningTimeoutMinutes>