Tim Dexter

A mad few daze getting presentations and demos ready for internal meetings on BIP for Fusion Apps. Yep, it's coming together and its going to be good from a user and a developer point of view. I want to say its going to be 'sweeeeet', my son's favorite answer for all things good at the moment, but I won't.

Back to data engines, I said I would get into the more complex case that I stated at the end of the last entry.

How can we get away from this 'pull' model where a report is scheduled to pick up the data, what if we wanted to control the timing of the report, say those XML files were being dropped into a directory periodically and each time the new file was dropped in we wanted to get BIP to run the report. While we are at it, maybe we have retail branch sales data coming into a central server, all of those XML files have the same data structure. We do not want to define a report for each branch, we only want a single report definition that picks up the appropriate filename at runtime, processes it and sends an email to the branch manager with their results and a copy to corporate.

This is a real use case scenario I worked on with a customer recently andI believe they are well on their way to implementing it. Branches would periodically drop their data files into a central directory and need a report sent back tout suite. The first hurdle to over come is how to invoke Publisher when a new file hits the directory? This customer has a centralized scheduler called Control-M - fairly widely used I think. This product has the ability to act as a directory daemon looking for files as they are dropped into a directory and then invoking some other process - theres our hook to get BIP to execute a report. Its not that tough to create your own in Perl or similar language. Here's the architecture I came up with:

Whats going on?

The Control-M product is constantly polling a specific directory looking for new or updated files. These are XML data files from the branches, when a new file appears it invokes the shell script, this is passed the filename as a parameter. It in turn invokes the java web service client class that then calls the BIP server to run a given report. The web services we provide allow you to have tight control over the report, run it now?, run it later?, which template to use?, what output to generate?, where to deliver it? All of this information can be passed to the WS client code from the shell script or you could have the WS client class to parse the XML to find out this type of information e.g. the branch's email address.

On the BIP server we have a single report defined that uses an HTTP data source with the filename as a parameter. This calls a servlet that is looking over the directory and based on the incoming filename parameter. I guess you could just have a servlet acting as the daemon to recognise new/updated files and make a call back to BIP. In this case Control-M is orchestrating the whole process so the servlet is just returning the XML file to BIP for processing.

Thats it really, the only point of note is the return codes that get passed back to the WS class and thence to the script and ultimately back to the Control-M application. Fairly simple architecture, that allows another application to control the BIP server. If you have such a requirement, I'd love to hear from you!

Well it's almost mid May here in Colorado, so you know what that means, yep, its snowing! Not too heavily but the temperature has dropped from a balmy 70F yesterday to a chilly mid 30s today. Apparently we need it, inspite of nearly a 120% of average snowpack for the year we are facing drought conditions in Colorado this summer - so we are xeriscaping our garden. My wife and I are enjoying a discussion around me wanting to rip up the water hungry grassy turf we have in our yard and replacing it with concrete ... Im kidding. Anyhoo, enough 'weather' related musings ... remember Im English, we are nearly all obssessed with it.

Most of you that use the BIP Enterprise release know that it can get data from almost anywhere, files, dbs, web services even Excel with a little help. I have had a few questions about the 'how' on getting 'remote', non-database data i.e. a 'data engine' already exists and it serving up XML. I thought I would cover some of those and end on the big question that I have been working on with a customer. What about a data engine that BIP can not connect directly to? This tied in with, 'we dont want to use your scheduling engine we have our own, how can it work with BIP?' Tasty questions, we'll come back to later.

So, what are the options for fetching data  from an existing data engine? Thats going to depend on how connected your engine is to your network. As I mentioned above BIP can connect to data sources in multiple ways.

HTTP/S - if your engine can serve up data upon an HTTP request, then BIP can call it and pass parameters on the URL. If you can write a servlet that can call the engine and serve up the data on its behalf you could go down that route too.

Web Services - can you wrap your engine in a web service? Again, BIP can call for the data via the service and pass parameters into the service to influence the data set.

XML File - this is probably the simplest method. IF your engine can generate a file and drop it into an accessible directly for BIP to pick up then you have a solution. Its a simple case of registering the directory with BIP and then setting up the filename in the report definition for BIP to look for.

Excel - this needs a little effort to get the data from the file but we provide the APIs you just need the servlet to read it and serve up the XML data to BIP upon request. 

All of these options will require the BIP report to be scheduled to pull the data periodically. In the first two cases thats probably OK, BIP is making an indirect call to the data engine requesting data at the time the report runs. In the last two cases BIP is going to rely on the files being refreshed prior to the scheduled report running otherwise you are going to see the same data.

How can we get away from this 'pull' model where a report is scheduled to pick up the data, what if we wanted to control the timing of the report, say those XML files were being dropped into a directory periodically and each time the new file was dropped in we wanted to get BIP to run the report. While we are at it, maybe we have retail branch sales data coming into a central server, all of those XML files have the same data structure. We do not want to define a report for each branch,  we only want a single report definition that picks up the appropriate filename at runtime, processes it and sends an email to the branch manager with their results and a copy to corporate. I could go on, but thats enough of a scenario for the time being. Next time I'll dig in and talk about how you might tackle just such a scenario using BIP.

Another tenous and cryptic title, today we're talking connection pooling with BIP.

We have had a question recently on how to set up a connection pool for BIP rather than direct JDBC connections. Pooling has its advantages of course - rather than each report having its own 'personal' connection to the database at runtime it which can get expensive very quickly in terms of resources. Instead, BIP can pick up a connection from a pool, assign it to the report, once the data is fetched the connection is released back to the pool.

Now BIP provides a page to enter the JNDI connection pool information but we are little vague or we at least expect you to know what you are doing when it comes to setting up the pool in the J2EE container. To supplement the doc I thought I'd walk through setting up the connection pool in OC4J and then how to get BIP to use it.

Firstly, you are going to need the oc4jAdmin password to be able to do any of this. If you installed BIP on your server then the installer will have prompted you for a password - hopefully you can remember it. If someone else installed it for you - either talk nicely to them to get the password or buy them a beer and get them to read this blog article and get them to set up the pool. Once you have the password login to the Enterprise Manager, typically:

http://server:port/em

This will get you to the main home page.



Now click on the Administration tab and then on the Services > JDBC Resources icon. You'll end up here:

There is a sample pool and data source set up already for reference. We are going to create a new pool and datasource to use it. Click on the 'Create' button for the Connection Pools section.

Select the BIP application and 'New Connection', this is going to make the pool available to the BIP server. Now hit continue.
Fill out the form providing a name for the pool, etc. Check the documentation if you need to get into Connection Factory Properties for your database.

Before you move on test the connection.

Now move to the Attributes page to fill out the size of the pool, etc. Worth reading the OC4J doc here to get some advice on the numbers to use. It going to depend on your expected reporting load on the system, etc.

The Proxy Interfaces tab is only needed to wrap vendor implementations of java.sql objects when using managed data sources. I dont need it as Im using Oracle objects - check the doc if you need more info.
The pool is now set up so click Finish. Next we need to create a DataSource for the pool - this will manage the pool.

Assign the Application and select Managed Datasource to allow OC4J to 'look after' it for us. 

Fill out a name for the dataource and enter the JNDI location. Check the doc for more details but here's what it states on the JNDI name.

Enter the Java Naming and Directory Interface (JNDI) location (or path) for the data source. Deployed applications use this information to locate the data source. For example, the JNDI location of the OracleManagedDS data source is jdbc/OracleManagedDS.

The one I created has the following location, 'jdbc/BIP10gSource', its a virtual location so you will not find it on the disk. The location is important as we are going to reference it in the BIP JNDI connection setup. Once you're done click Finish, you are taken back to the main data source page where you can test the datasource. We now have a pooled connection we can use in the BIP server to a database.

Now bounce the OC4J instance and bring up the BIP server and get in as an Administrator and navigate to the JNDI Connection page (Admin > Datasources > JNDI Connections) and Add Data Source.

Give it a name and the JNDI location that you defined in the OC4J UI - then hit Test Connection to make sure its all working.

You can now use the connection for your reports - just select the pool name rather than a direct database connection. You're now swimming so gimme 30 laps!

Well another release looms, 10.1.3.3.3 should be out within the week. Another point point point point release that we have squeezed some new stuff into. Nothing major but useful none the less - bigger things will come with 10.1.3.4 later in the year, will have to save those for now. I must thank Leslie for the majority of the following text, where would we be without her!

So, whats in there?

1. Bundling of DataDirect drivers - we have noticed that folks have been struggling when it comes to setting up connections to databases other than Oracle. The database vendors JDBC libraries do not always work as we expected leading to issues when you are either retrieving data or setting up the scheduler schema. So we are now shipping drivers ourselves for

• IBM DB2 v8.1, v9.1
• Microsoft SQL Server 2000, 2005
• Oracle 9, Oracle 10g

To help on the scheduler setup front the admin page has been updated to help you out a bit more.

2. Link to this report now configurable - I wrote about this feature a while back. Its the ability to get the URL shortcut to a specific report. Its neat but not all of you wanted it available to your users. So it's not configurable on a per report basis.

3. RTF Output Enhancements - as many of you have seen all output formats are not created equal with BIP. PDF outputs get everything we can get into them - the RTF output came in a poor second. We have addressed some of those shortcomings. We now support the following in the RTF output :

• "Keep Paragraph with the Next Paragraph" - The RTF standard includes a command that enables you to "keep a paragraph with the next paragraph." This command prevents a page break between the paragraph for which it is enabled and the following (or next) paragraph
• Keep Paragraph Intact - The RTF standard includes a command that enables you to keep all the lines of a paragraph together ("keep paragraph intact") and prevent the paragraph from breaking across pages.
• Support for Table "Heading Rows Repeat" - The RTF standard includes a command that enables the repeat of the table heading rows when the table breaks across pages.
• Support for "Table Row Keep Together" - The RTF standard includes a command that enables you to prevent a table row from breaking across pages.

3. Support for Multiple Cascading Parameters - Cascading parameters enable you to filter available values for each parameter in a set of parameters that have a relationship. The choice you make from the first parameter in a set of cascading parameters determines the available values for the next parameter. In this way, you can filter choices down to a manageable number for each cascading parameter. Previously only two levels of cascading parameters could be supported. BIP now supports cascading parameters beyond two levels.

4. Support for Vertical Alignment of External Graphics - Support for the attribute "vertical-align" for the FO element "external-graphic" has been added in the 10.1.3.3.3 release of BI Publisher. This enables you to format the alignment of an externally referenced image inline with text. This attribute can be applied when coding an XSL-FO template or an RTF template. The valid values for the attribute are:

• bottom
• top
• middle
• text-top
• text-bottom

5. Support for Multiple Currency Symbol Displays in Report - The format-currency function now accepts a third parameter, "displaySymbolOrNot" to enable the display of multiple currency codes in a single report. The International Standards Organization (ISO) currency code must be present in the report data to pass to the format-currency function.
This is pretty cool, you can now get BIP to include currenct symbols next to your values in a report.

Some nice enhancements for a minor release, once it is released I'll cover some of the features in more detail.

Scouring the forum while listening to web demos today and came across a post from 576489, a mystery poster - who are you Sir or Madam?

Anyhoo, the title of the post was 'Limitations in excel output' he/she was asking me to provide some more info on the Excel output we provide. As many of you know or have noticed - our Excel output for all flavors of BIP/XMLP is not true binary Excel output. It's actually XHTML with the mime type set  to get Excel to open it. Understand its not Excel HTML but XHTML - read non lazy HTML. So, there is going to be a limit in what you can generate in the Excel output. Some Q & A:

Q1. Can I have formulae or macros in the Excel ouput?
A1. No

Q2. Can BIP use multiple sheets in the Excel output?
A2. No

Q3. What happens when the number of rows generated exceeds the Excel maximum?
A3. BIP will truncate the data

Q4. Where are the images in the Excel, I had some in my template?
A4. You need to set a couple of properties to allow use to access them. Yes, you need to be connected to the network to get the images.

Base image URI html-image-baseuri Base URI which is inserted into the src attribute of the image tag before the image file name. This works only when the image is embedded in the template. Image file directory html-image-dir Enter the directory for XML Publisher to store the image files that are embedded in the template.

With standalone 10.1.3.4 the Excel output is actually MHTML where we can embed the images directly in the HTML file so you don't need the properties set.

Q5. Can I generate master-detail Excel outputs?
A5. Yes, you can build multi level report outputs in the Excel output. 

Q6. I have an account number in my data but Excel treats it as a numuric value (right aligning, etc) - I want it left as a string value?
A6.  Either add a <space> character next to the field holding the account value or use the concat function to add the space to the value i.e. concat(MYVALUE,' '). This will force Excel to treat the value as a string. 

Q7. When opening an Excel output in IE7 from BIP the browser starts to open a window, then beeps and closes the window?
A7. Thanks to Ed on this one:

1. Select 'Tools' -> 'Internet Options' -> 'Security' (tab) -> 'Trusted Sites' -> 'Custom Level' (button) -> 'Downloads' from the browser menu.
2. Set 'Automatic prompting for file downloads' to 'Enable'.
3. Save the setting and close the browser window.
4. Start a new browser session and try again. 

Q7. Are things going to get better with Excel formats?
A7. 
      a. With standalone 10.1.3.4 the Excel output is actually MHTML where we can embed the images directly in the HTML file so you don't need the properties set.
      b. The standalone release also has the Excel Analyzer that allows you to create a binary Excel template that can be run on the server and delivered to users.
      c. We have Excel templates in EBS R12 and discussions to get things going in 11i.

Our Excel is not true Excel and it has a limited set of functionality but for many users its enough just to get the data out into Excel. If you have more questions, post em in comments and I'll update the document.

Spent some time this morning with one of our star developers ... well they all are in my opinion. But Incheol has been tasked with coming up with the BIP Fusion integration. Things are changing in Fusion with respect to the BIP architecture and for end users when it comes to reporting but its good change I assure you.

Rather than the embedded approach we have right now in 11i/12, where templates are managed by BIP but report defnitions are in the realm of the concurrent processing UI and schema. In Fusion there will be complete report definitions managed by the BIP server ie extracts, templates, parameters, etc. Im not getting into the details of LOVs here, in 11i/12 LOVs or ValueSets are not just used in report submission but get leveraged all over the place so we're still nailing the 'where, when and how' down for those, so dont ask ... yet!
With the server on the outside of apps the development process will be much much smoother and we think faster, without the need to go to a report definition form in one app, then to an extract definition program, then to a layout definition program, then to the XMLP UI to upload said objects - its all in one place, the BIP server with, data extract building tools, connectivity to the desktop template building tools and even an online template builder so you never need to leave the comfort of your favorite browser!

Getting back to Incheol, he has been working on some of the integration between BIP and Fusion Apps and how developers will create reports and make them available to users and then how users will be able to invoke said reports. There are 3 scenarios that have been identified so far:
1. Ability to schedule a report
2. View an existing instance of a previously run report
3. Run a report from a link ie run it immediately

The first and second are being worked on in conjunction with the scheduling team, Incheol has been looking at the third scenario from an end user perspective but focusing on the developer experience.

The links to reports in this image will not sit in their current position - there are plans to have a common region on the page that will list pertinent reports for the page or the users current role - so ignore the position of the links. Its whats going on under the links thats important. When the link is clicked the data from the associated ADFbc component ie a VO is extracted and the relevant report is called via a BIP API that in turn calls the report on the server via a web service call. The data is passed along with template and output required, the server runs the report and returns the output requested.

This is a little different to other scenarios where the report is called and the BIP server extracts, formats and delivers. Here we are essentially telling the BIP server that we already have the data so just format it for us and pass back the output so dont deliver it.

The returned report will have all of the data present in the VO and not just what you can view on the page itself. The ADFbc will handle all the security required to generate the appropriate data set for the current user. We'll be turning this into a common component that development teams and customers alike can embed in their pages.

A small glimpse of whats coming and as we get more concrete I'll try and share more ...    

Got a good one today that had me a little stumped until I RTFM! To be more precise RTFJD - the JD is the javadoc. The data engine we have does not only consume data templates but you can also throw SQL at it and get XML in return. Neat! One of our customers has been struggling with this, they want to be able to pass parameters to the sql at runtime - we support that but they were having pain getting it to work. Our trusty support guys have been nagging me for some help and I finally got to it last night.

The data engine has a setSql method to allow you to pass the sql and you can then pass parameters as a Hashtable or ArrayList thus:

DataProcessor dataProcessor = new DataProcessor();
 dataProcessor.setConnection(jdbcConnection);
 //Set the query
 dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
 //Create hash to hold param val
 Hashtable parameters = new Hashtable();
  parameters.put("p_DeptNo","10");
 dataProcessor.setParameters(parameters);
 dataProcessor.setOutput("c:\temp\EmpDetails.xml");
 dataProcessor.processData();

Looks straight forward enough but it would not compile or run. I scratched my head for a while. I know we dont docunment the setSql method that well. I ended up in the javadoc and found my problem. I was using the java.util.Hashtable. Hey, I know it, I use it regularly so why not. However the data engine actually needs the Collections version of the Hashtable - com.sun.java.util.collections.Hashtable .. dang! A quick update on the import statements et voila, I have XML coming out based on parameter values. A good lesson for me to read all the doc and apologies to support and the customer for taking so danged long about it!

If you're interested here's the complete code for the method:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.sun.java.util.collections.Hashtable;
import oracle.apps.xdo.dataengine.DataProcessor;
import oracle.jdbc.driver.OracleDriver;


public class DTSql {
    public DTSql() {
    
        try {
         //get connection
        Connection jdbcConnection = getConnection();
         //Initilization//
         DataProcessor dataProcessor = new DataProcessor();
        dataProcessor.setConnection(jdbcConnection);
          //Set the SQL
          dataProcessor.setSql("select * from departments where department_id = :p_DeptNo");
          //Create hash for parameters
          Hashtable parameters = new Hashtable();
          parameters.put("p_DeptNo","10");
          dataProcessor.setParameters(parameters);
          dataProcessor.setOutput("c:\temp\EmpDetails.xml");
          dataProcessor.processData();
        } 
        catch (Exception e)
         {
         e.printStackTrace();
         }
  
    }
        public static Connection getConnection() throws SQLException {
            String username = "hr";
            String password = "hr";
            String thinConn = "jdbc:oracle:thin:@IPG.US.ORACLE.COM:1525:ora10g";
            DriverManager.registerDriver(new OracleDriver());
            Connection conn =
                DriverManager.getConnection(thinConn, username, password);
            conn.setAutoCommit(false);
            return conn;
        } 
    public static void main(String[] args) {
        DTSql dTSql = new DTSql();
    }
}

Gareth from down under has come up trumps again with a great tutorial on bursting in EBS. I made some rash promises at OAUG to provide more information/help on EBS Bursting - why re-invent the wheel when Gareth is already rolling.

Check out his entry a great reference. Issues section duly noted Gareth :0)

BI Publisher EBS Bursting 101: A tutorial/case study including Bursting Control File to email Suppliers Remittance Advice in 1 easy step

I also promised some more info on the standalone bursting - its coming

Apologies to regular readers may be wondering where I have been. I took a week off after OAUG to sit on a San Diego beach to get toasted to a crisp and to avoid any sharks. We were on that same beach the day before it happened! Needless to say my wife was constantly telling me to watch the kids in the surf, checking for any big gray fins in the water and if seen to run or swim like hell!

While I was away, Noelle has been working on some Flex template goodies. We got a requirement a while back to produce a report similar to this:

Its nothing to 'write home about' standard table stuff but the interesting feature is the 'percentage bar' embedded in the table. Two issues to solve here:

1. Building the 'bar' component - its not an out of the box feature.
2. Getting the bar to render inside the table

Noelle, being very cunning, solved both!

Its not exactly the same look and feel but we were after the functionality - the look can be tweaked quite easily.

Tackling the 'bar' component first, Noelle first needed to build the 'percentage bar'. To do this, Noelle used an ActionScript class to create and render the bar. It basically takes a label object and extends it to render the text value and a rectangle shape, calculates the percentage fill to be used based on the data.

Here's the script

// ActionScript file

package {

// Need these packages for rendering and drawing

import flash.display.*;
import flash.geom.*;
import flash.text.TextField;
import mx.controls.Label;

// We are going to extend the label object

public class PercentageBar extends Label {
 override protected function updateDisplayList(unscaledWidth:Number, unscaledHeight:Number):void

 {
  super.updateDisplayList(unscaledWidth, unscaledHeight);

  // Create rectangle shape and width

  var rndedRect:Shape = new Shape();
  var rectWidth:Number=100;
  var rectHeight:Number=15;
  // Fill for the rectangle is derived from value in the main.mxml ie the parent application

  var rectFill:Number=this.parentApplication.percentCalculated;

  //Set the fill type, colors, alphas and ratios

  var fillType:String = GradientType.LINEAR;
  var colors:Array = [0xFF0000,0xFFFFFF];
  var alphas:Array = [1, 1];
  var ratios:Array = [0, 255];

  // matrix - not required but allows you to specify the fill
  var matr:Matrix = new Matrix();
  matr.createGradientBox(rectFill, rectHeight, 0, 0, 0);
  var spreadMethod:String = SpreadMethod.PAD;
  //Start rendering the rectangle
  rndedRect.graphics.beginGradientFill(fillType,colors,alphas,ratios,matr,spreadMethod);
  rndedRect.graphics.lineStyle(1,0x000000,1,false);
  rndedRect.graphics.drawRoundRect(2, 0, rectWidth, rectHeight, 10, 20);
  rndedRect.graphics.endFill();
  
  // Add the rectangle to the label object
  this.addChild(rndedRect);

  //Create the label for the rectangle
  // Value drawn from the percentCalculated value in the main.mxml

  var label:TextField = new TextField;
  label.width = 26;
  label.text = (this.parentApplication.percentCalculated+ "%");
  label.x = 105;
  label.y = 0;
  //Add the text field to the label
  this.addChild(label);
    }
  }
}

Those familiar with java should not be too scared of the code - if you spend some time with it its not that tough to understand. Its code yes! but it adds a huge layer of flexibility to the flex reports.

Thats the 'bar' component, so how to bring it into the main table layout. Thats pretty easy too ...

Remember that the bar component needed the 'percentCalculated' value from the parent application ie the main flex template? Well, we need a function to calculate that:

[Bindable] public var percentCalculated:Number;

// determine the percent of the target number - round before passing back
private function calcPercent(row:Object, column:DataGridColumn ):void
{
  var a:Number;
  var t:Number;
  a = row.actual;
  t = row.target;
  percentCalculated = Math.round((a/t) * 100);
}

Notice we need to declare the 'percentCalculated' variable as public and bindable so the action script class can access it. Its a simple calculation to get the percentage based on the rendered table data.

To get the 'bar' to render we just need to set the last column to use its own renderer ie the PercentageBar AS class that was created.

<mx:DataGrid id="myDatagrid" width="500" height="300" dataProvider="{dataXML.product}" editable="false" enabled="false"
 <mx:columns>
  <mx:Array>
   <mx:DataGridColumn dataField="name" headerText="Product Category"/>
   <mx:DataGridColumn dataField="actual" headerText="Units Sold Actual" id="actual"/>
   <mx:DataGridColumn dataField="target" headerText="Units Sold Planned" id="target"/>
   <mx:DataGridColumn itemRenderer="PercentageBar" width="135" headerText="% of Target" labelFunction="calcPercent"/>
  </mx:Array>
 </mx:columns>
</mx:DataGrid>

You can get the complete source to the flex project here. Thanks again to Noelle for the research and code.

So, a little effort but hopefully you get an idea of how you can take existing Flex objects and extend them to create your own visualizations of the data. You can now tell your users that the 'world is their oyster' when it comes to reporting ... or may be not!   

Well, the elements did their best to thwart me getting to Denver this morning - and OAUG, nearly. I left just after 6am to get to Denver for the 8.30 presentation knowing the roads were going to be dicey. We spend all winter here driving around on ice, a couple of nice days and driving skills go out the window. I saw a couple of shunts and somehow someone rolled their car - they looked OK. Its just over 40 miles to the conference center - I got there by the skin of my teeth. We had a room change that I did not spot - a bigger room which was good but threw me for a second. I was really pleased to see so many faces even if they were a little bleary eyed. Demos did not go so well but I think everyone got something out of it - I certainly did.

We still have such a mix of users some of you are now getting very sophisticated with your Publisher user whereas others are still just starting out - I think we need a 'beginners guide' session and a more advanced session to cater for all your skill levels.

After the presentation we retired for a marathon, hours and a half Q&A session - it was great to see so many folks wanting to hang around and ask questions - even better was the fact that I did not need to answer all the questions!

I have lots of feedback on documentation and ideas for articles on the blog. I was pleased to see folks starting to think about migration of other report types e.g. plsql and sql - if you have samples send them to me and I'll try and walk through some of them here on the blog. Got some feedback on support that I will feed back to the support folks. All in all a good morning, those of you that said you would kindly share experiences good or bad please send me details, email address here. As I wrote last night the ppt is available here and thanks for coming.

Its the night before the morning after the night before - crptic? If you're at OAUG you'll hopefully get it. Tonight is the night geeks everywhere let their hair down and get down and boogie - or stand around the edge of the room laughing at people falling over as the night progresses. Not sure who the music is tonight but I hope you all had a good time.

Hopefully everyone will have clear heads for the XMLP session tomorrow - if not, don't worry I'll wake you up. Its room 203 @ 8.30am be there! I certainly hope to be there - we are getting rammed with heavy wet snow tonight and it's supposed to continue tomorrow!

For those of you that either can not get out of bed or did not make it to OAUG I have posted the final presentation here.

Trying to get back on track here ... too many demos to get working for various events. Back to bookbinding - hope fully the introduction I provided gave you an idea of what the bookbinding API is and what it can be used for. Before we get to the nitty gritty its worth knowing ehats going on under the covers.

We have our disperate PDFs to the left coming from various sources - the BookBinding APIs sits amid the Publisher engine. It takes a control file in the form of XML as a set of instructions on how to build the bound document - the control file will reference layout templates that can be overlaid atop the the final combined document.

The control file is the most important piece - there is no builder for this and it can get complicated but if we start with the basics and work our way up to the more complex you'll understand the structure.

For the examples I have taken a bunch of Oracle product 'brochures' that we can then bind. They reside in the 'oracle_books' directory in the zip file available here. Unpack the zip and check out the first example.

Example1 - Simple Merge plus front page

In this example we will merge the three Oracle overview documents and add a front page to the document. So we will have a document structure as follows:
1. Front Page
2. Database Overview
3. Application Server Overview
4. E Business Suite Overview

The XML control file for this is pretty straightforward:

<?xml version="1.0" encoding="utf-8"?>
<outline xmlns="http://xmlns.oracle.com/oxp/book/">
  <!-- 
   The 'title-page' element should point to a PDF title page inserted 
   at the beginning of the book. (OPTIONAL)
  -->
  <!-- So here is the title page reference, in this case it is going to use an RTF template -->
  <title-page type="rtf">templates/title_page.rtf</title-page>
  <!-- nex follow the three documents we want to combine -->
  <item>
    <content>../oracle_books/database_overview.pdf</content>
  </item>
  <item>
    <content>../oracle_books/appserver_overview.pdf</content>
  </item>
  <item>
    <content>../oracle_books/ebs_brochure.pdf</content>
  </item>
</outline>

I have provided an html page and annotated control file that hopefully provides you with enough information to work out what is going on. The first example takes a template, title_page.rtf from the templates directory and then prefixes it to the following three pdf documents. Cant get much simpler than this.

To run the example you'll find a cmd file that can be run directly in Windows other OS' you need to set environment variables accordingly . Its a straightforward java call to the command line interface of the bookbinder API.

set CLASSPATH=..libxdo.zip;..libxdoparser.jar;..libxmlparserv2.jar;..libi18nAPI_v3.jar;..libaolj.jar
set JAVA_CLASS=oracle.apps.xdo.template.pdf.book.PDFBookBinder
set JAVA_OPTS=-mx150m
set JAVA_CMD=%JAVA_HOME% -classpath %CLASSPATH% %JAVA_OPTS% %JAVA_CLASS%
echo %JAVA_CMD% -debug true -tmp ..tmp -xml Example1.xml -pdf Example1.pdf
%JAVA_CMD% -debug true -tmp ..tmp -xml Example1.xml -pdf Example1.pdf

The command line arguments take the form

-debug true|false -tmp <temporary directory location> -xml <control file location> -pdf <output file location>

Give it a try - you might have to do a little jiggery pokery with the cmd file to get it working. Notice I have JAVA_HOME set to pick up the java executable to run the API.

Enough for today, try out the first few examples and we'll pick up some of the more complex examples next time.

<?xml version="1.0" encoding="utf-8"?><OUTLINE xmlns="http://xmlns.oracle.com/oxp/book/"><!-- So here is the title page reference, in this case it is going to use an RTF template --><TITLE-PAGE type="rtf"></ITEM></OUTLINE>

OAUG has started and I have to start with a couple of apologies. Firstly to the members of the XMLP SIG and Abhishek who runs it. I completely missed Sunday's early bird 8.30am get together - not that I could not drag myself out of bed that early, I promise. I had in fact been up most of the night nursing sickly family members after a rather dodgy meal on Saturday night at one of our local haunts - Sunday morning was spent in an emergency room and clearing up the dinner from the night before - enough said I think! I believe Abhishek is going to try and organize something for later in the week.

The other apology is to the folks presenting this morning on Label Printing and FSG Reporting with XMLP. I very much wanted to attend but my car conspired against me - this is the last time, its headed for the heap or at best the local high school's metal shop!

I did manage to get into Denver this afternoon on my motorcycle - what a day to ride, mid 60s and empty'ish roads on the the way there. With this fella to greet you ...



I got to stand at the back of Mike's session on all things BIP - its interesting being on the other end of a presentation. The acoustics in the room were not great but Mike's timing was impeccable getting through slides and demos with time for questions - perfect!
 We were discussing after the session the mix of audience there was in the room - there are obviously still folks that have not been Bipped. But there are others that have built some very interesting reports/integration and there is clearly a need for a more advanced session - well that was our thought. We joked that we could probably fill a day of BIP only sessions, all the flavors, newbie stuff, template building, data extraction, security, the list goes on ... I need to resurrect the plan to provide webinars for you folks!

We have a little known API that is really very powerful - the fact that its little known is my fault. The documentation is light to say the least and I promised Leslie that I would have a white paper out a long time ago to walk folks through some examples. Its not a paper yet cos I can be lazee and irreverent here on the blog and get information out much more quickly. It will be a paper eventually I promise, mybe even get into the documentation so you are not trawling all over the place for information.

So, what is it? How many of you are involved in pulling together multiple documents or maybe coming up with a programmatic way of pulling them together? Maybe, quarter end financial numbers for a management briefing book that come from multiple systems. How about shipping documents? You have an invoice, delivery instructions, picking slip, customer letters - 'shipping documents' covers a multitude of potential documents depending on what your shipping - but again they may come from different systems or at least separate reports from the same system.

From the graphic you can see that Publisher can take disperate PDF outputs and merge them together into a single document. No great shakes on the surface, plenty of applications can merge reports. We have the simpler APIs to do simpler merging but the Bookbinder API takes things to a whole new level. 

Imagine being able to take those disperate documents and apply another template over the top of them during the merging process. We allow you to add common layout objects, page numbering (chapter and master), chapter start and end pages, table of contents, cross references, you can even split a big result document into a series of volumes for easier loading through the Adobe interface.

In  the next few articles I'll cover working from a simple merge all the way through to an all singing all dancing book binding spectacular! If you have made a start with the APIs and want to get stuck in to the examples you can get them here.

Sometime in the dim and distant past I wrote about mortgage rates and generating charts for masses of data. It was not actually that long ago last Monday in fact but the intervening 7 days have seemed like months. The hacking cough, aching bones, 'flesh creep' and feeling tired all the time - never felt so bad in my life. I now understand all the fuss about getting a flu shot in the fall, although it appears it would not have done me any good this year. I got Type A which flufacts.com states.

Influenza Type A is the most common and also the scariest of the three influenzas, causing the most serious epidemics in history.

Now, I was not scared but bloody hell it was rough!

Getting back to charts, the mortgage rate chart image I posted looked like this:

In the blown up inset you can see that the line is actually made up of multiple rectangles - thats the SVG output we get from the BIBeans chart engine. Its fine for charts with none too many data points but this chart has around 4500 points which leads to a very 'choppy' chart that does not look good nor prints too well either.

I came up with a way to allow you to smooth the chart. Now, when shared with the rest of the development team I got flamed by a couple of them - maybe flamed is too strong a word - let's say lightly toasted!

My approach was to allow the developer/user to specify a smoothing value for the chart, let's call it 'x'. In the chart definition I then used this value to skip every 'x' values in the data set. So for 4500 data points with a smoothing value of 10 I ought to get 450 points - you can see why I got 'toasted'. A better way would be to calculate an average across those 10 skipped values and use that. Thats a fair point but on a chart with 4500 data points and you want it smoothed so you users can get a feel for the data do you want complete accuracy or a visualization so that they can see the 'rough' view of the data?
If the former, then get the extraction portion of the report to do the calculation for you - thats a lot of heavy lifting in the template to calculate that - you could build an extension function to do it I guess - maybe look into that another time. If you want the latter, read on ...

Once the smoothing is implemented you can get something like this:

I admit, there is some data lost in the smoothing but its a better looking chart, IMHO, from a users point of view. Its going to depend on the smoothing value you assign to the data. If you compare the two you can see some of the smaller peaks and troughs are lost. Reducing the smoothing value brings them back but things get choppy again so its a balancing act. Playing with it a value of 20 is about right in this case.  I think even an average value based chart would still lose some of the finer detail. If you're still with me, how did I do it?

First I created a smoothing parameter for the template

<?param@begin:chartLimit;'20'?>

Its got a default value but this can be overriden at runtime.

Now we have to get our hands dirty and get into the chart code. I used the chart dialog to start out and then got stuck in:

chart:
<Graph graphType="LINE_VERT_ABS" >
 <LocalGridData  
       colCount="{count(.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0])}"
       rowCount="1">
 <RowLabels>
 <Label>
 BC_6MONTH
 </Label>
 </RowLabels>
 <ColLabels>
 <xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]"
 <Label>
 <xsl:value-of select="BID_CURVE_DATE" />
 </Label>
 </xsl:for-each>
 </ColLabels>
 <DataValues>
 <RowData>
 <xsl:for-each select=".//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]"
 <Cell>
 <xsl:value-of select="LIST_G_BC_30YEAR/G_BC_30YEAR/BC_6MONTH" />
 </Cell>
 </xsl:for-each>
 </RowData>
 </DataValues>
 </LocalGridData>
</Graph>

 

the parts to take note of are, surprisingly the parts in red, duh! They all share the same common calculation:

.//G_BID_CURVE_DATE[position() mod number($chartLimit)=0]

This is essentially only allowing values through where the current record position, when divided by the chartLimit value = 0. So for a chartLimit value of 10 we would get the following points: 10, 20, 30 and so on. The first instance calculates the number of points to be plotted and is assigned to the colCount attribute - the charting engine needs this so dont ignore it. The second is for the chart labels - optional. Lastly the actual data point to be plotted. Simple eh?

At runtime, depending on your flavor the chartLimit parameter can be passed to the template to 'smooth' the chart to the users preference. I have zipped up a standalone report definition here. Those of you on an Apps flavor can use the template and sample XML to test it thru your application.