You are here: BI Publisher Displaying items by tag: XML Publisher
Displaying items by tag: XML Publisher

This article is in continuation to our last article Adding Parameters and List of Values

Make a copy of OrderDM as OrderDetailDM  by Clicking the ‘Save As’  icon.

Add a new SQL Data set using following SQL query for the Order Items data.

 

Update the Group G1 => ORDERITEM from the structure tab. 

Navigate to structure tab and click the output section to view the XML Structure.  There is no relationship between ORDERS and ORDERITEM Groups in XML.  These are Multipart Unrelated Data Sets


In real time scenario, we often need a master detail or parent child relationship between two data sets.  This relationship defined through data link between two data sets, where one dataset defined as master/parent and other as details/child data set and this is referred as Multipart Related Data Sets.

There are two ways to define data link. Elements Level Link and Group Level Link.

Element Level Links: The element level link creates the join on columns of one query to column of other query. This is preferred option of defining the master details relationship.

In this example, Orders is master and OrderItems is detail data set. Both are linked with OrderID. There are multiple Orders items for a given Order. 

Select click the >> icon on ORDER_ID element of ORDERS Dataset. This will open the action windows. Click on Create Link action, This will popup the Create Link box, select the ORDER_ID1 element from the list and click OK to complete the operation

This will create data link on OrderID column between Orders and OrderItems. Hover the mouse on i icon to view the relationship.


Navigate to structure tab and click the output section to view the XML Structure. The OrderItem appears one level down the Orders



View the sample XML to verify the master detail relationship. There are multiple ORDERITM nodes for one ORDER node.

 

Creating Subgroups

This is another way to regroup the data at higher level or add the required hierarchy to flat data.

Look into sql query from Orders data set.  It lists out the orders for the Customers. There is 1.M relationship between Customers and Orders. 

Lets add the higher group on Customer level but before this we need to add proper order by clause to sort the data on CUSTOMER_ID AND ORDER_ID.

Select the ORDER data set from the Diagram pane and click the  icon at CUSTOMER_ID element level. Click the Group by action link form the action popup. This will add a new Group with CUSTOMER_ID element at higher level.


Click the >> icon at NAME element level, from the action menu, click on Move selected element to Parent Group.

This moves the Customer Name to Customer Group. Update the Tag name from the Structure tab to make it more readable.

 


Observe the new hierarchy. There three level.


==>CUSTOMERS

  ============> ORDER

 =====================> ORDERITEM

 

Generate the Sample XML and review the XML hierarchy.



Thats all for now. will few more features about the Data Model in next article.

Happy Reportng.

This article is part of BI Publisher for beginners series. You will see many more articles on Data Model design, Template desing, Administration, Web Services, Scheduler. The contents are picked-up from BI Publisher Training documents from  Adiva consulting. If you have any question regarding these articles or training program, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

Published in BI Publisher
Saturday, 28 July 2012 09:55

BI Publisher Training

Oracle BI Publisher 11g (11.1.1.6.0)

Oracle BI Publisher is an enterprise reporting solution for Production Reporting. It is available as part of OBIEE suite, Standalone Enterprise Release as well integrated with most of the Oracle Enterprise Application as Oracle Fusion Application, Oracle eBusiness Suite (EBS 11i, R12), People Soft, Siebel CRM , JD Edwards and many more. 

During this course, participant gets an Overview of the BI Publisher product, Set up, configuration Administration, Report Development and Report Scheduling.

Course Content

PART I: Oracle BI Publisher 11g Overview 

1.    Overview of BI Publisher Enterprise Edition
2.    Describing the Oracle BI Publisher Applications Architecture and components
3.    Overview of Different Report Component (Layout, Data, Translation)
4.    Desktop BI Publisher Desktop (Word Template Designer)
5.    Designing First Report using Word Template Builder

PART II: Data Model and Report Designer Editor

1.    Overview of Data Model , Data Model Editor
2.    Data Model Components.
3.    Working with different Data Source options.
4.    Designing  Simple and Master Detail Data Model 
5.    Working with Parameters, LOVs
6.    Working with  Event Triggers, Substitute variables, Dynamic SQL query source
7.    Understanding Data Model Properties.
8.    Understanding Groups, Functions, Links, Aggregation
9.    Understanding Bursting Definition.    
10.  Overview of Report Designer and Report creation process.
11.  Understanding different Layout types (RTF, PDF, EText, Excel, Interactive)
12.  Setting Run Time Properties
13.  Style Templates & Sub Templates
14.  Understanding Translation

PART III : Designing RTF  Template using Word Template Designer

1.    Create Simple Listing Report
2.    Working with Groups
3.    Working with Tables, Forms and Charts
4.    Implementing conditional logic to handle complex layout
5.    Formatting Numbers, Date and Currencies.
6.    Defining Headers and Footers
7.    Inserting Images and Charts
8.    Implementing XPATH Commands in RTF
9.    Working with Sub-Templates
10.  Advance Report Layout 

PART IV : Design Interactive Reports Using Layout Builder

1.    Overview of BI Publisher Layouts
2.    Designing Simple interactive Report
3.    Designing a Master Detail Report Using Charts

PART V : Scheduling the Reports

1.    Overview of BI Publisher Scheduler
2.    Creating Report Jobs
3.    Viewing and Managing Report Jobs
4.    Viewing and Managing Report History

PART VI :BI Publisher Administration

1.    Introduction to Oracle BI Publisher Administration
2.    Understanding the Security Model
3.    Setting Up Data Sources
4.    Setting Up Delivery Destinations
5.    Defining Run-Time Configurations
6.    Diagnostics and Performance Monitoring
7.    Moving Catalog Objects Between Environments
8.    Overview of Web logic Console and Enterprise Manager (EM) to Administrator  BI Publisher

Who can be benefited?

  • BI Consultants
  • Oracle Application Consultants [Technical & Functional]
  • Data Warehouse consultants
  • Application Developers

Prerequisites:

  • Basic understanding with SQL
  • Basic understanding with XML   
Published in Training

In this blog I am going to explain how to generate Table of Contents with links to their respective page in RTF template dynamically.

BI Publisher provides the ability to create dynamic section headings in your document from the XML data. You can then incorporate these into a Table of Contents.


To create dynamic headings:

Let us create sample XML data assuming we have different areas and within areas we have different plants.

<?xml version="1.0" encoding="UTF-8"?>

<dataroot>

<TOC>

<G_AREA>

<Area>Area1</Area>

<G_PLANT>

<Plant>Plant1</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant2</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant3</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant4</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant5</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant6</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant7</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant8</Plant>

</G_PLANT>

</G_AREA>

<G_AREA>

<Area>Area2</Area>

<G_PLANT>

<Plant>Plant1</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant2</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant3</Plant>

</G_PLANT>

<G_PLANT>

<Plant>Plant4</Plant>

</G_PLANT>

</G_AREA>

</TOC>

</dataroot>

 Step1:

Create a RTF template and for the heading in the body of the document, and format it as a "Heading", using your word processing application's style feature. You cannot use form fields for this functionality.

For example, you want your report to display a heading for each company reported. The XML data element tag name is <COMPANY_NAME>. In your template, enter <?COMPANY_NAME?> where you want the heading to appear. Now format the text as a Heading.

Now we need to update the styles for <?Area?>  with Heading1 and for <?Plant?>  with Heading2.

Step2:

Once styles are mentioned, create a table of contents using your word processing application's table of contents feature. Select the page where table of contents to be displayed and goto References tab then select table of contents(Automatic)

After Table of Contents is selected you can see the below content.

At runtime the TOC placeholders and heading text will be substituted.

Output:

"If you found this article useful, please rate the same"

If you need any assistance in BI Publisher implementation or RTF Template design, we can help you out. please send your query to  This e-mail address is being protected from spambots. You need JavaScript enabled to view it

Published in BI Publisher
Wednesday, 14 December 2011 06:24

Working with XPath in RTF Template

X Path

  •   XPath is a syntax used for selecting parts of an XML document
  •   The way XPath describes paths to elements  is similar to the way an operating system describes paths to files
  •   XPath is almost a small programming language; it has functions, tests, and expressions
  •   XPath is a W3C standard
  •   XPath is not itself written as XML, but is used heavily in XSLT


Terminology

<INSTITUTE>

    <DEPARTMENT>

          <SECTION>

          </SECTION>

           <SECTION>

                <STUDENT>

                     <ROLL_NO> </ROLL_NO>

                      <MARKS>

                              <SUBJECT1></SUBJECT1>

                              <SUBJECT2></SUBJECT2>

                              <SUBJECT3></SUBJECT3>

                       </MARKS>

                </STUDENT>

          </SECTION>

      </DEPARTMENT>

</INSTITUTE>

 

  •   INSTITUTE is the parent of DEPARTMENT; DEPARTMENT is the parent of the two SECTIONS
  •   The two SECTIONS are the children of DEPARTMENT, and the STUDENT is the child of the second SECTION
  •   The two SECTIONS of the DEPARTMENT are siblings (they have the same parent)
  •   INSTITUTE, DEPARTMENT, and the second SECTION are the ancestors of the STUDENT
  •   The two SECTIONS, the STUDENT, and the ROLL_NO are the descendents of the DEPARTMENT

 

Paths

Operating system:

XPath:

/  = the root directory

/INSTITUTE  = the root element (if named INSTITUTE )

/users/dave/foo = the file named foo in dave in users

/INSTITUTE/DEPARTMENT/SECTION/STUDENT  = every STUDENT element in a SECTION in every DEPARTMENT in the INSTITUTE

foo  = the file named foo in the current directory

STUDENT  = every STUDENT element that is a child of the current element

. = the current directory

.  = the current element

.. = the parent directory

 .. = parent of the current element

/users/dave/* = all the files in /users/dave

/INSTITUTE/DEPARTMENT/SECTION/*  = all the elements in /INSTITUTE/DEPARTMENT/SECTION

 

Slashes

  •   A path that begins with a  /  represents an absolute path, starting from the top of the document

  Example:  /email/message/header/from

  Note that even an absolute path can select more than one element

  A slash by itself means “the whole document”

  •   A path that does not begin with a  represents a path starting from the current element

  Example:  header/from

  •   A path that begins with  //  can start from anywhere in the document

  Example:  //header/from selects every element from that is a child of an element header

  This can be expensive, since it involves searching the entire document

Brackets and last()

  •   A number in brackets selects a particular matching child (counting starts from 1, except in Internet Explorer)

  Example:  /institute/department[1] selects the first department of the institute

  Example:  //student/roll_no[2] selects the second roll_no of every student in the XML document

  Example: //department/section[1]/student[2]

  Only matching elements are counted; for example, if a department has both sections and student, the latter are ignored when counting student

  •    The function last() in brackets selects the last matching child

  Example: /institute/department/section[last()]

  • You can even do simple arithmetic

  Example: /institute/department/section[last()-1]

 

Stars

  •   A star, or asterisk, is a “wild card”--it means “all the elements at this level”

  Example: /institute/department/section/* selects every child of every section of every department in the institute

  Example: //department/* selects every child of every department (sections, students, etc.)

  Example: /*/*/*/student selects every student that has exactly three ancestors

  Example: //*  selects every element in the entire document

Arithmetic expressions

           +                             add

           -                              subtract

           *                             multiply

          div                          (not /) divide

           mod                       modulo (remainder)

 

     Equality tests

  =                      “equals”          (Notice it’s not  ==)

!=                     “not equals”

But it’s not that simple!

             value = node-set  will be true if the node-set contains any node with a value that matches value

             value != node-set  will be true if the node-set contains any node with a value that does not match value

Hence,

             value = node-set  and  value != node-set  may both be true at the same time!

     Other boolean operators

and                  (infix operator)

or                     (infix operator)

              Example: count = 0 or count = 1

not()                 (function)

The following are used for numerical comparisons only:

<            “less than”                                  Some places may require &lt;

<=          “less than or equal to”             Some places may require &lt;=

 >            “greater than”                             Some places may require &gt;

         >=          “greater than or equal to”        Some places may require &gt;=

 

Some XPath functions

  XPath contains a number of functions on node sets, numbers, and strings; here are a few of them:

  count(elem) counts the number of selected elements

  Example: //section[count(student)=1] selects section with exactly two students children


  name() returns the name of the element

  Example: //*[name()='student'] is the same as //student


  starts-with(arg1, arg2) tests if arg1 starts with arg2

  Example: //*[starts-with(name(), 'stu']


  contains(arg1, arg2) tests if arg1 contains arg2

  Example: //*[contains(name(), 'tud']

 Example:

Here is the example of an institute which has one department and a few students in it.

<INSTITUTE>

<DEPARTMENT>

     <DEPARTMENT_NAME>COMPUTER SCIENCE ENGINEERING</DEPARTMENT_NAME>

          <SECTION>

                <STUDENT>

                     <ROLL_NO>1201</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>45</SUBJECT1>

                              <SUBJECT2>55</SUBJECT2>

                              <SUBJECT3>65</SUBJECT3>

                      </MARKS>

                </STUDENT>

                 <STUDENT>

                     <ROLL_NO>1205</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>56</SUBJECT1>

                              <SUBJECT2>48</SUBJECT2>

                              <SUBJECT3>58</SUBJECT3>

                      </MARKS>

                </STUDENT>

                <STUDENT>

                     <ROLL_NO>1257</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>51</SUBJECT1>

                              <SUBJECT2>49</SUBJECT2>

                              <SUBJECT3>53</SUBJECT3>

                      </MARKS>

                </STUDENT>

          </SECTION>

     </DEPARTMENT>

</INSTITUTE>

 

Now let’s display all the student details using a table with department name, roll number and marks.

1.JPG


Output:

 

2.JPG

 


Why is the department name missing?

When we say <?for-each:STUDENT?> the control is at <STUDENT> , (as you can see in the below diagram) but department name is not the child of student so we have to move the cursor to appropriate tag using xpath.

6.JPG

3.JPG


Output:


4.JPG


Here we used <?../../DEPARTMENT_NAME?> instead of <?DEPARTMENT_NAME?> so that the control jumps to two levels up in hierarchy and displays the DEPARTMENT NAME.

7.JPG


Now, let’s calculate totals marks secured by each student. To do so, add a column for Total and use <?sum(./MARKS/*)?> syntax to calculate TOTAL.

<?sum(./MARKS/*)?> syntax implies that we are using sum function and calculating all the children in marks

8.JPG

Output:

9.JPG

"If you found this article useful, please rate the same"

Published in BI Publisher

The objective of this post is to explain the “sorting” capabilities available in the RTF. Many might already be aware of the standard BI Publisher syntax for sorting in the RTF template:

<?sort:VENDOR_NAME?>

 

 

The above construct sorts the data by the VENDOR_NAME in ascending order – an alphabetic sort is performed. However, what if the requirement was to sort on a number field. For example, if you want to sort by the VENDOR_NUMBER, then the above will produce incorrect results as an aphabetic sort would be applied to a numeric field. The following BIP construct can be used to specifc the order and the sort type:

<?sort:VENDOR_NUMBER;'ascending';data-type='number'?>

 

The above BIP construct performs a numeric sort on the vendor_number. That's rather simple to do. Let's say we would like to sort on a field but the field name is not known at design time. The field name exists in the xml data. Let's say the element P_SORT_BY specifies the field on which sorting must be done. So, if the value of P_SORT_BY is VENDOR_NAME, sorting must be performed on the field VENDOR_NAME. This can be acheived by using the following XSL:

<xsl:sort select=".//node()[local-name()=P_SORT_BY]" order="ascending" data-type="number"/>

Let's try to decipher the above xsl syntax. The above XSL has three parts – select, order and data-type. While order and data-type are self explanatory, the Xpath in the select actually does the magic. Let's try to understand this XPath in detail. The function node() returns an node. Specifying just node() will return all the nodes in the XML data. In order to filter, square brackets [] are used and the appropriate filter condition are specified inside. The call local-name() returns the name of the node. Therefore, the condition local-name()=P_SORT_BY will return all nodes whose name matches the value specified by P_SORT_BY.

 

The “order” and “data-type” fields can also use xdoxslt functions in them. Thus, if the sort type is known at run time, the following can be used to set the sort type at runtime:

<?xdoxslt:set_variable($_XDOCTX, ’sort_datatype’, value)?>

<xsl:sort select=".//node()[local-name()=P_SORT_BY]" order="ascending" data-type="{xdoxslt:get_variable($_XDOCTX, 'sort_datatype')}"/>

The above sorts by the field whose value is specified by the P_SORT_BY and the sorting type is decided based on the variable sort_datatype. Similarly, the order can also be obtained from a xdoxslt variable.
 

Published in BI Publisher

Last week, one of my consultant asked, how to set the default output type as EXCEL for one of the EBS report.  I found out, client was not on the latest patchset and thats why , the  Default Output Type field  on Template Definition UI was not there.

 

This feature was added at later releases and was not part of earlier release.  If you are in same situation, please refer following patches to get this feature.

Patch 5612820
Patch 7017250

Thanks.

Published in BI Publisher

If you have installed OBIEE 11g successfully, lets try to explore some of the new features and tools available with BI Publisher 11g.

Unlike 10g, where Report is a single entity and data sets are the part of Report definition, in 11g, Data Sets (which termed as Data Model in 11g) and Report definition are two separate entities.

Data Model, which drive/extract the xml data for report is the advance version of Data Template, So there is no Data Template in 11g, its all Data Model with a very nice Data Model editor to design simple to complex Data model. This support some new data source like, BC4J View Objects and EXCEL, which were not supported in earlier releases as data source.

 

 

Report definition includes the Data Model reference, Layout template and other report specific metadata required to render or generate the Report output.

For these many years, we are using RTF templates as main Layout Template, now there is new format available with 11g, it is xpt format, which looks like Oracle’s proprietary format. As claimed, it generates almost pixel perfect output and could be a good substitute for PDF Forms. A nice Report Designer is available to design these xpt reports.

As I mentioned earlier, the complete report consist of two main objects, Data Model and Report definition. Data Model should exist, before we start Report definition.

Before design the data model, make sure the required jdbc connection is setup through BI Publisher Administration UI. To access JDBC data source UI, click the Administration from the Top bar and select the Manage BI Publisher Administration from the Common Administration Page.

 

 

Open the Data Model editor by navigating to New=> Data Model.

Enter the description, default data source type. This is simple report and we are not using any PL/SQL logic, so leave the default package as blank.

The Data Model allows you to define the backup data source, in case the primary data source is not accessible, this can be define through Administration UI. I left it blank, as I am not using any backup data source.

If you have used Data Template, you might be remembering the next three properties. I have checked the Include Parameter Tags as I want Parameters elements to be part of XML Data. I also checked the “Include Empty Tags for Null Elements” as  I want to include the null elements for null column values.


The next property, “XML Tag Display” allows you control XML Tag case in XML data. I selected as upper to make sure all the elements appears as Upper case in XML Data.

The attachment section allows us to upload the sample XML data or schema, this will helpful to design and test the Layout Template. The Data model allows you to generate and upload the sample XML automatically.

Next Step is to define the Data Set. Select the Data Set tab from the left panel and Click the * icon from the right panel, this will list down the entire supported data source. We will use the SQL Query data source for our report.

Select the right data source. We can directly type our query or use the query builder to design the query. The Query builder is the same as we see in 10g release, so lets skip it and paste the query directly. I have added P_DEPT parameter to filter the data based on depart number. Save the query.

When we save the query with the bind variable, the process asked if we want to to create the Parameter for the bind variable. Press OK to create the Parameter
 

In right panel we see two rectangle boxes, which represent the data structure, One corresponding to Outer or top most group and the other one “G_1” corresponding to our SQL query.  It will list the entire columns as element within this group.

If we save model at this stage, this will generate flat xml with no hierarchy. Lets add one level hierarchy to represent Dept, Employee parent child relation. To create a group on DEPTNO, select the >> on the same line as DEPTNO, this will popup the option menu, select the “Goup by” option.  

This will add the Parent Group with DEPTNO as element.  Move the DNAME element to parent group as well by selecting “Move selected element to parent group”.   This will bring DEPTNO and DNAME element to parent group
 

Select the SAL element from G2 group and drag it to “Drop here for aggregation” section of Parent Group and select function has Summary. This will add the summary column (CS_1) for SAL element at department level to calculate the Department Salary

If you have observed, there are three tabs at top of dataset right panel.  Diagram, Structure and Code.

Navigate to the structure tab. This has three sections, Data Source, XML View and Business View. I think this concept came from OBIEE Administration. Where we have physical, logical and presentation layer.

Data Source represents the actual data structure source based on our source query.

XML View represents the actual XML TAG in final XML output. We are allowed to change the XML Tag name. Lets change the summary column CS_1 to DEPT_SALARY.

Business View, as name suggest represent the Display Name for that element and as per my understanding at this stage, It could be use to drive Report /Column titles/headers.  Lets change the CS_1 to Department Salary. We can change other Display Name as well.

If we navigate to Code tab, we can see how our Data Model Structure code looks like. It looks very similar to 10g Data Template Structure.

Event triggers are same as before and after Report triggers in Data Template. Lets skip for this exercise.
Flex fields are Oracle Apps specific so leave this as well.
Data Model has already created P_DEPTNO parameter for you, So lets create List of Value for P_DEPTNO parameter and associate the LOV to P_DEPTNO parameter.

After creating the DEPT_LOV, select the P_DEPTNO Parameter, change it type as Menu and associate the DEPT_LOV with it.


Save the Data Model.

It is time to test our hard work, so lets click the XML icon from the top right corner, just before the save icons.

Since this is just a test run, we can select the number of rows to execute. This is very nice feature and allows creating a sample xml data file, which can be uploaded automatically to Data Model. The sample xml file is required to design the Layout Template

Click the open menu available next to Return button and select the “Save as Sample Data” option to save the xml output as sample data.

I would push the Layout Template Design to next blog, till then just keep on exploring the Data Model designer; it has lots of new functionality, like expression builder,  very handy to create complex expression, which are not available through data template, handling XML Tags and Display Names, merging data from different Data Source like SQL query and Excel sheet.

So have fun with new Data Model designer, till we dive into Layout Builder to create pixel perfect Template with new xpt format.

Thanks.

Published in BI Publisher
Wednesday, 18 August 2010 17:31

Exploring BI Publisher Bursting functionality

Bursting is one of the most widely implemented features of BI Publisher. Over the period of our BI Consultancy, Adiva consultants implemented around 10 implementation of Bursting Reports, that’s include the EBS bursting which is control file based bursting and the Enterprise Bursting Reports. Our implementation includes the Invoice, Purchase Order, Telecommunication billing, Bank Payments through EFT/Etext, Tax documents, Employee salary slips, Student admission notices and Business promotion emails. We implemented a complete Business Promotion framework for one of out client using this functionality to send email and fax for the promotional events.

 

 

Although the feature is documents in BI Publisher user guide and there are many blogs available on the same subject line, still every time we work on new implementation, we struggle with one or other issue as each bursting requirement is different and User guide does not cover all the functionality.

In this article, we tried to cover most of the common implementation. I would like to thanks existing blogs and Oracle support, who helped us to provide all valuable information during our bursting implementations and that’s motivate us to share those valuable information with Community.

Bursting Report is the Single process, which accepts a data stream and splits it based on multiple criteria, generates output based on a template, then delivers the individual documents through the delivery channel of choice.

Bursting Processor Engine accepts the three inputs

1) Bursting Control File : Bursting Control file is the XML file holds the metadata to control the bursting process. It has following structure.

=>RequestSet
 =>Request
    =>Delivery Section
                             =>Delivery Channel
      =>Document Section
  =>Template
       =>Request
     => Delivery Section
                =>Delivery Channel
      =>Document Section
  =>Template
              
2) XML Data file
3) Temporary directory location

As we go through sample examples, you will get better idea. We would talk about structure and available features though these examples.
  
We will use following sample xml data for the bursting report.
 

XML Data file, Sample control files, RTF templates and a sample java program can be downloaded from here

1) Lets design the very simple bursting report control file.  Employee Bursting Report with File System delivery. It burst the individual employee report and delivers to specific file system location. The file name is dynamically generated based on Employee ID.


Control file with File delivery

Instead of defining the File as one of the delivery channel, it could be specified at Document Section as output attribute. Both definitation has same effect.


Please use the BurstingSample.java program to test this template. Make sure you have all required libraries in class path.

Defining delivery channels

A single burst report can be delivered to one or multiple channels. Beside File delivery, EMAIL, FAX, FTP and SFTP delivery also supported. Each deliver definition in Delivery section identified by ID and that referenced in Document section to associate the document to require deliver channels.

EMAIL Delivery:

Attribute value can be hard-coded or defined as substitute variable. These substitutes values can be passed as User properties to BurstingProcessorEngine.class API or can be referenced from XML data element.

Passing substitute variable as user properties while calling BurstingProcessorEngine:

public void run(){
try {
  BurstingProcessorEngine dp = new BurstingProcessorEngine();
   dp.setTempDirectory("c:\\emp\\tmp"); //Set the temp file
   dp.setXMLAPI("c:\\emp\\BurstingControlFile_DEPT_SUMMARY.xml"); //Set Bursting Control file.
   dp.setData("c:\\emp\\Employee_data.xml"); //Data File
  //User Properties//
  Properties prop =   new Properties(); //add properties and variables//
  prop.put("user-variable:EMAIL_SERVER", "MySMTPSERVER");
  prop.put("user-variable:EMAIL_PORT", "25");
  prop.put("user-variable:FROM_EMAIL_ADDRESS"," This e-mail address is being protected from spambots. You need JavaScript enabled to view it ");
  prop.put("user-variable:CC_EMAIL_ADDRESS", This e-mail address is being protected from spambots. You need JavaScript enabled to view it );
  dp.setConfig(prop);
  dp.process();
 }catch (Exception e) {
 e.printStackTrace(); }

For single delivery definition, there could be multiple Messages.  The same report can be sent to different recipient with different subject and message content. 

The same report can be delivered to multiple delivery channels. We need to associate delivery channels with document in document section.

 

 

FAX Delivery:

 BI Publisher supports the Fax delivers through fax modems configured on CUPS. You can configure fax modems on CUPS with efax (http://www.cce.com/efax/) and FAX4CUPS (http://www.gnu.org/directory/productivity/special/fax4CUPS.html). Please refer the BI Publisher User Guide on how to setup the Fax Server. Once you setup the Fax server, it can be accessed through URL. Get the Fax Server URL and use it in control file as follows.     

 

  

 

    <xapi:delivery>

           <xapi:fax  server="ipp://myhost:631/printers/fax">

              <xapi:number id="FAX1">916505069560</xapi:number>

           </xapi:fax>

        </xapi:delivery>

 

I used the “ipp://myhost:631/printers/fax” as sample URL and 12345567789 as sample Fax No.

 

PRINT Delivery:

The BIP Delivery Server supports Internet Printing Protocol (IPP) as defined in RFC 2910 and 2911 for the delivery of documents to IPP-supported printers or servers, such as CUPS.  Please refer the BI Publisher user guide on how to setup the IPP Print server through CUPS. Once you setup the server, Printer can access through Print URL. Use the same URL to define the Print delivery channel for Bursting.

 

<xapi:delivery>

  <xapi:print id="PRINT_DELIVERY"     printer=" ipp://myhost:631/printers/P2165" copy-no="2" paper- size="letter"    orientation-requested="3" />

 </xapi:delivery>

 

Here ipp://myhost:631/printers/P2165” is the sample URL. Print attributes can be hard-coded or derived from Substitute variables. 

 

FTP Delivery 

 

     <xapi:ftp id="ftp_delivery" server="myCompany.com" user="${user}" password="${password}" remote-

     directory="${RemoteDir}" remote-file="${EMPLOYEE_ID}.pdf" />

 

SFTP Delivery:

 

For SFTP delivery, there is one additional attribute “secure-ftp”.

 

<xapi:ftp id="sftp_delivery" server="myCompany.com" user="${user}" password="${password}" remote-directory="${RemoteDir}" remote-file="${EMPLOYEE_ID}.pdf"  secure-ftp=”TRUE”/>

 

Template and Output Type:

 

Following table describe the type and output-type attributes values for the supported templates by Bursting Report:

 

Template Type

type attribute

Valid output-type attribute value

RTF

ptf

pdf , rtf, html, excel

PDF

pdf

pdf

ETEXT

etext

text

EXCEL

excel

excel

XSLFO

Xslfo

pdf , rtf, html, excel

 

 

Make sure to set the valid type (template type) and output-type value while defining the Document Section. type, output-type and template location can hard-coded or derived through substitute variables. Defining Key is optional but required when we want to generate status report.

<xapi:document key="${EMPLOYEE_ID}" output="c:\emp\out1\${EMPLOYEE_ID}.pdf"  output- type=${outputType}">

<xapi:template type="rtf" location=${Template_location}"/>

</xapi:document>

 

Conditional Template Layout..

Using the filter attribute at template section allow to burst the report conditionally. The value of filter should be a valid XPATH expression against the xml data.

 

In the following example, we are using different template for different department.  

 

There could be multiple templates within one document section and there could be multiple document section within one Request. Using the Filter we can apply the Template conditionally.

 

Conditional Delivery

 

The same Filter functionality can be used to implement the conditional delivery based on the recipient delivery preference.

 

 

Single Template multiple output    

 

 

Multiple Requests

 

Here is an example of multiple bursting reports within a single process. These are Employee, Department and Organization level report. Employee report will be delivered to employee and Department Level Report will be delivered to respective Department Manager. The organization level report will be for the record purpose

.

 

 

Sample Java program  

 

import java.util.Properties;

import oracle.apps.xdo.batch.BurstingProcessorEngine;

import oracle.apps.xdo.common.log.Logger;

 

public class BurstingSample {

    public BurstingSample() {

    }

 

    public static void main(String[] args) {

        BurstingSample burstingSample = new BurstingSample();

        burstingSample.run();

    }

   

    public void run(){

        try {

            Logger.setLevel(Logger.STATEMENT); //trun on the debug mode//

            BurstingProcessorEngine dp = new BurstingProcessorEngine();

            dp.setTempDirectory("c:\\emp\\tmp"); //Set the temp file

            dp.setXMLAPI("c:\\emp\\BurstingControlFile.xml"); //Set Bursting Control file.

            dp.setData("c:\\emp\\Employee_data.xml"); //Data File

            //User Properties//

            Properties prop =

                 new Properties(); //add properties and variables//

             prop.put("user-variable:EMAIL_SERVER", "MYSMTPHOST");

             prop.put("user-variable:EMAIL_PORT", "25");

             prop.put("user-variable:FROM_EMAIL_ADDRESS"," This e-mail address is being protected from spambots. You need JavaScript enabled to view it ");

             prop.put("user-variable:CC_EMAIL_ADDRESS", " This e-mail address is being protected from spambots. You need JavaScript enabled to view it ");

             dp.setConfig(prop);

 

            dp.process();

           

        } catch (Exception e) {

            e.printStackTrace();

       }

    }}

 

We have lot more to discuss about the BIP Bursting, but this is enough for now. We will further explore Bursting Status Listener, EBS integration (XDOBURSTREP) and Enterprise Bursting.  How about BI Publisher 11g bursting functionality, we need to explore that as well.

Published in BI Publisher

Today one of my consultants shared his problem on submitting the Concurrent Program to run XML Publisher report. They are customizing EBS functionality and it requires submitting the concurrent program programmatically. The request was submitted and completed successfully but the report output was XML instead of PDF and what I found, they were missing one single statement - FND_REQUEST.ADD_LAYOUT () before calling the FND_REQUEST.Submit_Request.

Most of us know how to submit a concurrent program from PL/SQL but here is the sample PL/SQL code on how to apply the template/layout to XML Publisher /BI Publsiher Concurrent Program.

For example, we want to submit Open Balance Supplier Letter Data  report from PL/SQL and here are the details.

Data Definition Code:  APXSOBLX
Application Short Name:  AP

Template Code: APXSOBLX_1
Template Name: Supplier Balances Letter

Concurrent Program Name :    Supplier Open Balance Letter
Short Name : APXSOBLX   

Concurrent Program short name always same as Data Definition Code.

Here is the PL/SQL logic to fire this :

create or replace PACKAGE SAMPLE_CP_BIP_REPORT_PKG AS
PROCEDURE submit_req ( requestId OUT NUMBER, errbuf OUT VARCHAR2, retcode OUT VARCHAR2);
END SAMPLE_CP_BIP_REPORT_PKG ;
/
 
create or replace
PACKAGE BODY SAMPLE_CP_BIP_REPORT_PKG AS
PROCEDURE submit_req (
requestId OUT NUMBER,
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2)
IS
 
l_request_id NUMBER;
l_layout BOOLEAN;
l_phase VARCHAR2(50);
l_status VARCHAR2(50);
l_dev_phase VARCHAR2(50);
l_dev_status VARCHAR2(50);
l_message VARCHAR2(100);
l_wait BOOLEAN;
 
BEGIN
l_layout := FND_REQUEST.ADD_LAYOUT
('AP',
 'APXSOBLX_1',
'en',
'US',
'PDF');
 
IF l_layout THEN
l_request_id := FND_REQUEST.SUBMIT_REQUEST
('AP',
'APXSOBLX',
'Supplier Balances Letter ',
NULL,
FALSE,'','','',
'', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '');
COMMIT;
requestId :=l_request_id;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETCODE := 2;
ERRBUF := sqlcode||':'||sqlerrm;
--fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
END submit_req;
END SAMPLE_CP_BIP_REPORT_PKG ;
/

 We are using following two function  from the FND_REQUEST package :

1) FND_REQUEST.add_layout (
           template_appl_name in varchar2,
                        template_code     in varchar2,
                        template_language in varchar2,
                        template_territory in varchar2,
                        output_format     in varchar2) return boolean;

This need to be call before submit request to add the layout to Request. It initiates the post process to apply the Layout.

Arguments
  --    Template_APPL_Name      - Template Application Short name.
  --    Template_code                   - Template code
  --    Template_Language           - Template File language (iso value)
  --    Template_Territory              - Template File Territory (iso value)
  --    Output Format                     - Output Format

2) FND_REQUEST.Submit_Request
     submit_request (
     application IN varchar2 default NULL,
     program     IN varchar2 default NULL,
     description IN varchar2 default NULL,
     start_time  IN varchar2 default NULL,
     sub_request IN boolean  default FALSE,
     argument1   IN varchar2 default CHR(0),
     argument2   IN varchar2 default CHR(0),
                                           --
                                           --
                                           --
     argument100 IN varchar2 default CHR(0))
                 return number;

This submits the concurrent request and returns the Request ID.

Attributes:
   --   application -            -Short name of application
  --   program                   - concurrent program short name
  --   description -            -Optional Program Description
  --   start_time -              -Optional. Time at which the request has to start
  --   - running
  --   sub_request           - Optional. Set to TRUE if the request is sub request, The default is false.
  --   argument1..100     - Optional. Arguments for the concurrent request, Please pass the parameter value define in CP Report definition.

There are other functions available within FND_REQUEST package like add_notification, add_printer. We can use them as per our requirement.

Please feel free to ask your question or provide any suggestion on this article.

Thanks
-Raj

Published in BI Publisher
  • «
  •  Start 
  •  Prev 
  •  1 
  •  2 
  •  Next 
  •  End 
  • »
Page 1 of 2