Running Siebel Reports with Parameters

“I want to select or pick a value before running the reports and filter the reporting data for Siebel reporting.”

This is a very common requirement in reporting in general, which is to provide a parameter (or prompt) to the users, have them specify a value, and filter the returned data in the reports. With BI Publisher Server standalone UI, you can quickly create a parameter and run the reports. But how can we do with Siebel reporting ?

In Siebel you can select a particular row in the view window and run a report which will contain only the data related to the row. For example, in Account View, when you select a particular account and run the ‘Account List’ report it will show you only the selected account related information in the generated report. Also, Siebel reporting filters the data based on the user’s position, so depending on who run the report they will get a different set of the data.

These are all handled by Integration Objects, the reporting data extraction layer. But how about filtering the data even further ? With the above example, when I get a data related to a specific account I still want to filter the data so that I can see only the accounts who have purchased iPhone in the last quarter by passing a product name as a parameter value.

Siebel Reporting with BI Publisher had added this capability with 8.1.1.1, which is called ‘Parameterized Reporting’. Now you can provide a drop down box or free text form where the users can select or type a value and filter the reporting data result based on the parameter value.

There is only one limitation. You can’t make this parameter values dynamic. It’s common that you want to run a query to the database and retrieve the parameter values, like the way you do with BI Publisher Enterprise Server UI. But with Siebel this option is not available today. So basically you need to define the parameter values beforehand within the RTF template.

Anyway, today, one of our consultants, Shankar Duvvuri, who is our new BI Publisher expert especially in Siebel integration, has written an example scenario about how to setup and use the parameterized reports in Siebel and wants to share with you. So, Enjoy!

How to Create Parameterized Report

In this example, we want to create a report that shows revenue data and provide a drop down box parameter to the users so that they can select which quarter they want to see the revenue data for. So we are going to need a drop down box with values of ‘Q1’, ‘Q2’, ‘Q3’, and ‘Q4’ and have the users select one of the values before they run the report to filter the revenue data. So if a user selects a ‘Q1’ then only the revenue in Q1 will be displayed in the generated report.

 

Create Parameter in RTF Template

First, we need to create a parameter, which displays a pick-list of quarters (Q1,Q2,Q3,Q4) to the user, within the RTF template. Here is an example.

 

<?param@begin:Qrt; 'Q1', 'string', 'Q1,Q2,Q3,Q4'?>

You can either type this in the RTF template as text or use the text-form and type the above syntax inside the text-form.

In the above example, we are creating a parameter called Qrt with pick list values of: Q1,Q2,Q3 and Q4. The default value of the pick list is set to Q1. The parameters can be added anywhere in the template as either form field or free text. For more details about syntax and other types of parameters (like free text) refer to the Siebel Reports Guide.

If you want to make the parameter to be a free text form where the users can type the value instead of selecting from a list, then you can just ignore the parameter value section. So it would be something like below.

 <?param@begin:Qrt;'Q1';'string'?>

For free text, we are declaring Qrt as the parameter name with Q1 as the default. At run time this would display the parameter with name Qrt and default value Q1, which then can be changed to any value of users choice.

 

Use the Parameter to Filter Data in RTF Template

Once you have created the parameter as above, now you can use it to filter the data, to condition, or just to print the value. To reference the parameter in the report just prefix a $ before the name of the parameter.

In order to filter the data you can use the parameter inside the ‘for-each’ clause.

 

<?for-each:ListOf_ssRevenue[Quarter=$Qrt]?>

Having a default value in the template helps us simulate the testing with BI Publisher MS-Word Add-in. You can manually change the default value to test a different scenario for your condition or calculation.

Test the Report

Once the RTF template is ready, you can upload it to the Siebel Server using the Web Client.

If the report already exists then we need to delete the report first and then click on the Upload Files button. (Due to a defect we can’t override the existing template.)

Once the upload is done, run the report in the associated view. You will get a pop-up window (parameters applet) like below, which shows the newly added parameter.

Choose the one of the value and click ‘Submit’ button, you will get another window where you can select the report output type. Select the output type and submit, then you will get a report that only contains only the values that are related to the selected parameter value.

Enabling SSL for BI Publisher Standalone

How to enable SSL (Secure Sockets Layer) for my BI Publisher Server installed with OC4J standalone ?

SSL is a cryptographic protocols that provides a encrypted therefore secured communications over internet. This can be enabled for your BI Publisher Server to secure the communication between the server and the clients.

I’m sure many of you have installed BI Publisher as a part of Oracle BI EE suite or as just a BI Publisher Server standalone following the standard installation with the installer. The standard installation installs an embedded OC4J server and deploy BI Publisher Server on the OC4J server. In this deployment architecture you don’t have any HTTP server. OC4J Server directly interacts with clients http requests and returns response to the clients for the BI Publisher Server.

Today, I will talk about how to enable the SSL for such configuration, which is that BI Publisher Server standalone deployed on OC4J standalone. I will focus only on enabling SSL communication.

Here is a list of the brief steps that you need to perform.

• Create a Keystore
• Create and Update secure-web-site.xml
• Register the secure-web-site.xml in server.xml
• Test

Create a Keystore

A keystore stores certificates, including the certificates of all trusted parties, for use by a program. Through its keystore, an entity such as OC4J (for example) can authenticate other parties, as well as authenticate itself to other parties. In Java, a keystore is a java.security.KeyStore instance that you can create and manipulate using the keytool utility that is provided with the Sun Microsystems JDK. The underlying physical manifestation of this object is a file.

Here are the steps to create the keystore.

1. Change the directory to ORACLE_HOME/j2ee.

2. Create a keystore with an RSA private/public keypair using the keytool command. In this example, we generate a keystore to reside in a file named mykeystore, which has a password of 123456 and is valid for 365 days, using the RSA key pair generation algorithm with the following syntax:

% keytool -genkey -keyalg "RSA" -keystore mykeystore -storepass 123456 \ -validity 21

Note:

The keystore option sets the filename where the keys are stored.

The storepass option sets the password for protecting the keystore.

The validity option sets number of days the certificate is valid.

The keytool prompts you for more information, as follows:

What is your first and last name? [Unknown]: Kan Nishida What is the name of your organizational unit? [Unknown]: Consulting What is the name of your organization? [Unknown]: Oracle What is the name of your City or Locality? [Unknown]: New York What is the name of your State or Province? [Unknown]: NY What is the two-letter country code for this unit? [Unknown]: US Is <CN=Kan Nishida, OU=Consulting, O=Oracle, L=New York, ST=NY, C=US> correct? [no]: yes Enter key password for <mykey> (RETURN if same as keystore password): Always press RETURN for the key password. In the OC4J 10.1.3 implementation, the keystore password must be the same as the key entry password.

The mykeystore file is created in the current directory. The default alias of the key is mykey.

Create and Update secure-web-site.xml file

1. If you do not have a secure-web-site.xml file, copy the default-web-site.xml to ORACLE_HOME/j2ee/home/config/secure-web-site.xml (by convention).
   
2. Open the secure-web-site.xml and add secure="true" to the <web-site> element, as follows:
   
   

   <web-site port="4446" display-name="Default OracleAS Containers for J2EE Web Site" secure="true" > ... </web-site>

   
   
3. Change the <web-site> port setting to some available port. For example, port="4443". (To use the default of 443, you have to be a super user.)
   
4. Add the following under the <web-site> element to define the keystore and password.
   

   <ssl-config keystore="your_keystore" keystore-password="your_password" />

   
   Where your_keystore is the path to the keystore—either absolute, or relative to ORACLE_HOME/j2ee/home/config (where the Web site XML file is located)—and your_password is the keystore password. For example:
   

   <!-- Enable SSL --> <ssl-config keystore="../../keystore" keystore-password="123456"/>

Register secure-web-site.xml file in server.xml

Add the following line in server.xml:

<web-site path="./secure-web-site.xml" />

Save the changes and restart OC4J server to reflect the changes.

Access to BI Publisher Server for Testing

Now all the settings are done. Let’s test. You should be able to use ‘https’ to access to your BI Publisher.

https://hostname:port/xmlpserver where the port number is the one you set in the secure-web-site.xml file. e.g. https://knishida-lap.us.oracle3.com:4446/xmlpserver

Now OC4J listens for SSL requests on one port (in this example 4446) and non-SSL requests on another (the default is 9704). You can disable either SSL requests or non-SSL requests, by commenting out the appropriate *-web-site.xml pointer in the server.xml configuration file:

<web-site path="./secure-web-site.xml" /> - comment this to remove SSL <default-site path="./default-web-site.xml" /> - comment this to remove non-SSL

Conclusion

Well, it’s pretty simple right?. But of course if you want to really secure then you can do more in the configuration. Also if you have a http server involved, which is a typical architecture with Oracle Application Server, then it requires extra steps. You can take a look at OC4J Security Guide 10.1.3 for the detail. Also, if you need to integrate with Oracle BI EE, which is SSL enabled then there will be more steps, though they are documented in the Oracle BI EE Deployment Guide.

How to Migrate BI Publisher Reports to Production?

How can I migrate all the reports developed at a development instance to a testing or production instance with BI Publisher Server ?

This is very typical thing you as an administrator or reports developer would concern. The good news is, migrating BI Publisher reports from one instance to anther is pretty simple. All the reports are stored in a BI Publisher repository, which is typically on a file system or inside Oracle XML database. So, all you need to do is to copy the BI Publisher repository from the source system and paste it to the target system. Here is a diagram that shows the migration.

Of course you need to have BI Publisher server installed and up-and-running at the target system before the migration. Either if your repository is on the file system or XML DB, this process would be pretty much the same. I’ll talk about the process assuming that the repository is on the file system in this post.

What is BI Publisher Repository?

So what is really the BI Publisher repository. It stores the following types of contents and information.

• Reports
• Server Configuration
• Translation

BI Publisher Repository Screenshot

It not only stores the reports but also contains all the configuration related information. Let’s take a look at the detail for each. 

1. Reports

Under ‘Reports’ folder in the repository all the reports are organized in a folder based structure, which is in the same way that you see in the BI Publisher Enterprise server’s reports list UI page. Each report has its own folder, which contains one report definition file (.xdo) and report templates such as .rtf or .pdf.

1.1. Reports Definition

All the information for the BI Publisher reports are stored in the reports definition files which has a file extension as ‘.xdo’ It stores the information like sql query, report name, parameter name, list of values, template name, reports translation, bursting setting, report property, etc.

1.2. Reports Template

When you work on reports development most of the work is done within the report layout templates such as RTF Templates, PDF Templates, to design the reports layout and formatting. Once you complete the development you upload them to the BI Publisher Enterprise Server and register them as layout templates for each report. These uploaded report templates are also stored in the BI Publisher repository and resides along with the reports definition file (.xdo) in a same report folder.

2. Enterprise Server Configuration

All the BI Publisher server configuration related information is also stored in the BI Publisher repository. The configuration information includes the following.

• Server Configuration
• Security
• Scheduler
• Delivery
• Data Source

These information is stored under Admin folder within the repository.

3. Translation

There are two types of the translation. One is a translation for the BI Publisher Server product UI. BI Publisher Server supports 28 languages translations (includes English) for its UI. All the translated strings are stored in a set of XLIFF files and each language has its own XLIFF file to store the translation. These translations are located under Admin/Translation.

And another is a translation for the reports. If you are a French and open a report you want to see all the text are written in French. If you are a German you expect the same way. BI Publisher separates the translation from the report so you don’t need to create multiple reports for each language. Instead, you can just have one report and multiple translation XML files (XLIFF format), which contains only the translatable strings. These translations are located along with each report.

Now you get an idea of the BI Publisher Repository. Let’s start the migration.

 

Migration Process Overview

Before you start the migration, again, ensure the BI Publisher Server exists at a target instance! The migration process has two parts.

1. Migrate BI Publisher repository
2. Perform Post-Migration Configuration

1 Migrate BI Publisher Repository

First, we need to obtain the repository from the source instance. Then locate it to the target BI Publisher repository.

1.1 Obtain BI Publisher Repository from a Source Instance

The screen below shows the root directory of the BI Publisher repository

1. Identify the BI Publisher Repository at the source instance
   You can find this information in a BI Publisher’s system configuration file, which can be found at %J2EE_HOME%/applications/xmlpserver/xmlpserver/WEB-INF/xmlp-server-config.xml (e.g. C:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF)

<?xml version="1.0" encoding="UTF-8"?> <xmlpConfig xmlns="http://xmlns.oracle.com/oxp/xmlp"> <resource> <file path="C:\OracleBI\xmlp\XMLP"/> </resource> </xmlpConfig>

1. Go to the BI Publisher Repository folder
   e.g. C:\OracleBI\xmlp\XMLP
2. Copy all the folders except ‘DemoFiles’ under the above folder. (If you want to migrate a set of demo XML data files you can copy the ‘DemoFiles’ folder as well.
3. Save the folders onto your local machine or check them into your source control system

1.2 Locate BI Publisher Repository Content to a Target Instance

1. Login to the target instance machine
2. Go to the BI Publisher Repository directory.
3. Copy the source repository folders, which are the ones you have copied at the above steps, to this folder.

1.3 Validate the Repository Migration

1. Restart your target J2EE server, such as Oracle OC4J server.
2. Login to the Target BI Publisher Enterprise Server

Once you have logged in, you should see all the directories and reports migrated from the source instance under Reports tab in the new target BI Publisher Enterprise Server.

Post-Migration Task at Target Instance

After the BI Publisher Repository has been migrated successfully you want to perform a post-migration task to review a target specific information. Often that the development application instance doesn’t share the same information such as data source, delivery system, scheduling information, etc with the target instance.

For example, when you have a project where you develop financial reports out of a financial database, usually you would have one financial database for development, one for Testing, and one for Production. And you would expect that each of the BI Publisher Enterprise Server instance would talk to each financial database but not share the same database among multiple BI Publisher servers. This is just an example for a data source but it can be applied to other settings such as follows.

• Data Source
• Security
• Scheduler
• Delivery

When you have completed the migration process all the above settings are just the copies from the source system so you want to review the settings and update them appropriately so that they can be specific to a target system.

When you do this review and need to update any of the information, it is strongly recommended that you do it through the BI Publisher Enterprise Server Admin UI, not through the manual file modification. Just to make sure you don’t accidentally broken the system.