Tuesday Nov 18, 2014

Bordering Text

A tough little question appeared on one of our internal mailing lists today that piqued my interest. A customer wanted to place a border around all data fields in their BIP output. Something like this:

Naturally you think of using a table, embedding the field inside a cell and turning the cell border on. That will work but will need some finessing to get the cells to stretch or shrink depending on the width of the runtime text. Then things might get a bit squirly (technical term) if the text is wide enough to force a new line at the page edge. Anyway, it will get messy. So I took a look at the problem to see if the fields can be isolated in the page as far as the XSLFO code is concerned. If the field can be siolated in its own XSL block then we can change attribute values to get the borders to show just around the field. Sadly not.

This is an embedded field YEARPARAM in a sentence.

translates to

 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" 
  xml:space="preserve">This is an embedded field <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> in a sentence.</fo:inline>

If we change the border on tis, it will apply to the complete sentence. not just the field.
So how could I isolate that field. Well we could actually do anything to the field. embolden, italicize, etc ... I settled on changing the background color (its easy to change it back with a single attribute call.) Using the highlighter tool on the Home tab in Word I change the field to have a yellow background. I now have:

 This gives me the following code.

<fo:block linefeed-treatment="preserve" text-align="start" widows="2" end-indent="5.4pt" orphans="2"
 start-indent="5.4pt" height="0.0pt" padding-top="0.0pt" padding-bottom="10.0pt" xdofo:xliff-note="YEARPARAM" xdofo:line-spacing="multiple:13.8pt"> 
 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" xml:space="preserve">This is an embedded field </fo:inline>
  <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
   font-family-generic="swiss" font-family="Calibri" background-color="#ffff00">
    <xsl:attribute name="background-color">white</xsl:attribute> <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> 
 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" xml:space="preserve"> in a sentence.</fo:inline> 

Now we have the field isolated we can easily set other attributes that will only be applied to the field and nothing else. I added the following to my YEARPARAM field:

<?attribute@inline:background-color;'white'?> >>> turn the background back to white
<?attribute@inline:border-color;'black'?> >>> turn on all borders and make black
<?attribute@inline:border-width;'0.5pt'?> >>> make the border 0.5 point wide
<?YEARPARAM?> >>> my original field

The @inline tells the BIP XSL engine to only apply the attribute values to the immediate 'inline' code block i.e. the field. Collapse all of this code into a single line in the field.
When I run the template now, I see the following:


Its a little convoluted but if you ignore the geeky code explanation and just highlight/copy'n'paste, its pretty straightforward.

Thursday Oct 02, 2014

Multi Sheet Excel Output

Im on a roll with posts. This blog can be rebuilt ...

I received a question today from Camilo in Colombia asking how to achieve the following.

‘What are my options to deliver excel files with multiple sheets? I know we can split 1 report in multiple sheets in with the BIP Advanced Options, but what if I want to have 1 report / sheet? Where each report in each sheet has a independent data model ….’

Well, its not going to be easy if you have to have completely separate data models for each sheet. That would require generating multiple Excel outputs and then merging them, somehow.

However, if you can live with a single data model with multiple data sets i.e. queries that connect to separate data sources. Something like this:

Then we can help. Each query is returning its own data set but they will all be presented together in a single data set that BIP can then render. Our data structure in the XML output would be:


Three distinct data sets within the same data output.

To get each to sit on a separate sheet within the Excel output is pretty simple. It depends on how much native Excel functionality you want.

Using an RTF template you just create the layouts for each data set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIP will place each output onto a separate sheet in the workbook. If you want to name each sheet you can use the <?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s as sophisticated as it gets with the RTF templates. No calcs, no formulas, etc. Just put the output on a sheet, bam!

Using an Excel template you can get more sophisticated with the layout.

This time thou, you create the layout for each data model on separate sheets. In my example, sheet 1 holds the department data, sheet 2, the employee data and so on. Some conditional formatting has snuck in there.

I have zipped up the sample files here.


Wednesday Mar 05, 2014

Internal Links

Another great question today, this time, from friend and colleague, Jerry the master house re-fitter. I think we are competing on who can completely rip and replace their entire house in the shortest time on their own. Every conversation we have starts with 'so what are you working on?' He's in the midst of a kitchen re-fit, Im finishing off odds and ends before I re-build our stair well and start work on my hidden man cave under said stairs. Anyhoo, his question!

Can you create a PDF document that shows a summary on the first page and provides links to more detailed sections further down in the document?

Why yes you can Jerry. Something like this? Click on the department names in the first table and the return to top links in the detail sections. Pretty neat huh? Dynamic internal links based on the data, in this case the department names.

Its not that hard to do either. Here's the template, RTF only right now.

The important fields in this case are the ones in red, heres their contents.


<fo:block id="doctop" />

Just think of it as an anchor to the top of the page called doctop

Back to Top

<fo:basic-link internal-destination="doctop" text-decoration="underline">Back to Top</fo:basic-link>

Just a live link 'Back to Top' if you will, that takes the user to the doc top location i.e. to the top of the page.


<fo:block id="{DEPARTMENT_NAME}"/>

Just like the TopLink above, this just creates an anchor in the document. The neat thing here is that we dynamically name it the actual value of the DEPARTMENT_NAME. Note that this link is inside the for-each:G_DEPT loop so the {DEPARTMENT_NAME} is evaluated each time the loop iterates. The curly braces force the engine to fetch the DEPARTMENT_NAME value before creating the anchor.


<fo:basic-link  internal-destination="{DEPARTMENT_NAME}" ><?DEPARTMENT_NAME?></fo:basic-link>

This is the link for the user to be able to navigate to the detail for that department. It does not use a regular MSWord URL, we have to create a field in the template to hold the department name value and apply the link. Note, no text decoration this time i.e. no underline.

You can add a dynamic link on to anything in the summary section. You just need to remember to keep link 'names' as unique as needed for source and destination. You can combine multiple data values into the link name using the concat function.

Template and data available here. Tested with 10 and 11g, will work with all BIP flavors.

Monday Feb 24, 2014

Wildcard Filtering continued

I wrote up a method for using wildcard filtering in your layouts a while back here. I spotted a followup question on that blog post last week and thought I would try and address it using another wildcard method. 

I want to use the bi publisher to look for several conditions using a wild card. For example if I was sql it would look like this:

if name in ('%Wst','%Grt')

How can I utilize bi publisher to look for the same conditions.

This, in XPATH speak is an OR condition and we can treat it as such. In the last article I used the 'starts-with' function, its a little limiting, there is a better one, 'contains'. This is a much more powerful function that allows you to look for any string within another string. Its case insensitive so you do not need to do upper or lowering of the string you are searching to get the desired results.
Here it is in action:

For the clerks filter I use :


and to find all clerks and managers, I use:

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk') or contains(JOB_TITLE,'Manager')];./JOB_TITLE?>

Note that Im using re-grouping here, you can use the same XPATH with a regular for-each. Also note the lower case 'or' in the second expression. You can also use an 'and' too.

This works in 10 and 11g flavors of BIP. Sample files available here.

Monday Nov 25, 2013

Conditional Borders

How can you conditionally turn cells borders on and off in Publishers RTF/XSLFO templates? With a little digging you'll find what appears to be the appropriate attributes to update in your template. You would logically come up with using the various border styling options:




Buuuut, that doesnt work. Updating them individually does not make a difference to the output. Not sure why and I will ask but for now here's the solution. Use the compound border formatter border-top|bottom|left|right. This takes the form ' border-bottom="0.5pt solid #000000". You set all three options at once rather than individually. In a BIP template you use:

<?attribute@incontext:border-bottom;'3.0pt solid #000000'?>
<?attribute@incontext:border-top;'3.0pt solid #000000'?>
<?attribute@incontext:border-left;'3.0pt solid #000000'?>
<?attribute@incontext:border-right;'3.0pt solid #000000'?>
<?end if?>

3pt borders is a little excessive but you get the idea. This approach can be used with the if@row option too to get the complete row borders to update. If your template will need to be run in left to right languages e.g. Arabic or Hebrew, then you will need to use start and end in place of left and right.

For the inquisitive reader, you're maybe wondering how, did this guy know that? And why the heck is this not in the user docs?
Other than my all knowing BIP guru status ;0) I hit the web for info on XSLFO cell border attributes and then the Template Builder for Word. Particularly the export option; I generated the XSLFO output from a test RTF template and took a look at the attributes. Then I started trying stuff out, Im a hacker and proud me!  For the users doc updates, I'll log a request for an update.

Monday Jul 15, 2013

Minning and Maxing in Pivots

A tricksy question from a hobbiteses this past week or so. How can I use minimum or maximum in an RTF template pivot table?

Using the pivot table dialog box, you get sum or count. So, how to get a min or max? You need to understand the pivot structure a bit to understand how to get the min|max. I wrote about the pivot table format a few years back here.

 Its the C field that holds the calculation as the last parameter.

<?crosstab:c8949;"//G_1";"DEPARTMENT_NAME{,o=a,t=t}";"HIRE_YEAR{,o=a,t=t}";"JOB_ID";"sum" ?>

I was not sure if we could simply swap out the sum|count function for our min, max functions. But, Im a hacker at heart, so I gave it a whirl. It worked, I used the BIP min and max functions:


They both work nicely!

So, the C field would look like:

<?crosstab:c8949;"//G_1";"DEPARTMENT_NAME{,o=a,t=t}";"HIRE_YEAR{,o=a,t=t}";"JOB_ID";"xdoxslt:maximum" ?>

If you do not need the default totals (that use the functions you define.) You can just delete them from the table.

Sample template and data here.

Now, the average values need cracking!

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.


Thursday Apr 26, 2012

Macro Can not be Found Error

There have been messages on the forum and via email in the past few weeks about an issue that occurs with MS Word Template Builder. It appears that the latest MSOffice patch breaks all versions of the Template Builder prior to You'll get an error similar to: 

'The macro cannot be found or has been disabled'

It actually appears to not only break our plugin but any VBA based code that might be embedded inside MSWord.

Rather than re-regurgitate the current work around (we're assuming MS will get a fix out at some point) Im pointing you to Damir's blog entry here.


Jim from S&C Electric let me know of another solution:

We had a similar issue.

Our fix was the find the files with exd extensions and change the name so they were not available.

Maybe this will help.

Thanks Jim

Friday Feb 17, 2012

Rocky Mountain User Group Preso

For those of you that could not make the Rocky Mountain User Group session yesterday. Nikos, from the product management team has kindly loaded my presentation and the sample templates I showed, up to OTN. Its a bit of a monster so be patient.


Its worth 'running' the powerpoint for the fantastic comedic moments we shared in the room, well, kinda. You need my deep southern Louisiana accent for the full effect :0)

Wednesday Oct 26, 2011

Tricky Grouping

A week off trying to catch up with all the stuff that builds up was not enough but it was pretty fruitful. You clear the decks the week before you leave, you even answer a few mails in the first few days of vacation, until your manager slaps your wrists. But there is always a pile of work waiting when you get back. It was not too bad this time; I at least deleted the crap mail during the week via my phone.

How the heck did we survive without smart phones? I took a trip to north Denver last week to pick up a part for our bear ravaged pop up camper. I think he wanted a cold beer from the fridge just wish he'd used the front door rather than slicing the camper open. That gaping hole is meant to be covered!
I guess I could have checked a map before we left but my phone got us through the mess of roads up there. An 80 mile round trip for a $10 louvered cover! Still, we found a great Indian place for lunch, delicious curry!

Today's tidbit (titbit for my British brethren) is a bit tough to describe. It comes from Charlotte in New Zealand. On the surface it looks simple and it is when you know how :0) But there's a wrinkle in to smooth out.
Here's the data:

  <LOCATION>1 The Street Anytown USA</LOCATION>
  <LOCATION>1 The Street Anytown USA</LOCATION>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  <LOCATION>11 The Street Anytown USA</LOCATION>
  <LOCATION>11 The Street Anytown USA</LOCATION>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  <LOCATION>21 The Street Anytown USA</LOCATION>
Heres the desired output:

Serialnumber Status Reason Date
HSS0000156 Planned Major Repair/Upgrade 15/JUN/11
HSS0000276 Planned Major Repair/Upgrade 18/MAY/11
HSS0001046 Planned Major Repair/Upgrade 21/JUN/11
HSS0001303 Planned Major Repair/Upgrade 19/MAY/11
HSS0001403 Planned Major Repair/Upgrade 14/JUN/11

Hok-Min, Zen Master of the RTF template, jumped on this for me and nailed it first time. On first look you think, ah this is just a simple re-group left, using some xpath to repeat the ATTRIBVALUE element twice in the table based on the ATTRIBDESC. Along the lines of:

<?ATTRIBVALUE[../ATTRIBDESC='Planned Major Repair/Upgrade']?> and <?ATTRIBVALUE[../ATTRIBDESC='Lettable Date']?>

But you oh so very easily end up with this:

Serialnumber Status Reason Date
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade

and start scratching your head? With the and 11g template builders there is a nice Group Left feature that removes the need for nasty nested tables but for this slightly corner case, it drops you into a world of misery. You need to take a step back from what the wizard has given you.

Yes, you need to group by serial number but you do not need to loop over the remaining data (current-group().) Rather, you just need to drop into that current-group() and bring the values you need up to the level of the serial number group. So you just need to modify your XPATHs thus:

<?current-group()/ATTRIBVALUE[../ATTRIBDESC='Status Reason']?>

i.e. provide a complete path to the element you want within the serial number re-group level. Neat!

Template and sample data available here.

Thursday Oct 13, 2011

Open World Presentations

The dust has finally settled at the Moscone Center.

I hope everyone had a wonderful time at Open World.  

For those of you who would like copies of the presentations we made, as promised, they are now available on the Publisher OTN page.  Here are direct links:

Create All Your Reports More Easily and Quickly with Oracle Business Intelligence Publisher (14982)

Oracle Business Intelligence Publisher: The Reporting Platform for Oracle Applications (12426)

Become a Reporting Superstar with Oracle Business Intelligence Publisher Best Practices (16762)

Oracle Business Intelligence Publisher: Anatomy of an RTF Template (15010)

Still to come:  Hands On Lab exercises and an updated RTF Template that goes along with Anatomy of a Template.


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!


« May 2015