Thursday Apr 11, 2013

Variable Numbers to Words

Satyender posted a comment to the Numbers to Words post asking:
How can I store the result of <?xdofx:to_check_number(TOTAL_INV_AMOUNT,'USD','CASE_UPPER','DECIMAL_STYLE_WORDS')?> inside a variable.

Checking this out, BIP chokes on the assigning to the variable with a nice error:

 Namespace prefix 'xdofx' used but not declared

Turning to BIP RTF template guru in residence Hok-Min, he suggested avoiding the xdofx: wrapper altogether in this case and calling the function more directly. The underlying function in java is:

public static String toCheckNumber(String locStr, String amount, String preOrCurCode, String caseStyle, String decimalStyle)

Applying that to Satyender's needs we end up with:

<?variable@incontext:salval; xdoxslt:toCheckNumber($_XDOLOCALE,.//SALARY,'USD'

We still need the xdoxslt prefix but we can now assign the value to a variable. There is a caveat from Hok Min.

Note that the amount has to be in string format.  If it is not a string, it has to be converted to a string, e.g. string($CALCULATED_SALARY).  If you use XML element name directly (like in this case SALARY), then it is already a string, so no need to do conversion.

I know this raises the question of why do we need the xdofx: prefix at all? Im discussing that with Hok Min as I write and will get back to you.

Tuesday Feb 26, 2013

E-Business Suite 12 - XML Publisher enabled reports

With some help from the EBS folks, I recently re-ran a report on the latest EBS environment that stores the seeded content that we ship to customers. The document here (sorry,I had to zip it), lists the reports and their publisher templates. The vast majority are still based on an Oracle Reports extract rather than a publisher extract. I can not say that 100% of the reports have a Publisher template but its close. Hopefully it'll be a useful reference.

For more information on the reports check the specific products' documentation.

Thursday Dec 13, 2012

Beat the Post-Holiday Blues with a dose of BIWA

You know its coming so why not plan ahead.  Come and join like minded professionals at the BIWA Summit 2013

Early Bird Registration ends December 14th for BIWA Summit 2013. This event, focused on Business Intelligence, Data Warehousing and Analytics, is hosted by the BIWA SIG of the IOUG on January 9 and 10, at the Hotel Sofitel, near Oracle headquarters in Redwood City, California.

Be sure to check out the many featured speakers, including Oracle executives Balaji Yelamanchili, Vaishnavi Sashikanth, and Tom Kyte, and Ari Kaplan, sports analyst, as well as the many other speakers. Hands-on labs will give you the opportunity to try out much of the Oracle software for yourself--be sure to bring a laptop capable of running Windows Remote Desktop. Check out the Schedule page for the list of over 40 sessions on all sorts of BIWA-related topics. See the BIWA Summit 2013 web site for details and be sure to register soon, while early bird rates still apply.

Klaus and Nikos will be presenting the ever popular Getting the Best Performance from your Business Intelligence Publisher Reports and Implementation and we will run 2 sessions of the BI Publisher Hands On Lab for building Reports and Data Models.

Hope to see you there.

All hail the Excel Queen

An excellent question this past week from dear ol Blighty; actually from Brian at Nextgen Clearing Ltd in the big smoke (London). Brian was developing an excel template and wanted to be able to reference the data fields multiple times inside the Excel template. Damn good question and I of course has some wacky solutions, from macros and cell referencing in Excel to pre-processing the data with an XSL stylesheet to copy the data multiple times so it could be referenced multiple times. All completely outlandish, enter our Queen of Excel, Shirley from the development team. Shirley is singlehandedly responsible for the Excel templates, I put her through six months of hell a few years back, with a host of Excel template requirements. She was more than up to the challenge and has developed some great features. One of those, is the ability to use the hidden XDO_METADATA sheet to map the data to custom named fields so they can be used multiple times in the template. So simple and very neat!

Excel template and regular Excel users will know that you can only use the naming function once ie the names have to be unique across the workbook so you can not reuse a cell/group name. To get around this you can just come up with as many cell names as you want and map them in the XDO_METADATA sheet to the data columns/fields in your XML data set:. For example:

XDO_GROUP_?G_D_DETAIL? <xsl:for-each-group select=".//G_D" group-by="./DEPTNO">

As you can see DEPTNO has been referenced twice and mapped to different named values in the left hand column. These values can then be used to name individual cells in the Excel template. You'll also notice a mix of Publisher <? ...?> and native XSL commands. So the world is your oyster on the mapping and the complexity you might need for calculations or string manipulation.

Shirley has kindly built out a sample Excel template, data and result here so you can see how it all hangs together. the XDO_METADATA sheet is hidden, just right click on the sheet names and use the Unhide command to show it.

Tuesday Dec 04, 2012

Interesting fact #123423

Question from a customer on an internal mailing list this, succintly answered by RTF Template God, Hok-Min

Q: Whats the upper limit for a sum calculation in terms of the largest number BIP can handle?

A: Internally, XSL-T processor uses double precession.  Therefore the upper limit and precision will be same as double (IEEE 754 double-precision binary floating-point format, binary64). Approximately 16 significant decimal digits, max is 1.7976931348623157 x 10308 .

So, now you know :)

Thursday Nov 29, 2012

Spring Cleaning

I recently got a shiny new laptop; moving my shiz from old to new, was not the nightmare it used to be. I have gotten into the habit of using a second hard drive in the media bay where the CDROM normally sits. That drive contains my life's work with BIP. I can pull it out and plug it into another machine very easily. I have been sorting through some old directories and files, archiving some, sharing others with colleagues.

For instance, a little dated but if you were looking for a list of Publisher reports available in EBS R12.1, here it is. Im trying to track down a more recent R12 instance and will re-post the document.

I also found another gem; its a little out there in terms of usefulness but Im sharing it none the less. You can embed, locally or remotely reference SVG graphics (in XML format) and bring the images into the BIP outputs. Template and sample data here.

A nice set of templates showing page number control and page suppression - they will need some explanation, so I'll save them for another post.

The list goes on but I'll save them for later. Back to the clean up!

Wednesday Nov 14, 2012

Chart Filtering

Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data?

In an RTF template, you need to do a little finagling in the chart definition. In an online template, a couple of clicks and you're done.


Build your chart as you would normally to include all the data to start with.

Now flip to the Advanced tab to see the code behind the chart. Its not very pretty but with a little effort you can get it looking a little more friendly. Here's my chart showing employees and their salaries.

<Graph depthAngle="50" depthRadius="8" seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="true"/>
 <Title text="Executive Department Only" visible="true" horizontalAlignment="CENTER"/>
 <LocalGridData colCount="{count(.//G_2)}" rowCount="1">
   <xsl:for-each select=".//G_2">
    <Label><xsl:value-of select="EMP_NAME"/></Label>
    <xsl:for-each select=".//G_2">
     <Cell><xsl:value-of select="SALARY"/></Cell>

Note the emboldened text. Its currently grabbing all values in the G_2 level of the data. We can use an XPATH expression to filter the data to the set we want to see. In my case I want to only see the employees that are in the Executive department. My  data is structured thus:


            <JOB_TITLE>Public Accountant</JOB_TITLE>
            <EMP_NAME>William Gietz</EMP_NAME>

So the XPATH expression Im going to use to limit the data to the Executive department would be .//G_2[../DEPARTMENT_NAME='Executive'] Note the ../ moves the parser up the XML tree to be able to test the DEPARTMENT_NAME value. I added this XPATH expression to the three instances that need it ColCount, ColLabels and RowData. Its simple enough to do. Testing your XPATH expression is easier to do using a table of data. Please note, as soon as you make changes to the chart code. Going back to the Builder tab, you'll find that everything is grayed out. I recommend you make all the changes you can via the chart dialog before updating the code.

Online Template

Implementing the filter is much simpler, there is a dialog box to help you out. Add you chart and fill out the various data points you want to show. then hit the Filter item in the ribbon above the chart. That will pop the filter dialog box where you can then add a filter to the chart.

  You can add multiple filters if needed and of course you can use the Manage Filters button to re-open and edit the filters.

Pretty straightforward stuff!

Tuesday Oct 30, 2012

OpenWorld Presentations and Anatomy of an RTF Template w/ files

For those who missed it ... or those who made it and couldn't get enough, check out the presentations delivered at OpenWorld:

Overview and Roadmap

The Reporting Platform for Oracle Applications

Best Practices

and even though it wasn't presented at OpenWorld an updated version of

Anatomy of an RTF Template

to include documented example files  (RTF template, Sub-Template and sample XML data) so you can re-use and play with the code directly. 

Huge thanks to Tim and Hok-Min who did all the hard, original work on this example loaded with tips and tricks.


Friday Sep 28, 2012

BI Publisher at OpenWorld 2012

For those going to OpenWorld, hope you can join us for any of the following Sessions, Hands On Labs or just stop by and visit us in the DEMOgrounds:

Moscone South S-262

Oracle Business Intelligence Publisher Overview, What’s New, and What’s Planned

Monday, 1-Oct 13:45 - 14:45
Moscone South - 305
Mike Donohue - Oracle
with Ed Farler - CSC

Oracle Business Intelligence Publisher: Reporting for Oracle Applications

Wednesday, 3-Oct 11:45 - 12:45
Palace Hotel - Rose
Mike Donohue - Oracle
Note: new room to accommodate everyone who pre-registered

Oracle Business Intelligence Publisher Best Practices: Be a Reporting Superstar

Wednesday, 3-Oct 15:30 - 16:30
Moscone South - 305
Nikos Psomas & Klaus Fabian - Oracle

Two sessions of the Hands on Lab: 

Tuesday 2-Oct 17:00 - 18:00
Thursday 4-Oct 11:15 - 12:15
Marriott Marquis - Salon 5/6
Nikos Psomas, Klaus Fabian & Kasturi Shekar -- Oracle

You can also download the Focus on BI Publisher sheet that lists all Publisher related Sessions, Labs and DEMOground stations.

Hope to see you there.

Thursday Sep 27, 2012

Quick Quips on QR Codes

Yes, I'm an alliterating all-star; I missed my calling as a newspaper headline writer.
I have recently received questions from several folks on support for QR codes. You know them they are everywhere you look, even here!

How does Publisher handle QR codes then? In theory, exactly the same way we handle any other 2D barcode font. We need the font file, a mapping entry and an encoding class. With those three pieces we can embed QR codes into any output.

To test the theory, I went off to IDAutomation, I have worked with them and many customers over the years and their fonts and encoders have worked great and have been very reliable.
They kindly provide demo fonts which has made my life so much easier to be able to write posts like this. Their QR font and encoder is a little tough to find. I started here and then hit the Demo Now button. On the next page I hit the right hand Demo Now button. In the resulting zip file you'll need two files: >> Automation2DFonts >> TrueType >> IDAutomation2D.ttf
 Java Class Encoder >> IDAutomation_JavaFontEncoder_QRCode.jar - the is useful to see how to call the encoder.

The font file needs to be installed into the windows/fonts directory, just copy and paste it in using file explorer and windows will install it for you. Remember, we are using the demo font here and you'll see if you get your phones decoder to looks a the font above there is a fixed string 'DEMO' at the beginning. You want that removed? Go buy the font from the IDAutomation folks.

The Encoder

Next you need to create your encoding wrapper class. Publisher does ship a class but its compiled and I do not recommend trying to modify it, you can just build your own. I have loaded up my class here. You do not need to be a java guru, its pretty straightforward. I'd recommend a java IDE like JDeveloper from a convenience point of view. I have annotated my class and added a main method to it so you can test your encoders from JDeveloper without having to deploy them first. You can load up the project form the zip file straight into JDeveloper.

Next, take a look at IDAutomation's example java class and you'll see:

QRCodeEncoder qre=new QRCodeEncoder();
 String DataToEncode = "IDAutmation Inc.";
 boolean ApplyTilde = false;
 int EncodingMode = 0;
 int Version = 0;
 int ErrorCorrectionLevel = 0;
 System.out.println( qre.FontEncode(DataToEncode, ApplyTilde,
                        EncodingMode, Version, ErrorCorrectionLevel) );

You'll need to check what settings you need to set for the ApplyTilde, EncodingMode, Version and ErrorCorrectionLevel. They are covered in the user guide from IDAutomation here. If you do not want to hard code the values in the encoder then you can quite easily externalize them and read the values from a text file. I have not covered that scenario here, I'm going with IDAutomation's defaults and my phone app is reading the fonts no problem.

Now you know how to call the encoder, you need to incorporate it into your encoder wrapper class. From my sample class:

      Class[] clazz = new Class[] { "".getClass() };  
      ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));
      ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));
      ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));
      ENCODERS.put("qrcode",mUtility.getClass().getMethod("qrcode", clazz));

I just added a new entry to register the encoder method 'qrcode' (in red). Then I created a new method inside the class to call the IDAutomation encoder.

/** Call to IDAutomations QR Code encoder. Passing the data to encode
     Returning the encoded string to the template for formatting **/
public static final String qrcode (String DataToEncode)
  QRCodeEncoder qre=new QRCodeEncoder();
   boolean ApplyTilde = false;
   int EncodingMode = 0;
   int Version = 0;
   int ErrorCorrectionLevel = 0;
  return qre.FontEncode(DataToEncode, ApplyTilde, EncodingMode, Version, ErrorCorrectionLevel);

Almost the exact same code in their sample class. The DataToEncode string is passed in rather than hardcoded of course.

With the class done you can now compile it, but you need to ensure that the IDAutomation_JavaFontEncoder_QRCode.jar is in the classpath.
In JDeveloper, open the project properties >> Libraries and Classpaths and then add the jar to the list. You'll need the publisher jars too. You can find those in the jlib directory in your Template Builder for Word directory.

Note! In my class, I have used

package oracle.psbi.barcode;

As my package spec, yours will be different but you need to note it for later.

Once you have it compiling without errors you will need to generate a jar file to keep it in.
In JDeveloper highlight your project node >> New >> Deployment Profile >> JAR file. Once you have created the descriptor, just take the defaults. It will tell you where the jar is located. Go get it and then its time to copy it and the IDAutomation jar into the Template Builder for Word directory structure.

Deploying the jars

On your windows machine locate the jlib directory under the Template Builder for Word install directory. On my machine its here, F:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib. Copy both of the jar files into the directory.

The next step is to get the jars into the classpath for the Word plugin so that Publisher can find your wrapper class and it can then find the IDAutomation encoder. The most consistent way I have found so far, is to open up the RTF2PDF.jar in the same directory and make some mods.

First make a backup of the jar file then open it using winzip or 7zip or similar and get into the META-INF directory. In there is a file, MANIFEST.MF. This contains the classpath for the plugin, open it in an editor and add the jars to the end of the classpath list. In mine I have:

Manifest-Version: 1.0
Class-Path: ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.jar ./jewt4.jar 
./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.jar ./xmlparserv2.jar 
./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./versioninfo.jar 
./barcodejar.jar ./IDAutomation_JavaFontEncoder_QRCode.jar
Main-Class: RTF2PDF

I have put in carriage returns above to make the Class-Path: entry more readable, make sure yours is all on one line. Be sure to use the ./ as a prefix to the jar name. Ensure the file is saved inside the jar file 7zip and winzip both have popups asking if you want to update the file in the jar file.
Now you have the jars on the classpath, the Publisher plugin will be able to find our classes at run time.

Referencing the Font

The next step is to reference the font location so that the rendering engine can find it and embed a subset into the PDF output. Remember the other output formats rely on the font being present on the machine that is opening the document. The PDF is the only truly portable format.

Inside the config directory under the Template Builder for Word install directory, mine is here,
F:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config.
You'll find the file, 'xdo example.cfg'. Rename it to xdo.cfg and open it in a text editor.
In the fonts section, create a new entry:

       <font family="IDAutomation2D" style="normal" weight="normal">
             <truetype path="C:\windows\fonts\IDAutomation2D.ttf" /> 

Note, 'IDAutomation2D' (in red) is the same name as you can see when you open MSWord and look for the QRCode font. This must match exactly. When Publisher looks at the fonts in the RTF template at runtime it will see 'IDAutomation2D' it will then look at its font mapping entries to find where that font file resides on the disk. If the names do not match or the font is not present then the font will not get used and it will fall back on Helvetica.

Building the Template

Now you have the data encoder and the font in place and mapped; you can use it in the template. The two commands you will need to have present are:

<?register-barcode-vendor:'ENCODER WRAPPER CLASS'; 'ENCODER NAME'?> 

for my encoder I have:

<?register-barcode-vendor:'oracle.psbi.barcode.BarcodeUtil'; 'MyBarcodeEncoder'?>

Notice the two parameters for the command.
The first provides the package 'path' and class name (remember I said you need to remember that above.)
The second is the name of the encoder, in my case 'MyBarcodeEncoder'. Check my full encoder class in the zip linked below to see where I named it. You can change it to something else, no problem.
This command needs to be near the top of the template.

The second command is the encoding command:


for my command I have

DATATEXT is the XML element that contains the text to be encoded. If you want to hard code a piece of text just surround it with single quotes.

qrcode is the name of my encoder method that calls the IDAutomation encoder. Remember this.
MyBarcodeEncoder is the name of my encoder. Repetition? Yes but its needed again.

Both of these commands are put inside their own form fields.

Do not apply the QRCode font to the second field just yet. Lets make sure the encoder is working. Run you template with some data and you should get something like this for your encoded data:


Grooovy huh? If you do not get the encoded text then go back and check that your jars are in the right spot and that you have the MANIFEST.MF file updated correctly.
Once you do get the encoded text, highlight the field and apply the IDAutomation2D font to it. Then re-run the report and you will hopefully see the QR code in your output. If not, go back and check the xdo.cfg entry and make sure its in the right place and the font location is correct.

That's it, you now have QR codes in Publisher outputs. Everything I have written above, has been tested with the 5.6.3, codelines. I'll be testing the 11g code in the next day or two and will update you with any changes.

One thing I have not covered yet and will do in the next few days is how to deploy all of this to your server. Look out for a follow up post.

One note on the apparent white lines in the font (see the image above). Once printed they disappear and even viewing the code on a screen with the white lines, my phone app is still able to read and interpret the contents no problem.

I have zipped up my encoder wrapper class as a JDeveloper project here. Just dig into the src directories to find the file if you just want the code. I have put comments into the file to hopefully help the novice java programmer out.

Happy QR'ing!

Thursday Sep 20, 2012

Barcodes and Bugs

A great mail from Mike at Browning last week. He has been through the ringer getting his BIP barcoding sorted out but he's now out of the woods. Here's the final result.

By way of explanation, an excerpt from Mike's email:  

This is an example of the GS1_128 carton shipping labels we are now producing with BIP in our web application for our vendors who drop ship products to our dealers.
It produces 4 labels per printed page, in PDF format, on peel & stick label paper. Each label has a unique carton number, and a unique carton serial number in the SSCC-18 barcode.
This example is for Cabelas (each customer has slightly different GS1-128 label format requirements – custom template for each - a pain!).
I am using custom java encoders I wrote for the UPC and SSCC-18 barcodes, and a standard encoder (code128b) for the ShipTo zip barcode.

 Is there any way yet to get around that SUPER ANNOYING bug when opening the rtf template in MS Word, and it replaces my xsl code text in the barcode fields with gibberish???
Every time I open it I have to re-enter all the xsl code. Not only to be able to read & edit it, but also to get it to work in BIP (BIP doesn’t like the gibberish if I upload the template that has it).

Mike's last point, regarding the annoying bug in the template builder, is one that I have experienced occasionally. The development team have looked at it and found it to be an issue with MSWord and not a plugin problem. That's all well and good but how can you get around it?

Well, you can take advantage of the font mapping that BIP offers to get the barcodes into the PDF output. As many of you know, getting a barcode font to appear in the PDF output, you need employ the use of the xdo.cfg file in the template builder config directory.
You would normally have an entry such as this:


      <font family="Code 128" style="normal" weight="normal">
       <truetype path="C:\windows\fonts\128R00.TTF" /> 

to map a barcode font to get it to render in the PDF output when testing from the template builder plugin.


Mike's issue is only present when the formfield is highlighted with a barcode font. The other fields in the template are OK.
What you can do to get around the issue is to bend the config entry to get around having to use the barcode font in the template at all. Changing the entry to something like:


      <font family="Calibri" style="normal" weight="normal">
       <truetype path="C:\windows\fonts\128R00.TTF" /> 


Note that we are mapping the Calibri; a humanly readable and non 'erroring' font in the template, to the code 128 barcode font. Where you used to highlight the field with the barcode in MSWord, you now use the Calibri font instead. At run time, BIP will go look for the Calibri font mapping and will drop in the Code128 font. Of course, Calibri is an example; you need to pick a font that you are not going to use any where else in the layout.

Tuesday Sep 18, 2012

Ubiquitous BIP

The last number I heard from Mike and the PM team was that BIP is now embedded in more than 40 oracle products. That's a lot of products to keep track of and to help out with new releases, etc.
Its interesting to see how internal Oracle product groups have integrated BIP into their products. Just as you might integrate BIP they have had to make a choice about how to integrate.

1. Library level - BIP is a pure java app and at the bottom of the architecture are a group of java libraries that expose APIs that you can use. they fall into three main areas, data extraction, template processing and formatting and delivery. There are post processing capabilities but those APIs are embedded withing the template processing libraries.

Taking this integration route you are going to need to manage templates, data extraction and processing. You'll have your own UI to allow users to control all of this for themselves. Ultimate control but some effort to build and maintain. I have been trawling some of the products during a coffee break. I found a great post on the reporting capabilities provided by BIP in the records management product within WebCenter Content 11g. This integration falls into the first category, content manager looks after the report artifacts itself and provides you the UI to manage and run the reports.

2. Web Service level - further up in the stack is the web service layer. This is sitting on the BI Publisher server as a set of services, runReport and scheduleReport are the main protagonists. However, you can also manage the reports and users (locally managed) on the server and the catalog itself via the services layer.

Taking this route, you still need to provide the user interface to choose reports and run them but the creation and management of the reports is all handled by the Publisher server. I have worked with a few customer on this approach. The web services provide the ability to retrieve a list of reports the user can access; then the parameters and LOVs for the selected report and finally a service to submit the report on the server.

3. Embedded BIP server UI- the final level is not so well supported yet. You can currently embed a report and its various levels of surrounding  'chrome' inside another html based application using a URL. Check the docs here. The look and feel can be customized but again, not easy, nor documented. I have messed with running the server pages inside an IFRAME, not bad, but not great.

Taking this path should present the least amount of effort on your part to get BIP integrated but there are a few gotchas you need to get around.

So a reasonable amount of choices with varying amounts of effort involved. There is another option coming soon for all you ADF developers out there, the ability to drop a BIP report into your application pages. But that's for another post.

Monday Sep 17, 2012

Linking to BIP reports from BIEE Analyses

Bryan found a great blog post from Fiston over on the OBIEEStuff blog. It covers the ability to link to a BIP report from a BIEE analyses report with the ability to pass parameters to it. I have doubled checked and you need to be on OBIEE to see the 'Shared Report Link' mentioned in Fiston's post when you open a BIP report from the /analytics side of the house. Enjoy!

OBIEE to BIP trick

Thursday Sep 13, 2012

Dynamic Bursting ... no really!

If any of you have seen me or my colleagues present BI Publisher to you then we have hopefully mentioned 'bursting.' You may have even seen a demo where we talk about being able to take a batch of data, say invoices. Then split them by some criteria, say customer id; format them with a template; generate the output and then deliver the documents to the recipients with a click. We and especially I, always say this can be completely dynamic! By this I mean, that you could store customer preferences in a database. What layout would each customer like; what output format they would like and how they would like the document delivered. We (I) talk a good talk, but typically don't do the walk in a demo. We hard code everything in the bursting query or bursting control file to get the concept across. But no more peeps! I have finally put together a dynamic bursting demo! Its been minutes in the making but its been tough to find those minutes! Read on ...

It's nothing amazing in terms of making the burst dynamic. I created a CUSTOMER_PREFS table with some simple UI in an APEX application so that I can maintain their requirements. In EBS you have descriptive flexfields that could do the same thing or probably even 'contact' fields to store most of the info.

Here's my table structure:

Name                           Type
------------------------------ -------- 
CUSTOMER_ID                    NUMBER(6)
TEMPLATE_TYPE                  VARCHAR2(20)
TEMPLATE_NAME                  VARCHAR2(120)
OUTPUT_FORMAT                  VARCHAR2(20)
EMAIL                          VARCHAR2(255)
FAX                            VARCHAR2(20)
ATTACH                         VARCHAR2(20)
FILE_LOC                       VARCHAR2(255)

Simple enough right? Just need CUSTOMER_ID as the key for the bursting engine to join it to the customer data at burst time. I have not covered the full delivery options, just email, fax and file location. Remember, its a demo people :0) However the principal is exactly the same for each delivery type. They each have a set of attributes that need to be provided and you will need to handle that in your bursting query. On a side note, in EBS, you use a bursting control file, you can apply the same principals that I'm laying out here you just need to get the customer bursting info into the XML data stream so that you can refer to it in the control file using XPATH expressions.

Next, we need to look up what attributes or parameters are required for each delivery method. that can be found in the documentation here.

 Now we know the combinations of parameters and delivery methods we can construct the query using a series a decode statements:

select distinct cp.customer_id "KEY",
     cp.template_name TEMPLATE,
     cp.template_type TEMPLATE_FORMAT,
     'en-US' LOCALE,
     cp.output_format OUTPUT_FORMAT,
     'false' SAVE_FORMAT,
     cp.delivery_channel DEL_CHANNEL,
     decode(cp.delivery_channel,'FILE', cp.file_loc 
      , 'EMAIL',
      , 'FAX', cp.fax) PARAMETER1,
     decode(cp.delivery_channel,'FILE', c.cust_last_name||'_orders.pdf'
      ,'FAX', '') PARAMETER2,
      ,'FAX', null) PARAMETER3,
      ,'EMAIL','Your current orders'
      ,'FAX',NULL)    PARAMETER4,
      ,'EMAIL','Please find attached a copy of your current orders with BI Publisher, Inc'
from cust_prefs cp, 
customers c,
orders_view ov
where cp.customer_id = c.customer_id
and cp.customer_id = ov.customer_id
order by  cp.customer_id

Pretty straightforward, just need to test, test, test, the query and ensure it's bringing back the correct data based on each customers preferences. Notice the NULL values for parameters that are not relevant for a given delivery channel.

You should end up with bursting control data that the bursting engine can use:

 Now, your users can run the burst and documents will be formatted, generated and delivered based on the customer prefs.

If you're interested in the example, I have used the sample OE schema data for the base report. The report files and CUST_PREFS table are zipped up here. The zip contains the data model (.xdmz), the report and templates (.xdoz) and the sql scripts to create and load data to the CUST_PREFS table.  Once you load the report into the catalog, you'll need to create the OE data connection and point the data model at it. You'll probably need to re-point the report to the data model too.

Happy Bursting!

Tuesday Sep 11, 2012

Hey, Wheres your field?

I have finally been getting back to playing with Publisher this past week and actually building demos!
I came across a nice feature in the online template builder. I had been tweaking the data model of an already built report including an existing online template.

I finished my data model changes and re-opened the online template to make changes and got this:

Nice! It recognized that some of the fields were now missing from the data model and was asking me to map new fields into the template. Once I had mapped them it then opened the template for editing. Very neat!

Monday Sep 10, 2012

"Automation Error Unspecified Error" ... err Error

One the best error messages I have seen in a long time and I've seen some doozies! 

There have been a fare few internal emails flying over the past week about issues with the template builder for MSWord not working. The issue has been found, so if you are hitting some behaviour similar to this:

I have installed BI Publisher Desktop for 32 bit. I have to load the data from XML to RTF Template. As per instruction when I click on tab Sample XML nothing happen.

When I click on any other tab from BI Publisher menu, I am getting one error in pop-up menu “Automation Error Unspecified Error.

I am unable to open any of the tab of BI Publisher menu including help.

Have no fear, it's for once, not a BIP issue but a Microsoft one! Check here for what you need to do to resolve the error.

Wednesday Sep 05, 2012

Trigger Happy

Its been a while, I know, we’ll say no more OK? I’ll just write …

In the latest BIP release and if I’m really honest; the release before this (we'll call it dot 5 for brevity.) The boys and gals in the engine room have been real busy enhancing BIP with some new functionality. Those of you that use the scheduling engine in OBIEE may already know and use the ‘conditional scheduling’ feature. This allows you to be more intelligent about what reports get run and sent to folks on a scheduled basis. You create a ‘trigger’ analysis (answer) that is executed at schedule time prior to the main report. When the schedule rolls around, the trigger is run, if it returns rows, then the main report is run and delivered. If there are no rows returned, then the main report is not run.
Useful right? Your users are not bombarded with 20 reports in their inbox every week that they need to wade throu. They get a handful that they know they need to look at. If you ensure you use conditional formatting in the report then they can find the anomalous data in the reports very quickly and move on to the rest of their day more quickly. You could even think of OBIEE as a virtual team member, scouring the data on your behalf 24/7 and letting you know when its found an issue.

BI Publisher, wanting the team t-shirt and the khaki pants, has followed suit. You can now set up ‘triggers’ for it to execute before it runs the main report. Just like its big brother, if the scheduled report trigger returns rows of data; it then executes the main report. Otherwise, the report is skipped until the next schedule time rolls around. Sound familiar?

BIP differs a little, in that you only need to construct a query to act as the trigger rather than a complete report. Let assume we have a monthly wage by department report on a schedule. We only want to send the report to managers if their departmental wages reach and/or exceed a certain amount.

The toughest part about this is coming up with the SQL to test the business rule you want to implement. For my example, its not that tough:

select d.department_name, sum(e.salary) as wage_total
from employees e,
departments d
where d.department_id = e.department_id
group by d.department_name
having sum(e.salary) > 230000

We're looking for departments where the wage cost is greater than 230,000 Dexter Dollars! With a bit of messing I found out you can parametrize the query. Users can then set a value at schedule time if they need to.

To create the trigger is straightforward enough.

You can create multiple triggers for users to select at schedule time. Notice I also used a parameter in the query, :wamount. Note the matching parameter in the tree on the left. You also dont need to return multiple columns, one is fine, the key is if there are rows returned. You can build the rest of your report as usual.

At scheduling time the Schedule tab has a bit more on it. If your users want to set the trigger, they check the Use Trigger box. The page will then pop fields to pick the appropriate trigger they want to use, even a trigger on another data model if needed.

Note it will also ask for the parameter value associated with the trigger.

At this point you should note that the data model does not make a distinction between trigger and data model (extract) parameters. So users will see the parameters on the General and Schedule tabs. If per chance you do need to just have a trigger parameters. You can just hide them from the report using the Parameters popup in the report designer, just un-check the 'Show' box

I have tested the opposite case where you do not want main report parameters seen in the trigger section. BIP handles that for you!

Once the report hits its allotted schedule time, the trigger is executed. Based on the results the report will either run or be 'skipped.'

Now, you have a smarter scheduler that will only deliver reports when folks need to see them and take action on the contents.

More official info here for developers and here for users.

Tuesday Sep 04, 2012


Its been a while, I know :( I have posts in the pipe just gotta smoke em out!

The latest update for BIP was released last week. A bunch of defects have been addressed as you can see below.


This patch is just for BI Publisher standalone installs. For those of you using BIP within the wider BIEE suite there is the BP1 patchset. More details on that here.

Wednesday Jun 13, 2012

EBS Customers Please Read

There's an urgent update from Steven Chan over on the EBS Apps blog that you need to check out!

URGENT BULLETIN: Disable JRE Auto-Update for All E-Business Suite End-Users

Friday May 18, 2012

Secrets Revealed to Advanced Charting

We get a lot of emails and questions here at Publisher Tower concerning charts and how to do X. I write about some of the solutions here if I think they could be useful to a wider audience but its tough to document everything for everyone's specific features.

The chart dialog in the template builder gets you so far but there are cases where you are going to have to get into the code to make things work the way you want them to. I have documented a bunch which I have pulled together as links below. But if you do venture into the chart code, where do you start?

I have re-documented the location of the chart DTD document recently as it disappeared from OTN and having bugged a few people about it, its still not there, c'est la vie. But those of you with the Template Builder for Word (TB) have your own copy you can refer to. Just dig into your TB install directory and look for the dvt-jclient.jar (11g) or bipres.jar (10g) files open them with a zip utility a dig down through the directories to oracle\dss\graph\.
There you will find the fabled and rare, graph.dtd ... this is the golden fleece of the BIP charting world. In it, you will find secrets beyond your imagination, treasures beyond compare ...  OK, its not that exciting but there is a lot of charting info to be gleaned. There is not much in the way of comments but you can at least look up features and then see what attributes they will need to achieve your needs.

Just remember, Word has almost unlimited undo's, just get stuck in a try stuff out you are not going to break anything!

Some blogged chart solutions via google.


Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« July 2016