Tuesday, January 20, 2009

BI Publisher: Data Templates vs. Reports6i

Top 5 Reasons to use Data Templates over Reports6i

To start off there are more than 5 reasons!

1. Displaying a reports in real-time
2. Advanced PL/SQL
3. Easy of use
4. Multiple Integration Points
5. No Binary Conversions
6. Used across multiple platforms
7. Integration with multiple data sources
8. Excellent debugging facilities
9. Eliminates Ambiguous Crashing (who doesn't love terminated by signal 11)

I forgot to mention a couple of other benefit about data templates vs reports6i that we haven't ever discussed. Data templates are the only way a developer can generate the xml in real-time in the eBusiness suite or locally on there pc. In comparison, a developer always has to submit a concurrent request in order to generate an xml file with reports6i.

Other benefits of using data templates would be that the logic has to be put into a pl/sql package, whice essentially makes the business logic globally available to all other objects in the database. This provides an amazing code reduction. Developers can create other data templates (that are similar but different) and reuse existing business logic.



Anonymous said...


Is possible with BIP to create a report that you doesn't know the initial quantity of columns?

Imagine by default we display a person's list with columns first_name, last_name, and email.

How I can dinamically add a column depending of the parameter we receive, for example to add the fourth's column age.

Thanks & Regards,

Ike Wiggins said...

yes, you can do this. The approach would be to create a lexical and populate it in your before report trigger (same as in reports6i).

The variable must be defined in the package spec and the default package attribute must be populated in the data template

Example: Notice the use of the ampersand.

select &dynamic_select
from ic_item_mst

or take it further

select &dynamic_select
from ic_item_mst
where item_id = 1

Make sense?


Anonymous said...

Hi Ike,

Thanks very much, I will try your suggestion. You saved my day.


Nil said...

I am facing some problem in this issue.
Please help me....

I need to develop mechanism for varying XML Publisher output by instance in other words Instance based Output of XML Report Publisher without customizing particular rdf or package.

for this we need to call particular template or format that template depend upon Instance name. So we need instance name in XML file but we don't have instance name in that particular XML Data file.

How can I add Instance name in rtf template and XML data file without customizing XML Source package or rdf Report to compare that Instance name with current instance name in RTF Template.

For example:
If Instance name = Production then call watermark of company logo otherwise simple report output.

Note: don’t have Instance name in XML Data file.

Thanks in Advance

Ike Wiggins said...

How can I add Instance name in rtf template and XML data file without customizing XML Source package or rdf Report to compare that Instance name with current instance name in RTF Template.

Answer: You will need to modify the rdf to have the instance name. The workaround for this would be extremely challenging ie: you could create a bip extension (java). The irony of that approach would be that you need the instance to create a JDBC connection to get the instance name. In sense you would developing that solution would be double effort, with little value and high cost.

In short, their is no known workaround to your question...


Nil said...

Thank you for your prompt reply.


Simon Tomey said...

Great post Ike.

In case it helps...dynamic columns (and widths) are also possible within the presentation template (I believe - but haven't proved it). For Financials users the standard FSG template is an excellent example of dynamic items.

Ike Wiggins said...


Your correct dynamic columns (and widths) can be achieved using BIP Format Templates. However, dynamic columns can be achieved in reports6i but it's much harder to do.

However, BIP is superior when it comes to dynamic columns like trending reports or pivot tables. Dynamic columns can be achieved with the following command: for-each@cell.

Anonymous said...


Is it possible to create a logo dynamically (Runtime) depending upon the customer in Packing slip report.

Thanks & Regards,

Ike Wiggins said...

yes this is possible but only if your report is custom.

Anonymous said...

Yeah I have created a Custom report in Oracle Apps. and want to display logo according to the customer name

Ike Wiggins said...

See this post:


Anonymous said...

Thanks very much for your help.

Anonymous said...

I need information about BI publisher. I need to know about the features related with ETLs.
How can I talk with you (Ike)

Ike Wiggins said...

See http://bipublisher.blogspot.com/2007/09/plsql-taking-it-to-next-level.html


Look for pipelined functions for oracle.

usman said...


Is it possible for me to create deferent templates (more then one) for one rdf and xml file?


Ike Wiggins said...

Can you clarify:

Q. Why would you need more than format template?

Q. Are you expecting to generate multiple outputs with two format templates?

Potential Answers:

A. Use xdo import statements in your format template. See documentation.

A. If multiple outputs are expected for the same (one) xml file than you would use bursting to accomplish that.

RD said...

The bad thing about data templates is that the use of ref cursor queries is very limited. For the last two projects I worked, ref cursors were essential. But of course this was because extremely complex queries crash Reports Developer. So the need for the ref cursor might have been eliminated.

The number one complaint I have about data templates is that there is not an editor! That is fine for simple reports, but extremely cumbersome if there is a tad of complexity.

The extremely bad thing about RTF templates is that you cannot create the pre-printed stationary look when your lines are not a fixed height.

The layout editor of Reports Developer, IMHO, offers a lot of graphical control which is either very difficult or impossible to do with publisher templates.

Publisher templates offer a lot of ease of use in some ways, but they are not the ultimate replacement for bitmapped RDFs that Oracle tries to promote them as. I think they try to get everyone excited about them because they no longer want to maintain Reports Developer.

Phasing it out is fine, but just get BI Publisher up to speed on being able to do every possible feature that Reports Developer could do first.

I hate the bugginess and strange crashes in Reports Developer as much as anyone, but BI Publisher is just not ready to be a complete replacement in all cases as Oracle claims.

Anonymous said...

Great blog.

here is a "problem" I'm facing.

In a single data template, I want to re-use a query_1 query (it has its sqlStatement element), on a query_2 query (in another sqlStatement element) in the form of a in-line view. Example:

Q1: select ... from emp e

select depto, sum(...)
from ([ some way to refer to Q1 ]) e
group by ...

Using bind variables or link elements does not really handle the same proposed issue, as in Q2 I'm only aggregating from the core query Q1.

Advantages of this facility (if at all possible):
- code once (the core query Q1), and use many times (in dependent queries Q2, Q3, etc.)
- *maybe* eliminate calculations in the report template altogether (rtf file, or else), by using successive sub-queries in separate sqlStatement elements

(please see thread http://forums.oracle.com/forums/thread.jspa?threadID=1056621
for related discussion, specifically, today post Feb 11, 2011 8:20 AM)