Sum Rows -RTF Template

In order to group same data and sum the values in single row can be done in below mentioned way.
lets assume we have xml data as follows

<DATA_DS>
<PRODUCT_SALES>
<PRODUCT>Keyboards</PRODUCT>
<SALES>123</SALES>
</PRODUCT_SALES>
<PRODUCT_SALES>
<PRODUCT>Mother Boards</PRODUCT>
<SALES>23</SALES>
</PRODUCT_SALES>
<PRODUCT_SALES>
<PRODUCT>Keyboards</PRODUCT>
<SALES>230</SALES>
</PRODUCT_SALES>
<PRODUCT_SALES>
<PRODUCT>Mother Boards</PRODUCT>
<SALES>13</SALES>
</PRODUCT_SALES>
<PRODUCT_SALES>
<PRODUCT>Mother Boards</PRODUCT>
<SALES>33</SALES>
</PRODUCT_SALES>
</DATA_DS>

Normal table output looks like below


If we want see data as below



create a rtf template




Codes for each form field used in above table are

F                         <?for-each-group:DATA_DS/PRODUCT_SALES;PRODUCT?>
PRODUCT           <?PRODUCT?>
SALES                <?sum(current-group()//SALES)?>
E                         <?end for-each?>

Cross Tab/Pivot analysis -RTF Templates

Pivot table or Cross tab analysis can done in following way in the RTF templates

Assume we have xml data like below structure.

<ROWSET>
<FRUIT_SALES>
<FRUIT>Mangos</FRUIT>
<YEAR>2004</YEAR>
<SALES>123</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Mangos</FRUIT>
<YEAR>2005</YEAR>
<SALES>23</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Water Melons</FRUIT>
<YEAR>2004</YEAR>
<SALES>143</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Water Melons</FRUIT>
<YEAR>2005</YEAR>
<SALES>43</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Apples</FRUIT>
<YEAR>2004</YEAR>
<SALES>145</SALES>
</FRUIT_SALES>
<FRUIT_SALES>
<FRUIT>Apples</FRUIT>
<YEAR>2005</YEAR>
<SALES>45</SALES>
</FRUIT_SALES>
</ROWSET>

From this XML we will generate a report that shows each Fruit and total 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:

Text to Display	Form Field Help Text	Description
FRUIT HEADER	<?horizontal-break-table:1?>	Defines the first column as a header that should repeat if the table breaks across pages.
for1	?for-each-group@column:FRUIT_SALES;YEAR?	Uses the regrouping syntax to group the data by YEAR; and the @column context command to create a table column for each group (YEAR).
YEAR	<?YEAR?>	Placeholder for the YEAR element.
end	<?end for-each-group?>	Closes the for-each-group loop
for2	<?for-each-group:FRUIT_SALES;FRUIT?>	Begins the group to create a table row for each FRUIT
FRUIT	<?FRUIT?>	Placeholder for the FRUIT element.
for3	?for-each-group@cell:current-group();YEAR?	Uses the regrouping syntax to group the data by YEAR; and the @cell context command to create a table cell for each group (YEAR).
sum(SALES)	<?sum(current-group()//SALES)?>	Sums the sales for the current group (YEAR).
end	<?end for-each-group?>	Closes the for-each-group loop
end	<?end for-each-group?>	Closes the for-each-group loop

Note that 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.

Format Currency-RTF

We can use following syntax to display the amount columns in different currencies

<?format-currency:amount_xml_tag;'currency_code';'true or false'?>

amount_xml_tag= amount column that available in data model
currency_code= currency code to display the symbol for amount
 If user want to see the currency symbol in the out put ,just set 'true' else 'false' for last argument in the above syntax

examples

<?format-currency:ACTUAL_AMT;'INR';'true'?>
<?format-currency:ACTUAL_AMT;'USD';'true'?>
<?format-currency:ACTUAL_AMT;'GBP';'true'?>

If else xdoxslt function syntax -RTF

The normal behavior of if-else condition looks like

If <contion> then <value> --for true condition
else <value>
end

in RTF it can be achieved using following syntax

<?xdoxslt:ifelse(condition,true,false)?>

Example

<?xdoxslt:ifelse(BUDGET!=0,xdoxslt:div(ACTUAL,BUDGET),'-')?>

 

Oracle BI Publisher:Recommended Configuration

 JVM settings & JDK version
–64 bit JVM/JDK (on a 64 bit OS)
–JDK version 1.6 (update 2) or higher
Memory (RAM for the JVM)
–8 GB on 64 bit JVM is recommended for large, high volume use
–2 GB on 32 bit OS suitable for small to mid volume deployments (2gb limitation for JDK on win OS)
Storage
–Repository: Varies. 30 GB Hard disk space (must be shared for cluster)
–Temp Space: 20 GB (for document processing) not shared

Turn ON Logging for BI Publisher for debugging code

By default in BI Publisher detailed log will not be enabled. So it becomes tough to identify the problems whether in the XML data file/Template file /Application/Code or App engine.

So we cannot come to know where exactly error occurring. That’s the reason we have turn on logging for BI Publisher for Debugging reports for debugging. Have to create xdodebug.cfg file and which will give us XDO logging information that are related XML, XSL, Translation (XLIFF) and Template files such as PDF, RTF, XSL in the temporary directory specified in LogDir parameter.

Steps to Turn ON Logging for BI Publisher for Reports debugging

1.Create a folder xdolog and place it under

<Middleware_Home>/user_projects/domains/bifoundation_domain/config/bipublisher

This has to be used in LogDir parameter in step 2.

Note: The above mentioned path is optional .You can create it anywhere

2.Create a file named xdodebug.cfg and place it under

<Middleware_Home>/Oracle_BI1/jdk/jre/lib/

3.Add below listed lines in xdodebug.cfg file

Under Windows

LogLevel=STATEMENT
LogDir=c:\<Middleware_Home>\user_projects\domains\bifoundation_domain\config\bipublisher\xdolog

Under Unix:

LogLevel=STATEMENT
LogDir=<Middleware_Home>/user_projects/domains/bifoundation_domain/config/bipublisher/xdolog

4.Retest the issue

5.Check the files created in the specified LogDir (xdolog) directory.Here you can see XDO.log

6.Remove xdodebug.cfg when finished replicating the issue.

The logging is specifically useful for troubleshooting the Template(RTF/PDF) or Data File(XML file) specific issues. The generated XML file is the actual data file which is run from the MS Word Design Helper Preview mode to narrow down the issue.

Showing BI Publisher Report along with its Parameters in OBIEE Dashboard

Normally BI Publisher report Prompts will not be displayed in dashboard when we pull BI Publisher report into Dashboard section. It only displays the Report document without any prompts as shown in below.



In order to show these prompts we have 2 ways

1.Using BIP Report URL

2.Drag and drop BIP report (some configuration need to be done to make this work)

1. Using BIP Report URL

We can use Dashboard link object or else Iframe html code to invoke  bi publisher report along with prompts in Dashboards

Example IFrame code to call BI Publisher Report

<html>

<head>

<title>Call BIP Report IFrame </title>

</head>

<body>
<iframe src="BIP_Url" height=500px width=600px></iframe>
</body>

</html>

BIP_Url  can be taken from BI Publisher Report ->Show Report url with No Header option.

Place that code in Dashboard Text object and check for Contains HTML Markup option

2. Drag and drop BI Publisher Report in Dashboard section

We have to do some configuration changes in instanceconfig.xml to make this work.

Navigate to below location to find instanceconfig.xml file <Middleware_Home>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1

modify instanceconfig.xml of BI Presentation Services and add the ReportingToolbarMode element in the AdvancedReporting section. For example:

<ServerInstance>
<AdvancedReporting>
<ReportingToolbarMode>6</ReportingToolbarMode>
</AdvancedReporting>
</ServerInstance>

Note:

The following list describes the ReportingToolbarMode element values:

1 = Does not display the toolbar.

2 = Displays the URL to the report without the logo, toolbar, tabs, or navigation path.

3 = Displays the URL to the report without the header or any parameter selections. Controls such as Template Selection, View, Export, and Send are still available.

4 = Displays the URL to the report only. No other page information or options are displayed.

6 = Displays the BI Publisher toolbar to display the parameter prompts of the BI Publisher report



Restart OPMN services to reflect this change.

Now login OBIEE and add Bi Publisher report to a section. Now we can observe BI Publisher Report along with its prompts as shown in below.

Passing Multiple Values to BI Publisher Report via single Parameter

1.     Problem Description

In general BI Publisher supports multiple values to be passed as filters to the Report .But where in the case if the number of values passed from a parameter reaches more than 1000 then sql throws exception saying   ORA-01795: maximum number of expressions in a list is 1000 .

2.What happens in background

To make reports work with Multiple Select Prompts/Parameters, we need to handle that logic inside the sql dataset. Usually  this functionality can be achieved using IN operator in WHERE clause .

Example:

SELECT * FROM W_INVENTORY_PRODUCT_D

WHERE PRODUCT_NUM IN (:P_NUM)

Above query works well until P_NUM parameter holds less than 1000 literals. Whenever it reaches more than 1000 then sql throws exception like as mentioned above. Because IN operator won’t allow more than 1000 literals or hard coded values.

Let say we passed two values(Part_ABC,Part_XYZ) from prompt,then in side BI Publisher query will be generated as below.

SELECT * FROM W_INVENTORY_PRODUCT_D

WHERE PRODUCT_NUM IN (:P_NUM4118,:P_NUM4119,'X')

Bind Variables ...

     1: P_NUM4118:Part_ABC

Bind Variables ...

     2: P_NUM4119:Part_XYZ

So for each selected  value XDO engine creating Bind Variable and assigning appropriate value at run time .Hence if we select more than 1000 values from the prompt then XDO engine will create more than 1000 Bind variables where it will cause IN operator exception.

3.Resolution

To make it work for even more than 1000 values, need to alter sql dataset code. This change  is very small and of course worth.

Parameter Definition:

Create a Parameter :P_NUM

and enable

Multiple Selection

Can Select All (Null Value Passed)  options

Dataset definition:

SELECT * FROM

(SELECT PRODUCT_NUM FROM W_INVENTORY_PRODUCT_D)

WHERE

PRODUCT_NUM IN (:P_NUM) OR LEAST(:P_NUM) IS NULL

Here LEAST() is a function that returns the least value . NULL will be returned if it has null as argument.

Example

LEAST(1,3,5) returns 1

LEAST (1,null,7) returns null

LEAST(null) returns null

As per the Parameter definition NULL will be passed if ALL value selected in the prompt.

So in this case when ALL selected ,condition  LEAST(:P_NUM) IS NULL  becomes true and acts like 1=1 ,so all rows can be fetched. So that we can avoid the sql error  maximum number  1000 reached by passing null (ALL).  

RTF Subtemplates

Sub templates are used whenever you like to show some section/body in the report conditionally and also we can reuse the content in several places like header and footer in many pages.

Below are the steps to work with Sub templates.

1.Create a Subtemplate

2.Upload the Subtemplate into BI Publisher server

3.Call the Subtemplate from the Maintemplate

4.Call the desired section from  Subtemplate  into Main template

Creating  a Subtemplate

create a rtf template with the Template syntax.

Template syntax

<?template:%templatename%?>

for example

<?template:Header?> Place the Header content here.. <?end template?>

save the template with a name (letsay subtemp)

Upload the Subtemplate into BI Publisher server

Log in to BI Publisher

Navigate New>Subtemplate

select the subtemp.rtf file and save as Header_Template (it will append .xsb as the file type).

Call the Subtemplate from the Maintemplate

After you upload the subtemplate into server, it can be referenced from another Main rtf template using the following syntax.

<?import:xdoxsl:%subtemplate_path%?>

for example ,to load the header defined in Header_Template.xsb place the below syntax any where in the Main rtf template

<?import:xdoxsl:///Sample/Header_Template.xsb?>

Assume subtemplate saved in Shared Folders/Sample directory.

Call the desired section from  Subtemplate  into Main template

to display the Header section in the Main report, Call the Header template from the Main rtf template from the place where you want to display(usually it should in Header region),Use the below syntax to call a template

<?call-template:%template_name%?>

for example : Place the below syntax in the Main rtf template Header region.

<?call-template:Header?>

Thats it,Now we have common Header and we can reference it from any report.

Bursting :BIP 11g

Bursting is the process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations.

A single bursting definition provides the instructions for splitting the report data, generating the document, and delivering the output to its specified destinations.

Before to proceed once review the scheduler configuration

Reviewing the Scheduler Configuration:

1.a. Log in to Oracle BI Publisher and go to the Administration page.

b. On the Administration page, in the System Maintenance section, click Scheduler Configuration to examine the database connection



2 .a. The Scheduler Configuration page appears. Examine the Database Connection area. It should show the JNDI connection by default.



b. Click Test Connection. A confirmation message appears if the database connection is successfully established.

3 .Click the Scheduler Diagnostics tab. Review the results. The Result area must show “passed” as indicated in the following screenshot.

Scheduling a Report to Burst to a File Location:

Here we are going to burst a rpoert into our local drive.

Prerequisite:You will need to create a folder under your local drive named BIP. This is the folder to which the reports will burst when you schedule the report to burst.

1 .a. From the Catalog page, navigate to My Folders and select New > Data Model.

b. Create a Dataset Bursting_DS.



2 .a. Click the Bursting node in the Data Model pane. This will open the Bursting pane.



b. Click the add icon in the Bursting pane. The Bursting pane expands and provides an additional definition area.



c. Enter the following information in the Bursting panes:



3.a. In the SQL Query pane below the Query Builder button, copy and paste this code:

select
d.department_name KEY,
'SimpleRTF' TEMPLATE,
'RTF' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
'FILE' DEL_CHANNEL,
'D:\BIP' PARAMETER1,
d.department_name || '.pdf' PARAMETER2
from
departments d

b. Your output will be delivered to the folder specified in the bursting model. In this example, it is BIP. Notice that the code includes the name of the template (SimpleRTF). The Data Model should look like this:



Here is a brief description of the bursting definition used in the example:

Bursting definition is a component of the Data Model. After you have defined the data sets for the Data Model, you can set up one or more bursting definitions. When you set up a bursting definition, you define the following:

• The Split By element: It is an element from the data that will govern how the data is split. For example, to split a batch of departments by each invoice, you may use an element called department_name. The data set must be sorted or grouped by this element.


• The Deliver By element: It is the element from the data that will govern how formatting and delivery options are applied. In this example, it is likely that each department will have delivery criteria determined by customer, therefore the Deliver By element may also be department_name.


• The Delivery Query is a SQL query that you define for Oracle BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details. It will also define the path to which the data will be delivered. In this example it is D:\BIP.

c. Click Save to save the Data Model.

4.a. Create Report for this data model and open the Report Properties .



b. In the General tab in the Advanced area, select the Enable Bursting check box and ensure that “Burst to File” is selected from the drop-down list.

c. Click OK.

d. Click Save in the report header.

5.Create a Report Job  by

Click New > Report Job to schedule this report with bursting as the output option.

and select this report path in the General tab



6.On the Output tab, select the “Use Bursting Definition to Determine Output & Delivery Destination” check box to enable bursting. Observe that the other options for output will be hidden when this check box is selected.



7 .On the Schedule tab, select Frequency to report as Once and the Run Now option.

8.If the Notification tab is disabled in your instance, you don't have to define anything here.

Note: You will be able to use this option only if a delivery channel is set up in your environment. Without a mail server set up, this option will be disabled. In this example, it is shown as user@localhost.com. You can enter the email address configured to your mail server.

9. Name the scheduling job as Bursting2File in the Submit Job dialog box, and then click Submit.

10.a. From the Catalog, select the Salary Report for Bursting report. Click the Job History link.

The Report Job History for the recent schedule job Bursting2Fileis listed as successful.

You can click Report Job to view the details.



11 .Navigate to the BIP folder and review the contents displaying the burst reports at the specified location. In this example, it is the BIP folder that you have created as part of the prerequisite.



The steps above demonstrated the scheduling of a report to burst to a file location.

Additional Info:

To burst the report to email ,give the bursting definition in the following format in the 3^rd step(above shown).

select

     d.department_name KEY,

     'SimpleRTF' TEMPLATE,

     'RTF' TEMPLATE_FORMAT,

     'en-US' LOCALE,

     'PDF' OUTPUT_FORMAT,

     'EMAIL' DEL_CHANNEL,

     d.department_name||' Department Salary Report' OUTPUT_NAME,

     'to@mycompany.com' PARAMETER1,

     'cc@mycompany.com' PARAMETER2,

     'bipublisher@oracle.com' PARAMETER3,

     'Publisher Bursting Dept: '||d.department_name  PARAMETER4,

     'BODY: Bursting Sample for Department: '||d.department_name  PARAMETER5,

     'true' PARAMETER6,

     'reply_to@mycompany.com' PARAMETER7

from

     departments d





And follow the remaining steps as mentioned above to burst the report to email.

Displaying Measures as rows in a table: BIP RTF Template

Usually we will display the measures as the columns in a table. We can also modify this display structure in output to display these measure columns as rows by putting little in the form field codes. Assuming below xml

- <DATA_DS>

-     <POPULATION>

               <COUNTRY>India</COUNTRY>

               <YEAR>2001</YEAR>

               <MALE>60</MALE>

               <FEMALE>40</FEMALE>

  </POPULATION>

-     <POPULATION>

               <COUNTRY>Japan</COUNTRY>

<YEAR>2001</YEAR>

               <MALE>50</MALE>

               <FEMALE>50</FEMALE>

  </POPULATION>

-     <POPULATION>

               <COUNTRY>India</COUNTRY>

               <YEAR>2011</YEAR>

               <MALE>70</MALE>

               <FEMALE>30</FEMALE>

  </POPULATION>

-     <POPULATION>

               <COUNTRY>Japan</COUNTRY>

               <YEAR>2011</YEAR>

               <MALE>60</MALE>

               <FEMALE>40</FEMALE>

  </POPULATION>

</DATA_DS>

We can build the table using table wizard as

code the form fields as

@cell (1,3) write the code as

for         <?for-each-group@column:POPULATION;YEAR?>

field       <?YEAR?>

end        <?end for-each-group?>

@cell (2,1) write

COUNTRY <?for-each-group:POPULATION;./COUNTRY?><?variable@incontext:G1;current-group()?><?COUNTRY?>

@cell (2,3.1) write

i.e right side to Male

Males     <?for-each-group@cell://POPULATION;./YEAR?><?sum ($G1[(./YEAR=current()/YEAR)]/MALE)?><?end for-each-group?>

@cell (2,3.2) write

i.e right side to Female

Females <?for-each-group@cell://POPULATION;./YEAR?><?sum ($G1[(./YEAR=current()/YEAR)]/FEMALE)?><?end for-each-group?>

Working with Event Triggers in BIP 11g

We can call database function from the bip reports using the Event Triggers facility in the Data model.
Note: the function return type should be BOOLEAN.

1. Create a package (EVT_TRIGGER_PKG) in the database and define one function (SHOW_DATA) with return type as BOOLEAN.

--Package Specification
CREATE OR REPLACE
PACKAGE EVT_TRIGGER_PKG AS
PARAM_1 NUMBER;
PARAM_2 VARCHAR2(100);

FUNCTION SHOW_DATA(CAT_ID NUMBER,CAT_NAME VARCHAR2)RETURN BOOLEAN;

END EVT_TRIGGER_PKG;

--Package Body
CREATE OR REPLACE
PACKAGE BODY EVT_TRIGGER_PKG AS

PARAM_1 NUMBER;
PARAM_2 VARCHAR2(100);

FUNCTION SHOW_DATA(CAT_ID NUMBER,CAT_NAME VARCHAR2)RETURN BOOLEAN AS
BEGIN
INSERT INTO CATEGORY VALUES(CAT_ID,CAT_NAME);
COMMIT;
RETURN TRUE;
END SHOW_DATA;

END EVT_TRIGGER_PKG;

Here show_data() function inserting a row in the table whenever it triggered from BIP.

2.Now come to Publisher ,create one datamodel with SQL datasets ,let say
SELECT * FROM CATEGORY
And give the Oracle DB Default Package as EVT_TRIGGER_PKG,then this package will be displayed in the Event Triggers Section in the BIP.
3.Create 2 parameters (PARAM_1,PARAM_2) input type as text.These parameters must be exist in the default package as Global Variables
4.Create a BeforeDataTrigger by calling EVT_TRIGGER_PKG.SHOW_DATA(:PARAM_1,:PARAM_2)


5. Save the Datamodel and view the xml by giving the input parameters
6. In xml output you can see the input values which are stored in Category table through the function and fetched to BIP xml (report).Since it is a Before Data Trigger,
so here the Before Data trigger execution process is

Creating Excel Templates

An Excel template is a report layout that you design in Microsoft Excel for retrieving and formatting your reporting data in Excel.
Prerequisites
Following are prerequisites for designing Excel templates:
1.Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
2.To use some of the advanced features, the report designer will need knowledge of XSL and XSLT.
3.The report data model has been created.

Design the layout in Excel

First create a data model and export the xml to your local directory.
Let assume the xml structure like below

Open a new excel file and save it with (Excel 97-2003 .xls) extension.
• Create a new sheet in your Excel Workbook and name it "XDO_METADATA".
• Create the header section by entering the following variable names in column A, one per row, starting with row 1:
Version
ARU-dbdrv
Extractor Version
Template Code
Template Type
Preprocess XSLT File
Last Modified Date
Last Modified By
• Skip a row and enter "Data Constraints:" in column A of row 10.
• In the header region, for the variable "Template Type" enter the value: TYPE_EXCEL_TEMPLATE


Create one more sheet and insert a table as like below and enter the NAME BOX entry as XDO_?MALE? for the male count in the table, here MALE represents the xml tag in our data model.

Applying a Defined Name to a Cell
1. Click the cell in the Excel worksheet.
2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: C6.
3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?MALE?
4. Press Enter.


Similarly do it for female as well by giving XDO_?FEMALE?
For the Total cell use the native excel function to sum up the above two values by giving the formula as =SUM(C6:C7)
For calculating percentages also we can use excel native functionality.
Note:
BI Publisher defined names
To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:
Data elements: XDO_?element_name?
where
XDO_ is the required prefix and
?element_name? is either:
the XML tag name from your data delimited by "?"
a unique name that you will use to map a derived value to the cell
For example: XDO_?MALE?
Data groups: XDO_GROUP_?group_name?
where
XDO_GROUP_ is the required prefix and
?group_name? is the XML tag name for the parent element in your XML data delimited by "?".
a unique name that you will use to define a derived grouping logic
For example: XDO_GROUP_?DEMO?
Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

Creating Charts:
We can use the excel charts to embed graphs in the template, To do this just select the required cells which you want to show in chart and goto insert tab then click on any chart type ,here I am clicking PIE chart


Upload this template to BI Publisher and run the report then we will get the output like


Note:About the XDO_METADATA Sheet
Each Excel template requires a sheet within the template workbook called "XDO_METADATA". Use this sheet to identify your template to BI Publisher as an Excel template. This sheet is also used to specify calculations and processing instructions to perform on fields or groups in the template. BI Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.
It is recommended that you hide the XDO_METADATA sheet before you upload your completed template to the BI Publisher catalog. This will prevent report consumers from seeing it in the final report output.