Oracle BI Publisher Blog

After a lot of hard work and heartache the standalone XML Publisher Enterprise was released this morning via the edelivery site on oracle.com. Its a fully featured enterprise reporting application.
Some highlights:

• You can run the server on any mid tier
  
• You can manage users, reports, delivery channels from a centralized web interface
  
• You can build reports very quickly using an advanced web interface accessing multiple datasources (multiple db's, web services, http, data template) in each report
  
• Users can run, schedule and deliver reports down multiple delivery channels.
  
• Excel Integration - users can log in from the Excel interface and run reports bring data back into Excel for further analysis
  
• ... the list goes on.

You can download a copy from  http://edelivery.oracle.com
1. Pick your language and continue
2. Enter your details and check the agreements, continue
3. Choose 'Oracle Application Server Products'
4. Pick your platform, we have ported for Windows and Linux but it can run on any platform any App Server and 'Go'
5. Look for the 'Oracle® XML Publisher Release 5.6.2 Media Pack for X' in the list.
6. There is a server side and client side component, download them and follow the readme's

Its fast and easy to set up and get going ... let us know how you get on.

We have had a few enquiries recently asking how to hook up XMLP Enterprise with a SQL Server db, heres the steps you need to follow to get it working.

• Download the SQL Server 2000 Driver for JDBC Service Pack 3 - we had some issues with the 2005 jdbc driver we are investigating whether this is an XMLP or MS issue ... you can still access the SQL Server 2005 edition with the 2000 driver sucessfully.
  
• Once installed look in the <<INSTALL DIR>>/lib directory there will be 3 jars
  mssqlserver.jar
  msbase.jar
  msutil.jar
  Copy them into your $OC4J_HOME/j2ee/home/applib if you have installed XMLP under OC4J otherwise put them in the common apps libraries directory of your J2EE container.
  
• Now bounce your server, the jdbc libraries will be loaded automatically for all applications on the instance. If you only want them loaded for the xmlpserver application then copy the jars to OC4J_HOME/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib and bounce.
  
• Now login as Administrator in the Enterprise server and go to the Admin -> JDBC page and enter the details required for the data connection:
  
  
  
  
  • Data Source Name - give the connection a name 
    
  • URL the url to connect to the db, standard MSSQL stuff, basically   jdbc:sqlserver://server:port;databaseName=dbname e.g. jdbc:sqlserver://111.11.1.111:1133;databaseName=AdventureWorks
    
  • Username  - simple stuff 
    
  • Password - even simpler  
    
  • Database Driver Class - this needs to be com.microsoft.jdbc.sqlserver.SQLServerDriver
    
  • Now hit the test button to check the connection works. If it fails check your connection string is valid and the class definition above.
    
  • The data source will now be available in the report builder to builder queries against.
    

You're now ready to start writing reports against SQL Server!

I have seen several questions around accessing a report via a URL for the XMLP Enterprise release, some of you have teased apart the mechanism and gotten it working, heres the 101 for the rest of you. Now Im assuming that you are trying to call a report via a URL from another application, that might be from a portal or maybe an APEX application.

Security

As you know the reports are placed in folders and those folders are then secured to a role and a role assigned to a user. So first off you're going to need to ensure that a user actually has access to a report in the first place and if they do then you'll need to call XMLP to render it. There are two options.
1. Use the Guest folder - this can be enabled via the server configuration file, any report in this folder is open for all to see and run. Not the best solution but if the report is not sensitive then it will work no problem.
2. Use SSO - you can hook the XMLP server up to an SSO server as a partner application to the calling application and use LDAP for both application to minimize the user maintenance. This way you call any report via a URL and as long as the user has rights to see/run the report then XMLP will render it without the need for the user to log in. Setting up LDAP and SSO are covered in the user guide now available on OTN.

Building the URL

So the basic URL for a report is as follows:
http://server:port/xmlpserver/ReportDirectory/ReportName.xdo

where

• server:port - is the name of the server and port number where xmlp is running
  
• xmlpserver - a required string, this is the name of the application
  
• ReportDirectory - the folder path to the report
  
• ReportName - the name of the report

This will render the complete report inside the XMLP page with all the report controls. The default template, output and parameters will be used to render the report.
For example:
http://xdopf.us.oracle.com/xmlpserver/Private/Salary+Report/Salary+Report.xdo



If you want some more control then we need to start adding some name/value pairs to the URL. The easiest way to generate the URLs is too just export the report, the URL generated will look similar to the basic URL but the name/value pairs will be added. For example:

http://xdopf.us.oracle.com/xmlpserver/Private/Salary Report/Salary Report.xdo?_xpf=&_xpt=1&_xdo=%2FPrivate%2Fkfabian%2FSalary+Report%2FSalary+Report.xdo&dep=10&_xt=Standard&_xf=html

Lets ignore the first part of the URL, its the same as before, so we have:

Breaking this string up we have the following parameters on the URL:

• _xpf - for internal use - just leave it as is
  
• _xpt - defines whether the report output should be rendered in the full XMLP window (as above) use a value of 0 or a 1 for just the document itself.
  
• _xdo - this provides the path to the current report, its optional so you can leave it out.
  
• dept - this is a parameter value for the report, in this case the department for the data, notice it takes the department id. The parameter definition is to show the user the department name and then pass the id to the query. Of course you can have multiple parameters and their values on the URL
  
• _xt - this controls the template to be used, this is the template name i.e. Standard not the template file name.
  
• _xf - this controls the format of the output to be generated e.g. PDF, HTML, etc

Of course you'll need the obligatory '?' for the first parameter and '&' for the subsequent ones. So you can now create a URL to point to the required report and pass parameter values, output format and template.

Good Luck!
 

Its coming ... its even bigger I hear rumors of Oracle taking over a block of Howard Street in San Francisco to "extend" the Moscone Center for the week :o)
This year there will be two XMLP events for EBS and PeopleSoft customers at the Extreme Weekend - lots of hands on stuff to dip your proverbial toes in the XMLP water. Places are limited so book early.
There are also over 20 sessions related to XMLP and its use throughout the all of Oracle including EBS, JDE, PeopleSoft and Enterprise. The sessions are not just from development, we have customer and a partner sessions lined up. Last year they were turning folks away from the doors so get there early too.
Of course the demo pod will be swamped all week but drop by, hang out, have a chat and get a demo or three!

The above sessions are of course subject to some change but you get the idea.

A recent enquiry about web service support for Enterprise has promted me to spend some time writing up how to do it here ... our docs are somewhat lacking :o( we'll address that in the next release.
Firstly, it can be done, secondly its pretty simple once you understand the limitations and the of course what the WSDL behind the service can actually do.

Im going to use the service described here, http://www.webservicex.net/stockquote.asmx?WSDL. In the WSDL there is a getQuote method that we canuse to get stock quote information. It requires of course a quote parameter value such as 'orcl'. Today it will return:-

  <StockQuotes>
 <Stock>
  <Symbol>ORCL</Symbol> 
  <Last>18.47</Last> 
  <Date>11/1/2006</Date> 
  <Time>4:23pm</Time> 
  <Change>0.00</Change> 
  <Open>18.56</Open> 
  <High>18.65</High> 
  <Low>18.34</Low> 
  <Volume>31724216</Volume> 
  <MktCap>95.960B</MktCap> 
  <PreviousClose>18.47</PreviousClose> 
  <PercentageChange>0.00%</PercentageChange> 
  <AnnRange>11.75 - 19.25</AnnRange> 
  <Earns>0.666</Earns> 
  <P-E>27.73</P-E> 
  <Name>ORACLE CORP</Name> 
 </Stock>
</StockQuotes>

Anyhoo, how do you get this working in Enterprise.
Step 1: Create a new report

So give the report a name, description, etc then add a new Data Model

Step 2: Define the data model 

Give it a name, the type should be set to 'Web Service', the Details section will update for you. now enter:

WSDL URL - http://www.webservicex.net/stockquote.asmx?WSDL
Method - GetQuote

At this point we can not add the parameter, we need to create in the UI first andthen add it later.

Step3: Add the quote parameter
Click on Parameters and click New to create a new parameter.

Enter the following:

Identifer - whatever you wish
Data Type - String
Default Value - if you want
Parameter Type - Text
Display Label - the user will see this so make it something obvious
Text Field Size - I guess 4 is going to be the max you'll need

Step 4: Add the parameter to the Data Model

Save your work and go back to your new Data Model and click on the 'Add' link next to the Parameters label. The drop box will default in the new parameter.

You're done. Now click on the 'View'link and enter a stock quote and hit the 'View' button and the quote XML will appear. Now all you need is a layout template.

Of course you can create multiple data sources adding a query to the web service and then combine the result into a single report. For those of you with Enterprise installed you can get the report definition here so you can load it to your instance. Remember that the URL is going to be outside of your company firewall so you'll want to start the server up with the proxy parameters.

-DproxySet=true -DproxyHost=www-proxy.us.oracle.com -DproxyPort=80

Date formatting in XML Publisher is very powerful, it can provide a variety of formats from simple 03/12/99 to 'Friday, December 31, 1999 6:15 PM GMT' ... notice the timestamp and the timezone components. All very neat but how do you get there?

Get the right format ...

Well the first hurdle is to get your dates in the XSD date-time format:
                  YYYY-MM-DDThh:mm:ss+HH:MM
Looks straightforward and you ought to be able to get the database 'to_char' function to serve it up no problem, but there is a wrinkle in there in the form of that 'T' character its used as a separator between date and time components. Try and use that format mask with the to_char and the database will choke ... the format is not recognised. There is hope, even for Oracle Reports under EBS users :)

1. If you are not interested in the time and zone info then just use the 'YYYY-MM-DD' format mask, no need for the 'T'. Oracle Reports and the db support this mask. XMLP will then format the date appropriately. any timestamp is going to be 12:00:00 AM and the timezone will default to GMT
2. Use the XMLP extraction engine - the engine will extract all dates using the XSD mask - simple
3. If you're using Oracle Reports or your own plsql/sql extraction routine and want the timestamp and zone then only way I have found to construct the mask is to use something like:

    to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'hh:mm:ss+HH:MM')

this will at least get the date into the right format.

I stand corrected on this thanks to an anonymous comment, I was playing with masks in the 
db while writing and could not get the format to work, however our anonymous friend has:
to_char(sysdate, 'YYYY-MM-DD"T"hh:mm:ss+HH:MM') which works perfectly ... apologies!

Date formatting and calculations

Now you have date in the right format you can apply masks in the layout very easily. You can either use the MSWord formats or XMLP provides a format-date function ... there is a complete section devoted to the formatting in the user guide. Page 118 is the start and covers all XMLP flavors.
The other advantage of the mask is that calculations on dates become possible with XSLT 2.0, prior to this all dates were treated as strings not much use when it comes to calculating the number of days between two dates. Thats another article in itself ... there are plenty of resources out there now.

Another series of articles on the way ... this time concerning the relatively new data extraction engine from XML Publisher that is currently available in the Enterprise and E Business Suite flavors.

First a little history ... 

Why build another extraction tool, there are others that can generate XML, Oracle Reports, various solutions from the database folks, the list goes on. To answer why you have to understand the volumes of data we are trying to handle here and the furture of reporting for Oracle Applications. For EBS in particular we had multiple reporting solutions, the majority of which were based on Oracle Reports. The plan going forward is to remove Oracle Reports from the techstack in the fusion timeframe. Therefore we needed an extraction engine that offered all that Oracle Reports offers and was just as fast - Oracle Reports is danged fast at generating XML data. On top of that we are not talking about extracting data for a 2 page invoice, the engine needs to extract millions of rows to build tens of thousands of pages for some reports. 
We looked at what was out there, SQL XML provided fast extraction for large data sets but we needed among others, event triggers and flexfield user exits, plus users would need to learn the wrapper functions used to generate the hierarchies. After a lot of research we decided to build an engine ourselves with all the bells and whistles needed to cover the Oracle Reports replacement requirements.

Features ... features ... features

To dig in a little to the features the engine provides:

Fast, scalable extractions - its fast, faster than Oracle Reports. We worked closely with the Oracle Performance team and had to re-write it a couple of times to get their approval. It needs to be fast too, those of you that use Oracle Reports EBS know that out of the box we generate flat text character based output. With XMLP we're generating highfidelity output ... that requires more processing time so the more time we save on the extraction the more we have for formatting without slowing the report generation.

Oracle Reports Features - if its going to replace OR in EBS then it has to match OR on features:

• Multiple Queries/Joins -  enabling master/detail extractions
  
• Event Triggers - pre- and post- fetch for business rule processing. Currently plsql support but will add java
  
• Flexfield Support - gotta get that natural account description with out the select statement - this is an EBS feature.
  
• Formula/summary columns -  again similar to Oracle Report functionality allowing you to create aggregation values and pl/sql based formula columns in your extraction
  
• Data Structure - this allows you to build a hierarchy into your XML data similar to the grouping abilities in Oracle Reports
  
• Group filters - a la Oracle Reports
  
• etc

on top of that its also has:

• Rich Java API layer - call a data template from your jsp or java app
  
• Distributed Queries - this is neat. You're not tied to a single db nor even to an Oracle db. You can construct a data template that, for example hits an MSSQL instance for customer data and an Oracle db for their invoices. the engine will generate a single result set of hierarchical XML i.e. Customer1
   Invoice1
   Invoice2
  Customer2
   Invoice1
   Invoice2
  
  
• Static XML File support - query across a db and an XML file. Text and XLS support coming.
  
• Pluggable Data Templates - not here yet but theidea here is that you can build a core data template and then allow a plugin DT to be applied over the top to get more data from other sources.

Im interrupting the Data Template flow with a really great question and answer from the forum that I wanted to share on time zone formatting. For those of you that want to include a timezone portion into your output dates XMLP can help but you might not find what you expect!

Let's assume we have the following date element in our data stream:

 2006-07-27T12:48:00.000+02:00

Notice the timezone offset from GMT i.e. Paris, France.
Using 
       <?format-date:DATE_TEST; 'LONG_TIME_TZ'?>
we  get
 Thursday, July 27, 2006 10:48 PM GMT

The GMT appears incorrect we were expecting 'Paris' ... hey thats a bug! Look closer and you'll notice that the time component has been adjusted back by 2 hrs. So it is actually correct for the GMT timezone.
So how do we stop the adjustment and see 'Paris' i.e. Thursday, July 27, 2006 12:48 PM Paris
This can be achieved using:
    <?format-date:DATE_TEST; 'LONG_TIME_TZ';'Europe/Paris'?>
that 'Europe/Paris' is a java time zone specification. The list can be gotten here, http://www.thescripts.com/forum/thread15954.html
This is in the user docs but  a little tough to grasp(;o). If you are in EBS then there is a profile option, more details in the user guide. If you are using APIs the java time zone can be passed into the template as a parameter.  Neat!
 

Apologies for the delay ... I have lots of content and examples for you but I did not have a means for you to test your data templates. I have now rectified that and have taken one of our internal testbeds and packaged it up so you can test to your hearts content. The test bed is a web application that runs inside OC4J, I have not tested it with any other mid tier flavors but if you're game.

Before we start on the content lets install the app and test a sample data template.

1. Download the EAR file here. Well you would be able to download it if I can find somewhere that can host it. for now drop me a mail and I'll get it to you. 
   
   Finally found a home for the file on my website, you can get the file here, http://www.banzel.com/download/DTTestBed.ear
    
   
2. I have tested with OC4J 9.0.4, 10.1.2 and 10.1.3 so once you have the EAR its a simple case of deploying it. If you do not have OC4J installed, go for the latest 10.1.3 release and have a nice UI to help you deploy the app.
   
3. Once installed you just need to point your browser to http://yourserver:port/DTTestBed/DT1.jsp
   4. The first page you'll see is:
   
   
   
   

   I have created a small data template that will run against the scott/tiger schema, so download it from here. Then use the browse button to find it and hit Upload.

   
   
4. You'll then get a page like this:
   
   
   

   
   
   Heres where the fun starts, so in the page you have a parameter section, these are the parameters parsed out of the data template in this case we only have one, pDeptNo. You can fill in a value or leave it blank to get the complete data set.
   Then we have the runtime options:
   

   
   
   • Generate XML Schema  - fairly obvious but very useful if you are going to be doing some PDF template mapping
     
   • Generate Default Layout - this will generate an RTF template for you based on the data structure of the data template, again useful to get you started
     
   • Layout Template - if you have already developed an RTF template (no it does not support PDF templates yet) then you can load it to be applied to the resulting data. If you choose this option then a default template will not be generated.
     
   • Database - we need a connect string to the database, hostname:port:sid
     
   • User Name - obvious
     
   • Password - obvious if you have it
     
   • ORG ID - this is for EBS customers that want to test data templates running against the EBS db - its not required.
   
   
   
   
5. So fill in some values so you have something like this:
   
   
   

   

   
   

   and hit Submit
   7. If all went well then you'll get a page of results similar to this:

   
   

   

   
   

   There will be links to the various objects that XMLP has generated. These are all created in a temp directory under the root of the web app in mycase

   D:Oc4j10.1.3j2eehomeapplicationsDTTestBedDTTestBedtemp

   . Be sure to clean that directory up regularly it will fill your disk all to quickly.
   

Hopefully you have downloaded and installed the Data Template Test Bed I provided in my last post, you may have even loaded and run the sample I provided ... hopefully sucessfully. You may have even gone off and got started on your own. Well we are going to take a step back here and get back to basics and then move up to the more powerful stuff like filters, triggers and flexfields ... we're going to get there quickly so please be patient.

Being the consumate geek and not wanting to break tradition when it comes to starting out with a new technology we're going to build and test a Hello World data template ... now known as 'DTs' for brevity. First lets take a look at the anatomy of a DT, it'll help later.

<dataTemplate name="EMPLOYEES" defaultPackage="" description="Employee Data">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters>
      <parameter name="pDeptNo" dataType="number" defaultValue=""/>
   </parameters>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[SELECT DEPTNO,DNAME,LOC 
 from dept where deptno = nvl(:p_DeptNo,deptno)
 order by deptno]]>
 </sqlStatement>
  <sqlStatement name="Q2" dataSourceRef=""><![CDATA[SELECT  EMPNO,ENAME,JOB
 ,MGR,HIREDATE,SAL,nvl(COMM,0) COMM  
 from EMP 
 WHERE DEPTNO = :DEPTNO]]>
 </sqlStatement>
  </dataQuery>
   <dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>
</dataTemplate> 

I have highlighted the main players in a DT:

• dataTemplate - header element where you provide a name and description for your DT. The name will be used as the root name of the element.
  
• properties - these are the runtime switches for the extraction engine:
  
  
  
  • include_parameters - should the parameter values be extracted to the XML or not
    
  • include_null_Element - if a value when extracted is null do you want an empty element in the xml
    
  • xml_tag_case - upper or lower case tag names
    
  • db_fetch_size - maximum number of records to fetch
    
  • scalable_mode - if you know this is going to be a large dataset or an intensive extract then set this to true
    
  • include_rowsettag - for simple XML with no hierarchy do you want ROWSET as the root element
    
  • debug_mode - generate a debug file while processing or not. The test bed I posted will always do this for you.

• parameters - these are the runtime parameters for the DT
  
• lexicals - these are EBS specific and will allow you to fetch flexfield values ... more on these later
  
• dataQuery - heres where we start to define the
  sqlStatement - this is where we define the query(s)
  
• group - this section defines the structure of the data we want to see and contains
  
• element - the reference to the column in a specific query. These also have a name for the resulting XML element and you can include functions here.

So thats a quick overview, lets do the HelloWorld ... its as simple as it gets when it comes to DTs.

<dataTemplate name="HELLOWORLD" defaultPackage="" description="Hello World DT">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters/>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[select 'Hello World!' WELCOME from dual]]></sqlStatement>
 </dataQuery>
   <dataStructure>
  <group name="HELLO" source="Q1">
   <element name="WELCOME" value="WELCOME"/>
  </group>
   </dataStructure>
</dataTemplate>

<?xml version="1.0" encoding="UTF-8"?>
<HELLOWORLD>
 <HELLO>
  <WELCOME>Hello World!</WELCOME>
 </HELLO>
</HELLOWORLD>

Bonjour mes ami!

I have just got back from a conference in the warmer climes of the French Riviera. Its been quite a shock; after nearly a week of mid 60s temperatures and blue seas coming back to somewhat chilly and mid 30s Colorado has taken some getting used to again. Thankfully the promised thaw is upon us and the piles of snow in my yard are now dwindling, much to my sons' chagrin the snowboard run we had in our back yard is becoming a distant memory. Ahh c'est la vie ... so to another article in the fragmented data template series.

I have spent some time looking at our documentation on data templates, its pretty good so please check it out. Im picking on a series of some of the tougher features to get your head around. for this article its creating and joining multiple queries.

There are two methods to join queries, lets assume we have the following queries:

SELECT DEPTNO,
DNAME,
LOC 
FROM DEPT
ORDER BY DEPTNO

SELECT  DEPTNO,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
NVL(COMM,0) COMM
FROM EMP

we want to create a data template that will use the DEPT query as the master and the EMP as the child. We need to link the two queries.

Method 1: Query Link

We can create a link similar to Oracle Reports, like this:

<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPTNO"/>

We name the link and then specify the queries and the linking columns, so the <dataQuery> section of our data template will look like:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT DEPTNO,DNAME,LOC from scott.dept 
    order by deptno ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP ]]> 
 </sqlStatement>
<link name="DEPTEMP_LINK" parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2" childColumn="DEPTNO"/>
</dataQuery>

It works fine but we have to duplicate the DEPTNO column in the child query. Plus on a more real world query its going to be slower than taking ...

Method 2: Bind Variables

We did alot of testing on the joining of queries and found that usng bind variables was consistently faster then query links, so if you can, and there should not be a reason why you should not, use binds. What do I mena by bind variables, well taking our two queries we are able to have a master-detail relationship between the two i.e. the dept based query is the master and the emp the detail. We can join them quite easily using 'dept_id' as our joingin criteria:

<dataQuery>
 <sqlStatement name="Q1">
 <![CDATA[ 
    SELECT DEPTNUM,DNAME,LOC 
 from scott.dept 
    order by deptno ]]> 
 </sqlStatement>
 <sqlStatement name="Q2">
  <![CDATA[ 
     SELECT  DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
     from scott.EMP
 where DEPTNO = :DEPTNUM ]]> 
 </sqlStatement>
</dataQuery>

Notice, I have changed the column names a little just to make the join clearer (dont cut 'n' paste this one.) We use :DEPTNUM from the first query in the where clause of the second, et voila we have two queries joined. Its simpler, less typing and even better its faster!
Salut!

While I have been away I provided two days on in depth techie training to Oracle Consulting in EMEA, they worked me hard (very hard) ranging from inserting barcodes to bursting, all good fun and informative I hope. The second day we spent some time sharing experiences of developing extractions, templates, etc and Im indebted to Serge Vervaet from Oracle Belgium for some great tips. The first of which was on building a data template. The XMLP extraction engine is fast, you can migrate your Oracle Reports to the format relatively easily but there is currently no tool to help you build them so once you have your query sorted out its off to Notepad or an XML editor. For big extracts with lots of columns even I have to admit its very tedious typing out those grouping structures, Serge came up with a way to reduce the tedium and speed up the process by doing away with the grouping section all together - use inline CURSORS in your queries. This creates all the hierarchy you need in your query without the need for the grouping section.

Lets take our old friends the EMP and DEPT queries:

SELECT DEPTNO,
DNAME,
LOC 
FROM DEPT
ORDER BY DEPTNO

SELECT  DEPTNO,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
NVL(COMM,0) COMM
FROM EMP

To get a hierachical XML output we need to add the following data structure.  

<dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>

SELECT DEPTNO,
  DNAME,
  LOC, 
  CURSOR(SELECT  EMPNO,
           ENAME,
           JOB,
           MGR,
           HIREDATE,
           SAL,
           nvl(COMM,0) COMM  
      from EMP ) as EMP 
  from dept 
  where deptno = nvl(:p_DeptNo,deptno)

Notice the CURSOR command and the 'as EMP' the latter will create the group G_EMP in the XML data:

<EMPLOYEES>
 <PDEPTNO>10</PDEPTNO>
 <LIST_DEPT>
 <DEPT>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
  <LIST_G_EMP>
   <G_EMP>
    <EMPNO>7369</EMPNO>
    <ENAME>SMITH</ENAME>
    <JOB>CLERK</JOB>
    <MGR>7902</MGR>
    <HIREDATE>1980-12-17T00:00:00.000-08:00</HIREDATE>
    <SAL>800</SAL>
    <COMM>0</COMM>
   </G_EMP>
    ...
   <LIST_G_EMP>
  </DEPT>
 </LIST_DEPT>
</EMPLOYEES>

So we saved some time and effort and still got structure we needed. Sample DT here. I have to say we have not exhaustively tested the performance but Serge is getting good results and reducing his development time :o)

Many of you will have noticed, maybe even been confused by another product that has hit the Oracle streets namely 'Business Intelligence Publisher', BI Publisher for short and now affectionately know as 'BIP' by the cognoscenti.
Its not a new product, its XML Publisher with a new identity for its place in the BIEE suite of products, where it now generates the 'high fidelity' output from the BI Suite (more on this in a later posting.) Its also the new name for the recently released 10.1.3.2 Enterprise release, BIP Enterprise, again, more on that later too.
For now it remains as 'XML Publisher' under E Business Suite PeopleSoft Enterprise and JD Edwards EnterpriseOne and World products. Have no fear they may have different names but under the covers its the same core engine at work. 

Update on versions ...

Yeah, things are getting complex on our numbering system  so here's the low down. Im mapping ERP releases to the original XMLP release numbers ...

Applications
EBS 11.5.10  - baseline release comes with R4.5
EBS 11.5.10 RUP2 - comes with R5.0
         Of course for EBS 11i you can apply the latest XMLP release from metalink 5.6.2. The 5.6.3 release is coming soon.
EBS R12 - baseline release comes with release 5.6.3
PeopleTools 8.48 - comes with release 5.6
JD Edwards - comes with 5.6

BI EE & Standalone

BIEE Release 10.1.3.2 - contains BI Publisher 5.6.3
BI Publisher Enterprise 10.1.3.2 - based on 5.6.3 release.

Our marketing and I would have you believe that XML Publisher can empower your end users to build their own layouts and for the majority of your reports I still think thats true; creating listing reports with sub totals, re-grouping and page breaks are well within the capability (after a little training) of all but the most ardent technophobe. But when it comes to more complicated documents, usually the customer facing types, invoices, purchase orders, etc they very quickly need to get into some more advanced features which the template builder does not yet support and IT needs to step in. But IT does not need to do it all and revert to the slow report development seen before the advent of XMLP.

Another great tip from Serge in EMEA consulting came out at last weeks training event. Give the user a blank MSWord document and one formfield ... now ask them to design the complete layout of a report use the formfield to position the data and put in some sample data into the fields and even go as far as formatting them i.e. date, string or number. Then have them mark up the document with the look and feel they want, logos, colors, etc. If they think an invoice is going to stretch across multiple pages have them design the second and possible subsequent pages. Once they are happy with it, get them to sign off on it, you can then enable the template putting required data tags and for-each structures. Before handing back to the user for testing with some sample data Serge goes as far as to hide the form fields that contain the logic for the report from the user. This is simple enough, just mark the control fields and then set the font to hidden, you can use the ¶  to show/hide the fields. If the user needs to move data about, they can without getting worried about 'breaking' the report. Word of caution here thou, they could move things around just that little too far.

So even on the tougher templates the end user can still be involved in the design and get what their users want in the output and get that report out and into production much faster <:o) 

Charts charts charts ... yep XMLP/BIP can do em ... in fact it can do about 30 different types but the Insert Chart dialog of the template builder now only exposes about 6 of them, with a few bells and whistles ... if you want to go further then you have to get coding. I've covered some alternatives for the coding in previous posts but Im now posting what I hope is going to be a useful 'cheat sheet' for some of the tougher chart styles. Again, thanks to another Oracle Consulting star, Kan Nishida who put the samples together you can now quickly grasp how to put together the following chart types:

• Multi Bar Chart
  
• Dual Y Bar Chart
  
• Split Dual Bar chart
  
• Vertical Bar Stack
  
• Line Chart
  
• Dual Line Chart
  
• Pie chart
  
• Pie Ring Chart
  
• Multi Pie Charts
  
• Area Chart
  
• Area Vertical Stack

Some easy ones, some not so easy, Kan has provided some useful hints in the template on whats needed for a specific chart type and of course you can always take a look at the web properties for the dummy chart images to get a better idea. RTF template, XML data and PDF output available here.

Update
Got a request for a multi bar/line combo chart ... here it is.

The name is a bit of a mouthful when combined with the release version but  some of you may have noticed but maybe not? that there is a new version of the BIP Enterprise server out there. Not 'hot off the presses' news I know ... its been here for a few weeks now but hidden under the skirts of the latest BIEE release.  It has alot of new functionality across the board and I'll cover some of that over coming entries.

Today, BIEE Integration points, a lazy post for me because thanks to Abhinav on the BI Blog there is already content out there on the BIEE Integration - you can now use BIP to generate those high fidelity outputs based on Answers, direct BI server queries or from other sources and then embed them directly inside a dashboard page for your users. Check out the integration details here.

As of today you can only get the BIP release from the BIEE download details available here. We are working on posting the standalone release to OTN asap.

Had a request today asking how to get started with the BIP API layer. I think the use of the APIs is straightforward enough, there are lots of examples to get you started in the developer sections of the user guide. The tough bit is, what libraries and jars need to be in the class path to be able to work with the APIs without annoying compile errors? So heres the list:

• xdocore.jar  - the core BIP/XMLP library
  
• aolj.jar - this is an Oracle EBS library, we need it whether you're developing in EBS or not
  
• i18nAPI_v3.jar - this is the i18n library used for localization functions
  
• xdoparser.jar  - this is the scalable XML parser and XSLT 2.0 engine
  
• xmlparserv2-904.jar  - the main XML parser/XSLT engine - these will come together again in a coming version.
  
• bipres.jar - charting library
  
• bicmn.jar - charting library
  
• jewt.jar - charting support library
  
• share.jar - charting support library
  
• collections.jar - you only need this if you are working with the delivery APIs or bursting engine.

Now if you're using JDeveloper then the charting and XML Parser libraries will already be available to you. I'd recommend creating a directory with all of the above thou and using them as custom libraries in your project. That way you'll know when it comes to deployment you're not going to get any unexpected surprises at runtime.

Where to get the libraries:
1. EBS - you can point your java project to the JAVA_TOP directory  - not the simplest solution
2. Download the BIP Server and open the EAR file and find the jars
3. Download the Template Builder for Word, install and check the jlib library under the install directory.

So now you know what you need and where to get it.

Need a quick start ... how about applying an RTF template to some XML and generating PDF output ...

package xdotestbed;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.RTFProcessor;
 
public class XDOTest 
{
  public XDOTest()
  {
  // Need to use a try-catch block
  try
  { 
    // Set the RTF template
    FileInputStream fiS = new FileInputStream("c:\\temp\\check.rtf");
    //Instntiate the RTFProcessor
    RTFProcessor rtfP = new RTFProcessor(fiS);
    // Set the output XSL
    rtfP.setOutput("c:\\temp\\check.xsl");
    // Process
    rtfP.process();
  }
  catch (FileNotFoundException ex)
  {
    ex.printStackTrace();
   }
  catch (IOException ex)
  {
    ex.printStackTrace();
   }
  catch (XDOException ex)
  {
  ex.printStackTrace();  
   }
   
   //Now process the XSLFO template against the XML data
      // Instantiate the FOprocessor
      FOProcessor processor = new FOProcessor();
      // set XML input file
      processor.setData("c:\\temp\\check_data.xml");
      // set XSL input file
      processor.setTemplate("c:\\temp\\check.xsl");
      // set the output format
      processor.setOutputFormat(FOProcessor.FORMAT_PDF);
      //set output file
      processor.setOutput("c:\\temp\\check.pdf"); 
    // Now we process, have to surround with a try-catch block thou
    try
    {
      // Process !
      processor.generate();
    }
    catch (XDOException e)
    {
      e.printStackTrace();
    }

  }
 
   // Heres the main method to run the class. 
 // You could of course add arguments for the RTF, XML
 // and output PDF and avoid hard coding them in the class.
 // Sorry, Im being lazeeee ... 
 public static void main(String[] args) {
        XDOTest xDOTest = new XDOTest();
    }
}

Not too tough right ? With a little effort you can get the whole thing streaming rather than writing to disc, bolt on a call to the Data Template engine to fetch the data, use the FormProcessor to stick multiple outputs together ... the list goes on.

Quick one today, trying to work on the bursting series ...
You have developed Data Templates for your EBS instance, but what about setting the org id. In good ol Oracle Reports you just added the following command in your before report trigger:

srw.user_exit('FND SRWINIT')

and it was all done for you. In the world of BIP we go one step further. The executable that runs your data template sets the org id for you so you do not have to do anything.

Now there is a caveat to this, as long as you are using the BIP delivered executable (XDODTEXE) in EBS to call the data engine to run your data templates you're fine ... if you are accessing the data engine via the java APIs or the BIP Enterprise release then its your responsibility to set the org id before the query executes. The simplest way to do it is to call a pl/sql package in the pre-fetch trigger and in there set the org id:

fnd_client_info.setOrgContext(ORG_ID)

then you're set and you'll get the data you are expecting ... well as long as you wrote the query correctly of course ... been there done that, got the T shirt :o)

The bursting articles are still in process ... I did some work a while back for a training course on the 'anatomy of an invoice template' ... there are some useful 'bits' that I thought you might like to see. 

One of the most requested features on the forum for RTF templates is to show a fixed number of rows per page. Maybe you have pre-printed stationary that can only take a certain number of lines, maybe you have a functional requirement for it. For whatever the reason id the functional folks have given it to you to implement.  Up until now there has been a template floating around that I think I let loose that shows how this can be done for invoices. There is little explanation of whats going on and how it's done. I'll try and make ammends to those of you that may have gotten a little lost but plugged it in anyway and it worked so what the heck.

I have started off small and we'll build this template up into a full invoice format that can run against the standard AR Oracle Report in 11i (RAXINV) ... those of you that are not EBSer's or are not interested in the invoice format, dont worry, you can follow along and apply the same principles to any data set. Here's the features out template is going to have.

1. A fixed number of rows per page,
2. 'Filler' space for lines, because that last page may only have 3 lines but you want to maintain the layout
3. Page totals or 'Continued' in place of a total
4. Last page only content
5. Header and page number resetting as we hit each new invoice in the batch.

For all of the explanations we are going to use the attached XML. Its a full invoice batch, thats multiple invoices having multiple lines.

The Data

Lets start with number 1, for the fixed row enumeration we are only interested in the lines section of the XML, so we can focus on this portion Im going to ignore the rest of the XML. We are only interested in the G_LINES group of the XML structure:

<G_LINES>
 <LINE_NUMBER>1</LINE_NUMBER>
 <LINE_CUSTOMER_TRX_ID>1903</LINE_CUSTOMER_TRX_ID>
 <LINE_CUSTOMER_TRX_LINE_ID>1801</LINE_CUSTOMER_TRX_LINE_ID>
 <LINE_CHILD_INDICATOR>0</LINE_CHILD_INDICATOR>
 <LINE_TYPE>LINE>LINE</LINE_TYPE>
 ...
</G_LINES>
<G_LINES>
 <LINE_NUMBER>2</LINE_NUMBER>
 <LINE_CUSTOMER_TRX_ID>1903</LINE_CUSTOMER_TRX_ID>
 <LINE_CUSTOMER_TRX_LINE_ID>1817</LINE_CUSTOMER_TRX_LINE_ID>
 <LINE_CHILD_INDICATOR>1</LINE_CHILD_INDICATOR>
 <LINE_TYPE>LINE>LINE</LINE_TYPE>
 ...
</G_LINES>

There are obviously a lot more elements and a lot more lines but all we are interested in is, the grouping and the line type for now.

The Template

Now take a look at the template; if you just look at the table and nothing else it all looks pretty normal. We have a for-each, some fields followed by an end for-each ... straightforward stuff right. You'll notice the for-each has a little more going on thou and there's that 'if' condition:

<?for-each:$invLines?>
<?if:position()>=$start and position()<$start+$lpp?> 

I'll come back to these, lets look first at the fields above the table:

LinesPerPageVariable - this does what it says on the can and sets up the number of lines we want to see per page and assigns it to a variable 'lpp'. Now remember native XSL 'variables' are not like other language variables, think of them more as constants.
        
        <xsl:variable name="lpp" select="number(15)"/>

LinesTreeVariable - this holds not just a single value but a complete tree of values. Word of caution here, if you are going to do this you need to be aware that the XSLT engine is going to load this tree into memory ... so do not load huge trees. In our completed invoice template we will only load the lines tree for each invoice. In this example we are loading all the lines in the XML regardless of invoice ... thats OK thou, our XML is small, just be aware of what are doing here.
We have a variable 'invLines' and we are loading the members of the G_LINES group where the TYPE is equal to 'LINE'. Notice we use an XPATH expression to do this. We also use the 'incontext' command to ensure we are picking up the lines only for the current position i.e. within the current invoice. For this example remember we have no invoice header so we pick up all lines into the tree.

          <xsl:variable xdofo:ctx="incontext" name="invLines" select=".//G_LINES[LINE_TYPE='LINE']"/>

FEinvLines - this is the first loop we need to to go over the G_LINES group we created earlier. Although there is only a single group we need to iterate over it so we can set up a variable to hold the starting line position.
The if statement is checking if the record position we have reached modulizing with the line per page count equals zero i.e. we have reached the first record the create a variable (constant) called 'start' and initialize it with '0'.

<?for-each:$invLines?>  
 <?if:(position()-1) mod $lpp=0?>  
   <xsl:variable name="start" xdofo:ctx="incontext" select="position()"/>

Now we get into the table, the first field in their is another for-each but this time we are going to loop over the members of the invLines tree. Notice the use of the '$' to reference the variable.

<?for-each:$invLines?>
 <?if:position()>=$start and position()<$start+$lpp?>

The if statement here is checking that the current record pointer 'position()' is either greater than 'start' ie the first record or less the 'lpp' value we set up earlier. If it is then show the record otherwise not. the rest of the table is standard stuff.

Now we wanted a fixed number of rows per page and the logic above will provide that but of course the template needs to signify the need for a page break after the alloted number of rows have been shown. Looking at the 'Page Break' field:

<xsl:if xdofo:ctx="inblock" test="$start+$lpp<=count($group)">
 <xsl:attribute name="break-before">page</xsl:attribute>
</xsl:if>

We have an 'if' statement wrapped around the page break instruction. This is just like the if in the table, if the specified number of rows has been met then insert a page break.

So thats fixed row enumeration, hope things are a little clearer. Next we'll add in the header for th invoices so we can then ensure we have a fixed layout per page of each invoice. Complete template, data and PDF are here.

Continuing on from yesterday, today we'll add in the repeating header and a filler for the invoice lines. Imagine you have pre-printed stationary, yesterday we tacled how to get X number of lines on each page, now assume that on the last page of the document you need a summary at the bottom of the page. If that last page only has 5 rows of data and we were printing 20 rows on the previous pages then once the 5 rows are rendered the summary will then render directly under the five rows i.e. not at the bottom of the last page ... still with me? With 5.6.2 we introduced the concept of a last page only command; we could use that here but I want to continue with the idea of filler rows and we'll tackle the last page only option later.

Taking a batch invoices as our example we can add the header and the filler rows.

Reset your Head

For the header we want to have say an invoice header with page numbering, etc. As we hit a new invoice we want the header information to change and the page numbering to reset. To do this we can use the '@section' option for the for-each command. We create the header layout in the MSWord header.

Notice we can not put formfields in to the MSWord header section so we either need to type in the commands or use a header template and reference it.
The only extra fields added are:a
FE G_INVOICE - this starts the looping over each invoice using the @section to reset the header information as each new invoice is reached.
         <?for-each@section:LIST_G_INVOICE?>
EFE - this closes out the loop above

Running this template (Inv_Header.rtf) gives us two invoices with a repeating header on each page with page numbering per invoice.

Spaced Out

Now lets add the logic for the filler rows, you can see the new fields and empty table just below the lines table in the Inv_Header_Spacer.rtf template. There are two new fields
Filling Out Space - this contains the logic to check if the number of rows rendered above equals the lines per page variable. If not then insert the table row until it does.
<?if:not(count($invLines) mod $lpp=0) and ($start+$lpp>count($invLines))?>
  <?for-each:$invLines?>
     <?if:position()<$lpp - (count($invLines) mod $lpp)?>

then we have the blank table row, then 
End Filling - this just closes out the for-each and the if statements. 

I just added a static string 'End of padded lines' to check that we are actually padding. Complete samples available here.

Next, page totals and last page only ...

Finishing up on this series we will now add a page total and a region that needs to show only on the last page of the document.

Page Totals - as many of you know we have direct support for page totals. You register a page total object in the template and then place it where its required to be rendered. The total is not actually calculated until the rendering engine starts to layout the data on the page, thats the only point at which the engine knows how many 'line' can fit on the page. In our case we know how many lines we are going to fit because we have fixed the number of rows before the rendering engine starts. We also already have those lines loaded into a variable 'invLines' so calculating a total is going to be relatively straightforward.

<?sum($invLines[(position()>=$start) and (position()<($start+$lpp))]/LINE_EXTENDED_AMOUNT)?>

Here's an XPATH expression again inside the '[ ]', this limits the number of rows we want  to sum. it's like a 'where' clause on our data.

If we wanted the word 'Continued ...' to be shown we test the current record position against the total number of lines for the invoice. If they were not equal then we would show the 'Continued ...' string.

Last Page Only - again we now have native support for this when you do not know how much data is going to fit on a page with 5.6.2. In this case thou, we know how much data is going on the page and can ensure that a section on prints on the last page. Take a look a the template, there is a table to hold the summary/special instructions for the invoice at the end of the template. All we have done is wrap an if condition around that table:

<?if:count($invLines) < $start+$lpp?>

This checks to see if we have reached the end of the invoice lines section and if so then render the summary section i.e. on the last page. Then we close out the if condition. To get the table to appear at the bottom of the last page you'll have notived that we created a two celled table and put the line table in the top cell then the summary in the lower one and then drageed the cell border down the page to position the summary at the bottom of the page.

We now have an all singling all dancing invoice template, the features we added were:

1. A fixed number of rows per page,
2. 'Filler' space for lines, because that last page may only have 3 lines but you want to maintain the layout
3. Page totals or 'Continued' in place of a total
4. Last page only content
5. Header and page number resetting as we hit each new invoice in the batch.

The final template, data and output are available here.

We can now take some of those individual features and apply them to pretty much any other document whether than be a batch of purchase orders or a single standard letter showing outstanding invoices. Hope it's been helpful.

BI Publisher Enterprise supports four security configurations (documentation).  Before changing any of your security configurations, I highly recommended that you create a local super user in BI Publisher.  This user will be able to login no matter what security configuration you choose.  I also recommend that this super user has a unique name that does not exist in your other security stores.  This makes it easier to test if your new security configuration is working.

Before going into the 4 different security models, it is important to understand a few tasks that you will need to do no matter how your configuration is setup.  These may seem a bit trivial.  However it is very important, because as you get into the different security configurations, these tasks are done in different places.

1. Create users and passwords.
2. Create BIP Required Roles.  These determine if users have permissions to do various things (e.g., edit reports, schedule reports, use the Excel Analyzer and other options).  In the docs, these are called 擢unctional Roles.?
3. Create User Defined Roles.  These roles will define what reports and data sources users have access to.
4. Manage data source permissions by assigning data sources to user defined roles.
5. Manage folder permissions by assigning folder to user defined roles.
6. Manage user access to data sources and folder by assigning users to user defined roles.
7. Manage system permissions by assigning BIP roles to users.

1. BI Publisher Security.  This means that BIP manages its own users, roles, and passwords.  This is the simplest method to setup.  In this case, steps 1-7 are all managed in BI Publisher. Also, step 2 is already done fore you.
   
2. Oracle BI Server Security.  In this case, users, roles, and role membership are managed in the BI EE server.  So, here are the steps I would go through to setup this security configuration:
1. Create users in BI EE server using the BI EE Administration tool (step 1 above)
2. Create BIP required roles in the BI EE server using the BI EE Administration tool.  The most important one to create is the XMLP_ADMIN roles.  I would create this and put you BI EE administrator in this group immediately.  This will ensure that at least one of your users will have administrative privileges in BIP.  (step 2 above)
3. Create user defined roles in the BI EE Server.  These can be roles that you are already using in the BI EE Server or they can be new roles that you create purely for BIP.  (step 3 above)
4. Login to BIP as the local super user.  Go to the administration tab and modify your Security Configuration to use BI Server security.  If you have an advanced deployment of BI EE (multiple machines or clustering), remember that when you set this up, you must tell BIP how to log into the BI EE server (not the BI EE website or presentation services). 
5. Apply your settings and restart BIP.
6. Login as a BI EE administrator and make sure the authentication works properly.
7. You値l notice now that you can no longer manager users in BIP and the Roles and Permissions page is also slightly different.
8. In BIP, assign data source to the user defined roles you created in BI EE.  (step 4 above)
9. In BIP, assign folder to the user defined roles you created in BI EE.  (step 5 above)
10. In the BI EE Administration tool, assign users to BIP roles and user defined roles. (step 6 and 7 above)
3. LDAP security.  This setup is very similar to BI EE security.  Users, roles, and role membership are all managed in the LDAP server (don稚 forget to create BIP roles and user defined roles in your LDAP server).  Access to folders and data sources is controlled in BI Publisher.
4. E-Business Suite Security.  This is designed to make it easier to use the Enterprise Edition of BI Publisher with the EBS.  EBS Responsibilities show up as roles in BIP.  I知 not an expert in EBS, so I値l let you read up on it here.

Since I知 discussing security, it痴 also worth noting that BI Publisher supports Oracle Single Sign-on.  Setup for this is documented here.

The Oracle Information Architecture Services team got in touch with me today to provide some information on their new BIP consulting services. They have a bunch of information on two BIP areas that they are currently providing.

Oracle Reports to BIP Migration

• White Paper
  
• Presentation

General BIP report development and delivery from E Business Suite, the OCS folks offer a complete end to end implementation. They can also implement bursting solutions for you.

• White Paper
  
• Presentation

If you're interested in either of these services or any other Oracle BI Services contact Sameer Singhal (sameer.singhal@oracle.com) or Kanichiro Nishida (kanichiro.nishida@oracle.com) two of the friendliest guys you'll ever meet :-)

Few questions recently on finding out who is logged into BIP so you can secure a dataset based on the user. In 5.6.2 the support was there, well sort of ... its a nasty work around but can be done. Check out the forum threads for the solution.
Im pleased to say in 10.1.3.2 it's sooo much better.

You can add the following string to your data sets, ':xdo_user_name' to limit the returned data set.
For example:

select  EMPLOYEES.LAST_NAME as LAST_NAME,
  EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
  EMPLOYEES.HIRE_DATE as HIRE_DATE,
 :xdo_user_name as USERID 
from   HR.EMPLOYEES EMPLOYEES
where lower(EMPLOYEES.LAST_NAME) = :xdo_user_name

Well, its that time of year again ... Collaborate 07 kicks off in Vegas on Sunday. Its funny I started this blog a short time before Collaborate 06 in Nashville last year; 78 articles and supposedly more than 230,000 hits later and here we are again. Sadly, I cant make the conference this year, lets call it a slight technical hitch. However, there are many sessions going on either featuring XMLP/BIP or its an integral part of a solution especially the EBS, PS and JDE sessions.
If you're going and have an interest in XMLP/BIP try and get along to one of the sessions, even if you are a Quest person and do not think theres much for you, get along to a session, many look like they are generic enough that you'll learn a ton. BIP is coming to a system near you. If you can not make a session, bug the folks down on the demo grounds for a demo. More info at the Collaborate website.

With the 5.6.3 (Apps) and 10.1.3.2 (Standalone/BIEE) release of the BIP core engine you now have the ability to get vertical with your report objects. Its a small enhancement but quite useful ... how many of you receive a documents now with a barcode striped down the side, or maybe you have a table of data, column data is narrow but column names are long, now you can just flip em.

There are some caveats - you can only flip the text or data while its inside a table cell and it only works for PDF and RTF outputs. To set the content to show vertically just
1. Select the cell,
2. Right click > Text Direction
3. Choose the direction you want.



There is another option and that is to use the 'text along a line' feature, this has been around for a while. Just:
1. Draw a line object on the template, can be straight, curve or freehand
2. Right click > Format Autoshape
3. Under the web tab enter: <?shape-text-along-path:YOURTEXT?>

When the output renders your text will follow the line.

Samples here.

BIP Enterprise 10.1.3.2 is nearly available as a standalone release on OTN. Until then you can still get it with a little effort from the BIEE10.1.3.2 installer, here are the steps, thanks to Mike:

1. Before installing Oracle BI Publisher, ensure that you have Java SDK 1.5.0 installed on your system. The Java SDK can be downloaded here: 

      http://java.sun.com/javase/downloads/index_jdk5.jsp 

Note: The Sun release name was originally Java 2 Platform, Standard Edition 1.5.0 (J2SE Development Kit 1.5.0). The numbering system changed to 5.0 (J2SE 5.0). Both 1.5.0 and 5.0 refer to the same platform and product.

Get and install JDK 5.0 Update 11 

2. Download Oracle Business Intelligence Enterprise Edition 10.1.3.2 CDs:

      Oracle Business Intelligence Enterprise Edition

Once you have the files downloaded, unzip them and start the Oracle Installer. It can be found in the disk 1 folder. For windows users, this would be:

WindowsServerOracle_Business_Intelligencesetup.exe

During the install, Select a Basic Install type ? this will install a stand alone OC4J that will be used to run BI Publisher.  You will have the option select a setup type. Select the setup type ?Oracle Business Intelligence Publisher?.

For more information on installing Oracle Business Intelligence Enterprise Edition 10.1.3.2, please reference

Infrastructure Installation and Configuration Guide

or

Oracle Business Intelligence Infrastructure Quick Installation Guide.

After you finish the install, BI Publisher should be up and running. You can of course control the application through the Enterprise Manager interface. .

You can launch BI Publisher on Windows from

Start > Programs > Oracle Business Intelligence > BI Publisher

or open the following URL in a web browser:

http://localhost:9704/xmlpserver

For information on BI Publisher, reference

Oracle BI Publisher User's Guide

Deploying Oracle Business Intelligence Publisher in J2EE Application Servers

BI Publisher on OTN

Well, its a first for me, 8" of snow and accumulating and it's the 24th of April.

• Integration with Oracle BI ? Dashboard, Answers, Advanced Reporting and Analytical Server
  
• Report Bursting
  
• Template Builder for Word: crosstab builder and new chart builder
  
• Template Builder for Word: Online integration
  
• Upload and scheduling of EXCEL Analyzer reports
  
• Scheduler support expanded to include MS SQL Sever and IBM DB2 as well as Oracle DB
  
• New consolidated scheduler/history screens ? schedule public and private
  
• Administration UI for configuration option and fonts ? for system and report specific.
  
• File data sources
  
• New templates: eText, XSL stylesheets
  
• Query builder: modify SQL queries, support MS SQL server, Oracle BI EE
  
• Search reports
  
• Support for PDF printing from APEX
  

This release includes CDs with an OUI installer for Windows, Redhat Linux, Oracle Enterprise Linux, and SUSE Linux.  The installer provides a Basic install that provides a stand alone OC4J and an Advanced install to install into an existing 10.1.3.1 Application Server.  Installers for Solaris, HP and IBM coming soon.

For installation into non-OC4J based J2EE application servers and to Oracle Application Server for platforms not yet released, please follow the manual install instructions.

Get the download here.

All of the latest help and user guides are posted here.

Happy Spring!

I think most folks see the advantages of BIP/XMLP no matter what the flavor. It's still relatively new, but XMLP has actually been around in the E Business Suite for coming up 3 years in May. It's still evolving yes, but the reporting engine itself I think is now proven. We have customers generating more than 10,000 invoices an hour, others generating huge 10,000 page documents and its not just listing reports and invoices, its certificates, shipping labels, marketing campaigns even books.

The common thing across all of these examples is that the layout templates, for the most part have had to be custom developed by IT departments or consultants. The real value add for BIP/XMLP is the templates and those provided out of the box for you the customer by the development teams. In EBS 11.5.10 there are about 200, in PeopleSoft 9 there are a smattering of templates available. For EBS that is about to change ...

With the R12 there are more than 850 templates available across more than 50 products ... I have put together a document (using XMLP of course) listing all the available templates, you can get that here. 'Hang on', I hear you cry what about the other near 2000 reports that you guys ship to us? Good news here too, we currently have a project going on in development to migrate the rest of the Oracle Reports based concurrent programs that you will be able to take advantage of later this year ... so thats going to be nigh on 2500 templates to customize to your hearts content.

What about my reports?

Well with R12 we have released migration utilities to help you migrate your own Oracle Reports to the XMLP platform in EBS. They are documented but here's the document itself. We take your RDF binary, use Oracle Reports to convert it to an XML format, parse that XML and generate a Data Template (extraction), supporting plsql packages and an RTF Template (layout). We are not guaranteeing 100% conversion, you are likely going to need to tweak the layout a little and deploy the plsql but we'll get you a long way along the process of conversion.

What about us 11i folks?

Right now, no plans to backport the templates to the 11i code line ... we need some carrots to get you to upgrade. But the migration scripts will be released with or very soon after the 5.6.3 release is made available. They come with a caveat, you folks use Oracle Reports 6i, we need 9i or higher available to carry out the conversion ... its not a biggy and I'll document the 'how' once 5.6.3 hits the streets.

What about us non-Apps folks?

Manish, fair comment. For those of you that are not in the EBS bucket theres hope for you too. The scripts are planned for release with the 10.1.3.3 release later this year. We needed to do some more work for you to generate you a complete XDO file. Still going to need at least the 9i reports executable somewhere on the disk but the principle will be the same. 

Looking to the future a little, there are plans on the table to provide a Template Bank. This will be a hosted version of the Template Manager with some bells and whistles. The plan is to make all of the development delivered templates available via this application, then hopefully consulting and of course you folks out there building templates. Should be a great big template sharing community!

I have posted about the new server side versions of BIP (10.1.3.2.x) and EBS (5.6.3) being available now ... what I neglected to mention was the availability of a new version of the Template Builder for Word. There are some new enhancements for both BIP and EBS user alike:

• Crosstab Wizard




• Multiple Levels


• Level and report Totals


• Enhanced Chart Dialog




• Multiple Line Charts


• 3D Charts with gradient fills


• Real time preview


• Table wizard


• Translation testing support


• Template Validation Checker

Those of you using BIP Server you get a few more bells and whistles. Rather than download sample data to your desktop and manually load it to the template builder, you can now connect to the BIP server, find a report, create a new template or modify an existing one, test and then upload to the server from the builder ... makes the whole development process much smoother. 

You can get the new version from the OTN download page, http://www.oracle.com/technology/software/products/publishing/index.html

Get Templating !

 

I covered what I thought were the tougher 2D barcodes a while back , we documented the simpler codes (and the 2D's for that matter ... same principle) but I think because we decided to break the doc into two sections, one for the template designer and another for the developer I think we confused things a little and caused a disconnect for most folks. So to make ammends until we revamp the docs. Today, I'll cover the simple stuff ...

Simple Barcodes

If you are using something like a code 39 barcode it may not require any pre-processing on the data to be 'coded'. This is the simplest form of barcoding. Firstly you need to build and test on your desktop. Here are the steps:

1. Install the barcode font in your WINDOWS_HOME/fonts directory
   
2. In MSWord highlight the field/data you want to have the barcode applied to
   
3. To test the output you need to let the publisher engine know where the font is on the desktop. Under the cleint install directory you'll find a config directory and under there an 'xdo example.cfg' file. Open it up and you'll see a sample entry for a font:
   
   <font family="GnuMICR" style="normal" weight="normal">
    <truetype path="C:\WINNT\fonts\GnuMICR.ttf" />
   </font>
   
   
4. Just change the entry to point to your font and for the family attribute pput in the name you see in Word when selecting the font.
   
5. Rename the file to xdo.cfg
   
6. Test your template, you should see the barcode font being used to replace the data in the output 

Now you need to deploy to the server. The template is simple enough, but we need the server publisher engine to know the location of the font. For both EBS and standalone there is an interface to load the font to the server, the steps are now pretty straightforward. In EBS just use the Font Manager, I covered how to load fonts in this article. For standalone:

1. On the operating system copy the font to the JDK/fonts directory
   
2. Login as an administrator and go to Admin tab > Font Settings
   
3. Now fill the details out about your font and select the font file from the drop list.
   
   
4. The font is now available to all templates in the system.

We have had several requests on how to write log messages while publisher is processing a job. Its not a published API ... it ought to be but for now ...

We have a class you can use in your java code called Logger, just use the following import:

import oracle.apps.xdo.common.Logger;

General Usage

// Log a message 
Logger.log("Copying string from buffer xyz to buffer zyx.", Logger.STATEMENT); 
// Log a message with the current object 
Logger.log(this, "Copying string from buffer xyz to buffer zyx.", Logger.STATEMENT); 
// Log an exception Logger.log(exception); 
// Log an exception with level. 
Logger.log(exception, Logger.EXCEPTION); 
// Log an exception with level and the current object 
Logger.log(this, exception, Logger.EXCEPTION); 

Logging level

• UNEXPECTED
  
• ERROR
  
• EXCEPTION
  
• EVENT
  
• PROCEDURE
  
• STATEMENT
  
• OFF

// Set the system logging level to EXCEPTION * 
Logger.setLevel(Logger.EXCEPTION); 
// This log message will not be logged because the message 
// logging level is lower than EXCEPTION 
Logger.log(this, "Copying string from buffer xyz to buffer zyx.", Logger.STATEMENT); 
// This log message will be logged because the message 
// logging level is the same or higher than EXCEPTION
Logger.log(this, "Copying string from buffer xyz to buffer zyx." , Logger.EXCEPTION); 

How do I get to the log?

Standalone - In standalone versions (10.1.3.2) you can turn logging on for the install via the Admin > Server Configuration page. Here you can turn the logging on or off.

EBS - this is a little more involved to get the logging running ...

1. Create a file called xdodebug.cfg
2. The contents of the file should be:

  LogLevel=STATEMENT 
  LogDir=c:temp 
 
  LogLevel specifies the level of logging, this should always be set to STATEMENT to maximize the information provided in the log.
  LogDir specifies where the debug files should be written to, this needs to be a writable directory for XMLP.

3. This file should then be saved to the $JRE_TOP/lib directory.

Once you have the logging turned on you can then run the process thats causing issues and your messages will be written to the log. Just remember to turn it off afterwards.

Now get loggin'

This week I got a great surprise in my mailbox, no, I had not won the Readers Digest grand draw, I got a mail from Ambarish Mitra, he responded to my plea on the forum for blog content. It's great getting information, tips and walkthroughs out through this medium, its a very free format and I can be very reactive and responsive to questions on the forum and through mail that I think might have a wider appeal. But as you might imagine posting regularly to a technical blog is not a small undertaking. I have consciously stayed away from a Dexter diatribe, yeah I witter on about the weather sometimes but Im British for heavens sake. I do have a more light hearted blog about our new life in Colorado but here all you'll get is good ol technical content that should be useful to you when working with BIP.

So, Ambarish's mail was very welcome, he spent some time reasearching and testing integrating OpenLDAP with BIP. He has documented the set up below, if you try it out and have comments please let us know using the comment link and we'll get back to you. 

If you, dear reader, have anything you wish to share, tips, hints, experiences, anything at all, drop me a mail with some notes, files, etc and I'll format it. You'll have my eternal thanks and get on the 'BIP Bloggers' list (see right) and become rich and famous ... OK, I can not promise riches and maybe it will be infamy.

Open LDAP Integration with BIP

References

1) Oracle® XML Publisher Enterprise User痴 Guide Release 5.6.2 Part No. B25951-01 April 2006
2) www.openldap.org 
3) RFC 2849: LDIF format http://tools.ietf.org/rfc/rfc2849.txt

Software/Platforms

Redhat Linux AS 4.0 box:
- Oracle DB server 10.1.0.5
- XML Publisher (Also, called BI publisher) 5.6.2
- Tomcat 5.5.16
- openldap 2.3.8

Windows 2000
- Template Builder for word (Windows) version 5.6 Build 45

Setup

1) Install and configure openldap according to the instructions given in www.openldap.org; ensure that all dependencies are met.

Configure the slapd.conf file in this manner: (only snippets presented, see man slapd.conf(5) for more details)

Note: In the following,
- replace $LOCAL with whatever is your directory,
- in suffix, change "company" to your company name
- change rootdn and rootpw.
- Change index according to your convenience. Read the man pages and openldap admin guide for more information.

<file snippet>
include         $LOCAL/etc/openldap/schema/core.schema
include         $LOCAL/etc/openldap/schema/cosine.schema
include         $LOCAL/etc/openldap/schema/inetorgperson.schema

....

pidfile         $LOCAL/var/run/slapd.pid
argsfile        $LOCAL/var/run/slapd.args

....

database        bdb
suffix          "dc=company,dc=com"
rootdn          "cn=Manager,dc=company,dc=com"
...
rootpw          oracle
...
directory       $LOCAL/var/openldap-data
# Indices to maintain
index   cn,sn,uid       pres,eq,approx,sub
index   objectClass     eq

....
</file snippet>

2) Upload some user profiles in the LDAP server.

Create an LDIF file according to the following:

Note:
- change the company according to what you have put in slapd.conf for suffix.
- change the cn=Manager according to what you have put in rootdn in slapd.conf
- change the uniqueMember according to your directory

<file snippet>
# Base DN.
dn: dc=company,dc=com
objectClass: dcObject
objectClass: organization
dc: company
o: My Company Name
description: My Company Name, Location

# Organizational Role for Directory Manager
dn: cn=Manager,company,dc=com
objectClass: organizationalRole
cn: Manager
description: Manager

# Put any other entries that you would like to add to the directory.
# Follow the LDIF format according to the RFC.

# The following entries are needed for the XMLP integration. That is what the
# XMLP users guide says (Section 5.5). Also, the last entry is for a new role.

dn: ou=Enterprise Roles,dc=company,dc=com
objectClass: organizationalUnit
ou: Enterprise Roles
description: Enterprise Roles (XMLP publisher)

dn: cn=XMLP_ADMIN, ou=Enterprise Roles, dc=company,dc=com
objectclass:groupOfUniqueNames
uniqueMember:cn=Manager,dc=company,dc=com
cn: XMLP_ADMIN

dn: cn=XMLP_DEVELOPER, ou=Enterprise Roles, dc=company,dc=com
objectclass:groupOfUniqueNames
uniqueMember:cn=Manager,dc=company,dc=com
cn: XMLP_DEVELOPER

dn: cn=XMLP_SCHEDULER, ou=Enterprise Roles, dc=company,dc=com
objectclass:groupOfUniqueNames
uniqueMember:cn=Manager,dc=company,dc=com
cn: XMLP_SCHEDULER

dn: cn=XMLP_ANALYZER_EXCEL, ou=Enterprise Roles, dc=company,dc=com
objectclass:groupOfUniqueNames
uniqueMember:cn=Manager,dc=company,dc=com
cn: XMLP_ANALYZER_EXCEL

dn: cn=XMLP_ANALYZER_ONLINE, ou=Enterprise Roles, dc=company,dc=com
objectclass:groupOfUniqueNames
uniqueMember:cn=Manager,dc=company,dc=com
cn: XMLP_ANALYZER_ONLINE

dn: cn=XMLP_DEMONSTRATOR, ou=Enterprise Roles, dc=company,dc=com
objectclass:groupOfUniqueNames
uniqueMember:cn=Manager,dc=company,dc=com
cn: XMLP_DEMONSTRATOR

</file snippet>

3) Now, add this to the LDAP:

ldapadd -f <filename>.ldif -x -D "cn=Manager,dc=company,dc=com" -w oracle

where, -f <filename>.ldif is the LDIF file that you wish to upload
 -x is to use simple authentication, not SASL
 -D is the credential dn (rootdn)
 -w is the password (rootpw)

See the man pages of ldapadd/ldapmodify for more information.

4) Use any LDAP client to see that the data is added to the directory.

5) Now, open the $TOMCAT/webapps/xmlpserver/WEB-INF/xmlp-server-config.xml file, and edit/add the following:

Note:
- change the LDAP_server to your hostname
- change the port to where the LDAP server is listening (default: 389)
- make all other changes necessary

<file snippet>
....

<property name="LDAP_PROVIDER_URL" value="ldap://LDAP_server:port/"/>
<property name="LDAP_PROVIDER_ADMIN_USERNAME" value="Manager"/>
<property name="LDAP_PROVIDER_ADMIN_PASSWORD" value="oracle"/>
<property name="LDAP_PROVIDER_USER_DN" value="dc=company,dc=com"/>
<!--  <property name="LDAP_PROVIDER_FACTORY" value="com.sun.jndi.ldap.LdapCtxFactory"/>-->
<property name="LDAP_PROVIDER_GROUP_SEARCH" value="(&(objectclass=groupofuniquenames)(cn=*))"/>
<property name="LDAP_PROVIDER_GROUP_SEARCH_ROOT" value="dc=company,dc=com"/>
<property name="LDAP_PROVIDER_GROUP_ATTR_NAME" value="cn"/>
<property name="LDAP_PROVIDER_GROUP_ATTR_MEMBER" value="uniquemember"/>
<property name="LDAP_PROVIDER_GROUP_ATTR_DESCRIPTION" value="description"/>
...
</file snippet>

6) Restart the tomcat server.

7) Now, open a XML window and login with: username/password
 where:
  username is "Manager" (rootdn without the cn=)
  password is "oracle" (rootpw)

and you're done ...

As regular readers will have noticed we have a new look and feel ... playing with CSS and templates has been 'interesting' ... still, may prove useful elsewhere. I have to thank Martin from the ATG Doc Tech team for the banner image ... I have somewhat mangled the original into a montone format that really does not do the original justice, an awesome picture of Oracle HQ. Check out his other pictures on Flickr, some great photos. 

The useful part of this post, Martin's image aside, is the fact that I have added an email feed for the blog. You no longer need come here every day to check for an update, you can get a mail whenever we have something new. Just fill out the 'Get a BIPScription' box on the right hand side bar with your email, confirm the return email and you're all set, BIP updates will be coming to an inbox near you.

Charts ... the bane of my templating life ... our chart dialog is getting better and better but you're still going to need to tweak things that we are just not going to get to anytime soon. Case in point (thanks for the question Neida), I have a pie chart that looks like this:

but I want this

See the percentage values have been rounded up, you'll laso notice that the actual chart is bigger in its frame (yes the image is larger but the chart itself fills the plot area better), its not a trick, because the labels take less room then the actual chart can fill the space. We're concerned with the rounding for this post. Its not the XML data that we need to round but we need to instruct the charting engine to do the rounding for us on the calculated pie slice values. I had to dig and dig, I have written in the past about the graph.dtd and trying to decode it using an XML editor and fooling JDeveloper into helping us visually but for this little gem I thankfully found the answer on the Oracle Reports graph FAQ.

http://www.oracle.com/technology/products/reports/htdocs/faq/Graph_FAQ_with_style.html

The solution for this particular issue is to override the default slice labels thus: 

<SliceLabel>
<ViewFormat decimalDigit="0" decimalDigitUsed="true" decimalSeparatorUsed="true" />
</SliceLabel>

Please, if you have charting questions take a look see here, there are some 40 questions and answers and you can use most of them in your charts. If you can not find the answer drop us a message on the forum and we can help. 

Every one of you I meet has a set of internal customers that are never quite satisfied with the output you produce, they dont want PDF or HTML, CSV only just about cuts it ... Im talking about finance users. In their world, Excel is king, they want to live and breath it every day. I have a friend who used to work in a finance department ... his life was spent day in day out in Excel, he built the most fantasticly complex spreadsheets that aggregated data from all over the company, pulling from dbs, flat files and other spreadsheets ... it was a marvel of modern Excel engineering. He had got him self a job for life so when he left the company there was pandamonium ... there's a lesson in there somewhere.

Now you have BIP to do all that aggregation for you across dbs, files and Excel and the spreadsheets can become simpler again. 

In the 5.6.2 Standalone (sorry Apps users) we gave you Excel Analyzer, with the 10.1.3.2 release we have added an extra peice of what we consider to be very useful functionality. For those of you not familiar with the Excel Analyzer, you will get a button you can hit on the BIP UI. This generates a binary Excel file that can be downloaded to their disk and opened.

On opening and taking a first look you'll think ... so what? You gave me a flat list of of data in a sheet, I can get that from any other tool.

 But thats just the start, you or your users can now go in and use as much Excel functionality as they wish, charts, pivot tables, what if, etc all based on that one sheet of data which can of course be hidden. So with a little effort we can get the data to something like this.

OK, great ... but so what? Well now you can keep that spreadsheet or distribute to colleagues and say next week, month, quarter you can then reopen the file, connect to the server and refresh the data ... now thats something different. When you download the file there is a new BI Publisher menu that lets you do a bunch of stuff.

This will let users that have access to the report data connect to the server and refresh that data.
On top of that, if there are parameters associated with the report then those will be rendered on a tool bar for the user to set prior to refresh

Even better you can now deploy the Excel template back to the server via the BI Publisher menu dialog and make it available to be scheduled as a regular report.

 Of course once its 'scheduleable' (is that a word?) you could have the Excel file mailed to a bunch of folks on a regular basis eliminating the need for them to even bother logging in from Excel every month ... the report is there, in the format they want i.e. Excel in their inbox.
You are now the star of the department ... the only downer is that you are now expendable because of Publisher ... sorry :0)

As an experiment I have created a thread in the Publisher forum for any discussion you might want to share on this article. We are painfully aware of how poor the commenting thang is on this blog host ... things will change. But in the meantime use the following link on the OTN forum:

http://forums.oracle.com/forums/thread.jspa?threadID=508714

You might not have heard of 'Bipnalysis' before ... in fact I know you will not of because I have just coined the term in the hopes that it might become a 'googlewhack' once the crawler finds this entry ... but beware, you will waste hours on this venture. Its a term to describe, strangely enough, 'doin' some data analysis with BI Publisher' ... it will be in the Oxford English Dictionary before you know it.

I mentioned the Excel Analyzer yesterday, today, the other analyzer thats available, thats the Online Analyzer. I was discussing the future of this component this morning with my manager, its a cool component and he uses it every week for doing on the fly bug and enhancement analysis. I always get oooohs and ahhhhs when I first show it to customers but it lacks some vital functionality and awkward questions soon ensue, I'll come back to that.

The analyzer is basically an online pivot table a la Excel, when you invoke it you get a window like this:

You're now in a drag and drop environment, the items on the left had side list can be dragged into one of the active areas on the right e.g. page item, row item, column item, etc. So you can take the flattened data and very quickly build a very interactive cross tab report.

As you drop items, the engine will effectively turn your flat fields into dimensions on the fly

Now it becomes interactive, you can expand and collapse column and row headers.

Even change the summarization calculation

But theres a catch to all this cool stuff ... you can not save this defintion for later use. There's an argument that it is very easy and fast to build the cross tab and if you want to do is do some quick and dirt analysis its great but even my manager has to rebuild the self same report every week because at the end of every meeting he has to throw it away.

So first on the list of enhancements is to be able to save this 'view' of the data for future use ... then with my PM hat on I get carried away. What if when I clicked that analyzer button, it asked me which 'view' I wanted or if I wanted to build a new one. What if you could store multiple views of the data and then show them on multiple tabs across and down the page; what if I could incoporate these views into a regular BIP report and email them off; what if I could pull in another data source or maybe some historical data of the same report I had been running over the last year ... calming back down. We're at least gonna start with be able to save the current view ... and for now I think that would make this feature really useful and the ooooohs and ahhhhs will not be followed by awkward questions but smiling faces. 

Ever since we first released BIP in 2004 or what was back then, for the old schoolers amongst us, XMLP I have been asked, 'Where's the training?'
We were a little stymied by our VP telling folks that they did not need training, XMLP was/is that easy. For the most part, its true, but many people like some hand holding, beginners docs, etc - Im in that camp. So began an uphill battle (maybe too strong a word) to get an XMLP training course from Oracle University. Its finally arrived and new locations are being added constantly, check out the OU site for more details:

Oracle XML Publisher Fundamentals  - Locations

The development team have had a lot of input and Bill Sawyer, one of our best curriculum writers has put together a great course which is informative and best of all useful. A high level agenda:

I got an update from Leta on a more updated agenda

Introduction 

• Understand the course agenda


• Describe the Oracle XML technology stack


• Describe the uses of Oracle BI Publisher


• Explain the limitations of classic tools


• Describe the advantages of Oracle BI Publisher


• Describe the basic structure of Oracle BI Publisher

Oracle BI Publisher ArchitectureDescribe the components of Oracle BI Publisher

• Describe the architecture of Oracle BI Publisher


• Describe the technology of Oracle BI Publisher

Introduction to XML Standards

• Describe XML, XSL, and other X Standards objects


• List the components of an XML document


• Create a well-formed XML document


• Describe XML namespaces


• Describe a DTD


• Describe XML Schema


• Describe XML Path Language (XPath)


• Describe XSL and XSL Transformations (XSLT)

Installing Oracle BI Publisher Desktop

• Install Oracle BI Publisher Desktop

Introduction to Oracle BI Publisher Desktop

• Create and use a basic RTF template for Oracle BI Publisher using Oracle BI Publisher Desktop

RTF Templates

• Explain the capabilities of an RTF template in Oracle BI Publisher


• Describe the two methods to create Oracle BI Publisher RTF templates

Advanced RTF Template Techniques

• Describe the advanced capabilities of  RTF templates

Other Templates

• Describe the basics of a PDF template for Oracle BI Publisher


• Describe the advanced capabilities of PDF templates for Oracle BI Publisher


• Describe the basics of an eText template in Oracle BI Publisher

Installing Oracle BI Publisher Enterprise

• Install Oracle BI Publisher Enterprise 5.6.2


• Install Oracle BI Publisher 5.6.2 patches for an E-Business Suite instance

Oracle BI Publisher Enterprise Administation

• Administer the Oracle BI Publisher Enterprise

BI Publisher Enterprise

• Access the Oracle BI Publisher Enterprise


• View and schedule reports


• Create a new report

Translating Reports

• Describe the options for translating reports using BI Publisher.


• Create the translation files using BI Publisher Desktop.


• Create the translation files using BI Publisher Enterprise.

Building a Data Template

• Understand the XML Publisher data template


• Create a data template


• Associate that data template to a report

The E-Business Suite Development Process

• Understand the process of creating an BI Publisher solution


• Use an existing Oracle Report as an XML source for XML Publisher


• Create a data definition registration


• Create a template registration


• Upload a template


• Run an BI Publisher report

There is something there for EBS and BIP Enterprise users alike. Those of you looking for JDE and PEopleSoft training, its in the works and wiil be found on their respective training sites.

I have to say, because of the long wait for the course many of you have gone out and got stuck in and learnt it yourselves. Its called XML Publisher Fundamentals - if you know how to add a barocde, or know what this command will do in a template, <?for-each:INVOICE_LINES[TYPE!='TAX'] or you know what the OPP in EBS or you already understand how to add a BIP report to a BIEE dshboard then I think you are beyond this course. Its an excellent introduction to the technology. Its now partially my responsibility to get more advanced content up here on this blog and onto OTN ... any requests?

I have seen several threads lately on the forum asking along the lines of -  Where the heck are the checkboxes on my report that I included in my template? Well, they are there they are just hidden, sort of. When you add a check box field to a template the Publisher engine interprets them correctly i.e. should they be checked or not, but the problem is, that none of the standard PDF fonts or the fonts publisher ships have a decent checkbox glylph. Therefore, if you're lucky you get a filled/unfilled diamond shape (if the publisher fonts are accessible) or you get nothing at all.

There is a solution to all this and its down to configuration, if you let the engine know where the checkbox font is and which glyphs represent 'checked' and 'unchecked' then it will pick them up and use them in your output.  

The method to use to configure the font and glyphs is going to depend on your platform. I tested with the Wingdings ttf font and used characters 253 (unchecked) and 254 (checked).

To get the right glyph number:
1. Open MSWord and use Insert->Symbol.
2. Now find the font you want
3. then the glyphs you want, the glyph characgter code will be in the bottom right hand corner of the dialog.

Dont forget to ensure you are licensed to use the font you choose for the check box glyphs.

Config File - EBS (pre-5.6), JDE, PS and if your using the APIs

Your documentation should tell you where the config file should located and what to put in it. For the check boxes you need:

1. The font location, we need to tell the publisher engine where the Wingdings font is located:

<font family="Wingdings" style="normal" weight="normal">
 <truetype path="c:\windows\fonts\wingding.ttf"/>
</font>

2. The glyphs to be used. Here we specify the font family name ie 'Wingdings' and the 253/254 glyphs

<property name="rtf-checkbox-glyph">Wingdings;253;254</property>

Config Manager - EBS (5.6 and above)

1. Load the font to the manager - thats been covered here
2. Update the 'Characters used for checkbox' field with the values you want ie Wingdings;253;254

Now when you run outputs to PDF you will see your checkboxes as expected and no longer be boxless.

I have to admit we missed documenting a new piece of functionality in the 10.1.3.3 release that I also have to admit that I only found out about yesterday ... thats rapid development for you.

If you want to integrate BIP with another application I have documented how to call BIP via a URL but in 10.1.3.3 we added another switch. In prior releases you could call a report via the URL but the complete UI would render. With 10.1.3.3 the _xmode switch was added, this allows you to control what gets rendered via the url.

To use the switch just add it to the URL for example:

http://127.0.0.1:8888/xmlpserver/HRM/Employee+Salary/Employee+Salary.xdo?_xmode=X

where X is the mode number. The modes are:

0 - the complete BIP UI

1 - BIP UI showing template and output format drop boxes, the View and Export button

>

2 - BIP UI showing all in mode 1 plus any parameters

Useful stuff ... enjoy!

Trying this post again ...

We have had several enquiries about deploying BIP server to a BEA middle tier, yes it can be done, we just have not certified or documented the process yet. Shinji, who leads the dev team spent some time going through the process and made some notes on some of the 'gotchas' to look out for.

He successfully deployed BI Publisher 10.1.3.2.1 on BEA WebLogic 8.1. and did not find any major issues. As I mentioned official certification is scheduled and will be on the 10.1.3.3 release (later this year). We will still support WebLogic for previous versions i.e.10.1.3.2.x.

Some notes ...

There are couple of things you have to aware of when you deploy BIP on WebLogic.

1)  JRockit  should not be used
JRockit SDK 1.4.2_08 (bundled with WebLogic8.1) has an issue in RandomAccessFile class
and it causes a problem generating HTML, RTF and Excel outputs in BIP reports.

Customers should use Sun SDK 1.4.2 or above instead of JRockit.

2) Use 'Exploded Archive Directories'

When you deploy xmlpserver.ear(or xmlpserver.war) on WebLogic by uploading  it from
WebLogic console, it deploys the application (or web module) in an archived file format.
It is a problematic to BIP configuration because you need to update WEB-INF/xmlp-server-config.xml
manually after the deployment.

To workaround this issue, you need to use "Exploded Archive Directories" approach.
(http://edocs.bea.com/wls/docs81/deployment/overview.html#1036326)

Basically, you manually have to unarchive xmlpserver.ear and xmlpserver.war by jar command.

Here are steps to deploy xmlpsever.war on BEA WebLogic 8.1.
Please refer to WebLogic guide for addtional information (http://edocs.bea.com/wls/docs81/deployment/quickstart.html#1036505)


 1) Create an exploded archive directory.

 > mkdir C:BIPubxmlpserver.war
 > cd C:BIPubxmlpserver.war
 > jar -xvf {folder_path_to_war}xmlpserver.war

 2) Copy XMLP catalog

 > cp {path_to_xmlp_catalog}XMLP C:BIPub

 3) Update xmlp-server-config.xml

 > cd C:BIPubxmlpserver.warWEB-INF

 open  xmlp-server-config.xml and update the repository path to C:BIPubXMLP

 4) Access the Administration Console (ex: http://hostname:7001/console)

 5) Click {domain name}/Deployments/Web Application Modules from the left tree pane.

 6) Click "Deploy a new Web Application Module ..."

 7) Select "C:\BIPub\xmlpserver.war" as  Target Module and Click Deploy.

 WebLogic automatically deploy and start up the xmlp server.

Thats it, everything else on the install is standard BEA fare and should go smoothly ... good luck.

In the latest template builder release there is a very neat feature that we have been trying to implement for quite a while. Some of you may know that with later versions of MSWord when you click File > New, you get a task bar on the right hand side that allows you to search for 'Templates on Office Online'. This allows you to search for templates from the MS website and then download them to Word ... et voila you have a layout ready to go.

Now all you need to do is 'markup' the template with some data fields. Of course the data needs to match the functional purpose of the layout. Single header fields are simple enough, just use the Insert Field dialog to drop fields into the template.

The key feature is being able to drop the detail or 'line' fields and then wrap the grouping tags for you. You can now drop the line fields into the table row, then highlight the row and use Insert > Repeating Group.

You can then select the grouping level you want to wrap around the fields. Of course you will need to know the grouping level you wish to wrap buts its a good step forward and opens up the tool to those folks less knowledgeable about the data i.e. your funkies. They can now download a template, mark it up, test and deploy very quickly.

Another question on the forum that deserves a bit more than a 5 line answer. Neeraj has been trying to merge PDF documents using the Publisher APIs. From the question I think he has merged the docs but he wants to add a security password on top of the merged docs too.

I am trying to merge multiple pdf document and want to secure the output using pdf security attributes.
Is there a way to achieve this while pdf merge ?

(I am able to do so while generating the pdf using FO Processor but not while merging existing pdf files.)

There are several ways to merge PDFs using APIs, the simplest is the PDFDocMerger API. Its a pretty simple API, just get the documents to be merged into an array, specify the output file and then call the process method.

package pdftester;


 
import java.io.File;
import java.util.Properties; 
import oracle.apps.xdo.XDOException; 
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;


 public class PDFMerger {
    public PDFMerger() {
    
    // Output destination of the merged PDF document.
    File outFile = new File("c:\\temp\\mergePDF.pdf");     
 // PDF documents to be merged.
    File[] inpFiles = new File[2];
    inpFiles[0] = new File("c:\\temp\\1.pdf");
    inpFiles[1] = new File("c:\\temp\\2.pdf");
     // Create PDFDocMerger instance.
    PDFDocMerger pdfMerger = new PDFDocMerger(inpFiles, outFile);
     // Set the security property
    Properties prop = new Properties();
    prop.put("pdf-security","true");
    prop.put("pdf-open-password","welcome");
    pdfMerger.setConfig(prop);
       
    // Run the merging process.
    try {
         pdfMerger.process();
    
        } catch (XDOException e) {
            e.printStackTrace();
        }
    }
     public static void main(String[] args) {
        PDFMerger pDFMerger = new PDFMerger();
    }
}

Neeraj wanted to add a password to the resulting document. We can us the setConfig method to set the appropriate properties to set the password.

    // Set the security property
    Properties prop = new Properties();
    prop.put("pdf-security","true");
    prop.put("pdf-open-password","welcome");
    pdfMerger.setConfig(prop);

Of course you can fetch that password from some repository rather than hard coding it.
If you want to have a go, you can get the PDFs and the java from here.

Spent an enjoyable few days at HQ this week catching up with the development team and discussing what we are going to do vis a vis new features for future releases. Taking a step back we have an imminent release coming in 10.1.3.3, which although only a 'point-point' release meant really for bug fixes, etc it has a bunch of new and improved features. I can share some of them here but they have to be read with the caveat that:

The following is intended to outline our general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.   It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision.  The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle. 

OK, thats out of the way ....

• Data Template - the engine has gone through another iteration of performance improvements, it can now handle CLOB, BLOB, long and raw data types natively (hoooray!) There will be a lot of new features coming in the engine in future releases even a scripting engine to allow you to carry out calculations  and conditional logic in the data structure layer ... very cool and of course still performant.
  
• A few new RTF template features most notably the ability to add a show/hide '+' graphic into the HTML output make reports more interactive - more on this next week.
  
• Flex Templates - I have written about these in the past and finally they become a reality. If you are a 'template builder' for your company, dig into Flex your business users are going to love you for it. I have written a primer for Flex Templates but I bearly scrtch the surface with what you can do with them. You're  going to be able to build some of the most interactive 'offline' reports your users have ever seen. They will be able to schedule them to be deliverd to their inboxes, you'll even be able to burst them ... imagine your sales managers being able to interactively looks at their performance over the last year, drill into transactions, look at trends ... all off line and built by you - get flex now!
  
• Online Template Builder - this makes a beta appearance in 10.1.3.3 pure DHTML drag and drop interface. It will really put the power of template building into the hands of your true end users. The beta will have some limitations but we think you'll like where its going and if you have suggestions, enhancements, we want to know about it.
  
• Online Analyzer - this was a somewhat hidden feature in 10.1.3.2. Its was a nice interface to play with your data but there was no way to save the viewof the data you built so it had limited value. In 10.1.3.3 we now allow you build 'templates' that can be saved and retrieved by your end users.
  
• You will be able to add a digital signature to your PDF output with this release, fast becoming a very important requirement for anyone dealing/delivering PDF output to customers/suppliers/partners.
  
• The underlying formatting engine has again improved its performance and now has a multi thread mode that it can be put into, to improve performance further.

Thats not all but after a tortuous trip back from SanFran last night finally getting in at 2am Im beat ... Happy Friday!

Im working on some larger articles at the moment, getting bogged down in java and XSL code and working on some documentation for the product - hence the lack of posts. We have been getting many enquiries about BIP certification, there is a document out there that will help:

http://www.oracle.com/technology/software/products/ias/files/oracle_bi_publisher_certification.pdf

Nope, it's not a new feature of publisher, sorry, we can't help with your persona but we can help with your template images.

Most of you know about the 'dummy' image approach to putting images/pictures into your templates. You insert a dummy image into the template and then in the properties dialog for the image navigate to the Web tab and use the following command:

url:{檀ttp://www.oracle.com/images/ora_logo.gif筑

This works great as long as the dummy image dimensions match or are of the same aspect. If not then you are going to get some interesting images at best. Not the best way to introduce your company to your clients with a whacky image on your invoices that looks only vaguely like your logo.  

You have another option, that is to add a field in the template that will call the image in directly. Its a piece of XSL rather than a friendlier publisher expression but when needs must. Its fairly straight forward but must be embedded inside a formfield.

<fo:external-graphic src="http://oracleimg.com/admin/images/ocom/hp/oralogo_small.gif"/>

There is a width and height attribute that you can set if you wish but leaving them out will result in the image being brought in as is.

This will pull the graphic in via a URI, in this case a URL. Remember if you're pulling via a URL then ensure you take care of any proxy settings required to get out of your intranet.

Got this great question today ... 

Is it possible to check whether a document generated by BI publisher fits 1 page?
If it's more than one page then we want to reduce the font until it fits on a single page.

Its a nice question to which I can quickly answer 'yes - potentially' - but before I could really say 'yes' thou, I needed to put my money where my mouth was and prove it. Finally, I have put together 10mins here and there to get something that works. Its a simple approach and will probably need some more work for production but the solution shows off some of the APIs we have and how they can be used to solve this problem.

From 10,000 feet

Getting the answer to the first part can be done quite simply but only after the final document has been created. Sadly, you can not use a nice 'if' statement in the template to check if you have more than one page and if so then reduce the font size until it fits. Page numbers can not be determined until the rendering engine has done its stuff and laid the data on the page. So it has to be a post generation check.

We need a flow such as

1. Set font size to X
        |
2. Generate Output
        |
3. Test page numbers
        |
4. If page number > 1 
        |
5. Set font size  X=X - 2 or some other number
        |
6. Goto 2

7. else End

Its a nighmarish BASIC program from my distant youth ... arrrrgggghhhh!

From about 12 inches

Lets get step 3 out of the way first cos its the easiest - there is an API we can use to count the number of pages in a PDF document.

Under the FormProcessor API there is a method getPageNumber(). We are going to use it on a completed document, we need to use the setTemplate method and pass it our completed document.

    int numPages;
    FormProcessor fp = new FormProcessor();
    fp.setTemplate("c:\\temp\\1.pdf");
    try {
            numPages = fp.getPageNumber();
            System.out.println("Number of pages: "+ numPages  );
       
        } catch (Exception e) {
            e.printStackTrace();
        }

Straightforward stuff really but as I said thats the easy piece. The other part to this is to change the font size in the template until it fits on a page. I have been playing with a sample template but have put it aside for now. If we had written an XSLFO template by hand we could easily use a variable in the template for the font size and pass that each time. But we are using RTF templates, so we need some logic to update/override the font-size attribute, remember it will have been set when you create the layout in the template. Im not even sure a template can be written that updates itself during processing ... if there are any real XSLT experts out there let me know and I'll post the solution. It's going to be simpler ...

The other steps ...

For now I have dodged the issue and use a parser to look for the relevant font-size attribute and update it to its current value -2 in the generated XSLFO template file from the RTF template.

So our java logic for the whole process will be :
1. RTF -> XSL
2. XSL+XML data - > PDF
3. Count pages
4. If > 1 page then use a parser to find instances of 'font-size' and 'height'. Assign initial value found to a variable, then reduce this by 2 points for all values.
5. With the new XSL+XML -> PDF
6. Retest page numbers and repeat as necessary.

The java class I have written is not perfect but I think you can easily use it as a start for a full solution. Once the intial XSL has been generated and the resulting PDF document tested for page numbers it then parses the XSL template using a DOM parser. This looks for the 'font-size' and 'height' attributes under the 'inline' elements and knocks them down by 2 pts.

There are surely going to be templates where reducing the font-size and height are not going to be enough but if you keep things simple you can do it. You could even get into scaling images as well so everything remains in proportion. My template is simple and heres where you may need to modify the class to handle some of the other 'height' and 'font-size' attributes if present. Here's the code with some annotation:

package xdotestbed;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
 
import java.io.OutputStream;
 
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.FormProcessor;
import oracle.apps.xdo.template.RTFProcessor;
 
import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLElement;
 

import org.w3c.dom.Document;
 
import org.w3c.dom.NodeList;
 
import org.xml.sax.SAXException;
 
public class FitSinglePage {
    //declare class variables
    String rtfTemplate;
    String xslFile;
    String pdfFile;
    String xmlData;
    XMLDocument newDoc;
    
    public FitSinglePage(String rtfF,String xslF,String xmlF,String pdfF) {
        //Assign program parameters
        rtfTemplate = rtfF;
        xslFile = xslF;
        xmlData = xmlF;
        pdfFile = pdfF;
        
        //Initial RTF -> XSL conversion
        processTemplate(rtfTemplate);
        // Initial PDF generation using XSL above
        generateOutput(xslFile,xmlData,pdfFile);
        
        // Wrapping a loop to stop the process running away
        //if it can never fit on a single page
        
        for (int x=0; x<11; x++){
        //Test the number of pages of the resulting PDF,
        //keep going until it fits 1 page
        
        while (countPages(pdfFile) != 1) {
            // Assign the updated xslFile to an XMLDocument instance
            newDoc = parseTemplate(xslFile);
            // Write out the template to the same file name
            writeTemplate(newDoc);
            // regenerate the PDF document with the adjusted
            //font size and height settings
            generateOutput(xslFile,xmlData,pdfFile);
           }
        System.out.println("Success!");
        break;
    }
    
} 
    
public void generateOutput(String xslFileLoc,String xmlData,String pdfFile){
    // This method will generate the PDF output each time
    
    FOProcessor fop = new FOProcessor();
    fop.setData(xmlData);
    fop.setTemplate(xslFileLoc);
    fop.setOutput(pdfFile);
    fop.setOutputFormat(FOProcessor.FORMAT_PDF);
    try {
        fop.generate();
    } catch (XDOException e) {
        e.printStackTrace();
    }
 
}
public void processTemplate(String rtfFile) {
    // Only called once to create the initial XSLFO template
    // from the RTF template
        try {
            RTFProcessor rtfP = new RTFProcessor(rtfFile);
            rtfP.setOutput(xslFile);
            // this prevents the processor generating attribute sets
            // You could allow it but it would require changes to the
            //the parser code
            rtfP.setExtractAttributeSet(RTFProcessor.EXTRACT_DISABLE);
            rtfP.process();
        } 
        
        catch (XDOException e) {
            e.printStackTrace();
        }
        catch (IOException ioe){
            ioe.printStackTrace();
        }
    
    }
 
    public void writeTemplate(XMLDocument newTemplate){
    
    //Write the updated XMLDocument to the XSLFO template file
    
    OutputStream os;
    try {
        os = new FileOutputStream(xslFile);
        newTemplate.print(os);
        os.close();
         } 
    catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    catch (IOException ioe){
        ioe.printStackTrace();
    }
       
}
 
public static void main(String[] args) {
        
FitSinglePage fitSinglePage = new FitSinglePage(args[0],args[1]
                                                        ,args[2],args[3]);
    }
 
public XMLDocument parseTemplate (String templFile){
       // Parse the XSLFO template method
        DOMParser dp = new DOMParser();
        try {
            InputStream inp = new FileInputStream(templFile);
            dp.parse(inp);
            inp.close();
        } 
        catch (SAXException e) {
            e.printStackTrace();
        }
        catch (IOException e) {
             e.printStackTrace();
        }
        XMLDocument tDoc = dp.getDocument();
        //Grab all instances of the 'inline' element and their children
        NodeList ns = tDoc.getDocumentElement().getElementsByTagNameNS
                             ("http://www.w3.org/1999/XSL/Format","inline");
        XMLElement attrVal;
       //Loop thru the inline elements
        for (int i = 0; i < ns.getLength(); i++)
    {
        attrVal = (XMLElement)ns.item(i);
      //Change the font Sizes
       if (attrVal.getAttribute("font-size").indexOf("pt") != -1)
       { 
        //System.out.print("Number: "+i +"::" 
                         //+ attrVal.getAttribute("font-size")+"\n");
        //Get the font size value e.g. 12.0pt
 String fontSize = attrVal.getAttribute("font-size");
        
 //Strip out the 'pt' part to leave a number e.g. 12.0
 String fontVal = fontSize.substring(0,fontSize.indexOf("pt"));
        
 //Set the new value and add the 'pt' back in e.g. 10.0pt
 attrVal.setAttribute("font-size",
 (Double.parseDouble(fontVal)-2) +"pt");
        
 //System.out.print("Number: "+i +"::" 
 + attrVal.getAttribute("font-size")+"\n");
        } 
        // Change the row heights
        if (attrVal.getAttribute("height").indexOf("pt") != -1)
        { 
         //System.out.print("Number: "+i +"::" + attrVal.getAttribute("font-size")+"\n");
         String heightSize = attrVal.getAttribute("height");
         String heightVal = heightSize.substring(0,heightSize.indexOf("pt"));
         attrVal.setAttribute("height",(Double.parseDouble(heightVal)-2) +"pt");
         //System.out.print("Number: "+i +"::" + attrVal.getAttribute("height")+"\n");
         } 
        
    }    
 
    return(tDoc);
}
 
public int countPages (String outDoc) {
   //Count the number of pages in the generated PDF document
    int numPages = 0;
    FormProcessor fp = new FormProcessor();
    fp.setTemplate(outDoc);
    try {
            numPages = fp.getPageNumber();
            System.out.println("Number of pages: "+ numPages  );
        
        } catch (Exception e) {
            e.printStackTrace();
        }
    return(numPages);    
 }
}

You can also get the class here, both compiled and not along with the template and XML data. You can run the class from the command line passing in 4 parameters :

java xdotestbed.FitSinglePage rtfFileName xslFileName xmlFileName pdfFileName

you'll need to set your classpath accordingly, substitute your values for the parameters above e.g.

java xdotestbed.FitSinglePage 1.rtf 1.xsl 1.xml 1.pdf

 and you'll need the following libraries:

aolj.jar - EBS lib, required even for standalone
bicmn.jar - BIBEans for charting
bijdbc14.jar - BIBEans for charting
bipres.jar - BIBEans for charting
collections.jar - Needed in mailing
i18nAPI_v3.jar - internationalization lib
share.jar - general library
versioninfo.jar - anothe EBS lib
xdochartstyles.jar - for charting on the publisher side
xdocore.jar - core library
xdoparser.jar - publisher parser
xmlparserv2-904.jar - XML  lib
xmlpserver.jar - XML  lib

You may not need all of them depending on whats in your template but they are all easily grabbed either from the standalone server install or the Template Builder for MSWord install directory.

Summing Up

It seems quite a niche requirement but if you need this type of functionality then the APIs and an XML parser can help. I could even see the need to manipulate the template repeatedly for other requirements. I chose a DOM paerser because templates are not that big. It would not be a huge task to move this over to SAX.
Overall, I at least had some fun and frustration building the solution  and if nothing else you got to see a few more APIs. 

Hey, do you have users that want RTF output and they are not happy because fonts are not the right size when they open the document? If 'yes', read on - if 'no' check out another blog article, yesterday's took me ages and its pretty cool.

If you're still here. What I mean by the wrong size font is, say you created a template that has some blank space for someone to add more information into an RTF doc. They open the doc, enter the data but then find themselves having to change fonts, etc. It's not too bad for a single doc but say you are updating dunning letters all day, its going to get tedious very quickly. Well there is a property that was introduced in 5.6.2 (all flavors) that allows you to set a default font for the document.

This means that any empty cell in a table or position within the document where the user might want to add more information will have a default font set, the default is 'Arial 12 pt.' The property takes the format:

FontFamilyName:Size e.g. Arial:12

Now remember, if you choose some esoteric font that only you have on your machine then your users are not going to get the same font unless they have it in their fonts directory.

If you're using MSWord plugin or the Template Viewer use the config file. 
For Template Builder you'll need to update the configuration file under the install directory

<property name="rtf-output-default-font">Arial:20</property>

For Template Viewer you can put it in the second tab of properties.

Name - rtf-output-default-font  Value Arial:20

I wrote a very high level article a while back on integrating Oracle Forms with Publisher. Sadly I have not had the time to take some time to clean up my examples and post them ... working on that I promise. There is a bright spot however and Jurgen and Rainer from the German sales team have stepped up and provided a great docment on the German Oracle.com website that needs more exposure.

http://www.oracle.com/global/de/community/forms/documents/Forms_BIP_WS.pdf 

It covers how to create a web services client that can be used in an Oracle Form to call Publisher to process and format data. Its a great tutorial and opens up other possibilities. Check it out!

Last time we looked at building and pre-filling the PDF data entry form. We now need to look at how we can allow the user to 'submit' the data they have entered back to the server and to then let them know that their data has been received.

There are a several of ways that Adobe will allow you to post data from a form.



Im using Adobe 6 you may be on another version, things should be similar thou. The properties dialog, for a button reveals the folloiwn submission methods:

• FDF - this is Adobe's 'Form Data Format' - you can get more info on this here. It's a proprietory format for sending (and receiving) form data and comments. Im not going to deal with this format.
  
• HTML - need I say more except Im not going to deal with this either
  
• XFDF - XML Forms Data Format. Yep, you guessed it, it's Adobe's proprietory XML format for the forms data - now this we can and will use. Not the friendliest format but guess what? We have an API for that.
  
• PDF - you can just submit the complete PDF form. This is useful if you wanted to store a copy of a form created by a user. Even better if they signed the form with a digital signature - as good as a paper signature. Of course we need to scrape the data out of the form too - yep, we have an API for that too and we're going to try that too.

Now I guess you could use the HTML format and Adobe provides an SDK to parse the FDF format but why bother, Publisher has all you need for for the last two formats and they have enough information.

Using the properties dialog we can choose XFDF or PDF. Initially I chose XFDF, I need to POST the XFDF to some CGI process on a web server. I elected to use a servlet. Looking at the graphic above you'll see I have entered a URL. Therein lies an issue, I have hardcoded the URL on the button - you got it. We have an API for that, now I have to admit this one is hidden and again I lent on Incheol for information.

We have an undocumented class FieldEditor, as the name suggests you can edit fields with it - that includes adding fields too. So, we can  create the button in its entirity and enter the submission destination and format and then put it on the page.

We first need to instantiate a PDFParser object, this takes the file we want to edit as an input. We then create a FieldEditor object and add the button and then re-generate the form. The addButton method takes a series of parameters.

            addButton(btnName(String),
                      btnLabel(String),
                      btnfontSize(float),
                      btnCode(String),
                      btnCoords(float[],
                      btnPage(int),
                      btnType(int))

Most I hope are self explanatory except maybe:
btnCode - you can create a submit or javascript type button and here is where you pass either the

            //Add the button
            float[] btnCoords = {350f,350f,470f,380f};
            float fontSize = 16f;
            String btnName = "SUBMIT_BTN";
            String btnLabel = "Submit";
            String btnDest = "http://www.oracle.com";
            int btnPg = 0;
            //Create a PDF Parser instance
            PDFParser pdfP = new PDFParser(pdfForm);
            //Create a FieldEditor instance
            FieldEditor fldEd = new FieldEditor(pdfP);
            //Add the button
            fldEd.addButton(btnName,btnLabel,fontSize,btnDest,btnCoords
               ,btnPg,fldEd.BUTTON_ACTIONTYPE_SUBMIT_FORM);
            //Regenrate the form
            fldEd.generatePDF(pdfForm);

Now the only drawback is that the API does not let you influence the look and feel of the button, so you get a flat button ... not exactly a nice beveled 'clickable' affair but it gets the job done. Before you ask, no there is not a method to update a button. We can look into that if enough of you scream loud enough. I also have to admit that we do not give you the control over the posting format of the button e.g. XFDF, PDF, etc. It defaults to HTML ... dang - Im logging an enhancement for this today.

Apologies for this tangent we got on to with buttons that led down a rather disappointing cul-de-sac. Next time, we get into receiving the contents of PDF via a servlet and letting the user know we got their information. 

I couldnt really say 'Crosstabs on Steroids' now could I, not with 755 being hit over the weekend and all the controversy surrounding that particular batter, what happended to 'innocent until proven guilty' - I guess the modern day press and media happened. I used to attend Giants games resonably regularly even before the 'Balcogate' mess and the excitement that Barry Bonds generated in the park when he stepped up to bat was infectious. Even if he did 'enhance' his game physically, I do not think there is a drug out there that can improve you ability to see and react to a ball being thrown at near 100 miles per hour, in my humble opinion its still an astounding feat. 

Anyhoo, moving on to an even more exciting and thought provoking subject, good question from Chrissy on the forum this weekend that I think deserves an investigation and an answer:

With the Analyzer I can create a report for my data with two column fields.
I would like to rebuild this in my RTFTemplate, but with the cross table assistant it's not possible to do this and I haven't found anything about this in the documentation.
Can anybody give me some hints & tips how to do it?
When creating a pivot table in excel you can add 2 data items. Can this also be realized in BI Publisher?

The Template Builder for Word does provide a dialog to help you build a cross tab but that will only help with a single 'measure' or 'data' value. Assuming we have the following data:

<?xml version='1.0' encoding='UTF-8'?>
<SALES>
 <SALE>
 <YEAR>2000</YEAR>
 <REGION>Americas</REGION>
 <SOFTWARE>1200</SOFTWARE>
 <HARDWARE>850</HARDWARE>
 <SERVICES>2000</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2000</YEAR>
 <REGION>EMEA</REGION>
 <SOFTWARE>1000</SOFTWARE>
 <HARDWARE>800</HARDWARE>
 <SERVICES>1100</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2000</YEAR>
 <REGION>APAC</REGION>
 <SOFTWARE>900</SOFTWARE>
 <HARDWARE>1200</HARDWARE>
 <SERVICES>1500</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2001</YEAR>
 <REGION>Americas</REGION>
 <SOFTWARE>2200</SOFTWARE>
 <HARDWARE>950</HARDWARE>
 <SERVICES>2100</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2001</YEAR>
 <REGION>EMEA</REGION>
 <SOFTWARE>1100</SOFTWARE>
 <HARDWARE>900</HARDWARE>
 <SERVICES>1300</SERVICES>
 </SALE>
 <SALE>
 <YEAR>2001</YEAR>
 <REGION>APAC</REGION>
 <SOFTWARE>1000</SOFTWARE>
 <HARDWARE>1400</HARDWARE>
 <SERVICES>1200</SERVICES>
 </SALE>
 </SALES>

Line of business (LOB) sales data across multiple regions and years.

With the dialog we can build a crosstab with Years as our column headers and Regions as our row headers.

We can generate something like

Now, as Chrissy asks what if we wanted something like this:

The dialog can not help you here, we are going to have to dive into code. Let me say here that we are looking to address this in a future release of the template builder but for now, we're coding. 

To get this to work I had to beef up the RTF layout to accomodate the 3 column sets (HARDWARE,SOFTWARE and SERVICES).

The graphic above shows the original 'wizard' generated cross tab at the top and the new and improved 'ripped' crosstab below. Things of note:

1. The embedded LOB table with the LOB titles, these need to be in a cell that will get repeated with the YEAR component.
2. The extra columns to handle the extra two LOB values

The other changes are hidden in the fields.

1. On the 999.00 - fields on the REGION row, notice the G and E wrap the three columns now. Each field contains:
    
     <?sum ($G1[(./YEAR=current()/YEAR)]/<<LOB_NAME>>)?>

2. Similarly on the 'Total' row we have G and E wrapping the 3 columns, each total value containing:

     <?sum(current-group()/<<LOB_NAME>>)?>

3. In the 'Total' column we need some XPATH to ensure we get the right numbers summed. For the regular totals:

     <?sum ($G1/*[name()="HARDWARE"or name()="SOFTWARE" or name()="SERVICES"])?>
   
    The XPATH is ensuring that the HARDWARE, SOFTWARE and SERVICES values are summed.

    Same for the bottom right cell:

    <?sum ($T/*[name()="HARDWARE"or name()="SOFTWARE" or name()="SERVICES"])?>

Thats it, not too much effort, files here. Yes, I admit you need some XPATH experience and a little XSL but other than that you too can achieve 'ripped abs', sorry 'crosstabs'.

If you are writing reports that will show currency data across multiple currencies then templates can get complex all to quickly. With the introduction of the Euro-currency things have gotten simpler. I rememeber writing Oracle Reports for my old team EMEA Financials and having to handle multiple currencies, even revaluation reports to get amounts to a single currency. We had some nice user exits to format currencies for us to make life simlper. You'll be pleased to hear that publisher can help you there too - no matter what flavor you are running. Life is admittedly easier for EBS users, and will shortly be as easy for standalone with PS and JDE following along later.

The xdo.cfg file can contain all the formatting information required any currency you happen to be reporting on, the entries for the currencies are very straight forward.

    <currency-formats>
      <currency code="USD" mask="999D99"/>
      <currency code="JPY" mask="999D99XX"/>
    </currency-formats>

Once you have defined the formats you can then reference them in your templates thus:

<?format-currency:CURRENT_BALANCE;'USD'?>

When the formatting engine executes it will pick up your formats and apply them to your currency values. If you apply a mask that does not exist then you'll get good ol 2 d.p. output ... not good if you want Yen!

I mentioned that life was easier for EBS users, you get a nice user interface to create the formats in the configuration manager tab, its strightforward stuff.

1. 
   

   Navigate to the Currencies sub-tab under the Administration tab. Click on Create Currency Format Sets.

   
   

   Enter a name and a code for this set. The code is a unique identifier and cannot be changed later.

   
   
2. 
   

   The View Currency Formats page shows the currencies formats available in this set. You can also update the currency set (changing its name), or update/delete any currency formats already created. Click on Add Currency Format.

   
   
   
   
3. 
   

   Select a currency from the LOV. This list is generated from the FND currency table so you can add more currencies if needed from System Adminstrator responsibility (but all iso currencies should be covered).

   
   

   The format mask should be in Oracle number format. The format should be MLS compliant so 'D' and 'G' should be used instead of explicitly specifying the separators. In fact use only '9', '0', 'D', and 'G' to compose the number format. (Some information regarding Oracle number formats can be found at this page: http://www.ss64.com/orasyntax/numfmt.html)

   
   

   There is currently no validation of the format mask. This may be included in a future release with APIs from the i18n library.

   
   
4. 
   

   Once the currency format set is created, you can assign it to the Currency Format Set property in the configuration page (under category FO Processing).

Standalone, JDE and PS users, you need to wait a while and keep plugging away at that config file for now. 

Dates, dates, dates ... no not the sweet dried fruit with a pit, we British only ever seem to eat at Christmas, I mean DATES, birsthdates, invoice dates, shipping dates ... they are tough nut to crack in XSL especially calculations. Date calculations are tough right now with publisher .... they will get better but there was a nice workaround provided by Darshan on the forum yesterday, definitely worthy of a wider audience. It was in repsonse to a question on how to calculate the difference between two dates from Valerie.

Valerie was expecting <?xdofx:$C_REPORT_START_DATE-C_DUE_DATE?> to provide a date result, sadly she got an error result. Until we can pick up the xsl2.0 date support in the Oracle XDK engine we are a little stuck on these calculations. Happily Darshan provided two workarounds, one forcing tghe calculation back into the data layer, the other in the layou template.

1) Create one Hidden parameter for that concurrent program and assign default value as ( select :$FLEX$.C_REPORT_START_DATE - :$FLEX$.C_DUE_DATE from dual) and refer this parameter in your layout template. You have to tweak above query to extract correct difference between dates. You could also do the caluculation in the main extraction query.

2) If you still want to do it in template then i would like to suggest you first that convert your dates into number by julius format_code      

  to_number(TO_CHAR(SYSDATE,'JSSSSS'))

<?xdofx:tO_NUMBER(TO_CHAR(to_date('2007-08-15', 'YYYY-MM-DD'),'JSSSSS'))
 - TO_ NUMBER(TO_CHAR(to_date('2007-08-14', 'YYYY-MM-DD'),'JSSSSS'))?>

Yep, there is another release, 10.1.3.3 out on OTN ... you folks keeping up? Its a point point release but we just need the smallest excuse to pack new features in. Currently we have Windows and Linux ports available, more on the way:

• We now have a download page mounted just for BIP10.1.3.3 server and template builder

                 http://www.oracle.com/technology/software/products/publishing/index.html

• Documentation available here
  
         http://download.oracle.com/docs/cd/E10383_01/welcome.html
  
  
• Certification Docs
  
          http://www.oracle.com/technology/software/products/ias/files/
                                  oracle_bi_publisher_certification_10133.pdf

Features? Oh yeah we got some of those:

• Template Builder Enhancements
  
• Flex Templates
  
• PowerPoint output
  
• Discoverer - BIP Integration
  
• Multithread support in Report Generation
  
• RDF to RTF report conversion utility
  
• CSV output
  
• PDF output for Flash
  
  
  
  
  • New Properties to support Flash Template
  
  
  
  
  
  
  • Page width of wrapper document
    
  • Page height of wrapper document
    
  • Start x position of Flash area in PDF
    
  • Start y position of Flash area in PDF
    
  • Width of Flash area
    
  • Height of Flash area
    
  
  
• PDF to PostScript Filter
  
• Data Engine Enhancements
  
  
  
  • Native support for BLOB, CLOB, long and raw data types
  
  
• Online Analyzer can now save views as templates
  
• New Bind variables:
  
  
  
  • xdo_user_roles
    
  • xdo_user_report_oracle_lang
    
  • xdo_user_ui_oracle_lang
    
  • xdo_user_ui_locale
    
  • xdo_user_report_locale
  
  
• Link to Report. Four modes available:
  
  
  
  • Current Page
    
  • No Header
    
  • No Parameters
    
  • Document Only
  
  
• Link to Oracle SQL Developer
  
• Digital Signature support
  
• Added Report Repository option under Admin > System Maintenance
  
• Link to Dashboards from BI Publisher
  
• Miscellaneous RTF Template enhancements
  
• Under Edit Report, Main Data Set: New items:
  
  
  
  • Database fetch size
    
  • Include Parameters
    
  • Include Null Element
    
  • Include Rowset Tag
    
  • Scalable Mode

Just a few things to take a look at ... I have covered some of these features already but I'll dig in to others in coming articles.

No not that Groovy. With the 10.1.3.3 release there is an important new feature for any of you Disco inferno's out there. You can now have BIP publish your Disco workbooks using templates, so its no longer a case of WYSIWYG in your Disco PDF output you can build templates to your hearts content. Whats more, its simple.

In a nutshell - use the BIP UI to pull in a workbook and create a report, use the template builder for Word to browse for your workbook, create and test the template then deploy back to the server et voila!

Ok, it was a small nut - thankfully the King of Disco himself, Abhinav has blogged the integration in his latest and greatest, there's pictures too which always makes life more interesting - http://oraclebi.blogspot.com/2007/08/bi-publisher-discoverer-integration.html

Enjoy and thanks Abhinav, you gotta teach me that 'do the hustle' thang sometime ... get down tonight! 

Last week I listed the new features that arrived with 10.1.3.3, among them there were some new output formats.

Powerpoint - yes, its finally here. You can now get your RTF templates to format data and generate a Powerpoint output. The only caveat - please use landscape formats for your RTF template if its destined for Powerpoint. Its not a huge requirement but you will get better looking output. So, no more cut and paste just set BIP going et voila your next management presentation is done ... well almost.

CSV - You can now get comma delimited output from all of your BIP reports without the need for a template. Its a simple output and only works with flat data but important enough to have made the 10.1.3.3 cut

Flash - I blogged about this output back in March! - time flies. Its generating a lot of interest too. You can create completely self contained interactive reports that you can send to users, they can open and do not have to connect back to the db to fetch the data. BIP injects the runtime data into the generated Flash file - very portable. This format has much promise and I have been doing a lot of work with it lately, I'll be blogging about it again. You'll find a tutorial in the user guide and sample reports in the 10.1.3.3 install to help get you started. First step is to get the Flex IDE from Adobe, its not free but you can get their SDK for free if you want to get into their XML format on your own - personally I use the IDE to lay out the objects on the canvas then Im into the code to wire things up - there is a huge Flex community building and extending existing widgets that you can put into your templates and a bunch of help from the Flexcoders mailing list.

Postscript - not an output format that a user would pick but one that has been a niggly thorn in our side for a while. We can now generate Postscript for your printers to consume without the need for third party solutions ... hoooray! Before you ask, no we do not have PCL support, that specification is huge and our current estimates on implementing are crazy huge ... we'll see.

Three (and a half (PS)) more outputs to add the PDF, RTF, HTML, EFT, EDI and Excel output fold ... any more requests?

More from the 10.1.3.3 release sorry 'Appers' - I have a great article on duplex output and printing coming for everyone soon.

Being able to link to a report has been possible in previous releases but its now so much easier for you. When you install 10.1.3.3 and open a report you'll notice a 'Link to this Report' link on the right hand side. Clicking it will give you some choices on how you want to link.

Selecting and clicking an option will pop a box at the top of the browser so you can copy the link.

and use it as a shortcut to a specific report. If its in a 'private' directory then you are going to be challenged for a password of course. The options are pretty self explanatory I think.

Current Page - go on have a a guess
No Header - hmmm ... its a tough one
No Parameters - getting harder I know
Document Only - if you have not got it by now I give up.

What's really nice is that you dont have to work out how to construct the URL anymore to include all of our cryptic html parameters I blogged about a while back.

Try it out, if there are other options you'd like, let us know. 

I did start a blog entry yesterday, it was going to be a Dexter diatribe, talking about the death or not of email - definitely not in my case, development processes and how good our team of developers are. Fortunately for you dear readers, I foolishly decided to use the server side editor that our blog host provides for the first time since it last crashed on me and I lost an article. I think we are getting a new host soon - bring it on Justin.

I could not face rewriting the whole article - but I did mention the fact that the development team drop Easter Eggs into the code for us non-developers to find. Not a conscious decision on their part I'm sure, it's the way we develop, there is such a melting pot of ideas during development that approved projects get 'extended', neat features get written into the code and of course our esteemed development director is brimming with visions of the future that he wants to bring to the present ... now!

The 10.1.3.3 release is no different, of the list of features I listed last week many were surprises, not good for the QA team and more rushing around for us to get them documented but great for you folks out there. I'll highlight some of them and others that did not make the list in the coming weeks.

Update
Those of you looking for the paragraph and image on the biggest Easter Egg that was here earlier ... you're not going mad, it was here but the Easter bunny ate it.

Feverish doc writer Leslie (and I mean that in the literary sense, she's not sick) and I have had multiple mails since 10.1.3.3 left the building asking if there was a mistake made in the 10.1.3.3 documentation ... 'it's the same document, have you made a mistake?'

Nope, no mistake, there are just no new features worth writing about its only a point-point-point release after all. Come on thats just means bug fixes!

But what about 'BIP ... the next chapter' ... it was lies, all of it!

Nah, Im kidding - there are a ton of features and the decision was talken to document them in a New Features Guide rather than update the User/Developer Guide ... hmmm. I guess the thinking was that it was a dot-dot-dot release so there would not be much to update. Well the new features span eight, admittedly shortish chapters, but run for nearly 70 pages ... thats a chunk of documentation for a new features guide that only runs to 112 pages including the 'this page is intentionally blank' pages.

You can get the documentation library here - http://download.oracle.com/docs/cd/E10383_01/doc/nav/portal_1.htm - New Features Guide is under the 'Getting Started' tab.

Hopefully, we can get that merged into the main doc before the next release and it's new features guide comes out, otherwise we will be in a mess and you'll be even deeper in the mire.
Now dont go printing it all out and wasting all those trees just so you have it next to you. Just persuade your manager that you need a second shiny new 24" flat panel monitor and that you'll rig up a generator to your bicycle in your cube/office/home so that you can save the company the power to run it ... you'll be getting fit and be clean and green all at the same time.

I wrote about the coming Flex templates in 10.1.3.3 back in April - they are now here and I wrote some tutorial like documentation for the official doc. You can get that here or if you prefer PDF here - Chapter 9. The documentation covers the basics, how does publisher work with Flex templates, how do you build tables, charts and how do you wire up the objects to the data. To go with the tutorial are some Flex project files which are not with the documentation, they'll appear on metalink Im sure but for now I'll post them here.

http://www.oracle.com/technology/products/xml-publisher/demos/flexemployeereport.zip

Just unpack and use the Import feature of the Flex IDE to get them in. I have annotated the code so hopefully in conjunction with the doc you'll be able to make 'head and tail' of it all.

Before you EBS/PS and JDE folks ask - no, sorry, not yet, its a BIP Enterprise thang at the moment ... its coming thou.

I do not get into the 'Flex' of Flex templates but, I have been working with them again recently and if folks are interested I'll post 'tints & hips' as I work them out. There is a growing community of Flex developers building some amazing extensions to Flex that you can use in your templates. I have to mention the FlexLib project where there are a host of extensions - shout outs for Doug McCune , Ely Greenfield and Mrinal Wadhwa for providing such a wealth of info.  If you get stuck in some Flex related issue, try our forum but you are going to get better results by asking the FlexCoders mailing list. I have to admit the Flex documentation is overall, very good and failing those resources just goooogle for anything Flex ... its out there. 

There are truly some awesome things that you can do with Flex that your users are going to be screaming for and loving you for building, so get Flexing.

I came across my XML Publisher Pilot web page today, it has a bunch of early examples we built way back then - OK it was only 3-4 years ago - we're young still but we have some history ...

Back in '98 I had just finished a degree (finally) and started with Oracle UK in the Financials for EMEA team under the EBS product. We had nearly 200 reports and umpteen pieces of regional functionality to build and maintain. I can still remember fixing my first bug on an Oracle Report - 10.6 character was still around,  and R11 had just come out - I was amazed at just how bad the reports were. Now, Im not talking content here, all the data was there of course and it could be argued that for many of the 'listing' reports 10 point Courier with no text decoration was fine. I was not on that side of the fence - we had a good solid tool in Oracle Reports 2.5, yes it blew up too regularly and core dumped when you least expected it to - I learnt very quickly to save at every step - boy did it need an autosave feature.

But, we were using it in character mode and generating what I considered to be crappy outputs. There were reasons of course - to move all of the Apps reports to Bitmap mode would have been a huge task with ~2000 reoports, there were other concerns around i18n to consider too. Almost a year later I moved to our sister team in California and discovered RXi for those of you using EBS you will no doubt have come across RXi - it addressed one of the big issues customers had with Oracle Reports - customization! It was painful, we never shipped quite the right report to you folks, there were fields you wanted and had to be added or removed, layouts needed changing - as soon as you touched that report - it was yours to maintain. RXi allowed you to create as many layouts as you wished on top of a basic extract, on top of that you could plug your own data into the extract - still character based but so much better.

In 2003 I got a new project - Financials Reporting Strategy - a grand title for a tough project. How could we get the best of RXi and Oracle Reports and produce a flexible reporting solution? We worked with the Oracle Reports folks, we even came up with a prototype that allowed you to create multiple bitmapped layouts on top of an Oracle Report and choose which one you wanted at runtime - sound familiar?

Then I got involved with a broader Applications Reporting Strategy - I was shown a reporting based on MSWord and XML, it had folks' names and their telephone numbers in output - I was blown away, this was awesome. I told my team to 'down pencils' - we had found the answer to the problem. I dragged my now development director in front of our VP and got him to demo it - it did not go too well - after the meeting I was gushing with enthusiam - the rest of the attendees were less than overwhelmed - 'give me a month I said.'

In that month, we worked danged hard to get a real world example working - the first report was an invoice listing.

The template parser back then was a little different, we now have '<? ?>' and XSL like commands - back then we swung from '[ ]' to '{ }' and back again and used 'Group:' rather than 'for-each:'

Three years later and the tool has come a long way ... we still have a long way to go of course. But you'll find it in EBS, PeopleSoft, JDE, BIEE as a standalone product, integrated with APEX and maybe even bundled with the database one day - its a fun ride, exhausting but fun.

In 10.1.3.2 we shipped some functionality to allow you anlayze your data in real time - I blogged about it back in May. With 10.1.3.3 we have addressed one of the glaring holes in the 'Analyzer' - the ability to save the built analysis. So you can now save  the analyses you build and recall them as a template at a later date i.e. with fresh data.

There is a new toolbar providing the ability to Save, Save As, etc. You can load an existing template, modify it and save it back or save it as a new template entirely.

Once created, it will appear in the template drop list in an 'Analyzer' section. 

you can view it interactively as before or generate PDF or RTF outputs from it.



The 'paper' formats explode all levels or dimensions of the report. The format is fixed for this release but later you'll be able to modify. Of course the workaround is to create an RTF template where you can change the look and feel to your heart's content.

Get BIPnalyzing!

It seems our friends in Redmond have done it again, another security patch has screwed up the Template Builder for Word. We apologise, its tough to know what any given patch is likely to do, this one -KB936021 at least mentions the MS XML Services which we do use so we maybe should have picked it up.

If you are experiencing the following error when trying to load an XML sample file when building a template or trying to connect to the BIP server to create a template:

       Compile error in hidden module: Module_starter

We have the following work around - which seems to work reliably
and does not require you to uninstall the security patch:

1. Go to the Start Menu:
               All Programs -> Oracle BI Publisher Desktop -> Template Builder for Word Language
2. Select your language
3. Click OK.
Now use the builder as normal.

We apologise for this interruption of service, normal Templating experience will resume shortly ... barring any more surprises from Washington.   

We have recently released a white paper on Publisher clustering and high availability. One of our Architects, Ragy gets the kudos and of course any questions you might have.

http://www.oracle.com/technology/products/xml-publisher/docs/BIP_HA.pdf

If you have got questions/comments drop them here and we'll get back to ya.

Finally 10.1.3.3 folks get to catch up with those of you with EBS 11i and R12  - makes a change I know. Im talking about converting your Oracle Reports to the publisher format.

The conversion libraries are pretty good, we have a team internally converting all of the EBS Oracle Reports and they have been very successful converting reports. There are of course corner cases, Reports lets you do the same thing in so many ways and the libraries need to cover them.

Now, rather than regurgitate the documentation heres a link - http://download.oracle.com/docs/cd/E10383_01/doc/bip.1013/e10416/convertrpts.htm#BABGJHAB 

As many of you hopefully know we can secure your PDF outputs from publisher. There was a question on the forum asking how it can be achieved - there are, of course, various ways.

The levels of security supported covered in the following graphic - for more details check the Adobe Reader/Acrobat help files and our of course.

You can see two password fields, the first prevents the document being opened the second prevents someone with the first password and Adobe Professional from changing the document. The changes are controlled by the following attributes in the graphic. You have complete control over the PDF outputs, but how do you achieve it?

E Business Suite Template Manager

In the template manager UI you'll see the configuration tab - this has the options above. They can be set at  template, data definition and site levels. The options will be laid out as above - if you want to secure a PDF output remember that you will need to set the 'Enable PDF Security' value to 'true.'

PeopleSoft and JD Edwards

For these apps you'll need to use the xdo.cfg file. This is an XML file that defines the security options. Check the user guide for details, the file will have the structure:

<config version="1.0.0"  xmlns="http://xmlns.oracle.com/oxp/config/">
    <!-- Properties -->
    <properties>
        <!-- System level properties -->
        <property name="system-temp-dir">c:/Temp</property>

        <!-- PDF security -->
        <property name="pdf-security">true</property>
        <property name="pdf-open-password">welcome</property>
        <property name="pdf-permissions-password">welcomeagain</property>
        <property name="pdf-no-printing">true</property>
        <property name="pdf-no-changing-the-document">true</property>
    </properties>
</config>

As you can see the passwords are going to be clear text - not great but you could argue that almost everyone will not have access to the config file. This file needs to sit under the JRE_TOP/lib directory on your web tier for publisher to find it.

BI Publisher Enterprise

The standalone release has very similar UI to the E Business Suite implementation - the security attributes are available at site and report levels.

RTF Template Method

There is another way to set these attributes via RTF template properties. In MSWord you can set custom properties for the document and we can use those to set the security attributes. Just define new attributes and prefix the property - take the property name and prefix it with 'xdo-' e.g. xdo-pdf-security

From the graphic you can see the custom properties Name and Values defined.

If you look at the  graphic the second property sets the password for the document:

Name: xdo-pdf-open-password
Value: {PASSWORD}

PASSWORD is actually an element value in the XML that can be retrieved at runtime and set as the document password. Again, clear text password in the XML data but you could have the XML swept away as soon as its been used.

Java API Method

Finally, if you are using the API to generate outputs you can set the security via an API call. Just about all of the document processing APIs have a setConfig method. This can be used to set the document security:

    // Set the security property
    Properties prop = new Properties();
    prop.put("pdf-security","true");
    prop.put("pdf-open-password","welcome");
    foProcessor.setConfig(prop);

In this case we have created a Properties object, loaded our security properties into it and then passed it to the processor API.

So, you can secure your PDF output - there are just multiple ways to do it.

Many of you know that publisher requires some 'extras' to get your document to a printer in a legible format. There are now printers that can handle the PDF format natively but the majority of printers need to have the PDf converted to either Postscript (PS) or Printer Control Language(PCL). We have recommended a couple of converters to help you out with this.

Ghostscript, offers both PS and PCL converters the other product we recommend is xpdf from foolabs and we have many of you using both products very successfully. In spite of their robustness many of you have expressed concern - 'it's another moving part in the whole document generation process' - 'and it's not owned by Oracle' -  both fair comments and I can understand the concern - however we have faith in these solutions and will help you to use them.

For BIP Standlaone users there is some good news, we have now built our own Postscript libraries to convert from PDF to PS. Very easy to use when setting up your printers in the user inteface.

As you can see its simple to use and if you want to continue to use another converter thats simple too. There are some caveats and limitations:

This release of the PDF to PostScript converter has the following limitations:

• 
  

  Only PDF version 1.4 or earlier is supported. This is also the currently supported version of the Oracle BI Publisher PDF output.

  
  
• 
  

  CID fonts, which are used mainly to support languages with large sets of glyphs like Chinese, Japanese, and Korean, are only supported when embedded in the PDF.

  
  
• 
  

  Transparent and half-tone renderings are not supported.

  
  
• 
  

  DeviceN color space is not supported.

  
  
• 
  

  Shading patterns are not fully supported.

  
  
• 
  

  Vertical writing is not supported.

Not forgetting you EBS and other apps users out there - this functionality will be coming to an application near you later this year (for EBS) and next year for others.

Another RTF template enhancement for 10.1.3.3 ... imagine you have a report that maybe has multiple master detail relationships, say a customer listing with each of their invoices listed. If would be nice to be able to expand and collapse customer invoices in the html output providing a level of interactivity.

In previous releases we had an ability to very simply show and hide sections in an output. The sample Customer Orders report that ships with the standalone release showed this off. But it was an all or nothing thang - show all/hide all - we always wanted to allow you to expand/collapse specific regions - we found a horrible way to do it it in a template but it was not practical.

In 10.1.3.3 you can now build such a report, I think it takes the HTML output for publisher standalone to a new level. There are of course some new RTF template commands to add to the template.

<?dhtml:showhide;hide?> <?end dhtml?>
 
<?dhtml-header:?> <?end dhtml-header?>
 
<?dhtml-contents:?> <?end dhtml-contents?>

Now rather than bore you with the written details - Im getting hooked on Jing, thanks AppsLabbers- its a 'project' from Techsmith to record your screen and your voice if you are so inclined all for free as long as you can limit yourself to a few minutes. Those of you that know me will know I found it really hard :o)

Well I was so inclined, so here it is, you can see the show hide in action here. Of course, the sample files are loaded here.
Whats really neat about the solution is that the javascript used to do the show/hide is not part of the BIP server UI but actually embedded in the HTML file itself so you can see that working too, just here.

Not quite here for Apps users - you're getting too much bad news lately - will write about something you can use, I promise.

I have recently seen a spate of requests for new functions to be built into BIP - we have enhancements coming out of our ears. For those of you that can not wait and have a little java experience or even have access to a java resource read on.

Before I get into the meat of this entry - Oracle will not support you if you choose to use the following methods described. At least I dont think they will - please dont log a TAR if you get stuck :o)

As many of you know, that RTF template you develop is converted to an XSLFO stylesheet and the Oracle XSL transformation engine is then used to apply said template to XML data - so at the heart of the whole engine is an XSL transformer. The Oracle implementation provides a framework that allows you to extend the XSLT engine to create your own functions. We even make use of it in our xdoxslt: functions. I have mounted a cut down version of the XDK dev guide here - it just contains the java section. Useful to understand whats going on in the XSLT engine - check out the 'Programming with Oracle XSLT Extensions' section.

So how do I do it?
From a high level, you create a java class with a method that will process the incoming data and return a result to the transformation engine to be rendered. To then use the new function, your extensions need to be declared in the template as a namespace and the new function name called using the namespace prefix. For example 

bipext:welcomeMsg('Tim')

where 'bipext' is the namespace and welcomeMsg is method to be called. Of course your java class needs to be in the classpath of the publisher engine to be picked up. 

Let's keep it simple to start with, I have created a very simple class that has only a single method:

package oracle.bip.extensions;
 
public class BIPExtensions {
    public BIPExtensions() {
    }
 
  public static final String welcomeMsg(String name)
           {
               return("Hello " + name+" !");
            }     
       }

The welcomeMsg method takes a string input value and returns a string, 'Hello <<name>> !' to be rendered in the output - yes, its very simple - not Hello World thou you have to admit :0)

Couple of other points to notice:

1. The package path is important - we'll use this in the namespace declaration.
   
2. The 'welcomeMsg' method is 'static' - the Oracle XDK can handle both static and non-static methods - they explain further in their documentation here.

We now have our java class and method, it needs to be placed into the classpath for the engine - I'll come back to this, so lets assume its done.

Now we need to declare that we want to use our new extensions in our template. We just declare these as a namespace:

<?namespace:bipext=http://www.oracle.com/XSL/Transform/java/oracle.bip.extensions.BIPExtensions?>

notice that 'bipext' is going to be the prefix we'll use, you can have your own. In the URI we must have :

http://www.oracle.com/XSL/Transform/java/

followed by the package path using period (.) separators and then the class name that holds the extension method(s).

Once, that is done we can call the function in the template.

<?bipext:welcomeMsg('Tim')?>

of course 'Tim' could be replaced with an element name - just so long as its a string or can be interpreted as such.
When the template is applied and the extension is called we get back

'Welcome Tim !'

and its inserted into our output.

Being able to test your functions relies on your class being in that all important classpath - if you are using JDeveloper or similar its simple enough to get your class archive in - just ensure its above the xmlparserv2XXX.jar and xdoparser.jar in the list.  
Using Template Builder for MSWord is a little more tricky and requires what can only be described as 'a bit of hacking' - but with some care you can test them. Template Builder does not support setting a classpath or registering new java functions - yet. The only way around it is to get you class into the xdocore.jar in the /jlib directory under the plugin install. If you are up for it ...

1. Make a copy and back up the original xdocore.jar and rename it
   
2. Using winzip or similar open the jar up
   
3. Now you need to merge the extension class into the jar(zip) file - just make sure you preserve that directory path in the archive. In our case, 'oracle.bip.extensions.BIPExtensions.class'. In the visual zippers this is drag an drop but you may need to work out the command line syntax - sorry, Im lazy and use a UI.
   
4. Close the archive

Hacking done - you're now ready to test the function using the Template Builder. Now remember, you get into a mess, just back up to step 1 again - remember support do not want a ticket asking about how to unhack Template Builder and dont tell em Tim told you to do it :o)

Once you're happy with the template, deploy it and your class to the server and you're good to go.

Now, here we tackled the simple stuff - you, Im sure will get very creative - got common functions all your templates need and can not be satisfied in a sub template? build an extension - the possibilities are endless and I can hear the cogs whirring from here. If you come up with a hot extension and want to share it, send it in and I'll post it here.

No, BIP has not become the ulltimate Oracle able to answer multiple questions - those of you using the BIEE/BIP integration will hopefully have noticed and read about the ability to pull an Answer, a BIEE report object if you will, into BIP as a datasource that you can now format using a publisher template.

What you may not have noticed is that you can pull multiple Answers into a single BIP report and create a template over the top of the combined data set. To achieve it we take advantage of the Concatenate Datasource functionality. All we need do it create multiple Answer based data sources:

then use the UI at the Data  Model level of the report tree and select 'Concatenated SQL Data Source'

 ... before you point out a problem. It's not one,  just an oversight on our part - I know you do not have SQL datasources but ignore that, we will concatenate your Answers into a single XML source.

another Easter Egg if you will.

Ever been building a dynamic URL in your RTF template and when it renders you get some interesting prefixes to the URL you were expecting?

MSWord is good at that, its a 'nanny' application and knows what is best for you, now take your medicine like a good little 'templater'.  Most popular is the 'C:Documents and Settingstdexter' prefix - nice, just what you need. We discovered another one today, the FSG folks are looking into providing a means to drill down on FSG numbers back into the appropriate GL ledger - its going to be a very neat solution that will add a whole new level of interactivity to FSG reports with Publisher. We had to learn how to ignore Nanny first thou ...

To create the URLs to the correct account they need to build it up using some of the data elements around the number element:

<fsg:RptLine RptCnt="p1001" RowCnt="r100002" LinCnt="l100122">
<fsg:RptCell ColCnt="c1000" RealNum="">Totaled 01-01              </fsg:RptCell>
<fsg:RptCell ColCnt="c1001" RealNum="0.000000">0.00</fsg:RptCell>
<fsg:RptCell ColCnt="c1002" RealNum="2685057669.910000">2,685,057,669.91</fsg:RptCell>
<fsg:RptCell ColCnt="c1003" RealNum="0.000000">0.00</fsg:RptCell>

To build the URL we need RptCnt, RowCnt, LinCnt, etc ... straightforward right. Well it would be if Word did not want to help you so much. The values we are interested in are attributes and therefore we reference those with an '@' sign e.g. @RptCnt. See, even my blog software thinks it knows best, putting a mailto link on the '@' - there it goes again!

When we built reportthe URL in MSWord we got a great 'mailto:' prefix on our URLs - no matter how many auto complete switches we turned off- Nanny Word was there to correct us like a 'dogged' school teacher determined to make you spell 'colour' as 'color' cos we're in America, or maybe 'favourite' as 'favorite' and the best transatlantic transgression - 'sulfur' instead of the correct British 'sulphur'. 'Sulfur' seems so '1st grade' - flame me if you will, its a pet peeve. Another reason for hating Word's nannying tendencies - it's America-English dictionary - yes, I could switch it to British-English but seeing as the majority of my internal docs are headed US based eyes I have to live with it. Right, back from rant land - we create URLs in our template like:

http://{servername}/fsg/?callfunct=XXXX&code={../@RptCnt}{../@RowCnt}{@ColCnt}

and because of those @'s we get the following in the output:

mailto:http://tdexter-us.us.orac.com/fsg/?callFunct=XXXX&code=1223|434343|34322|232

That 'mailto:' really screws things up arrrrgggghhh ... its done it again! Here's the solution, just put the URL in and let Nanny help you.Now when you have the document infront of you press Alt-F9 - this will toggle the field codes in the document and you can remove the offending mailto from the HYPERLINK field.

{HYPERLINK"mailto:%7b/MasterReport/fsg:HostName%7d?DRILLDOWN_ACTION=0&DRILL_DOCUMENT_ID=%7b/MasterReport/fsg:ReqId%7d&DRILL_DOCUMENT_NODE=%7b../@RptCnt%7d%7b../@RowCnt%7d%7b../@LinCnt%7d%7b./@ColCnt%7d"}

press Alt-F9 again and your URLs will be evaluated correctly at runtime. Be sure not to get caught by 'Nanny' or you'll get detention!

In 10.1.3.3 there is a feature that Im embarrassed to say I did not know about until I got an email today asking about it - I checked the docs and it appears our resident 'word wrangler' (read 'doc writer') Leslie was in the dark too. Im referring to the wonderful subject of user security - we boast a growing list of supported security configurations - our own native flavor, LDAP, BI Server, E Business Suite. Well, there's now another 'Oracle Database' - how many of you spotted it?

It essentially lets you set up all of your users in the database - which you will have done anyways - not sure how many of your business users have a username in your db but the option is there for you. Once you have your users you, can then hook the publisher server up to the db for user management. There is some set up to do but its pretty quick.

Quick recap on the report security model we support:

Reports sit in Folders, Folders are assigned to Roles, Roles can be assigned to other Roles and Roles are assigned to User. When a user logs in, we look at their roles and then work out what folders and therefore reports they have access to.

Getting the house in order

We have two sides of this security model to sort out before we can use it ...

Database side of the House

Assuming we have set up the users already we need to create some roles that BIP recognizes. Im not going to get into how to create and assign roles to users - get googling if you're unsure. BIP needs these roles created in the database:

• XMLP_ADMIN ? this is the administrator role for the BI Publisher server.
  
• XMLP_DEVELOPER ? allows users to build reports in the system.
  
• XMLP_SCHEDULER ? allows users to schedule reports.
  
• XMLP_ANALYZER_EXCEL ? allows users to use the Excel analysis feature.
  
• XMLP_ANALYZER_ONLINE ? allows users to use the online analysis feature.
  
• XMLP_TEMPLATE_DESIGNER - allows users to connect to the BI Publisher server from the Template Builder and to upload and download templates.

Create these roles and assign them as you wish to db users depending on what you want the user to be able to do. Thats not the end of it - you can create as many roles as you wish you'll use them on the publisher side and attach folders to them, for instance, you might create an HR_MANAGER role and assign an HR folder to it ... duh!

Lastly, assign the XMLP_ADMIN role to someone like SYSTEM or a user with admin privileges - this is important.

Once you have created the roles you can move on to the publisher side ...

Publisher Side of the House

With the database security option we manage the users and their roles in the database and then map folders to those roles in the BIP UI. To set it up:

1. Login into BIP with an Administrator responsibility
   
2. Navigate to the Admin tab then to the Security Configuration
   
3. In the Security Model section pick 'Oracle Database' from the drop down
   
   
   
   
4. Fill in the fields required to connect the database - check out the graphic for hints.
   
5. The user you enter has to be able to access data from dba_users/_roles/_role_privs tables - so user in the appropriate user
   
6. Save your work - you're going to need to bounce the server to get the changes to take effect.

Once the server is bounced you can now log in with your db user that has the XMLP_ADMIN role assigned. You can now start assigning folders to db roles so that users can access reports.

Navigate to the Admin tab then to the Roles and Permissions page. You'll now see all the db roles along with any of the new ones you may have created.

Notice the HR_MANAGER role, this is effectively a dummy role in the db but we can attach folders to it to make a more 'logical' role structure that is easily understood. You will of course not find the XMLP_XXXX roles in the list as you would when using BIP Security. Check the user guide on how to add folders to roles.

Once you have assigned the folders to the roles you want, save your work and your done. Users can now log in with their database username/password combos to get into BIP and access the reports they are permitted to access.

Kiran, from our dev team has been looking into desktop gadgets for BIP - functionality to let you monitor/submit jobs, servers, etc all from your desktop. As a first effort he has been toying with the Google Desktop gadgets. Have to admit I dont use the gadgets so much but the desktop search has become the most valuable piece of software running on my laptop - awesome - double Ctrl is my new favorite key stroke.

We now have a 'BIP Blog' gadget that you can install on your desktop, yet another way to get the latest and greatest from the blog - its an adaptation of the RSS gadget but useful none the less.

You need to install the latest Google Desktop, http://desktop.google.com/
You can download the gadget here.

Now extract the BIPubNews.gg file from within it and save in a temp location.
Double click on the BIPubNews.gg file to intall it on your Google desktop and you're done.

With OOW07 just around the corner, superstar Nadia from the applications technology strategy team has put together some useful documents on the Apps Tech track, whats on and where - these are almost cast in stone, lets say the concrete is 'going off' but you can still stick your hand print in it. Those of you that came last year will remember the lines(queues) to get into to some of these sessions so please book a seat ahead of the conference to avoid disappointment.

There is a new type of session this year 'meet the experts' or MTE - you get an hour and a half to grill experts with tough gruelling questions with multiple followups getting deeper and deeper into the product until we have completely lost the rest of the audience - be gentle please.

• Focus On EBS AppsTech - The E-Business Suite: Tools and Technology track sessions
  
• EBS Techology MTE  - this lists those MTE sessions specifically for EBS technology. You will not find us here - we now run across multiple apps.
  
• All Apps MTE - as the name suggests, this lists all the sessions for Apps. We are in this section, we'll have representatives from the PeopleSoft and JD Edwards 'publisher' teams join us so it's not an EBS only affair. Plus as many developers that I can so you get to talk directly to some of these folks rather than me boring your socks off.

Yours truly will be there and as I have mentioned before there are a host of sessions going on that are either exclusively about publisher or about technology that uses publisher. There are multiple demo pods featuring publisher staffed by experts of course so along with hte MTE session you have no excuses for not coming to talk to us 

By the end of the week, you'll be all 'published' up!

After a weekend (there are 3 posts on this) spent in the mountains trudging, sorry 'hiking' up hill and down dale in freezing weather with no sleep I have finally caught the 'cold' that I knew was coming - with a head thats fit to burst I sound ridiculous and 'dalk dike dat, dall der dime' - good for 'd' aliteration fanatics but Im not one of them. There is a useful post coming for those of you using data templates.

If you have moved up to 10.1.3.3 and suddenly you're data template based reports are failing with:

Data not defined

Panic ye not - we have found the issue and its easily fixable. For some reason, we got strict with you in 10.1.3.3, if you had some parameter defined in your data template that you were not passing a value to via the user inteface. If you had a data template like the following, notice the P1 parameter:

<dataTemplate name="prodTemplate" dataSourceRef="demo-hr">
 <parameters>
  <parameter name="P1" dataType="character" defaultValue="Hello"/>
 </parameters>
 <dataQuery>
  <sqlStatement name="prodStmt">
   <![CDATA[
      select last_name, first_name, salary
      from employees
      where salary > 4000
      ]]>
  </sqlStatement>
 </dataQuery>
</dataTemplate>

But you had a report definition that lacked the P1 parameter definition, see the first image.

It would run in 10.1.3.2 but in 10.1.3.3 we would not let you and we would throw an error.
Until we fix it, to get around the problem either define the parameter in the report, see the second image or remove the parameter from the data template.

After the initial gadget post this week, Hiro, in the development team has been hard at work looking to see what we can do. We now have a really cool gadget you can hook up to your BIP instance. Point it at a report on your BIP server and it will render any charts present on the report, you can even scroll through those charts present on the report. You can then have multiple instances of the gadget pointing to different reports and charts. 

You need Google Desktop and to get the multiple instances of the gadget working as above you need the 5.5 release. Of course you'll need the gadget, you can get that here. Set up is a cinch, download the gadget and then double click on the gadget file to add it to your desk top. After the initial 'adding' it becomes available in the 'Recently Used' Add Gadget dialog.

Now you need username/pwd to your instance - just the combination to view the report is enough. Then you need the URL to the report document - this must be in HTML and the chart images have to be in image format not SVG (use the preferences dialog to change it). In 10.1.3.3 you can use the Report Link feature in previous releases you can use the Export button to get the URL. Paste that into the Options dialog, click OK and your good to go. The gadget should pick up the first chart quite quickly and you can use the arrow icons to scroll through the charts on the report.

I have made a quick recording of the whole thing with the cool and free Jing

We'd like your feedback on this, what do you think? useful? what else would you like to see?

If you have built your own BIP gadget and want to share let me know and we'll post them here for everyone.

Hmmm ... thought this got posted yesterday ... it didnt, so please read as if its Sunday - it might make a little more sense. 

Woke up to nearly 8 inches of snow this morning - we had plans, we shelved them - its snowing all over the place and I just cant be bothered to fight my way into the Denver for a home show - so surfs up internet style.

I just spotted that 10.1.3.3.1 has hit OTN and is available for download - knew it was coming soon just not that soon. Are you keeping up with all these releases?

http://www.oracle.com/technology/software/products/ias/htdocs/101320bi.html.

Its available bundled into the BIEE release or as a standalone release - take your pick. As you can see its a point-point-point release so dont expect too much over 10.1.3.3. We have squeezed some new stuff in along with bug fixes. Biggest feature is a set of scheduler related web services - this really opens up te server to you if you want to call it frm another application etc. I'll be blooging about these and the scheduler APIs in the near future.

New features and install docs - http://download.oracle.com/docs/cd/E10415_01/doc/nav/portal_1.htm

That's my Sunday, now back to the warm fire and a cup of cocoa. 

There was a question on our internal mailing list today on our translation methods - the answers are in the user guide but the interesting thing was a mail from another BIPper. Microsoft have released a pretty decent XML editor at last. Last time I tried it, it was not a good experience, there still seem to be some niggly issues but overall its pretty useful.

Its not a style sheet editor but if you need to do some work with XML - I seem to spend my life doing this, debugging templates, building demo XML, etc. I get to use the great but expensive, XMLSpy but considering the features I use, the MS solution is pretty good. Get it here:

http://msdn2.microsoft.com/en-us/xml/default.aspx

Here's another 'Open XML Editor' - pretty good, not quite as sophisticated as the MS offering. And another from Peter, better than the Open XML and more on a par with MS.

Of course, if you have jdeveloper installed you get an XML editor included - but if you do not have it anyway, its a bit of an overhead if all you want to do is work with XML files.

OK OK, not a great pun - but multi threading has finally come to hte BIP engine in 10.1.3.3. You are not going to see much if any improvement on single CPU or single core CPU machines but if you are running on something a little more meaty - crank it up baby!

The doc is short and sweet on this - http://download.oracle.com/docs/cd/E10383_01/doc/bip.1013/e10416/bipperform.htm#BABBBFEG

Im pointing it out because, as I have mentioned in the past, our main User/Developer documentation has not been updated - we are having to put information into the new features guides for each release - they will sync up I promise. 

New Features Guide 10.1.3.3.x

Before you EBS, PS and JDE folks ask - nope, you do not have this yet - I know I know, you are all running multi processor machines and this would help immensely - its coming.

Hot off Bogeon, our release guru's computer comes a patch for BI Publisher standalone version 10.1.3.3.x users that might be hitting any number of the following issues:

6470598 - PERFORMANCE PROBLEM WHEN SUBMITTING DELIVERY REQUESTS VIA IPP
6473605 - ENTERPRISE BURSTING TAKES MUCH LONGER THAN REGULAR REPORTS
6499814 - CSV OUTPUT SKIPS NULL VALUES
6502347 - CLOB IN DATA-STRUCTURE IS NOT PROPERLY GENERATED WHEN SIZE EXCEED 1024

The patch you want from metalink is 6499814 and to save you more time here's the readme:

INSTALLATION FOR ENTERPRISE
----------------------------
1. Go to deployed xmlp enterprise application root directory.
  If you used the installer this is ORA_HOME/j2ee/home/applications/xmlpserver/xmlpserver
  where ORA_HOME is your Oracle Home directory for the XML Publisher server installation.
2. Stop the application xmlpserver.
3. Replace WEB-INF/lib/xdocore.jar with new file.
4. To remove cache in oc4j, go to ORA_HOME/j2ee/home/application-deployments and remove directory "xmlpserver".
5. Restart server.

At least the application is pretty simple ...

I received an interesting question from Mark Nelson, one of our SCs last week from a customer he is working with. It concerned fonts - as you know we use MSWord on a Windows platform to create templates. Now before you unix/linux fans ask - no we have not certified on OpenOffice or similar. We have built a few templates by hand using OO - some things work, some dont. Right ow the demand is not high enough to warrant certified support of OO or similar and the porting of the template builder to OO would be a mamouth task.

Moving swiftly on, fonts on Linux - so you build your template on Windows and lets assume you use a few external fonts, say Wingdings for some checkboxes. It all works and tests fine on your desktop - now you need to move to production on the server and you use Linux.

Where do you stand on using the MS Windows wingdings font on a linux server? Will it work? What licensing should I be aware of?

Well, we do not ship those fonts to you nor license them - I must give all credit to Mark for digging the following nugget of information up.   

Microsoft released their TTF fonts for use on other systems with no license charge, provided they were distributed in their original format.  They are no longer available on Microsoft痴 web site, to the best of our knowledge, but they are available here,

An easy way to install Microsoft's TrueType core fonts on linux   

The source forge site provides instructions on downloading and installing the fonts. I have to admit to not having tried the instructions but if any one out there can confirm or deny them I'll share experiences. 

Another shameless OOW session plug - I wrote about the Reports Repository last week - Joe Gum and Elise Mattei form the Finapps strategy will be presenting RR in all its glory. I have sat through a dry run and its not gonna be a 'death by powerpoint' session. I have known Joe for some time, he's a great guy and a lot of fun - its going to be an awesome session, informative but fun - no chance of nodding off in the back. 

The title is a little cryptic for those of you looking for Publisher related sessions but its all about sharing publisher templates rather than music, software and photos - get it? The room is filling up quick so get a reservation in - if you can not make it but have booked for the EBS Publisher session on Tuesday afternoon you'll get to see RR - I have cajoled Joe into joining us for a demo.

Next week - its regular OOW pictures and short commentary coming at you. I have Flickr set up to push images and editorial out to the blog via my cell phone. So I can blog from the demogrounds, sessions, some bar of an evening that Im sure to end up in - OK maybe not the bar. Hopefully, those of you that can not make it will get some 'publisher' flavor of OOW.
I'll not be 'twittering' thou - Jake mentions the OOW twitter stream created by Eddie on the AppsLab blog - I still can not see the value. If I can get images and editorial out to the blog in the same time that I can 'twitter' a message then why not blog it to the masses - easier to consume than a stream of messages I think - maybe I'll see the light and have my 'road to Damacus' moment but I doubt it. We have all but forgotten how to talk to one another - I even caught myself saying this week 'I'll drop them an email.' I caught myself and 2 minutes later it was sorted out on the phone 'voice to voice' - I followed up with a mail thou. Just talk people!

Update: Just went and looked at the OOW twitter feed and Eddie has hooked up Oracle blog related headlines to the feed any way - so if twitter is your preferred medium you'll at least get links to publisher blog entries. 

Scouring the forum this morning I almost had an 'instant message' session with Darice, we were pinging the forum back and forth so quickly - a great way to communicate and really shows the value of the forum. There were problems getting counting working when re-grouping. We ended up exchanging emails and I think things are going to be working now.
Another thread on counting came up today too and is worthy of mention here. It came from Vadim (nagornyi):

Here is sample XML:

<ROWSET>
 <ROW>
 <DEPT>A1</DEPT>
 <DATA>2112</DATA>
 </ROW>
 <ROW>
 <DEPT>A1</DEPT>
 <DATA>7985</DATA>
 </ROW>
 <ROW>
 <DEPT>B1</DEPT>
 <DATA>8452</DATA>
 </ROW>
 <ROW>
 <DEPT>C2</DEPT>
 <DATA>2159</DATA>
 </ROW>
</ROWSET> 

Using Table Wizard, I am getting this report:

A1 2112
 7985 
B1 8452 
C2 2159 

Everything OK, but at the report header I need to count the number of DEPT, i.e. 3. How to get it? Thanks.

There are ways to do this programmatically in XSL but its nasty, very nasty. Thats why we have a function that makes life tres simple - 'distinct_values'. Its a Publisher extension that does all the heavy lifting for you in the background, it takes the format:

xdoxslt:distinct_values(Node name)

and returns a numeric value.

In Vadim's case

<?count(xdoxslt:distinct_values(/ROWSET/ROW/DEPT))?>

A word of caution, its expensive, just like DISTINCT in SQL its going to require a lot of processing to read the node tree in and process it eliminating duplicates. There are other slightly less expensive ways to achieve the same using XPATH but for now, just use it wisely.

An update - Nilanshu came up with the XPATH solution I was aluding to above ... remember the data must be ordered by the data you want to count and remove the duplicates from.

If your data is ordered, then you may simply count all ROWS that have a different DEPT than the previous one, keeping into account the first row of course. This might give you a better performance for large XML files.

<?count(ROW[position()=1 or ./preceding-sibling::ROW[1]/DEPT!=DEPT])?>

XPATH 2.0 does provide something similar, but AFAIK XPATH 2.0 does not work in Oracle XMLP yet.

<?count(distinct-values('DEPT')?>

Have you ever set up the Enterprise release with OBIEE or another security model only to find something got screwed up and we have locked you out of Publisher? If so, read on ...

I have to thank Mike for digging up the solution. We use a configuration file to store security information including whether you want a local superuser or not. Its always a good idea to set this up no matter what the security model you want to use - company security policy not withstanding of course.

   <property name="SUPERUSER_PASSWORD" value="admin"/>
   <property name="SUPERUSER_USERNAME" value="admin"/>
   <property name="GUEST_FOLDER" value="false"/>
   <property name="ENABLE_SUPERUSER" value="true"/>
   <property name="SECURITY_MODEL" value="XDO"/>

Happy Thanksgiving to those readers in the US - for the rest of you Happy Thursday. Before I head off to help eat some monstrous great bird with a group of friends and family I have a great customer tip to share.

You may remember the article I posted last week on a check security solution from Evergreen - they provide the removable hardware that holds the check singatures and micr font, it sits between server and printer. They also now provide the software solution that integrates with Publisher and enables the signatures to be read and pulled into the final document.   

I received a mail from Dustin Frysinger and Keith Preston from the National Lime & Stone Company - they have been working on their own check security solution wanting to come up with a lower cost alternative. They had not invested in a SIMM printer module to hold the check signature images but they still wanted a hardware solution. I have mentioned mounting a USB or memory card as an accessible drive in the past; Dustin and Keith have actually implemented it. They have the check signatures on a memory card that can be kept locked up until a check run needs to be executed. When they are ready, the memory card is inserted into a card reader on the server and its mounted as a drive - I have played with mounting the card on a web server and gotten that working in the past. They took a simpler route and just mounted it as a drive - simpler is always better in my book - I have to kick myself sometime for being too fancy in my solutions.

In their template they have logic to pull in the signature images as required i.e.

url:{'file:///media/USB_DISK/sig1-5.gif'}

Rather than a regular URL they are using the 'file' URI to pull the image in. Of course that drive needs to be accessible from where ever Publisher is running. If the check print is run without the card in place then signatures are of course not pulled in and there is a blank area left. Still a little vulnerable may be - my bank never seems to check my signature until after tha mony has been drawn - maybe things are stricter for a company check. However its not hard to add the logic to put in a 'VOID' image if the signature image is not present - I blogged this a while back ... actually it was back in April 06 that's more than 200 articles ago! If you implement that logic alongside the card method then I think the checks can be as secure as they can be.

Its a nice solution to a tough problem - Im working with a few customers that do already have a printer SIMM module that they need to communicate with via escape sequences. Basically, we need to inject PCL codes into the document prior to sending to the printer to ensure images are pulled in as necessary from the printer module. Its a tough nut to crack because we do not generate PCL ourselves so we need some injection method - Im hoping to be able to publish that soon.

Dustin and Keith have been up to a host of other stuff but I'll save that for another day. If you want to know more about their approach, let me know via comments or mail and I'll hook you up - Im sure they'll be cool with that.

Now, Im off to stuff myself with stuffing, veg out on veggies and maybe eat some turkey - its a tough time of year for the poor old turkey.  

If you are trying to use sub templates in 10.1.3.3 or you used to have them in previous releases and they are not working for you now ... read on. You are not going mad ... we slipped some new security into the release and by default the security is 'On' and stopping 'external content' from being pulled into your templates.

This property controls whether to use secure file I/O mode for the XSLProcessor and for the XMLParser. When it is set to true, file I/O such as import sub-template or import an XML document is not allowed. Its set via the Admin > Runtime Configuration pages. It can also be set at the report level too.

So, now you know, apologies for any confusion, misery and heartache caused! 

Check printing seems to be the hot topic right now ... I have seen far too many requests for a check print template out in the publisher-sphere. Its a little complex with the cheque or check (depending on your version of English) at the top. It then repeats the invoices to be paid twice allowing 10 rows (this is adjustable) for each. If it spills to a second page then the check portion is not printed.

It also has some conditional formatting around the check signature images - this is a soft image solution but could easily be used with the 'hard' solution I blogged last week from Dustin and Keith from the National Lime & Stone Company.

In the zip file that contains the template, data, PDF output and signature images there is also an annotated version of the template that attempts to explain how the thing actually works under the covers.

Its delivered as is - its actually based on an Oracle Report XML output from EBS so apologies to non EBS folks but it should be enough to get you up and running at least.

And before you try, not that you would, but you might, thats a random routing and account number :0) 

I have to thank Dave Matlock on the forum for the following. I have not covered the web services we offer with BIP in 10.1.3.3.1 - its not going to be a 10 line java example ... sadly.

However, it appears that in PHP, it can be a 10 line code example, Im not greatly familiar with PHP so have not tried this out. If it really is this straightforward bring on PHP please.

I wanted to share this code with the community. I needed a way to integrate a PHP5 application with BIP Enterprise without sending the user to the BIP application. The answer was, of course, web services. Thankfully the BIP folks provided this interface. There is no error handling in the example below.

$wsdl = "http://hostname:8088/xmlpserver/services/ReportService?wsdl";
$client = new SoapClient($wsdl);

$params[] = array('name'=>'v_item', 'value'=>'NIW-TEST1', 'hasMultiValues'=>0);
$params[] = array('name'=>'v_item1', 'value'=>'NIW-TEST2', 'hasMultiValues'=>0);

$report = $client->getReportData('/Guest/Status/Status.xdo',
$params,
'uname', // 'Guest' can be used with a null pwd 
'pwd', 
'pdf',
'/Guest/Status/Status.rtf');

header('Content-type: application/pdf');
header('Pragma: no-cache');
header('Content-disposition: attachment; filename=Status.pdf');

echo $report; 

Thanks Dave, I need to find the time to get more familiar with PHP.

We released a patch on Friday that has a bunch of fixes and a few enhancements included - its to be applied on top of 10.1.3.3.1 installs. Just follow the readme for install instructions. It covers the following bugs/enhancement requests:

Just search on metalink for patch 6689777 : UPDATE FOR BI PUBLISHER ENTERPRISE 10.1.3.3.1

Pieter from the support organization contacted me yesterday - if you ever get him on one of your TARs, you have hit pay dirt, one awesome dude! He was pinging Leslie (documentation) and me about a feature that he could not find in the user docs - it was another that has slipped through the cracks. 

If you have links in your report outputs until recently you had no control over whether the link, when clicked, would open in the same page, or a new window - in fact by default it would be in the same window. We do have support to allow you to control the target a la HTML but its not documented until now.

There are a couple of methods depending on your version of Word. If you dont have 2003 or above you can use the following method.

In your links just add the following ??target=XXXX ie www.oracle.com??target=XXXX where XXXX is the target you want e.g. _blank. Your links will look a little funky but you'll get the desired effect.

If you have Word2003 or later you can use native Word functionality to set the target.

You can also pass a dynamic value to the template to define how the link should work. Use '{$myTarget}' in place of XXXX for 2000 and for 2003+ just put{$myTarget} in the target dialog. Of course 'myTarget' is a template parameter that you define in the template and pass at runtime.

This is one for those of you bursting with the standalone release - for the rest of you, I'll try to make it interesting.

I have to admit I was not going to blog today, too much other stuff on that needed my attention. The ever present Oracle Messenger window on my laptop has been blinking most of the day with questions and comments. I guess I chose for it to be ever present but working remote you need to try and be available through all channels. Klaus (DevMgr) will disagree here as we can never find the right moment to share a 'catch up phone call'.

One of the blinking windows caught my eye, Kevin, from our support organization had a question on our internal mailing list and was pinging me for a followup. Kevin, like Pieter, in my opinion is worth his weight in gold  - those of you that made it to the demo booth at OOW07 and found Kevin, I hoped pumped him for as much information as possible. He is a proverbial gold mine of all things XMLP/BIP - the guy remembers specific patch numbers for heavens sake. These maybe burned into his brain because its our fault for some stoopid bug and every one of you logging SRs has hit the same problem. Nevertheless, if you happen to meet or interact with Kevin in an SR, once more you have hit gold. I could not think of another word for 'pay dirt'- that's reserved for Pieter. I dont think its really a 'UK' english expression and I could not think of an equivalent ... moving on.

 So, Kevin was getting a null pointer exception with his bursting run - no matter what he did. One of those great error messages we sometimes dump.

oracle.apps.xdo.servlet.scheduler.ProcessingException: java.lang.NullPointerException 
at oracle.apps.xdo.servlet.scheduler.XDOJob.runBurstingReport(XDOJob.java:2012) 
at oracle.apps.xdo.servlet.scheduler.XDOJob.execute(XDOJob.java:315) 
at org.quartz.core.JobRunShell.run(JobRunShell.java:195) 
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520) 
Caused by: java.lang.NullPointerException

A bit of digging around on his system and a neat trick to get a look at the burst sql result and I found the problem. First the trick - once you have your bursting sql or even want to test it while developing. Just create a new Data Model and paste in your bursting sql

Now you have the main sql and the bursting sql - just get a Concatenated Data Source

When you run the report (not in Bursting mode) you'll get the bursting sql result too.

This is how I found the issue, Kevin's result set had an anomaly.

Notice we have null values for the KEY element - we cant handle that - and I dont think we should do. I'll accept we should fall over more gracefully but how should we handle the null values for the KEY value to match on? Thats up to you to handle - I got around it with an NVL value to avoid the null KEY value and thus the falling over. 

select  nvl(NOMINEE,'Kevin McDermott') KEY,
'Award1'  TEMPLATE,
'RTF' TEMPLATE_FORMAT,
'en-US' LOCALE,
...

Now back to work ...

The 10.1.3.3.2 BIP Standanlone release is out and about - its actually been out for a little while but this post somehow slipped through the cracks and is now only reaching the light of day. The .2 designates that its really a porting release there are a few new features.

More information here:

http://download.oracle.com/docs/cd/E10383_01/doc/bip.1013/e10416/bip_101331.htm#BABBHIJE

You can download the release here - http://www.oracle.com/technology/software/products/publishing/index.html

and get the latest docs here, http://www.oracle.com/technology/documentation/bi_pub.html remember we still have a new features guide + the regular user guide.

Another document slippage, I was a little embarassed this week to send someone to our documentation asking them to rtfm first. Only to find we had not documented the feature they were interested in, well, we had, it was just not there ... profuse apologies ensued.

The feature in question was our date parameter support we have the ability to default dates into parameter values and not just SYSDATE either - we can get quite sophisticated ... read on

Date Parameter - passes a date parameter. If you select a Parameter Type of Date, the Data

Type automatically defaults to Date. Enter the following:

• Display Label and Text Field Size in characters.


• Date Format String this has to conform to the java SimpleDateFormat format masks. More information here, http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html

Examples of these masks include:

• Default Dates - this can either be a fixed date, must match the date format mask or there are the following supported date functions:

These functions also support simple addition and subtraction operators so SYSDATE()-1 would be yesterday痴 date. When used these date functions need to be surrounded with curly brace followed by a dollar sign i.e. {$SYSDATE()$} or {$SYSDATE()-1$}

• Date From and Date To ? this will limit the date picker UI to dates within the range specified. Currently these fields can not accept the date functions documented above as input. Note dates must conform to the date mask entered above.

So, for those of you looking for date parameter info ... its here and it will make it to the docs soon.

Well I made it back to the wintery wastes of Colorado on Friday getting home at some ungodly hour of the night after fun and games getting my poor old Ford started after a week shivering in an open parking lot. Im being disingenuous to Colorado - I still love it. You can actually get up to 70 miles per hour on the freeways here and find parking in Denver - from the airport to the office took nearly half an hour last week - the great 101 and California traffic. I spent yesterday with the kids, sliding down mountains in 'white out' snow conditions, carving through light and fluffy powder  - it's good to be back!

I got to sit on lots of desks last week talking to team members about what they are up to for coming releases - 11g is on the horizon but there is at least one release before that, 10.1.3.4 and its packed with new features, as we get closer to the release I'll work them in more detail.

• Server Side Preferences
  
• Document cache Enhancements
  
• Cascading parameters
  
• Complex type support in Web Services
  
• Workspace Integration
  
• Smartspace Integration
  
• Web Services
  
• Enh for Scheduling Web Services
  
• Scheduler HTTP / WebService Notification
  
• OLAP Data Source
  
• Template Builder Enhancements
  
• SQR Integration
  
• Adding Context Sensitive Help to BI Publisher Server UI

Bear in mind, they are all not set in stone and in good Oracle tradition, I dont have a definite release. There are some exciting features coming, from an integration point of view, the Scheduler web services and the HTTP / WebService Notification stick out.
The former is going to allow you to interact with the scheduling engine and provide complete control over it with abilities to stop, start and modify schedules, you'll also have access to historical report outputs and snap shots of data.
The rather crptically named 'HTTP / WebService Notification' is the ability to call what is an 'after report' trigger. You can now hook up post processes to a BIP report using this feature. Maybe you want to take the report output from BIP and put it into a BPEL approval flow prior to delivery with an ability to stripe DRAFT through the document until its finally approved. Maybe you want to update all the records that have been reported in the output. The trigger and the web services are going to give you that ability and really open up the BIP server to minute control.

Before we get that far, I want to spend some time getting through the existing web services and showing you what you can do with them ... 

I had grand plans to spend last night getting the first of the web services posts together - I have all the code samples just need the eloquent prose around them ... my plans were dashed when my wife got me to help out on a school fund raiser 'flyer' - what ought to have taken an hour, stretched into the night - what joy it is to mess about with MSWord trying to combine 4 documents and get the look and feel the same ... NOT!
So you're getting something as useful, if not more so, that I had ready yesterday.

We have been pinging mails around over the last day or two - a customer asked 'what are the differences between the standalone release and the embedded EBS version.' We all thought someone had documented those, alas none of us had, but Pradeep one of our PMs that you'll find regularly lurking on the forum, took it upon himself to bash out a quick table of comparison.

I have embellished a little here and there ... 

By no means a highly detailed and exhaustive list, the big ticket items in my mind are the delivery and web services - please dont even ask right now. If we extend the question, then we get to JDE and PS differences from the standalone. I need some time to get that those together.

Im up in Denver, spending the day with the JDE folks so its a quick post today. There are a couple of interesting documents just posted to the BIP home page that you might find useful.

The first is a guide on how to migrate from Crystal to BIP, authored by Pradeep and Mike from the PM team - Converting Crystal Reports to BI Publisher Its a manual process but at least shows that it can be done with a little effort. The steps are laid out very nicely and should be easy enough to follow for those of you migrating from Crystal. A more automated approach is under discussion.

The second is Integrating BI Publisher with Oracle Forms - I have mentioned this paper before by Jurgen and Rainer in our German office but the paper has been updated since. Its a good intro the web services we support and then how those can be called from Oracle Forms. Well worth checking out - if you're looking for Forms integration of course.

The end of another hectic week - I have been on so many customer calls I have lost count - but they have raised some good questions which I can turn into blog articles so there were some good benefits for all parties. One such was this morning. Those of you using the standalone/BIEE release will know that we communicate with printers via Internet Printing Protocol (IPP) - typically using CUPS or a windows print server. Not news anymore but so many of you have your printers set up as network printers and they work  - so that old adage applies, 'if it ain't broke don't fix it'.

I was talking to just such a customer this morning, they have installed BIP on its own server but were concerned about installing and administering CUPS - they were not allowed root access to the server so if CUPS required it to administer printers, it would be a showstopper!
Kei, from our development team to the rescue ... you can configure the CUPS server to use a non-root enabled user to administer the printers and fax servers.

1. Create an OS user 'guest', set the password.
   
2. Open cupsd.conf and go to <Location /admin> section
   
3. Add a line "Require user guest" in there.
   
4. Restart the CUPS server

Here's the detail.
http://www.cups.org/documentation.php/ref-cupsd-conf.html

and whats required in the cups.conf file

--- cupsd.conf

<Location /admin>
#
# You definitely will want to limit access to the administration functions.
# The default configuration requires a local connection from a user who
# is a member of the system group to do any admin tasks.  You can change
# the group name using the SystemGroup directive.
#
AuthType Basic
#AuthClass System
#AuthClass User
Require user guest

Cooll stuff this CUPS!