James came up with a cunning crosstab report question on the forum this week.

Does not look that bad right? Sadly the new crosstab builder can not build what we need. Notice there is no summarization in the grid; just a listing of times for each employee, these are clock in/clock out times, no calculations.

Here's the data:

<?xml version="1.0" ?> 

<rowset>

 <row rownumber="1">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>08:15</TIME> 

 </row>

 <row rownumber="2">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>12:15</TIME> 

 </row>

 <row rownumber="3">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>13:15</TIME> 

 </row>

 <row rownumber="4">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>17:30</TIME> 

 </row>

 <row rownumber="5">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>10:00</TIME> 

 </row>

 <row rownumber="6">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>14:15</TIME> 

 </row>

 <row rownumber="7">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>15:15</TIME> 

 </row>

 <row rownumber="8">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Wednesday</DAY> 

 <DATE>11/18/2009</DATE> 

 <TIME>19:30</TIME> 

 </row>

 <row rownumber="1">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>07:50</TIME> 

 </row>

 <row rownumber="2">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>11:59</TIME> 

 </row>

 <row rownumber="3">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>12:35</TIME> 

 </row>

 <row rownumber="4">

 <EMPL_NAME>John</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>18:00</TIME> 

 </row>

 <row rownumber="5">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>9:00</TIME> 

 </row>

 <row rownumber="6">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>13:25</TIME> 

 </row>

  <row rownumber="7">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>14:45</TIME> 

 </row>

 <row rownumber="8">

 <EMPL_NAME>Mary</EMPL_NAME> 

 <DAY>Thursday</DAY> 

 <DATE>11/19/2009</DATE> 

 <TIME>18:20</TIME> 

 </row>

</rowset>

So I went back to the old skool crosstab methods and built it manually.

Just a four celled table with an @column loop for the headings, regular for-each-group for the row headings, @cell loop for the 'measure' and then an inner 'current-group()' loop for the times.

You can get the RTF template here if you need it.

Sometimes you can teach a new dog old tricks!

Shout out here for Hussein over on the ADF Dev to Dev blog. Nice article covering some of the BIP APIs and how to use them within an ADF project.

http://husaindalal.blogspot.com/2009/11/integrating-bi-publisher-standalone.html

Thanks to Jurgen and Klaus for finding Hussein and his prose.

Completely off topic today, but something Im sure nearly all of you use. Social networks, whether it be Facebook, Twitter, MySpace, the list goes on, I'm sure you are using at least one. Jake over at the lab has been a great flag bearer for the genre. I on the other hand have been somewhat skeptical, there are probably posts over on the lab proving so. Yep, Im a dinosaur, even my kids tell me so and I listen to more modern new fangled music than they do.

I have to admit, I have a twitter account, not much used. A facebook account, more used but mainly down to the ease of posting something via my trusty but horribly cracked iPhone (that's another story) Its been great to keep up with current friends and colleagues and find old school buddies and catch up, its fun but then what? That's kinda where my interest wains...

That is until this past week. Back in March my wife opened an ice cream shop in our town, its more of a village, blink and you miss it. The shop has an accompanying website cos Im geeky like that - I think we are the only shop in the vill ... town to have one. We get hits and comments and I write a blog for it posting pictures, news, etc. Our advertising budget is not huge so print media for the most part is out and 'sadly' except for Leslie and her WSJ subscription, who reads a paper these daze?

What about online advertising? I'm skeptical of that too, I make a point of blocking the common hosting servers that slow web pages while down loading their ads. I ignore them if they do get through. A quick straw poll and I found that most of my friends ignore them. So what alternatives are there?

My wife is a huge Facebook fan, so many friends and to my mind spends far too long on it :0) Rather like the pot calling the kettle black I suppose. Her computer skills have improved immensely since using it thou, so I should not complain. She still hits the button or icon 5 or 6 times if the reaction is not instant; makes for some fun trying to close 20 windows on a laptop that has been brought to its knees by 20 copies of Adobe Illustrator being opened at the same time :0)

I spotted 'fan' pages on FB a few weeks back and thought what better way to get some 'viral' exposure for the business in the surrounding area. My kids 'Facebook' too and they spend even more time on it than my wife, that's when they are not texting of course.

Its not going to cost me a bean so I duly started putting together the fan page (still working on some custom FBML content.) But we are now posting updates, pictures, getting to the videos and some freebies for the more motivated.

I then invited all of my wife's friends to become fans; within the week we were up to 50, admittedly some were from the UK, Canada and California which was not so good but its still useful. Why useful? Because of the viral nature of Facebook, even if the first fans are remote, they have friends that see them 'fanning' the shop, so they fan the shop. And so it goes on, eventually (I hope) getting back to folks in our area who may not know about the shop. How many degrees of separation are facebookers apart? We are just starting to break out of the 'friends' stage with fans that we do not know. Some from afar but others closer, so its starting.

Facebook, seems to have finally found it revenue stream in offering to target likely fans on their page with an ad on the right hand side of the page. Having already poo poo'ed online advertising earlier I dont think we'll be going there. Facebook does offer some great profiling thou, they know so much about users that I can run an add to 'male/female teenagers that live in our nearest town and like ice cream' ... awesome stuff!
They do have another cool feature that I do want to use. Im going to need my sons' help and their nasty texting habit. They do not know this yet but they will be texting their 100's of friends to text FB to become fans of the shop, if they so wish.
This is my master plan for world ice cream shop domination, getting more fans and the fans we want; ice cream eating fans that will nag their parents to bring them in.

This is all great but how about getting updates out there, we can move over to the fan page as administrators but what about other content? I created a 'twitter' account for the shop too, hey its free and 'real' people will find you, I think we boast more followers on twitter (excluding the scantily clad bots that seem to plague every twitter account) than fans on FB.
Twitter is not on my wife's radar at the moment so we needed some way to get her shop FB updates out as tweets. Facebook does that for you, even from a fan page now, easy. To take it a stage further I wanted to be able to write a blog posting from our Wordpress install, get that to FB and then out as a tweet. That took some finding but there is a plugin out there that almost does it. With a little tweaking I can now write a blog posting, hit a button to publish to FB and then FB pushes it out to twitter. Perfect!

I have finally seen the light, at least for my wife's business. For other bigger companies, maybe yours? The likes of Coca Cola and others boast huge numbers of fans, that kind of product awareness is invaluable ... every little helps I say! We'll be pushing as hard as possible into the social networking frontier for our little shop.

More from my journey of Siebel- BIP integration discovery last week. It was a steep learning curve but none the less very useful.
Firstly, there are 3 levels of integration available to Siebel 8.1.1 users

Level 1

• Siebel stores and manages the templates and manages data generation via integration objects


• At runtime, Siebel calls BIP via WS to process data+template to generate output and returns the document to the user.


• BIP just acts as a publishing service for Siebel

Level 2

• BIP stores the templates and a report definition. The report uses complex Siebel web services as its data source.
• At runtime, BIP is called via a button or menu item (custom), it then calls Siebel back via a WS to fetch the data (integration object)
• BIP then generates output and returns the document to the Siebel UI
• This is the recommended integration as user security can be applied to the data being returned by Siebel

Level 3

• BIP stores the templates and a report definition. The report uses SQL or stored procedure calls to retrieve data from the underlying database
• At runtime, BIP is called via a button or menu item (custom), BIP fetches data from Siebel DB
• BIP generates output and returns to Siebel UI

More details in a great white paper from John on the Siebel PM team, Siebel - BI Publisher Integration Concepts

If you are looking for info on how to migrate your existing Actuate reports in the context of Siebel - check out Kan's article over at the BIP Consulting Blog Once you have downloaded the migration assistant check out the viewlet and accompanying white paper.

Generating HTML from BIP? Trying to set a title for the page? Stuck? Can't find out how?

Its not in the documentation yet but its pretty simple to do. In the template in MSWord use File->Properties->Summary->Title.

Enter your page title. Its only going to work for HTML for Excel and RTF output the Title property is filled. The title will not come through into your PDF outputs. The other properties are ignored in the final output. A little inconsistent I know, but that's the way it is today.

Want to thank Srikant Subramaniam from the Oracle Fusion Middleware for Apps team for prompting this post.

As much as we here at Oracle would like to think that all data is in a database -- an Oracle Database at that, the fact is that sometimes ... dare I say it ... data is kept in Excel spreadsheets.

Srikant's team maintains some information in an Excel spreadsheet. To get this data into a nicely formatted PDF file was a tedious process that took 5 to 6 days. Srikant wanted to know if Publisher could help. The challenge was how to get the data out of the Excel spreadsheet into an XML file. After a little searching on the web, we worked out a way to do that.

Now his team can generate the desired output in a couple of hours. Over 20 times faster than it used to take. Saving at least 38 hours of precious time that can now be put toward other projects.

Srikant was kind enough to document this in a nice tutorial. The tutorial shows how to convert data from an Excel spreadsheet into a PDF document. The Excel data consists of a set of addresses, that are then grouped and sorted using the BI Publisher Template Builder to create the RTF template and the output PDF file. Here are the supporting files to run the tutorial.

Knowing that dates and nulls can sometimes be problematic, I took it a little further to test out an Excel spreadsheet with dates and nulls. Fortunately the NULL values are converted nicely and those elements with NULL values are absent in the resulting XML - just the way you want it so that values can be aggregated correctly.

The Dates however proved to be a little more challenging. In the XML data, they kept coming out as Excel number values and not in date canonical format. I was also using Excel 2007 so the process was a little different from what Srikant documented in the tutorial. Here are the differences for Excel 2007 and what I found I had to do to get Dates to come out in the XML correctly. Perhaps someone more clever has a better way ...

-- When converting the range to an XML List I was warned/prompted to use format or data type when mapping. When I chose data type (which I thought would work) I ended up with dates as the Excel number values in the spreadsheet and the exported XML data. So I went with "Use existing formatting" for the the mapping so the dates would still appear as dates in the spreadsheet.

However, exporting to XML still left the dates as Excel number values.

-- The "trick" is that you must create and apply a new "XML Map" (this appears to be the Excel term for XSD) that tells Excel that the date is to be mapped to an XML date format. The first step is to generate the XSD file. Set the focus in the data range and select "Create XSD files for the XML Schema at the active cell" from the XML Tools Add-in menu.

-- In the XSD you will see that the Date field is being treated as a string.

Modify it so that it's type is Date.

-- Upload the XSD as a new XML Map and map the field to this new XML map. Set focus on a cell in the region, right click and select the "XML Source" menu choice from the XML menu.

In the XML Source dialog click on the [XML Maps...] button. Add the XSD you just modified and rename it to something meaningful. Then delete the Root_Map that was there.

Click OK to return the XML Source dialog and drag and drop the Map elements onto the correct columns headers in the data range.

-- Now when you export to XML, the dates will be in canonical format and can be formatted correctly by the Template Builder or other XSL formatting tools.

I have been working with the Siebel CRM integration today, a good learning curve and I have learnt a lot more than I wanted to about Siebel CRM. One thing I did get stuck on was that my template was not generating the results I was expecting.
I went through and generated some sample data against what I thought was the integration object I was seeing on the page. Created my template, uploaded it, attached the report to the correct page. Went to that page and tried to generate the ouput, I was getting the header information but not the detail I was expecting. What to do?
The integration is of the embedded variety ie Siebel generates the XML and calls BIP to process it with a template to return the result. So the XML structure and content is hidden at runtime. Pick the wrong integration object, as I did, and you're a little stuck. Most of it was ignorance on my part but I wanted to see what was in the XML, element names and values.

I came up with the following template to let me at least seethe elements and assigned values:

<?for-each://*?>

Element: <?local-name()?> Value:<?current()/text()?>

<?end for-each?>

this pushed out

Element: ListOf_ssHlsCase Value:
Element: ssHlsCase Value:
Element: ssCaseNum Value:410194-14115562
Element: ssCreatedDate Value:10/20/2009 07:26:40
Element: ssDescription Value:
Element: ssName Value:Lew Golden - - 10/20/2009
Element: ssStage Value:Application
Element: ssStatus Value:Active
Element: ssSubStatus Value:
Element: ssSubjectContactFirstName Value:Lew
Element: ssSubjectContactLastName Value:Golden
Element: ssSubjectFullName Value:
Element: ssType Value:Other
Element: ssId Value:8SIA-8EJMY
Element: ssCreated Value:10/20/2009 07:26:40

Takes a little deciphering but I worked out that I was not using the integration object I thought I was, dagnabit! Useful thou I thought, so Im sharing it, enjoy!

A Sunday post, I know, dedicated or what! It is however a lazy post of sorts. Im sitting in my wife's ice cream shop serving ice cream to non-existent customers on a chillsome Sunday. Thinking up new marketing strategies for my poor wife to implement and idly surfing the Publisher pages on OTN.

There's a bunch of new content out there from Reporting Plaforms for Applications to Integration with MSSharepoint (nice article). For me, the best practices paper is the best (no I didnt write it :0) its packed with info and the PM folks have put notes on each slide in the PDF. Just click the note icon in the bottom right for some information on what the slide is getting at.

Its Monday, for most of you reading this, ease into the week with a peruse of the Publisher pages - http://www.oracle.com/technology/products/xml-publisher/index.html and a cup o java or char.

The snow has nearly all gone, just in time for the next batch! It was a douzy of a storm, fun driving conditions, amazing how much you forget over the hot dry 'roaded' summer. My newly licensed son was itching to get out there. Mom wouldn't let him, I had to agree, our insurance couldn't take it and we have way too many trees near our house. The trees looked very pretty thou and speaking of trees (what a segue) I have been asked numerous times if BIP can generate a tree structure, the more ambitious ask for an org chart. I plan to take a look at how to do the org chart another time, for now, how about a nice tree structure.

Between the two of us, Rob (colleague and data modeler extraordinaire) came up with a solution for a customer to generate this:

Its a tree of sorts, nothing fancy, we just wanted to get the structure right, so there are no details under the headings. Its getting the indenting correct that was the trick. The customer had a pretty fixed data structure and did not want to use nested tables.

The template is pretty simple, just a funky trick to get the indenting working. I mentioned the customer had a pretty fixed data structure. It made life easier, they only had 3 levels to worry about so the code to handle the indenting could be pretty straightforward and somewhat 'hard coded' for the data set.

It also has the advantage that the tree is in the right order ie

Level1
-Level2
-Level2
-- Level3
-Level2
Level1
Level1
...

The template looks like this

the fields contain

Of course the most interesting field is the C

<?if:number(LEVELS)=1?>
<?ORG_LONG_NAME?>
<?end if?>
<?if:number(LEVELS)=2?>
<?attribute@inblock:start-indent;'20.0pt'? > <?ORG_LONG_NAME?>
<?end if?>
<?if:number(LEVELS)=3?>
<?attribute@inblock:start-indent;'30.0pt'?> <?ORG_LONG_NAME?>
<?end if?>

A chhose statement would have worked just as well. You can see the dependence on the data order and the structure or more specifically LEVELS element. We need to check that to know how big the indentation should be.
The @block is also important, we need that to get the indentation to show only on the current text and not affect anything else. If we had been using tables we could have gotten around that.
Now we could have been a little smarter and written something to handle any number of levels ... answers on the back on a new Mac Book Pro 15-inch: 2.8GHz 4Gb 500Gb HD :0)

Been too danged busy again, we got a nice 2-3ft of snow over the last two daze, digging out has been fun! Nearly lost the car ...

A quick Friday post on Siebel integration. Two pretty useful docs that John from the Siebel PM team recently shared with me.

8.1.1 TOI: Siebel BI Publisher Integration Functional Overview

http://oukc.oracle.com/static05/opn/login/?c=576002953&t;=offering

Siebel Reporting Migration FAQ - Actuate and BI Publisher (external visible)

https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=764097.1

Have a good weekend

Thursday, October 22nd, 2009 by Mark Rittman

I was on-site yesterday helping an ISV integrate BI Publisher into their product, and hit a few issues getting the product installed and running on the Weblogic application server. BI Publisher comes integrated out-of-the-box with OC4J and Oracle Application Server, but you'll need to install and configure it manually to work with other application servers [...]

You can read the full article HERE.

Many Thanks to Mark for his posting.

Since my post last week or was it the week before. The weeks are blurring by at the moment. Last week, I could swear it was warm and sunny here in Colorado, the fish were biting along with the mozzies and sitting on our deck you could here the 'crickets' chirping into the night. Back in July, we got nightly visits from a huge bear which I managed to capture on film. He was a sausage stealing, trash tipping pain in the butt but we were not going to argue :0)

Now its 27F, sorry 'Rest of the Worlders', I have been assimilated. Its imperial measurements for me all the way. I even know what a 'quart' is, I think in inches and cups for measuring. America is sometimes staggering in its arrogance for the rest of the world! Maybe a bit strong but you know what I mean.
Yep, its freezing cold the wind is howling, the windshield (another Americanism along with tail pipe, thats a good one) blower on the car can not keep up with the freezing rain and I have to scrape it to see. Come back Summer, maybe not the bear thou!

Anyhoo, my last post on row filling and the added requirement that I did not cover got some folks into action. Most notably, Lakshmanan and the Ice Man aka Ike Wiggins. Both of them were way ahead of me. I guess from a need, born of necessity i.e. get it done for a customer. Rather than my approach of, 'when I can find time', they have independently come up with solutions to handle the row wrapping issue I mentioned at the end of the last post.

Shout outs are due and if you use their methods, please drop a quick mail of thanks.

Lakshmanan's solution is available here. Some other great posts on EBS related tidbits (another PC'ed American term) available on his blog.

Ike's solution is available here Ike has also posted sample template and data here. Like the blog's new look and feel Ike, very suave!

... nor gloom of night stays these couriers from the swift completion of their appointed rounds." Don't know if it's still true for the US Mail but, the Publisher team was on the go despite the "epic" storm in San Francisco today.

Siva Ramanathan from MasterCard and Noelle

gave a great session on Publisher in E-Business Suite, PeopleSoft, Siebel, JD Edwards EnterpriseOne, and JD Edwards World.

Siva gave a great demonstration of using the BI Publisher Template Builder. He also told the story of how it used to take a business analyst, an IT developer, and a consultant from the vendor of their 3rd party tool THREE MONTHS to create a new PO or modify an existing one (like changing the signature) - and this was happening across 50 operating units. Now, using BI Publisher, it takes a business analyst only THREE DAYS to create a new PO or implement changes and does not require the intervention of IT nor anyone else. Imagine ... 30 times faster implementation time with 1/3 the people to create or edit a Purchase Order ... Priceless.

Noelle, the superwoman of many hats, covered each Application's integration with Publisher and an update of what's new and planned for each. The audience was hanging on her every word when they weren't laughing at her jokes.

There was a little less traffic at the demo pod today ... possibly due the storm but, Fethiye, Leslie, Noelle, Alin, Klaus, Nikos and Pradeep kept the demos going all day long. And of course, Osama-bey, the inspiration behind the innovation, was there to pitch in as needed.

Also saw a presentation packed with lots of great details on using Publisher in PeopleSoft by Millie Babicz and Todd Kummer of SpearMC Consulting.

Coming up on Wednesday::

• S308767 Innovations Using Oracle XML Publisher
  Manoj Chitre, Claremont Graduate University
  Moscone West L3 Room 3005
  1:45 pm - 2:15 pm


• S310889 Creating Custom Siebel Reports, Using Oracle Business Intelligence Publisher
  John Bedford, Oracle; Pradeep Sharma, Oracle
  Moscone West L2 Room 2009
  1:45 pm - 2:15 pm
  


• S311667: Modernize JD Edwards EnterpriseOne Reports with Embedded Oracle Business Intelligence Publisher
  Gurbinder Bali, Oracle; Jeff Erickson, Oracle; Michael Zheng, Oracle
  Marriott Hotel Golden Gate A1
  5:00 pm - 6:00 pm
  

One more day to go at Open World 2009. It's been quite the busy week both with sessions and at the demopod.

Even though each of us were scheduled to be at the pod for a few hours each day, we found ourselves staying longer and longer because there were just so many folks to talk to. In fact, Glenn from Show Management came by the pod first thing this morning to tell us that the BI Publisher demopod had some pretty significant numbers the day before. We really stood out. This would explain why most of us are now losing our voices. But, that's what we get for being so excited to talk at length about our fabulous product!

Sadly, the demogrounds are now closed. But, we still have sessions tomorrow.

You won't want to miss Nick & Klaus at 12pm in Moscone West 3014. They'll be educating you on Best Practices for BI Publisher.

There are also sessions on E-Business Suite, Peoplesoft, Siebel and the last hands on JD Edwards lab. Stop by and check them out.

There seemed to be a fair amount of questions today at the demo pod around converting from Actuate/Crystal/Oracle Reports to BI Publisher. Allow me to point out where you can find tools and/or information to make your conversion to BI Publisher a little smoother.


To convert Oracle Reports to BI Publisher, Ike Wiggins demo'd a cool little conversion utility at a session this afternoon. Check out his blog, where you'll find the download, a little tutorial and FAQs.

To convert Crystal Reports to BI Publisher, sorry, no utility here. But, check out the white paper on the OTN. We heard from Corey at Gallup today on how they were able to convert their Crystal reports to BI Publisher.

To convert Actuate Reports to BI Publisher, there's a conversion utility. Just do a search on My Oracle Support for patch # 8288024.

And now for what's coming tomorrow....

Well, if you believe the forecast, I suggest a raincoat, umbrella, galoshes...you know, those awful black rubber boats you had to wear as a kid. I hear we're in for a lot of the wet stuff. Hopefully, the anticipated precipitation doesn't stop people from making it to the session over at the Westin where we'll be talking about how E-Business Suite, JD Edwards, Peoplesoft and Siebel use BI Publisher to produce those pixel perfect reports.

If you're at the Marriott, you might want to check out Paul Rodwick's session on the Oracle BI Roadmap and Strategy. Check out where Oracle BI's headed.

And, yes, there's another one of the JD Edwards hands on labs too.

If you just want to stay high and dry, come over to Moscone West and play a little golf at the Callaway Experience.

Did I mention, the BI Publisher demo pod is really, really close to the Callaway Golf Experience? No? Well, it is. We're in the back on the right, Pod W-083. Our knowledgeable BI Publisher team will be on hand to show you demos and answer your questions.

For all of you coming into town for Open World this year, fun and cool stuff awaits.

On display just outside of the Yerba Buena Gardens is the Oracle BMW Racing Boat. You can't miss it on your way over to Moscone West, which by the way is where the BI Publisher demopod resides this year. We're in the back, on the right, Pod W-083.

On your way to visit us at the demopod, you might want to partake in the awesome games at the Blackberry Digital Playground. There you'll find the old style arcade video games, like Donkey Kong and Ms. Pacman. In addition to the games, you'll also find some groovy furniture, a la Mork-n-Mindy. Remember the egg chair? Well, they have those! Leslie (THE BIP Documentation guru) and I tried a couple out and believe it or not, they are super comfy! We're trying to figure out how to snag a couple for the BI Publisher pod...

In addition to finding us at the demopod Monday, you'll find...

Product Manager Extraordinaire, Mike Donohue, giving his ever popular Introduction to BI Publisher at 1pm over at the Marriott.

For the BIP Road Map and Features, head on over to the Marriott at 4pm. On tap to be there is the father of BIP himself, Osama Elkady. He'll be joined by the very knowledgable Klaus Fabian and of course, PM Mike.

Want to learn how to convert your Oracle Reports, Crystal and Actuate Reports to BI Publisher? Well, you're in luck....BIP PM Pradeep Sharma along with Ike Wiggins and Cory Keogh (from Gallup) will tell you all about it at 5:30pm at the Marriott.

If you're looking to get your hands on BI Publisher with JD Edwards, there are 4, count em 4, hands on labs this week. I heard these were filling up fast, so you might want to pre-enroll quickly to ensure you get a spot.

Want to see a demo or talk to us about the product? We'd be happy to chat with you down at the demopod. Please stop by and see us!

Having a good discussion on the forum regarding fixed row enumeration. Maybe you have a fixed height for a table on some pre-printed stationary. I have documented a way to fix the number of rows on a page a few years back. the twist to the requirement is that if I have 'line' data that will not fit on a single row and needs to wrap then where I might have had room for 10 rows on the paper output, because of the string wrapping I only now have the physical room, on the paper for 6 rows. But my original logic did not allow for this.

I don't have the answer to that piece yet, looking into it. Development, well template meister Hok Min has a doozy of an enhancement for the 11g release. True fixed table height support, you need a 5inch high table filled with as many rows as will or will not fit? Hok Min has the command. But you'll have to wait for 11g. When is that coming? Sometime within the next calendar year is the official line, unofficially, sooner rather than later - see you are still in the dark, gotta cover my a$$.

Back on the fixed row, I got a requirement this week to fill out empty rows in a table if the amount of data was not enough. Its its still fixed rows, you just want to show them. Rather than the solution I showed a few years back I found a new and groovy way to do it. The good ol for...next loop. Where's that documented then? Its in there, just tucked a away with the shape support.

It showed itself recently in my 'date' post and I have used it again this week.

Normal table to render the 'line' data, the next two fields. The variable field, numRows Variable:

<?variable:numRows;number(12)?>

just stores a value for the number of rows you want to show. You can hard code this value too, if you would like. The next field:

<?for-each:xdoxslt:foreach_number($_XDOCTX, 1,numRows-count(.//G_TEC_ID1),1)?>

All this is doing is using a command to effectively give me a for...next loop, starting at 1 up to 12(variable value) - the number of rows in the G_TEC_ID1 group with a step of 1

Then I have an empty nested table with zero cell borders to get it to butt up to the edges of the parent table and some selective borders showing.

The next field closes out the filler - <?end for-each?>

Much faster to develop than the original, neater too. Will be looking into the wrapping rows issue over the next few daze as time permits.

I have seen some big reports in my time. There was the monster German Asset Summary report that I narrowly escaped having to maintain not long after I joined Oracle UK more than 11 years ago now. I say 'was', it got canned because it was bending Oracle Reports to do things it just did not want to do.

Alok beats all known contenders into a cocked hat, with his 'Annual Cotton Report', it contains, ready for this? 200 data templates, not just queries, data templates! It tips the scales at a meaty 3 Mb!

He was understandably having problems getting the report to load into the report builder. Hide from the development team to the rescue with a little javascript goodness. If you have a monster report and its not loading here's the tip.

You need to update XDOReportModel.TIMEOUTMSEC value in the javascript library XDOReportModel.js

You'll find it J2EE_HOME/applications/xmlpserver/xmlpserver/js/XDOReportModel.js

I got Alok's report to load by setting it thus:

XDOReportModel.TIMEOUTMSEC = 10000;

You will need to clear your browser cache to get the change picked up.

Please note, you are customizing at this point and Oracle Support, as fantastic as they are, can not help you if you screw up. Please back up the js library before you make any changes. Yep, it ought to be exposed as a parameter for the report builder but its not right now. I dont think the team ever imagined a 200 data template report.

A value of 10000 gives a 10 second time out but the report loads in about 7 seconds on my laptop.

If you have anything to challenge Alok's colossal cotton report or even a mini monster. Would love to hear about them. Not just in terms of queries but also in terms of size.

Guinness are currently ratifying Alok's mondo record report. I think I would need a pint or three of the black stuff after putting that together!

Each Fall it's exciting to see the Oracle homepage www.oracle.com announcing all of the exciting events planned for Oracle Open World. For anyone who is still on the fence about attending, you still have time. The registration is still on for this mega conference.

And if you have registered, do not miss out on BI Publisher events. Please check out the Focus On BI Publisher document that lists all the sessions featuring or related to BI Publisher as well as details on the BI Publisher demo booths.

This year we have:

• 3 OAUG SIG sessions (including the BI Publisher SIG session)


• 24 General Sessions



15 sessions featuring BI Publisher




6 sessions on JD Edwards Integration




2 sessions on Siebel Integration




5 sessions covering BI / XML Publisher in E-Business Suite




2 sessions on PeopleSoft Integration






• 2 Demo Booths (including 1 booth for PeopleSoft - BI Publisher integration)

Hope to see you there!!

Ever thought, it would be cool to know what your RTF template was going to generate, in terms of output e.g. HTML, PDF, Excel, etc

With the standalone release you can, both the output format and the template being used are available. Both parameters are available

_xt - template name (not the file name)
_xf - output format

they just need to be declared in your RTF template:

<?param@begin:_xt;''?>
<?param@begin:_xf;''?>

You can then reference the values just like any other parameter in a template.

<?$_xt?>
<?$_xf?>

the format parameter will generate 'html', 'pdf','excel','rtf' ie all lower case.

If you have some specific layout that only needs to be shown if a specific output is generated you can combine the parameter with an if statement:

Short and sweet!

<?if:$_xf='html'?>
...
...
<?end if?>

In my post on Formatting Concatenated Datasources I mentioned the better way to do thangs was to use a data template to connect and execute queries on each databases and bring back a grouped/formatted data set.
So rather than the concatenated data set, thus

<DATA>
<SUPPLIERLIST>
 <G_VENDOR_NAME>
 <G_VENDOR_NAME>
</SUPPLIERLIST>
<PO_LIST>
 <G_PO>
 <G_PO>
 <G_PO>
 <G_PO>
 <G_PO>
 <G_PO>
</PO_LIST>
</DATA>

we use a data template to generate a hierarchical data set thus:

<DATA>
 <G_VENDOR>
 <G_PO>
 <G_PO>
 <G_PO>
 <G_VENDOR>
 <G_PO>
 <G_PO>
 <G_PO>
</DATA>

Remember, this is executing a query on SQLServer (or other db) to fetch the VENDOR data and executing another 'child' query on Oracle (or another db) to fetch the vendor's PO data.
This will allow you to simplify the layout template removing the need for the variables and the XPATH expression. It will also improve the performance immensely. With the concatenated data sources we were looping over the vendors. Setting a variable for the vendor and then looping over all of the POs filtering out the ones we did not want. So for every vendor we had to loop over every PO - its expensive.
With the data template approach we just following the data structures

<?for-each:G_VENDOR?>

...

 <?for-each:G_PO?>

 ...

 <?end for-each?>

<?end for-each?>

Much less expensive and simpler too and that satisfies the other mantra for layout templates, KISS!

How do you do it then?
Well first off you need to set up the oracle and SQL Server datasources. In 10.1.3.4 we ship the drivers for you for the popular non-Oracle dbs so setting them up is a relative cinch. We even provide a simple connection string for you to fill out.

NB: if you are using SQL Server 2008, you can use the 2005 driver. Worked for me.

Once you have both data sources set up its then on to the data template. As I have mentioned previously, there is no 'builder' for data templates in 10.x releases. The world becomes a much nicer place with 11g but you'll have to wait a bit longer for that. If you are just starting out with data templates, check out the Data Template Progression entry to get started. Once you are comfortable with the format come back ...

You ready? Its not that tough to do, you just need to get the structure right. I fell in to a trap putting this example together (thanks Ahmed) but I can now share that with you. Here's the DT for the SQLServer (parent) and Oracle (child) queries and their structure.

<dataTemplate name="DATA" description="SQL Server - Oracle Extraction">
	<properties>
		<property name="include_parameters" value="true"/>
		<property name="include_null_Element" value="false"/>
		<property name="include_rowsettag" value="false"/>
		<property name="scalable_mode" value="off"/>
		<property name="debug_mode" value="on"/>
	</properties>
	<dataQuery>
		<sqlStatement name="VENDORS" dataSourceRef="locsqls"><![CDATA[
         select     VENDORS.VENDOR_ID as VENDOR_ID,
     VENDORS.VENDOR_ADDRESS as VENDOR_ADDRESS,
     VENDORS.VENDOR_NAME as VENDOR_NAME
         from     biptest.dbo.VENDORS VENDORS]]></sqlStatement>
		<sqlStatement name="POS" dataSourceRef="lorcl"><![CDATA[
           select po.PO_VENDOR as PO_VENDOR,
       po.PO_NUM as PO_NUM,
       po.PO_ITEM as PO_ITEM,
       po.PO_AMT as PO_AMT
           from     PO_TABLE po
       where po.po_vendor = :VENDOR_ID]]></sqlStatement>
	</dataQuery>
	<dataStructure>
		<group name="G_VENDOR" source="VENDORS">
			<element name="VENDOR_ID" value="VENDOR_ID"/>
			<element name="VENDOR_NAME" value="VENDOR_NAME"/>
			<element name="VENDOR_ADDRESS" value="VENDOR_ADDRESS"/>
			<group name="G_PO" source="POS">
				<element name="PO_NUM" value="PO_NUM"/>
				<element name="PO_ITEM" value="PO_ITEM"/>
				<element name="PO_AMT" value="PO_AMT"/>
			</group>
		</group>
	</dataStructure>
</dataTemplate>

I have emboldened the important parts where things deviate from a regular single db data template. Notice the dataSourceRef attributes on the sqlStatement element. These names need to match the ones you defined earlier.
Then the query link, notice Im using a bind variable (:VENDOR_ID). You can not use a link structure i.e.

<link name="SQLORCL_LINK" parentQuery="VENDORS" parentColumn="VENDOR_ID" childQuery="POS" childColumn="PO_VENDOR" condition="="/>

they are not supported. Something else of note, I did not hit it here but if you had VENDOR_ID on the SQLServer table and VENDOR_ID on the Oracle table you must ensure they have a different alias e.g. VENDOR_ID and VENDOR_ID1. Although they are on different dbs when it comes to linking them

where PO.VENDOR_ID = :VENDOR_ID

will at best confuse the heck out of the extraction engine and get unexpected results.

One other trap that I somehow fell into was to have each query under its own </dataQuery> element. they need to share one.

<dataQuery>
 <sqlStatement name="VENDORS" dataSourceRef="locsqls"><![CDATA[ SQL1 ]]>
 </sqlStatement>
 <sqlStatement name="POS" dataSourceRef="lorcl"><![CDATA[]]>
 </sqlStatement>
</dataQuery>

Otherwise you get weird table does not exist errors.

Once you have the data template in your report and valid you will get the data set back you want. You can then start adding the parameters, summary columns, etc you want.

<COMBINED>
<LIST_G_VENDOR>
<G_VENDOR>
<VENDOR_ID>1</VENDOR_ID>
<VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
<VENDOR_ADDRESS>1 El Camino Real, Redwood City, CA 94065</VENDOR_ADDRESS>
<LIST_G_PO>
<G_PO>
<PO_NUM>123</PO_NUM>
<PO_ITEM>TV</PO_ITEM>
<PO_AMT>100</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>124</PO_NUM>
<PO_ITEM>Monitor</PO_ITEM>
<PO_AMT>300</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>125</PO_NUM>
<PO_ITEM>PC</PO_ITEM>
<PO_AMT>400</PO_AMT>
</G_PO>
</LIST_G_PO>
</G_VENDOR>
<G_VENDOR>
<VENDOR_ID>2</VENDOR_ID>
<VENDOR_NAME>Big Bike Motorsports</VENDOR_NAME>
<VENDOR_ADDRESS>10 Downing Street, London, SW1, UK</VENDOR_ADDRESS>
<LIST_G_PO>
<G_PO>
<PO_NUM>233</PO_NUM>
<PO_ITEM>TV</PO_ITEM>
<PO_AMT>1000</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>234</PO_NUM>
<PO_ITEM>Receiver</PO_ITEM>
<PO_AMT>340</PO_AMT>
</G_PO>
<G_PO>
<PO_NUM>235</PO_NUM>
<PO_ITEM>Phone</PO_ITEM>
<PO_AMT>150</PO_AMT>
</G_PO>
</LIST_G_PO>
</G_VENDOR>
</LIST_G_VENDOR>
</COMBINED>

Nice!

In terms of performance, its a much better way to get formatted/grouped data in your layout. But as I was discussing with someone this morning. Its a balancing act, you really want the dbs and the extraction engine to do the heavy lifting in terms of nesting structure, summary columns and sorting but you also want to provide the folks who build templates for users as much flexibility as possible.
Its not an exact science by any means, I always try and at least have the data engine extract using a 'sensible' hierarchy. Most if not all users are going to want to list invoices showing header information followed by the lines. Very rarely will they want to turn that hierarchy on its head, so its a good structure to have the DT extract plus the summary columns and sorting that go with it e.g invoice total, sort by line number, etc.

If you want to see the report file, you can get it here.

Happy Data Templating!

Are you struggling a bit with getting the bursting control file working under EBS? Maybe you want to add a filter to a delivery channel or you are just lost in the XML structure.

IceMan to the rescue, Ike aka IceMan has built a very slick and extremely useful Bursting Designer. Lots of features to help build the control file.

Ike has also taken the time to put together user help - check it out on his blog.

http://bipublisher.blogspot.com/2009/09/bi-publisher-bursting-designer.html

Download, use and drop Ike a mail or leave a him a comment of thanks.

When you are building reports inside BIP standalone, do you concatenate or data template? If you need to use multiple queries in your report, either in the same database or separate and you need to join that data together in the output, what do you use? Or maybe some other system is spitting out a flattened hierarchy XML.

You have a choice, either use a data template to bring the data together simplifying the template or use the concatenated data source and use some funky tricks in the layout to bring the separate data together.

Let's assume I have vendors in an Oracle db and their purchase orders in a SQL server database. I need to generate a listing report showing vendors and their POs. I have covered data templates elsewhere in this blog and I will follow up with a multi database example next week. Today, lets deal with the concatenated data source. Say I have the following data:

<DATA>
<SUPPLIERLIST>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
  <VENDOR_ID>1</VENDOR_ID>		
 </G_VENDOR_NAME>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Big Bike Motorsports</VENDOR_NAME>
  <ADDRESS>10 Downing Street, London, SW1, UK</ADDRESS>
  <VENDOR_ID>2</VENDOR_ID>		
 </G_VENDOR_NAME>
</SUPPLIERLIST>
<PO_LIST>
 <G_PO>
  <PO_VENDOR>1</PO_VENDOR>
  <PO_NUM>123</PO_NUM>
  <PO_ITEM>TV</PO_ITEM>
  <PO_AMT>100</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>1</PO_VENDOR>
  <PO_NUM>124</PO_NUM>
  <PO_ITEM>Monitor</PO_ITEM>
  <PO_AMT>300</PO_AMT>
  </G_PO>
 <G_PO>
  <PO_VENDOR>1</PO_VENDOR>
  <PO_NUM>125</PO_NUM>
  <PO_ITEM>PC</PO_ITEM>
  <PO_AMT>400</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>2</PO_VENDOR>
  <PO_NUM>233</PO_NUM>
  <PO_ITEM>TV</PO_ITEM>
  <PO_AMT>1000</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>2</PO_VENDOR>
  <PO_NUM>234</PO_NUM>
  <PO_ITEM>Receiver</PO_ITEM>
  <PO_AMT>340</PO_AMT>
 </G_PO>
 <G_PO>
  <PO_VENDOR>2</PO_VENDOR>
  <PO_NUM>235</PO_NUM>
  <PO_ITEM>Phone</PO_ITEM>
  <PO_AMT>150</PO_AMT>
 </G_PO>
</PO_LIST>
</DATA>

You'll notice both the vendors and the PO are at the same level in the XML hierarchy. Looks tough to bring them together in the output to get

VENDOR1
PO1
PO2
VENDOR2
PO1
PO2.

With a very simple template approach we get

Not what you need right?

Looking back at the data, you might also have noticed that both the vendor and PO share the vendor ID - we can use that. In our RTF template we are going to loop over the vendors and inside loop over the PO but checking vendor ID to ensure we are getting the right POs.

There are two pieces to the solution that are different to a regular template loop over or re-grouping over a regular hierarchical or completely flat data set.

Variable - as we loop over the vendors we need some way of tracking what vendor record we are currently processing. In my template just after the for-each for the G_VENDOR group I have field with

<?variable@incontext:v_id;VENDOR_ID?<

Its not really a variable, you can not update with a value, you can just reference it or re-declare it. Im using the @incontext command to keep the variable localized to the vendor loop.

The other piece of interest is the loop command for the POs.

<&?for-each:/DATA/PO_LIST/G_PO[PO_VENDOR= $v_id]?>

Two things of note in here:

1. The for-each is not using 'G_PO' as you might expect. Im providing the complete path to the PO group. Why? Because Im currently looping over the vendors in an outer loop. Remember POs are at the same level as the vendors so <?for-each:G_PO?> will not find any data.


2. There is an XPATH expression associated with the for-each, '[PO_VENDOR= $v_id]' - this is instructing the rendering engine to only show PO records that have a vendor id equal to the parameter, 'v_id' - remember that was set and re-set every time you hit a new vendor in that loop.

That's it, those two pieces of 'code' allow you to generate a hierarchical output from the concatenated data set.

If you need the template and some data, get em here.

Yep, its a simple demo but you get the idea. Of the two options, layout or data template - Id go with the data template every time. Get the extraction engine to do the heavy lifting and KISS on the layout template :0) Complex layout templates mean scared business users that will not touch the template even if it is in friendly ol' MSWord. Im excluding version 2007 from that term of endearment, I have had it 6 months and I still don't like it!

Enjoy yer weekend, after my week, I'll be sleeping!

Multiple questions lately on working with dates, how can I work out the number of days between two dates? How can I create a list of consecutive dates, the list goes on. I have mentioned the lack of native date support in BIP ... it still uses a hybrid of XSL1.0 and 2.0, that's a whole other story.

To plug some of those gaps there are a bunch of data functions that are available now, couple of the more interesting ones can help to answer the two questions above.

There is an extension function you can use, from the javadoc and as Leslie has reminded me, the user docs:

date_diff

public static long date_diff( format, fromDate, toDate, locStr, tzID)

Method to get the difference between two dates in the given locale. The dates need to be in "yyyy-MM-dd" format. This function supports only Gregorian calendar.

Parameters:
format - the format to which the difference is required; allowed formats are y (for Year), m(for month), w(for week), d(for day), h(for hour), mi(for minute), s(for seconds) and ms(for milliseconds)
fromDate - the first date
toDate - the second date
locStr - locale string -> lang-Territory
tzID - timezone ID ->http://java.sun.com/j2se/1.4.2/docs/api/java/util/TimeZone.html
Returns:
the difference in dates in the desired format

For example

<?xdoxslt:date_diff('d','2009-09-14', '2009-09-20','en-US',1)?>

gives a result of 6!

You can substitute in columns for the dates, just remember the date format required.

The other question deals with generating a list of consecutive dates. Could solve it with a nice recursive template function, bleeeuuch! BIP has a nice 'for...next' loop function. Gotta thank 'Vetsrini' for jumping on this in the forum.

<?for-each:xdoxslt:foreach_number($_XDOCTX,2,10,1)?>
Hello - <?position()?>  = <?xdoxslt:sysdate() + position()?> = <?xdoxslt:ora_format_date_offset('2009-06-03',position(), '+')?>

The first line is the for...next loop, specifically the xdoxslt:foreach_number($_XDOCTX,2,10,1) command. By the numbers, 2 is our starting point, 10 is the stopping point and 1 is the step. Any of these can be substituted with column data values.
We have the loop to generate the list of numbers, now we want to generate consecutive dates by adding each number to a date. The following command

<?xdoxslt:ora_format_date_offset('2009-06-03',position(), '+')?>

does the business. We are starting with a fixed date (XSD format) but you could equally use a column value.

Running the command gives us:

Hello - 1 = 90917 = 2009-06-02T00:00:00.000+00:00
Hello - 2 = 90918 = 2009-06-03T00:00:00.000+00:00
Hello - 3 = 90919 = 2009-06-04T00:00:00.000+00:00
Hello - 4 = 90920 = 2009-06-05T00:00:00.000+00:00
Hello - 5 = 90921 = 2009-06-06T00:00:00.000+00:00
Hello - 6 = 90922 = 2009-06-07T00:00:00.000+00:00
Hello - 7 = 90923 = 2009-06-08T00:00:00.000+00:00
Hello - 8 = 90924 = 2009-06-09T00:00:00.000+00:00
Hello - 9 = 90925 = 2009-06-10T00:00:00.000+00:00

Cool huh? Yep, you get long XSD date formats, but you can format those quite easily.
Groovy Baby!

A flurry of mails that I had not got to over the last few daze have sparked some blogging activity at last. Got a question, how to remove the blue borders that are appearing around my images in HTML output when I add a link to them. For example:

No its not me as a kid but my 16 and 13 year old ... as kids. Dang they have grown in the last 4 years. The refrigerator is always empty, towels on the floor, water bill through the roof, my car has no gas in it, again - see you got me started now!

See that border, the almighty users are not gonna want that. Its fixable but we kept it a secret, Leslie will get it into the docs as soon as its possible but for now, read on.

In your template:
1. Double click or right click > Format Picture to get to the picture properties
2. In the Web tab put

<xsl:attribute name="border">0pt solid black</xsl:attribute>

3. Save and run et voila.

For the tech heads all we are doing is setting the border attribute on the image to 0pt effectively hiding it.
This is available from 10.1.3.3.2 onwards.

See, my sons sans blue border, they still don't clear up after themselves thou. Publisher is not that good :0)

Give yourself a pat on the back ... why so?
Well if you have been out on the BIP forum lately as I have, you'll notice a few things.

1. The explosion in the number of threads and questions out there. Jumping up a level to the Business Intelligence Foundation level where you get to see the forums by the numbers. You'll notice that the BIP forum is way up there with big view, threads and message numbers.

   That I guess could be construed as a good or bad thing. Bad in that there are so many questions out there, is the doc not up to it? But good in that there are so many folks working with BIP and moving beyond the basics and getting stuck into some hard core BIPing. I'll go with the latter, especially since ...



2. The number of folks giving back and answering questions is fantastic. Honorable mentions to Vetsrini, stevencallan, rwillems, Colectionaru, nagornyi, M14, Sattti, rmembrere and Norman. I have to admit to not having been frequenting the forum of late but have been back in recently and contributing. I'm impressed at the quality of the questions, by that I mean how tough the dang things are. No more noob questions we are into hard core grouping, bursting, extension questions ... good stuff!

One request, please add your name to your profile or at least a slightly amusing alias, you know who you are 'Oracle's Mascot'. It makes it easier to provide answers, for me anyhoo rather than 'Hi User1234567890'

And as for you Oracle folks abusing the forum, Im disappointed in you :0) You have your own main line (albeit sometimes broken) to the internal development community. If you ask a question and get an answer, please give back and provide an answer to someone else's question.

There is a mine of useful stuff in the forum answers. Trying to search for answers is a bit of a nightmare. If you have some time, try tagging your questions/answers it will help the search engines.

Finally, reward those answering your questions by giving them the points they deserve. Sadly, points don't mean prizes but they do mean some kudos within the community and contribute to possible future ACEdom.

Thanks for all the effort out there, it really contributes to a better Publisher community!

Just got off a call with Tyler Muth, APEX meister. He just dropped me a link to an excellent white paper on hooking up an APEX app with BIP using BIP's web service layer.

this is not the out of the box integration but fully functioning log into BIP, navigate folders, pick a report and run it.

http://www.oracle.com/technology/products/database/application_express/packaged_apps/integration.html

Enjoy APEXers!

Long'ish on and off exchange with 'Super Support Man' aka Kevin M over IM this morning trying to sort out a frustrating check printing issue a customer was having. Their bank had provided a specification sheet showing the required layout for the check including the the 'where' and 'how' the MICR string should look.

A commercial font was being used and it was not rendering correctly, basically the string was too long for the space allocated by the bank. It looked like a spacing or scaling issue on BIP's part. I had been scratching my head over it for a while - too long in fact. I can fully understand the pain and misery of not being able to cut checks - they still have to be one of the most mission critical documents for an organization today.

While chatting (typing) to 'Super Kev', I was idly trying some of the other MICR fonts I have on my machine in MSWord against the customer's font. Naming no names here are the results.

Not huge differences but enough that over a 25 character MICR string they will be significantly different enough for certain banks to reject test checks because of the length of the string.

Its not a completely fair image, two of the fonts are 'free', one of those is the MICR font included with Publisher. There are a few commercial fonts in there too but just using the vanilla font flavor.

If you want to use Publisher to generate checks and you purchase a commercial font, please check out the user guides. Im not a 'user guide' type of guy - strange considering how much effort I put into the BIP docs with Leslie. Writing documentation yourself gives you a whole new appreciation for the pain and misery folks go through writing it and then no one reads it. But I digress ... again!

Typically, the commercial vendors don't just provide a single font. They provide a range of them and a test document for you to print. From that printed document, you can then match the correct font to your bank's specification requirement.

If you use Publisher's font you should be good for spacing but there is an enhancement request out there to help you to tweak the character spacing if you need it.

Hopefully, in this case, things will now be resolved quickly and the customer can at last start cutting checks. Good luck to you with check printing and don't forget, RTFM :0)

Im seeing more and more folks wanting to render their stored XHTML in their outputs using BIP. I have written about the 'how' to render in the past but much more recently I have had customers that could not get to the rendering part because the extract was not extracting the XHTML in its raw format.

By raw I mean

'<p>This is a paragraph</p>'.

The data template engine under EBS 11i and R12 was escaping all the angle braces so the above string was coming out as

'& lt;p & gt;This is a paragraph& lt/;p& gt;',

ignore the spaces after the ampersand, just trying to stop the blogging platform from formatting them. Anyhoo, the sub template that converts the XHTML to XSL-FO was not working, its looking for '<p>' not '& lt;p& gt;'

Good news everyone, the dev team have implemented an enhancement for this so that you can get the raw data out. Still not quite out in the wild but look out for patch 8660920 if you are stuck.

All you need to do is change the dataType attribute for the CLOB column to 'xdo:xml' e.g.

<element name="ARTICLE_TEXT" dataType="xdo:xml" value="ARTICLE_TEXT"  />

A tiny tiny prize for the person that spotted the Futurama reference?

Kudos to Kan over at the BIP consulting blog, just wanted to spread the word a little about the couple of articles he has on the Actuate > Publisher converter.

Actuate to BI Publisher Reports Conversion for Siebel - Part 1

Too many questions on the love of my life, BIP charts. Oh the joy of finding out how to add a trendline or a fixed horizontal line, the list goes on. Its quite easy really but that's because I have an internal chart rendering tool that generates the XML I need to do cool stuff in charts. Some times I have to resort to scouring the chart DTD but the tool lets me test the feature I want. Sadly, the tool is not owned by BIP so we do not have the authority to make it public but I so wish it were. It would help you to help yourselves sooo much.

Two things to cover today ...

X Axis Labels

I've a Bar chart where X-axis labels are coming as horizontally-alligned and one value above, next value in a level below...so on. Is it possible change the alignment to -45 degree as it is available in excel chart? x-axis data label points will be displayed in uniform way then.

45 degrees is not possible at the moment. you can get them to show at 90 or 270 degrees thou

<O1TickLabel textRotation="TR_HORIZ_ROTATE_90" automaticRotation="AR_NO_ROTATE"/>

you can influence other properties too

<O1TickLabel textRotation="TR_HORIZ_ROTATE_90" automaticRotation="AR_NO_ROTATE">

There was a followup kindly answered by Vetsrini

Fixed Axis Scale

Is it possible to define a fixed scale for x-axis? I see by default Bipublisher decides scale based on the value coming from dataset. I want to set scale always from 0 to 500 with label showing at interval of 10.

<X1Axis axisMinAutoScaled="false" axisMinValue="0" axisMaxAutoScaled="false" axisMaxValue="500" majorTickStepAutomatic="false" majorTickStep="10"/>

and then,
Static Chart Line

How do I show a static line on my chart?

Vetsrini and I seemed to be in a race to answer charting questions yesterday :0)

Inside the Graph tag use:

<Y1ReferenceLine>
 <ReferenceLine index="0" visible="true" lineWidth="2" text="My Ref Line" value="550.0" displayedInLegend="true" lineColor="#3366ff"/>
</Y1ReferenceLine>

and finally

Trend Lines

Does BIP have the ability to create trend/regression lines?

Yep, it does, manual job like the others above but possible.
Under the Graph tag

<SeriesItems defaultFitlineType="FT_LINEAR"/>

Valid vaues for fitlinetype are FT_LINEAR|FT_LOGARITHMIC|FT_EXPONENTIAL

if you want multiple lines fitted to data points

<SeriesItems>
<Series id="0" fitlineType="FT_LOGARITHMIC"/>
<Series id="1" fitlineType="FT_EXPONENTIAL"/>
</SeriesItems>

I'll blog em as they come in and we manage to answer them. Happier charting!

I wrote about removing duplicate data a while back, I hit it as a problem again this week. Lets just say I was using an almost undocumented feature of the data extraction engine that gave me some unexpected results. Thats all Im saying cos Im not supposed to be using it.

I ended up with a bunch of duplicated rows which was anoying but because I was not supposed to be using it I could not moan or bug the dev boys and gals. So I needed a work around, thankfully the RTF template came to the rescue.
I combined a BIP function, xdoxslt:distinct_values and XSLs fabulous concept of a variable to get rid of my duplicate rows.

As I have mentioned before variables in XSL are strange things, they are not updatable which is a drag for most of us but they can hold complete chunks of the data tree which is a big plus for many of us.

All I had in my template was:

<?variable:metrics;xdosxlt:distinct_values(G_METRICS)?>

you could also go with this neat XPATH solution which checks an entry's siblings for duplicates.

<?variable:metrics;/APCARS/LIST_G_METRICS/G_METRICS[not(HOUR=preceding-sibling::G_METRICS/HOUR)]>>

We now have a variable that has the data we need. Now all we do is loop over it just like a regular repeating node

<?for-each:$metrics?> ... <? end for-each?>

Ideally I`d have that bug fixed so we get the right data set presented to the template but its not a bug cos Im not meant to be using that feature right :0)

I have just spent the past few days actually building a real life report that's needed by a customer. A regular occurrence for most of you Im sure. Having transitioned from the warm, cozy, fuzzy bubble of development to sales consulting over the last year. Sorry dev folks, its a bubble and its cozy compared to life on the outside.
It was a new experience for me and a real eye opener. Sitting in a cramped room with a bunch of fantastic Oracle Development Consultants who were under the hammer, bashing out report after report. I have a whole new respect for what they do and that extends to you dear readers.

As a member of the development team for so long, I was proud of the product and documentation we had delivered to customers. Sitting down and actually writing something in the heat of implementation opens your eyes. By no means have we done a bad job there are just things that would help in the doc and product to make life easier for customers and consultants alike.

I guess in development we get just a little too close to our babies, we build them up, nurture them, ask others what they would like them to do, consider the requests and between resource and time constraints we either do or dont add the features. Eventually we have a young adult ready to face the real world, armed with cool features and a set of documentation to help guide its users. We've been with them so long we sometimes just expect customers to join the dots to get from A to C, 'that's obvious' we say but its sometimes not for a newbie or not so newbie.

Enough of the child analogy Dexter! Bottom line, we could all do better when it comes to building a documenting a product. But then I guess that's not just BIP but the whole world of software.

Do I miss the bubble? I miss the folks in the bubble but being outside knocks you off balance a little and I have come to really like that!

Good news for all EBS BI Publisher enthusiasts! Now you can deliver faxes directly from the BI Publisher Delivery
Manager to any fax destination through RightFax 9.3 or above. To learn more about RightFax you can visit their
website - http://www.captaris.com/rightfax.

Oracle Collections team has implemented BI Publisher - RightFax integration for Strategy Dunning Program. You can find the details of this implementation and patch download option in metalink (patch details at the end).

BI Publisher Delivery Manager can now make use of RightFax XML interface to submit a fax job and to query the
status of the job. These XML requests and responses are transmitted over HTTP/S. RightFax can convert the XML request into FCL (Facsimile Command Language) on the server side. So we do not have to worry about embedding FCL code on client (BI Publisher) side anymore. RightFax supports PDF, Postscript and PCL documents and may also support Microsoft Office documents depending on its Server configuration. The Delivery Manager supports a single fax destination per delivery request; therefore, to send fax to multiple destinations, you will have to submit multiple delivery jobs.

Here are the steps to set up the RightFax Delivery Channel in BI Publisher Delivery Manager

1. Create a Delivery Manager instance
2. Create a Delivery Request instance
3. Add the request properties
4. Set your document to the DeliveryRequest instance
5. Submit the delivery request

And here is a sample code.....

  // create delivery manager instance
  DeliveryManager  dm = new DeliveryManager();

  // create a delivery request
  DeliveryRequest  req = dm.createRequest(DeliveryManager.TYPE_RIGHTFAX);

  // required host information (hostname / IP address / reference name from config file)
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_HTTP_HOST,"myhost");

  // required sender information
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_SENDER_RFUSER,"Administrator");
  

  // Optional properties
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_SENDER_FROM_NAME,"Dave Taylor");
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_SENDER_FROM_COMPANY,"ABC, Ltd.");


  // required destination
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_FAX_TO_NUMBER,"555-1111");

  // Optional properties
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_FAX_TO_NAME,"Fred Flintstone");
  req.addProperty(DeliveryPropertyDefinitions.RIGHTFAX_FAX_TO_COMPANY,"Acme, Inc.");

  // set the document
  req.setDocument("/document/invoice.pdf");

  // submit the request
  req.submit();

  // close the request
  req.close();


Did you say what all RightFax Delivery Properties?
Ok, so here goes a long list of delivery properties supported for RightFax delivery 
channel. It shows the corresponding RightFax XML interface element for submitting a 
fax job. Here RIGHTFAX_HTTP_HOST, RIGHTFAX_SENDER_RFUSER, RIGHTFAX_FAX_TO_NUMBER
are the only required properties, rest are optional.



  

    
Delivery Property
    
Description
    
Rightfax XML
interface element
for submitting
a fax job
  
  

    
RIGHTFAX_HTTP_HOST
    
Required.
HTTP host of the RightFax server.
    
 
  
  

    
RIGHTFAX_HTTP_PORT
    
Optional.
HTTP port of the RightFax server.
Default=80.
    
 
  
  

    
RIGHTFAX_HTTP_REMOTE_DIRECTORY
    
Optional.
Enter the remote directory name.
Default value is “/rfxml”.
    
 
  
  

    
RIGHTFAX_HTTP_REMOTE_FILENAME
    
Optional.
Enter the remote filename.
Default is “/RFWebCon.dll".
    
 
  
  

    
RIGHTFAX_HTTP_AUTHTYPE
    
Optional.
HTTP authentication type of the 
RightFax server url. Valid values are
RIGHTFAX_HTTP_AUTHTYPE_NONE,
RIGHTFAX_HTTP_AUTHTYPE_BASIC,
RIGHTFAX_HTTP_AUTHTYPE_DIGEST.
Default value is
RIGHTFAX_AUTHTYPE_NONE.
    
 
  
  

    
RIGHTFAX_HTTP_USERNAME
    
Optional.
HTTP username for the RightFax
server url. Required when
RIGHTFAX_HTTP_AUTH_TYPE is set to
values other than
RIGHTFAX_HTTP_AUTHTYPE_NONE.
    
 
  
  

    
RIGHTFAX_HTTP_PASSWORD
    
Optional.
HTTP password for the RightFax server
url. Required when
RIGHTFAX_HTTP_AUTH_TYPE is set to
values other than
RIGHTFAX_HTTP_AUTHTYPE_NONE.
    
 
  
  

    
RIGHTFAX_HTTP_ENCTYPE
    
Optional.
The encryption type can be set to
either of the following:
RIGHTFAX_HTTP_ENCTYPE_NONE
– no encryption (default)
RIGHTFAX_HTTP_ENCTYPE_SSL
– use Secure Socket Layer
    
 
  
  

    
RIGHTFAX_HTTP_USE_FULL_URL
    
Optional.
Set to “true” to send the full URL
for the HTTP request header. Valid
values are “true” or “false” (default).
    
 
  
  

    
RIGHTFAX_HTTP_TIMEOUT
    
Optional.
Enter a length of time in milli-
seconds after which to terminate the
request if a connection is not made
to the HTTP server.
The default is 60000 (1 minute).
    
 
  
  

    
RIGHTFAX_HTTP_PROXY_HOST
    
Optional.
Enter the proxy server host name.
    
 
  
  

    
RIGHTFAX_HTTP_PROXY_PORT
    
Optional.
Enter the proxy server port number.
Default=80.
    
 
  
  

    
RIGHTFAX_HTTP_PROXY_AUTHTYPE
    
Optional.
Valid value is either of the following.
RIGHTFAX_HTTP_PROXY_AUTHTYPE_NONE
– no authentication
RIGHTFAX_HTTP_PROXY_AUTHTYPE_BASIC
– Use HTTP basic authentication
RIGHTFAX_HTTP_PROXY_AUTHTYPE_DIGEST
– Use HTTP digest authentication.
    
 
  
  

    
RIGHTFAX_HTTP_PROXY_USERNAME
    
Optional.
Enter the username for proxy
authentication.
    
 
  
  

    
RIGHTFAX_HTTP_PROXY_PASSWORD
    
Optional.
Enter the password for HTTP proxy
authentication.
    
 
  
  

    
RIGHTFAX_SENDER_FROM_NAME
    
Optional.
Enter the name of the sender.
    
SENDER/FROM_NAME
  
  

    
RIGHTFAX_SENDER_EMPID
    
Optional.
Enter the employee id of the sender.
    
SENDER/EMP_ID
  
  

    
RIGHTFAX_SENDER_FROM_COMPANY
    
Optional.
Enter the name of the sender’s company.
    
SENDER/FROM_COMPANY
  
  

    
RIGHTFAX_SENDER_FROM_DEPARTMENT
    
Optional.
Enter the name of the sender’s
department.
    
SENDER/FROM_DEPARTMENT
  
  

    
RIGHTFAX_SENDER_FROM_PHONE
    
Optional.
Enter sender’s phone number.
    
SENDER/FROM_PHONE
  
  

    
RIGHTFAX_SENDER_RETURN_EMAIL
    
Optional.
Enter sender’s return email address.
    
SENDER/RETURN_EMAIL
  
  

    
RIGHTFAX_SENDER_BILLINFO1
    
Optional.
Enter the billing code of the fax
owner.
    
SENDER/BILLINFO1
  
  

    
RIGHTFAX_SENDER_BILLINFO2
    
Optional.
Enter the secondary billing code
of the fax owner.
    
SENDER/BILLINFO2
  
  

    
RIGHTFAX_SENDER_RFUSER
    
Required.
Enter the name of the sender’s
RightFax user name.
    
SENDER/RF_USER
  
  

    
RIGHTFAX_FAX_TO_NUMBER
    
Required.
Enter the fax number where the
document will be sent.
    
DESTINATIONS/FAX/TO_FAXNUM
  
  

    
RIGHTFAX_FAX_TO_NAME
    
Optional.
Enter the recipient’s name.
    
DESTINATIONS/FAX/TO_NAME
  
  

    
RIGHTFAX_FAX_TO_COMPANY
    
Optional.
Enter the recipient’s company name.
    
DESTINATIONS/FAX/TO_COMPANY
  
  

    
RIGHTFAX_FAX_TO_ALTNUMBER
    
Optional.
Enter the alternative fax number.
    
DESTINATIONS/FAX/ALT_FAX_
NUM
  
  

    
RIGHTFAX_FAX_TO_CONTACTNUMBER
    
Optional.
Enter the contact phone number of
the recipient.
    
DESTINATIONS/FAX/TO_
CONTACTNUM
  
  

    
RIGHTFAX_FAX_COVERSHEET
    
Optional.
Enter the coversheet template for
the current document. The file name
can be either a full path on the
RightFax server machine or a path
relative to RightFax\Production\Covers.
    
DESTINATIONS/FAX/COVERSHEET
  
  

    
RIGHTFAX_COVERTEXT
    
Optional.
Enter the text that should appear on
the coversheet.
    
COVERTEXT
  
  

    
RIGHTFAX_COVERTEXT_TYPE
    
Optional.
Enter the type of the coversheet
text. TXT (default) or RTF
    
COVERTEXT/type
  
  

    
RIGHTFAX_COVERTEXT_ENCODING
    
Optional.
Enter the encoding of the coversheet
text. NONE (default) BASE64
QUOTEDPRINTABLE
    
COVERTEXT/encoding
  
  

    
RIGHTFAX_CONTENT_TYPE
    
Optional.
Enter the MIME content type of 
the document. Default value
“application/octetstream”.
    
Set to Content-Type field
of file attachment.
  

Note: In addition to the above listed RightFax specific properties, all delivery manager
common properties, including BUFFERING_MODE (for document redelivery) and FILTER (for
document conversion), are supported.


You want to set the RightFax Server in a configuration file?
I am sure you already know that BI Publisher supports a delivery configuration file to
manage delivery servers, delivery properties and custom delivery channels. Now RightFax
server has been added to this list with server type as 'rightfax' 

  <server name="myhost" type=”rightfax”> 

This server type expects values for Host, port, uri, username, password, authType, 
encType, proxyPort, proxyUsername, proxyPassword, proxyAuthType, filter and
filterOutputContentType. 

Please check the BI Publisher ‘Administrator’s and Developer’s Guide’ to know more
about the delivery configuration file.

Sample Delivery Configuration file (xdodelivery.cfg) -


<?xml version='1.0' encoding='UTF-8'?>
<config  xmlns="http://xmlns.oracle.com/oxp/delivery/config">
  <!--   ========================================================  -->
  <!--     servers section                                         -->
  <!--     List your pre-defined servers here.                     -->
  <!--   ========================================================  -->
  <servers>
        <server  name="myhost" type="rightfax" default="true"  >
        <host>myrightfax1.companyname.com</host>
        <port>80</port>
        <uri/>
        <username/>
        <password/>
        <authType/>
        <encType/>
        <proxyHost/>
        <proxyPort/>
        <proxyUsername/>
        <proxyAuthType/>
        <filter/>
        <filterOutputContentType/>
        </server>
        </servers>
  <!--   ========================================================  -->
  <!--     properties section                                      -->
  <!--     List the system properties here.                        -->
  <!--   ========================================================  -->
  <properties>
        <property  name="ds-temp-dir">/tmp</property>
        <property  name="ds-buffering">true</property>
  </properties>
  <!--  ========================================================  -->
  <!--      channels section                                       -->
  <!--      List the custom delivery channels  here.               -->
  <!--   ========================================================  -->
  <channels/>
</config> 
 
Can we monitor the Job Status?

When the BI Publisher Delivery Manager submits a job, it receives an XML response from
RightFax server. This response includes the unique id for the fax job, which can be used to
check status of the job by submitting a separate request to RightFax server. However, BI
Publisher Delivery Manager is not using this unique fax job id approach. Instead, we
recommend the standard approach used by all delivery channels to get the job status.

In this approach, after a job is submitted the client program can make ‘asynchronous’ 
call using delivery manager APIs. You can create your own callback logic by implementing 
DeliveryResponseListener interface. You must implement the responseReceived() method. 
This method should  be called when the delivery request completes. You can pass this
callback when you invoke the submit() method of the DeliveryRequest. This will initiate
the delivery process in the background and the submit() method will immediately return
the control to DeliveryResponseListener to get the delivery status. Sample code follows:
static class MyListener implements DeliveryResponseListener{
public void responseReceived(DeliveryResponse pResponse){
      System.out.println("Request  done!");
      int  status = pResponse.getStatus();
      System.out.println("Request  status id : " + status);
      String  msg = pResponse.getStatusMessage();
      System.out.println("Request  status message : " + msg);
      //  Update the status in database here...
      }
}
public static void main(String[] args){
      try
          {
                DeliveryManager  dm = new DeliveryManager();
                DeliveryRequest  req =
                dm.createRequest(DeliveryManager.TYPE_RIGHTFAX);
                req.setDocument("d:/tmp/test.ps");
                req.submit(new  MyListener());
          }
      catch  (Exception e)
          {
                e.printStackTrace();
          } 
}
In asynchronous mode, server status is checked once per minute by default but you 
can control this interval by setting ASYNCH_CHECK_INTERVAL delivery property before
submitting the request.
Check the status codes returned by the Delivery Manager and its corresponding status
codes/error codes on the RightFax server.

  

    
Delivery Manager Status/Error Code
    
RightFax Status/Error Code
  
  

    
STATUS_CLIENT_IN_PROCESS
    
 
  
  

    
STATUS_FAILED_BUSY
    
 
  
  

    
STATUS_FAILED_CLIENT_ERROR
    
 -1 (failure to load XML DOM – possibly XML
formatting error)
 -4 (XSL info missing)
 -5 (unknown XML operation type)
  
  

    
STATUS_FAILED_INVALID_RECIPIENT
    
 
  
  

    
STATUS_FAILED_INVALID_REQUEST
    
 
  
  

    
STATUS_FAILED_INVALID_USER
    
 
  
  

    
STATUS_FAILED_IO_ERROR
    
 
  
  

    
STATUS_FAILED_SERVER_ERROR
    
 -3 (failed to connect to RightFax Server)
 -2 (failed to load XSL into DOM)
  0 (submit/query failure)
  9 (Too many errors)
  10 (Duplicate)
  11 (Error)
  12 (Needs attention)
  13 (Needs attention)
  Any other status code
  
  

    
STATUS_FAILED_TIMEOUT
    
 
  
  

    
STATUS_NOT_DELIVERED
    
 
  

 


So I believe you are all set to try the document delivery to a RightFax Server.. 
Happy faxing !! Please note down the patch numbers -



The patch number for EBS 11i (for XML Publisher 5.6.3) is -
7665602 - RIGHT FAX CHANNEL SUPPORT WITH PROVISION FOR DELIVERY STATUS AT LATER TIME

The patch number for EBS 11i (for Collections) is -
8435600 - UNABLE TO SEE FAILURE REASON IN THE CONCURRENT PROGRAM LOG FILE

I have something to confess and an apology at the same time. Last year the folks from IDAutomation approached me for some help to show our common customers how they could use the new java barcode package that they had developed. I agreed and then promptly got caught up in all sorts of other projects. More than a year later I have finally come up with a solution - its been gnawing away at me for all this time. Apologies to the IDAUtomation folks especially Brant.

So what is the package and what does it do? Im not going to get into detail here, you can check those out on the IDAutomation web site, suffice to say its a set of java APIs that generate a barcode font image that can then be embedded in a document. Not just the simpler or 'linear' fonts such as Code39, Code128, etc oh no! They also provide a package for the 2D barcodes such as PDF417 and the tougher and more beguiling (from a human eye perspective, Maxicode format. There is not a barcode font file in sight, its a small, neat and perfectly formed (well almost, I'll get to that later) java library that will generate a gif or jpeg image of the data you want encoded.

How do you go about using it? well first up its not free but the download is, so you can try it out and see if it fits. They have the java libraries to generate an image but you can also set the whole thing up as a servlet so the solution can be used across your intranet. For this article Im just going to cover the java class approach, saving the servlet for another time (not another year's wait I promise)
To use it, I have employed the BIP extension framework - no tough rules to adhere to and I introduced the framework back in September 2007 with a couple of followup posts here and here on some more real world examples.

Not to build too much anticipation but, Im going to split the blog entry here - long entries are tiresome to read at the best of times and reading me, blathering on for page after page is not fun ... so my wife tells me :0)
Until tomorrow ... adieu!

Another nugget of the gold stuff from Rainer, this time dynamic queries in data templates. Not an earth shattering discovery but its a nicely laid out walk through of how to add lexicals to your data template based queries using a before fetch trigger in the database. you can find the article here:

http://www.oracle.com/global/de/community/bip/tipps/dynamische_queries/index_en.html

for the German readers among you, you get it in your native tongue too

http://www.oracle.com/global/de/community/bip/tipps/dynamische_queries/index.html

If you're a newbie to the data template scene you might check out the Data Template Progression post and a few others in the data template archives.

A few weeks back I documented an extension I built for a potential customer that passed a chunk of the XML tree out and passed back some formatted text.

Well I have another coming up, more of that later this week. One problem was how to test the extension in the template builder. It was a bit of a pain, having to merge the extension class and any supporting classes into an existing zip or jar that the builder knew about.

Today, I actually bothered to reach out to 'template builder meister' Junichi and ask if I could add a classpath command or in some way include my extension jar. Ask and you shall receive ... simple stuff, this communicating with people :0)

Just open RTF2PDF.jar under jlib directory, for me thats 'D:\Program Files\Oracle\BIP\BIP Desktop\Template Builder for Word\jlib'. You will see MANIFEST.MF file inside. Back up that files and then add a file path to class-path in the manifest file, the new jar/zip will be used when you run the preview.

The manifest file looks like this out of the box

Manifest-Version: 1.0
Class-Path:  ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.
 jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.ja
 r ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./version
 info.jar
Main-Class: RTF2PDF

With my extension and supporting library I now have

Manifest-Version: 1.0
Class-Path:  ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.
 jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.ja
 r ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./version
 info.jar D:/Work/idauto/idfontextension.zip D:/Work/idauto/LinearBarCode.jar
Main-Class: RTF2PDF 

Update

Manifest-Version: 1.0
Class-Path:  ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.
 jar ./jewt4.jar ./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.ja
 r ./xmlparserv2.jar ./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./version
 info.jar ./idfontextension.zip ./LinearBarCode.jar
Main-Class: RTF2PDF

as my file entry ... works great!

When I now hit preview for any of the formats supported my extension is pulled in without the need for all that merging.
If you are now wondering how I opened the RTF2PDF.jar file, I used winzip or zip or you could use the java tools.
Thanks for the tip Junichi. Big prizes for anyone guessing what my latest extension project has been. OK there's no prize but you'll get a mention :0)

At ODTUG a few weeks back I saw Marc Sewtz's excellent presentation on the integration between Oracle Application Express (APEX) and BI Publisher. Lots of good examples and tips.

He mentioned a recent white paper on integrating Publisher with APEX via the Publisher web services by Jason Straub. Delighted to see this. Trying to pull it all together in one place, here is a collection of great references for APEX developers wanting to integrate with BI Publisher.

PDF Printing in APEX
from the APEX OTN pages

Apex Integration I
and
Apex Integration II
by Tim

Calling BI Publisher report from APEX via URLs
by Dimitri

BI Publisher Web Services and APEX
by Venkat

Call BI Publisher Web Services from APEX
by Tyler

and the most recent addition to the series ...

Integrating Oracle Application Express with BI Publisher
white paper by Jason

A fine example of evolutionary collaboration.

Thank you all.

Rainer Willems from our German office has come up with a way to allow you get formulas into your Excel outputs from an RTF template, its a neat idea. It needs some thought up front about where data is going to be rendered in the Excel output, but thats easy enough to find out, just run the template to Excel et voila.
You can check out the details here

http://www.oracle.com/global/de/community/bip/tipps/rtf_to_excel/index_en.html

and for German readers

http://www.oracle.com/global/de/community/bip/tipps/rtf_to_excel/index.html

Just one drawback, that Rainer mentions, once down this path with an RTF template its forever a 'Excel' output template. Thats unless you are on 10.1.3.4 Standalone or above when your template becomes self aware of what its being run for e.g. PDF, RTF or Excel. Then you can add some logic e.g if output != 'Excel' then hide this section of the template end if. More on that another day.

Been reminiscing about music from my youth, a boy of the eighties there was so much going on back then. I was into some weird stuff Gene Loves Jezebel, the Boot Hill Foottappers, the list goes on, I was also into the Sisters of Mercy where the title for this post comes from. Im nothing if not consistent with my tenuous blog titles.

Quick question on an internal forum asking how to show just the first and last record in a group. XPATH to the rescue there are a couple of functions we can use to fetch just the first and last record. Lets assume we are looping over EMPLOYEES.

<?for-each:EMPLOYEES?>

this will fetch all records. With the addition of the XPATH expression

<?for-each:EMPLOYEES[position()='1' or position()=last()]?>

The XPATH standard allows us to create a filter, if or boolean expression to limit the number of rows to be rendered.

position() - returns the current record being process. So position() = 1 will only return the first record

last() - returns the last record pointer. So position()=last() will give us the last record only.

Combining them together with an 'or' statement ensures we only get the first and last record in our group. Done!

Have you ever faced a situation where your dates are just gone wild? For example, you could swear that the date in your XML data is 2009-03-01 (March 1st, 2009), but it shows up on your report as 28-FEB-2009!

I have seen several emails the last few months come to the help mailing list with date formatting related questions, that I thought this might be a worthwhile topic for a blog article.

The cause of this, what some call “odd” behavior, is a combination of four things:

1. How dates are stored in the XML data
2. Whether and how you format dates in the RTF template
3. The timezone that you run the report
4. And this thing called Coordinated Universal Time known as UTC
   

If you find the acronym UTC confusing, it’s because it’s a backronym, not an acronym! Long story short, blame it on the French (or the Americans, depending on which side of the Atlantic you are!) You can read all about it here: http://en.wikipedia.org/wiki/Coordinated_Universal_Time.

So…, here is how these four things together can ruin your “date”!

1. At run time, BI Publisher server converts dates that you have applied a format mask in your RTF template to the timezone that the report is destined for (the report timezone).
2. When you apply a date format mask to a field in your RTF template, BI Publisher expects the incoming dates from the XML data to be in canonical format. That is, YYYY-MM-DDThh:mm:ss+HH:MM, a standard way to represent date and time in reference to UTC. The +HH:MM is the positive or negative difference of the timezone where the data originates from UTC/GMT.
3. If the time component and UTC offset is omitted (i.e. the XML date is in the format YYYY-MM-DD), BI Publisher interprets the date as YYYY-MM-DDT00:00:00-00:00 (i.e. midnight GMT).

If you are responsible for generating the data for the report, you don’t need to worry about converting your dates to canonical format. BI publisher will do that automatically for you.

For example, when you issue the query “Select SYSDATE from DUAL” from BI Publisher, it generates the following XML data set:

<ROWSET>
<ROW>
<SYSDATE>2009-06-03T18:44:32.000-07:00</SYSDATE>
</ROW>
</ROWSET>

Of course no one likes to see dates on their report in that format!

Provided, you want dates and time to be converted to the appropriate time zone the report is destined for, the best and most straightforward approach to address that is to apply a format mask to the date field in your RTF template and let BI Publisher take care of the rest. 

For information on how and the types of format masks that you can apply to a date field refer to Number and Date Formatting in the product documentation: http://download.oracle.com/docs/cd/E12844_01/doc/bip.1013/e12187/T421739T481157.htm#4535403

You can also view the Formatting Dates demo available in our Demonstration Library on our OTN page. The URL to out demonstration library is:
http://www.oracle.com/technology/products/xml-publisher/demoshelf/shelf.html

Keeping in mind that if you apply a format mask to a date field in your RTF template, it will result in the conversion of dates and times depending on the report timezone, it’s quite normal for a date to roll back or forward by 1 when your run the report!

EXAMPLE 1
If you use <?format-date:SYSDATE;'LONG_TIME'?> in your RTF template, with the following data set:

<ROWSET>
<ROW>
<SYSDATE>2009-06-03T18:44:32.000-07:00</SYSDATE>
</ROW>
</ROWSET>

It will result in these dates displayed on the report:
------------------------------------------------------------------------------------------------------------------------
Report Timezone                                                                             SYSDATE
------------------------------------------------------------------------------------------------------------------------
[GMT – 07:00] – America/Los _Angeles                                  Wednesday, June 3, 2009 6:44 PM
[GMT + 08:00] – Asia/Singapore                                              Thursday, June 4, 2009 9:44 AM
------------------------------------------------------------------------------------------------------------------------

Think about it, in this example, the value of SYSDATE is the time I am writing this blog article in California, Wednesday 18:44:32 (6:44 PM) PDT on June 3rd. My friend Babar in Singapore, 15 hrs ahead of me, is having his third cup of coffee at 9:44 AM, Thursday morning of Jun 4th! Nothing “odd” about that!

EXAMPLE 2
If instead the XML date in the data is in the format YYYY-MM-DD, as in the following data set,

<ROWSET>
<ROW>
<SYSDATE>2009-06-03</SYSDATE>
</ROW
</ROWSET>

Running the report using the same RTF template as above, it will result in these dates displayed on the report:
-----------------------------------------------------------------------------------------------------------------------
Report Timezone                                                                             SYSDATE
------------------------------------------------------------------------------------------------------------------------
[GMT – 07:00] – America/Los _Angeles                                  Tuesday, June 2, 2009 5:00 PM
[GMT + 08:00] – Asia/Singapore                                             Wednesday, June 3, 2009 8:00 AM
------------------------------------------------------------------------------------------------------------------------

The same reasoning applies here. BI Publisher interprets the value of SYSDATE to represent Wednesday, June 3, 12 AM GMT. For me, 7 hrs behind during daylight savings time, it’s still June 2, 5:00 PM. And my friend Babar in Singapore has just finished his first cup of coffee on June 3rd!

What if you don’t want dates to be converted based on the report timezone?

One solution to that is to extract dates in the format that you want them to appear on your report and don’t apply a format mask to date fields in the RTF template.  Chances are that BI Publisher will ignore the format mask anyway, unless the format that you extract the dates in matches the date part of the canonical format that dates are expected to be in (i.e., YYYY-MM-DD).

To extract dates in a specific format, you can use a TO_CHAR function in the query.  For example, "Select TO_CHAR(SYSDATE,’DD/MM/YYYY’) as SYS_DATE from DUAL" will generate the following data set:

<ROWSET>
<ROW>
<SYS_DATE>03/06/2009</SYS_DATE>
</ROW>
</ROWSET>

The other option is to extract dates in the format required by BI Publisher, and specify a timezone in the format mask for the conversion to override the default, which is the report timezone. If you specify a timezone with the same UTC offset as the dates in the XML data, no conversion takes place.

For example, <?format-date:SYSDATE;'LONG';’America/Los_Angeles’?> with the data in EXAMPLE 1, will always result in Wednesday, June 3, 2009 displayed on the report.

On the other hand, <?format-date:SYSDATE;'LONG';’UTC’?> with the data in EXAMPLE 2, will also result in Wednesday, June 3, 2009 displayed on the report.

Conclusion –
If you’d like your “dates” to behave, be nice to them and play by the rules!

I recently spent some time putting together a progression of data templates (DT) for a customer demo. Its a series of data templates that move you from the simple to the more complex, two query, before fetch trigger, group filter, summarizing all singing all dancing data template. I have heard requests this week for a three'er - looking at that. For now, satisfy yourselves with what I have.

Yep, still no builder but the 11g data builder is so cool it makes my geeky mouth water. Can not post images for fear of product management reprisal and bats up night dresses in the small hours (go Google it :0)
In spite of the lack of a DT builder I found it quite useful and so much faster to start off with the queries, get them working and returning data based on the parameters and to then move to the grouping, group filters (please dont use em) and summary columns. Rather than trying to write the whole this in one hit and then spend forever debugging.

During the demo we discussed the error messages and means to debug that BIP offers. Some of the layout errors are still mystifying at times '1=1' and '9<10' come to mind as true but very annoying error messages. You'll be pleased to here that turning on the debug for the data templates provides a mass of information about what is going on and of course what did not happen and why.

If you're interested you can get the progression here. Just unpack them under a directory under the BIP reports home directory. Fire up BIP and they will be available. They all run against the demo HR schema in an Oracle db. There is also a sql script for the supporting plsql package for the triggers.
Finally, just because its amusing and I wanted to share the mirth, check out this funny developer 'joke' from Bex on his blog, made me chortle!

No nota post about some prima donna football (soccer) player that does not want to muss his hair up nor loose more precious brain cells putting his head to the ball. Far more exciting than that, its more templating fun.
I have been exchanging a few mails with Tony this morning. How can one conditionally call a header into a report? is the crux of his question.

The answer, is relatively simple, for a change. You can use a sub template (I have written about those elsewhere in this blog) to store the header (and footer) layouts you want to possibly call.
They take the format

<?template:Header1?>

... header 1 layout table, etc

<?end template Header1?>


<?template:Header2?>

... header 2 layout table, etc

<?end template Header2?>

<?template:Footer1?>

... footer 1 layout table, etc

<?end template Footer1?>

and so on, just use an RTF template to make life simple on the layout.
You need to make this sub template accessible either via a URL or directory path, from the root directory - no relative path support at the moment.

In the main template you need to register the sub template you want to use with an import statement:

<?import:http://127.0.0.1:9704/HeaderFooter.rtf?>

Now you can call the various header and footer templates you have in the sub template. In the Word header you can not use formfields so you have to write the clear text such as:

<?if:.//DEPARTMENT_NAME=’Accounting’?>
 <?call@inlines:Header?>
  <?with-param:ReportName;string('Accounting Report’)?> 
 <?end call?>
<?end if?>

you can use a choose: statement too, nested 'if' will not work thou.
I built some samples, get em here. You'll notice the parameter passing in the template call, think I have covered that elsewhere too.

Spent some time over the last few days helping Bryan out on a project for a customer, I say helped but really just pushed him in the right direction, he joined the rather disparate dots and came up with the solution. The customer needed regular report outputs ie PDF, RTF, etc but they also needed an XML transformation. We tend to forget sometimes that BIP is very capable of transforming from one XML format to another format. After all, at its heart is an XSLT engine and what do they normally do? Transform XML of course!

So Bryan had the query and the ROWSET/ROW XML but needed to get to the customers format, they possessed an XML schema (XSD) for their format, he just needed the XSL stylesheet, simple right? Not quite so, without either investing in really learning XSL or purchasing an XSL mapper.

I had been looking at the BPEL engine recently and remembered that some of their tutorials had me use an XSL mapper inside JDeveloper. Just needed to fire it up and find it, I have been using 10.1.3.x JDev for my projects and searching through their documentation I found what I needed.

Its pretty neat and provides just about everything you might need in terms of mapping, it generates an XSL file for you and above all ... it's free!

If you want to try it out, you'll need the source XSD and target XSD and thereby hangs a tail. How to generate the XSD? they are not very nice to hand code and again you often need to invest in a tool to help build it. There are some options, a little known feature of the data engine in BIP is an API that will spit out a schema file based on a data template ... ahhh but you need to build out the data template first ... dang! Another option, Bryan found this little gem, is a free XML to XSD service out on the intertubes - http://www.flame-ware.com/products/xml-2-xsd/. Give it an XML and it will pass back an XSD. The XSD is only going to be as good as your input XML and you might need to tinker with it but for the purposes of mapping it should be more than sufficient. So, now you are armed with your two XSDs you can get the mapper fired up.

You just need to create an application and project in JDev then add a new XSL Map (New -> Integration Tier -> XML.)

You will then be prompted for the source and target XSDs

Use the Import Schema File ... button to bring your schemas into the project. The mapper UI will then pop up and you can start dragging and dropping source elements/attributes onto the target schema. You can 'run' the conversion at any time to check you are getting the desired result. Once happy, click the 'Source' tab, at the bottom of the mapping window to get to the XSL stylesheet.
Now its just a case of loading the XSL as a new template and setting the type to ensure BIP is expected to generate XML and you are off to the races as they say back in dear ol'Blighty.

This is Klaus from the BI Publisher team with a write-up on a recent customer request.

I have been asked on how to implement a chart that shows the top 5 customers (district, countries…) in a pie chart and then have a special slice that accumulates all the remaining customers.

I think this is very useful – pie charts with 20 or more slices are hard to read. If you just limit the pie chart to the top 5 customers, then the percentage value shows the percentage of a slice as a percentage of the top 5 and not of the complete data set.

A good approach is to keep the remaining customers in the chart in a summarized form.
I have seen these summarized columns called “Rest of World” in a chart showing countries.

You can download the demo files here. Please make sure that you copy the subtemplate.xsl to C:\test or change the import statement in the first form field to the correct location.

The solution requires XSL coding and the use of a sub template. These techniques are useful solve many complicated reporting problems. Subtemplates are a good technique to keep larger code blocks outside of the word document - we plan to add more support for sub templates in a coming release.

We are working for this example with the Paint Exec sample data that is used in the sample report /Executive/Revenue by Region. The data has the following form.

<ROWSET>
<ROW>
<Markets.District>CHICAGO DISTRICT</Markets.District>
<Products.Brand>Enterprise</Products.Brand>
<Measures.Dollars>1555548.0</Measures.Dollars>
</ROW>
</ROWSET>

We start with the Pie chart that is generated by the template builder for Word:

Top 5 Chart

Let’s first limit the pie chart to show only 5 slices.

1) First we fix the number of data rows for BI Beans to 5 – instead of the complete data set.

chart:
<Graph depthAngle="50" seriesEffect="SE_AUTO_GRADIENT" graphType="PIE">
<LegendArea visible="true" />
<LocalGridData colCount="1" rowCount="5">

2) We change from ordering the labels by Market District to ordering them by measure.Dollars per district.

<RowLabels>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" data-type="number" order="descending"/>

3) We need to select the first 5 elements – this is not mandatory for now but will necessary for adding the rest of world. Note: XSL requires us to use the escape sequence &lt; for “,”.

<xsl:if test="position()&lt;6">
<Label><xsl:value-of select="current-group()/Markets.District" /></Label>
</xsl:if>
</xsl:for-each-group>
</RowLabels>
<DataValues>

4) We sort the data elements by revenue per district.

<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" data-type="number" order="descending"/>

5) We select only the first 5 elements – this is not mandatory for now but will necessary for the next step

<xsl:if test="position()&lt;6">
<RowData>
<Cell><xsl:value-of select="sum(current-group()/Measures.Dollars)" /></Cell>
</RowData>
</xsl:if>
</xsl:for-each-group>
</DataValues>

The resulting graph looks less cluttered – but it does not show percentages of the total:

Rest of World
Now we need to calculate the remaining values.

6) Any complicated calculation should better be performed in a separate XSL stylesheet. We write a function in XSL called “others” that calculates the total of the remaing members.

<?xml version="1.0" encoding='utf-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" version="2.0">
<xsl:output method="xml" encoding="UTF-8"/>

<xsl:template name="others">
<xsl:variable name="list">
<xsl:for-each-group select="/ROWSET/ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" order="descending" data-type="number"/>
<v><xsl:value-of select="sum(current-group()/Measures.Dollars)"/></v>
</xsl:for-each-group>
</xsl:variable>
<xsl:value-of select="sum($list/v[position()&gt;5])"/>
</xsl:template>
</xsl:stylesheet>

This template groups all elements by “Market.District” and sort the resulting values by the sum of “Measure.Dollars” per District. The result is stored in the variable v.

Then we sum all elements in the variable with a position greater than 5 with the expression:

<xsl:value-of select="sum($list/v[position()&gt;5])"/>

The escape sequence “&gt;” means greater then or “>”. We save this XSL-stylesheet with charater set UTF-8 - using for example notepad – to the location:

C:/test/subtemplate.xsl

7) We need import this sub template from the RTF template. For testing we access the file from the file system – for the production implementation to store in on a web server and access it from there.

We add the following statement to the beginning of the document (not the chart!) to import the sub template:
<xsl:import href="file:///C:/test/subtemplate.xsl"/>

8) We increase the row Count from 5 to 6 – replacing step 1- for the additional “all others” slice.

<LocalGridData colCount="1" rowCount="6">

(9) We need to add a label for the summary of the remaining district

<RowLabels>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)" data-type="number" order="descending"/>
<xsl:if test="position()&lt;6">
<Label><xsl:value-of select="current-group()/Markets.District" /></Label>
</xsl:if>
</xsl:for-each-group>
<Label>ALL OTHER'S</Label>
</RowLabels>

(10) Finally we need to add another data row with the sum of all other districts by calling the function “others” defined in (6).

<RowData><Cell><xsl:call-template name="others"/></Cell></RowData>
</DataValues>
</LocalGridData>

When we run the chart we see the chart with a new entry and the correct percentages:

Below is the final code for the chart above.

chart:
<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="PIE">
<LegendArea visible="true" />
<LocalGridData colCount="1" rowCount="6">
<RowLabels>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)"
data-type="number" order="descending"/>
<xsl:if test="position()&lt;6">
<Label><xsl:value-of select="current-group()/Markets.District"/></Label>
</xsl:if>
</xsl:for-each-group>
<Label>ALL OTHER'S</Label>
</RowLabels>
<DataValues>
<xsl:for-each-group select=".//ROW" group-by="Markets.District">
<xsl:sort select="sum(current-group()/Measures.Dollars)"
data-type="number" order="descending"/>
<xsl:if test="position()&lt;6">
<RowData>
<Cell><xsl:value-of select="sum(current-group()/Measures.Dollars)"/></Cell>
</RowData>
</xsl:if>
</xsl:for-each-group>
<RowData><Cell><xsl:call-template name="others"/></Cell></RowData>
</DataValues></LocalGridData></Graph>

I hope this is helpful,

Klaus Fabian
Senior Development Manager, BI Publisher;

Yesterday I spent most of an entry talking about weather and a little about actual work. Today, no weather, well may a little - its danged raining here in Colorado, again! Great Memorial Day weekend, it's like living back on ol'Blighty. Hey there's a public holiday coming up, don't bother to check the forecast, it will be raining!

Back to the Crystal conversion, the issue we had in the layout template is not a consequence of the Crystal report itself but more of the data structure underneath it. The Oklahoma folks have a system to collect information about traffic incidents and claims, they use Oracle Forms to collect that info, the form has been 'bent' a little to allow their users to collect all the information in a single place. The form is not master detail per se ie section for the claim info and a child section for the drive, owner and responsible party, typically the insurance company. Its all stored at the same level so they have multiple lines with a line for each with a flag for the owner, driver and responsible party. Thats fine but the data set returned by the BIP (and Crystal) query results in a denormalized data set so they have something like.

<rowset>
<row>
<claim_id>1234567</claim_id>
<name>Jo Bloggs</name>
<address> blah blah blah</address>
<driver_flag>Y</driver_flag>
...

<row>
<claim_id>1234567</claim_id>
<name>Big Insurance Co</name>
</row></address><address> blah blah blah</address>
<responsible_party>Y</responsible_party>
...

and so on, potentially as many as four records for each claim. The issue comes when we need to show this information on the form. There are no 'sections' for each party, its all shown on an easy to read form. So we need to be able to distinguish between the driver and responsible party records under a given CLAIM_ID.

Those with some experience will have jumped on a grouping, . This gives you the means of discerning between claims (there maybe multiple claims in a batch) but we then get into a 'tricksy hobbitises' situation with the rest of the data. We need to show the driver and responsible party data mixed in the form, but as you can see above we have 'NAME' and 'ADDRESS' repeated as elements, if we had had nice prefixes to the elements life would have been easier. But life's not easy :0)

So, after a little head scratching I came up with a way to distinguish between the two data sets - variables. Now in XSL variables are not like regular variables, they are not updatable, more like a constant in that respect. But they do have a great quality we can use, they can contain not only a single value but a complete part of the XML tree. I can load chunks of data into them to create the separate records I need ie a driver variable tree and a responsible party variable tree. I can then reference the values in those variables just by prefixing the variable name so that $driver.NAME and $resp_party.NAME are now different values. Very cunning!

At the top of my template I have

<?for-each-group:ROW;./C_CLAIM_NO?> - just a group by claim number
<?for-each:current-group()?> - loop thru the rest of the data
<?variable@context:driver;* [DRIVER='Y']?> - load the record for the driver
<?variable@context:resp_party;*[RESPONSIBLE_PARTY=’Y’]?> - load the record for the responsible party
<?end for-each?>
...
...
<?$driver/NAME?> - reference drivers name
<?$resp_party/NAME?> - reference responsible party name
<?end for-each-group?>

To explain the variable declaration, the '*' is saying assign everything in the current record but only if DRIVER = Y ie the first record in the loop. The second variable is doing the same but only where RESPONSIBLE_PARTY = 'Y'. Now I can reference anything I like anywhere in the document by using the $variable_name/ prefix.

Its some hoop jumping to get it right and to be honest I would recommend using a data template to get the data into a nice friendly structure negating the need for the variables. But the exercise was to just migrate the report from Crystal, Crystal was not grouping the data it had to use the sneaky 'whileprocessingrecords' trick. If they have tricks then BIP is entitled to some too. If you're interested I have the template and final output available here.

Spent a great couple of days in Oklahoma City last week with the good folks of the Oklahoma Department of transport. When I was asked to go over there a few months back, I agreed on the condition that I got to see a tornado. I came sooo close its not even funny.
I arrived to weather forecasts of huge storms rolling over the city that night, being a weather geek there was just a little excitement brewing. There had been a tornado out in countryside NW of the city earlier on in the day. There were some great cloud formations (apologies, the iPhone camera does not do it justice)

Had some dinner and then decided to take a walk in the sweltering 80 degree heat at about 10pm. Got downstairs just in time to catch one of the best lightening storms I have ever seen, not much hitting the ground but at times it was like daylight with about 5 strikes per second (according to the excited weather dude on the TV) Then came the rain, boy did it rain, after about 10mins there was a river rushing down the road ... all very exciting. Then the tornado sirens started, looked about, everyone else was still watching the storm ... fair enough, no one else is bothered I'll stay out. Rather stupidly, I then rang my wife to tell her about the storm and the sirens and how cool it all was. 'Get back inside, stay away from the windows and get in the bath' was her pleading recommendation. Hmmmm ... not sure about the bath, Im sure the hotel would have us down in the basement if they were that worried, watched as the storm moved over us and then back to the room for some serious weather channel watching. Im convinced Im going to end up in a home when Im older, sitting in front of the weather channel and shouting incoherently at the talking heads. The local channels were more enjoyable anyway, with the weather dudes searching for 'hooks' and 'rotations' on the leading edge of the storm. they all had storm chasers out in the dark around town reporting that they might have just seen a 'wall cloud' and the 'base appeared to be lowering but it was dark so they were not quite sure' All good stuff for a weather geek, took a snap of the TV for posterity, I was under some of that purple you can see.

Thankfully no touch downs in built up areas, a few roofs blown off but no serious damage and no one injured. It was a good night!

Why was I in Oklahoma? To give the DOT folks some experience of BIP, nothing hands on but we dug into the tool and walked around the standalone server. The DOT folks are big Crystal users so we decided to have a go at taking one of their reports and migrating it. There is a pretty nice paper on this on the BIP web site.
Its a completely manual process but we worked through the paper and I added some tweaks here and there to hopefully seed things up a bit and we found some gotchas along the way that I wanted to share.

The report we took a look at seemed pretty innocuous on first sight, I was surprised, I thought they would give me a tough one. I should have been more wary of Corbi's wry smile as she opened the report :0)

There was only a single query in the report which we copied and pasted into a BIP report, created LOVs and parameters. So far so good, the report ran and generated data. We eventually noticed a problem, here's a gotcha for ya. We were getting a repeating element in the output. i.e. NAME. The problem was that in the Crystal Report the query was selecting

TABLE1.NAME
TABLE2.NAME

Crystal discerns between the two as it keeps the table name associated with the column. Once BIP renders the data it ignores the table name so we get repeated element names. Not a huge problem, but when you load the data into the Template Builder it only shows the first instance on the element. An easy but somewhat annoying work around would be to provide aliases to the columns to make the element names distinct. We made a change to two columns and moved on.

Next the layout, the paper talks about building the layout, I wanted a to take some advantage of the Crystal layout. We needed an RTF output or at least a .doc format that we could re-save as RTF. Crystal offers an export functionality, there are two RTF options, be sure to use the 'editable' RTF option. Once it generates you will get the layout for the layout, its then a case of replacing data with fields using the template builder and you're off to the races.

Here's where things got a little tough, the Crystal report was using a subtle command 'while reading records' to get past a small problem in the schema ... how I got around that, I'll save for tomorrow.

BIP Extensions? Regular readers may remember I wrote about building BIP extensions a while back. Its a means of adding functionality to your templates by making an external call to a java class. Its very very powerful and completely unsupported right now, as I warned before - do not log an SR if you get into trouble.
When I last wrote about them I got a few comments and Frank Menne from HSM in Germany went ahead and built one. Im only bringing them up again because I had cause to use one in a recent POC. In my orignal post I showed how you could pass a value thru to the extension for it to return some processed text. This POC required some more sophisticated processing. Take the following data:

<RECORD>

 <CNT>1</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>2</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>3</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>4</CNT>

 <TYPE>Individual</TYPE>

</RECORD>

<RECORD>

 <CNT>5</CNT>

 <TYPE>In Bulk</TYPE>

</RECORD>

<RECORD>

 <CNT>6</CNT>

 <TYPE>Individual</TYPE>

</RECORD>

public class certExtension {
    public certExtension() {
    }

    public static final String NumDesc(NodeList nodes, String eleName)
               {
                    String retStr ="";                   
                    NodeList nl;
                    Element pkg;
                    Element element;
                   String outp = "";
                    for (int i = 0; i < nodes.getLength(); i++) {
                           element = (Element) nodes.item(i);
                           nl = element.getElementsByTagName(eleName);
                           pkg = (Element) nl.item(0);
                           Node pd = pkg.getFirstChild();
                           CharacterData cd = (CharacterData) pd;
                           ...
                           }
                        }
                      return(retStr);
                 }

I wrote about a gotcha in the Oracle Reports to BIP conversion libraries last week. Not long after that, 'friend of BIP', IceMan aka Ike Wiggins, self confessed 'lazy man', read the article to realize this is not a bad trait :0) dropped me a not
e to say that he had developed a tool to aid in the process of converting a batch of Oracle Reports.

So, if you are starting to convert your 600 Oracle Reports and find the whole thing a little daunting. Never fear, IceMan is here. Get on over to Ike's blog entry, check out his dulcet tones on the video he recorded and then down load the converter client and you'll be 'off to the races' in no time.

While you are there, check out the BI Publisher IDE that Ike has developed. If you are working with the BIP APIs or want a one click, load test and generate tool for BIP, Ike has just what you need.

Don't panic, we're not turning into a BIEE blog, I have been doing dome work with it lately and could not find the following nugget out there. Colleagues on an internal list helped out with my request - it was a simple one but I wanted to record it for posterity.

As many of you have seen you can generate totals and sub-totals in your BIP templates quite easily, you can also embed fields into the total label text.

To get the customers name in the total text, its just a case of copy and pasting the customer name field into the table cell.

I needed to do the same in a BIEE pivot table. Not quite so obvious but doable, once I got some help from my friends, ooooh I get by with a little help from my friends ... danged Guitar Hero! I dont even like the Beatles, for a Brit, sacrilege I know.

Building out the pivot you can see that if you set a total for the group you get XXXXX Total.

the dialog to customize the total string is tucked away a little. Click the total icon again and select Format Labels

This will pop the dialog

You can now enter the text you want, the '@' character will be replaced at runtime with the actual group value.

The pivot now renders with the required text.

A long while back I posted an entry on linking to a report via a URL. All was good but it only worked if you either:

1. Were hooking BIP up to SSO with your calling application or
2. You placed the report in the Guest folder which was unsecured.

Now, back in an earlier release the dev guys sneaked a feature out that I was vaguely aware of and thought I knew but it took the almighty Bryan Wise to remind me. You can now pass the username and password on the URL to avoid the two cases above and open up other possibilities.

You can just add the following parameters to your URL

&id;=XXXXXX&passwd;=YYYYYY

For example:

http://127.0.0.1:9704/xmlpserver/HR Manager/W2/W2.xdo?id=Administrator&passwd;=Administrator

What about security? Well 'developer beware'!, I wanted some cool Latin phrase that no one would understand but the translators are a little funky in my opinion. Never the less, its a feature, its up to you if you want to use it, or maybe your security folks. So, now you know and I now have somewhere to point folks who ask me about it :)

Heading off to Collaborate next week. Very glad to see that OAUG, Quest and IOUG have created a joint scheduling tool.

I can now search across all three tracks and see that there are over 50 sessions that include Publisher in some way or form. A nice range of intro to advanced topics covering the various deployments of Publisher in EBS, JDE and stand alone. I plan to attend as many as my schedule will allow.

Here are a few being delivered by Oracle folks -- oddly all on Wednesday May 6. That must be our designated day.

Please also drop by the "Oracle Business Intelligence Enterprise Edition" demo pod (#9) in the Oracle Booth (#4780) in the Exhibit Hall if you have any questions or want to see a demo of BI Publisher Enterprise or BI EE. You may also be able to get application specific demos at the following demo pods:

Oracle E-Business Suite Tools and Technology (15)
JD Edwards EnterpriseOne Tools (38)
PeopleSoft Enterprise PeopleTools (1)
JD Edwards World Applications (40)

Stop by the Oracle Reception Booth after your visit to the demo pods. I'm told that there is a "extremely retro-cool" thank you gift.

If you have been suffering with Template Builder not installing correctly, or like me, you have installed and uninstalled so many different versions you might want to clean house. Once you have cleaned up things should go more smoothly.

Thanks to Junichi, who is the Template Builder lead developer, for these clean up steps.

1. Uninstall BIP desktop from control->Add or remove programs.
2. Open explorer and go to "C:\WINDOWS\assembly".
3. Check if there are assemblies which start with "TB" If present, remove them all.
4. Open the MS Word startup directory and check there are no files there. The directory is normally the following.

C:\Documents and Settings\<user name><Application Data\Microsoft\Word\STARTUP

5. Open MS Word and check that you don't see the BIP tool bar.
-> If you see it, please move Normal.dot to another directory and try again.

6. Please check that the OS user you use has an administrator privilege on the PC, this is really important.
7. Please go to Control Panel -> Add or Remove programs and check if the followings have been installed.
Shared Add-in Extensibility Update for Microsoft .NET Framework 2.0 (KB908002)
Shared Add-in Support Update for Microsoft .NET Framework 2.0 (KB908002)

8. Install BIP Desktop again

Step 5 did it for me!

After following the 8 step plan, Im now clean and sober, sorry that's the other plan Im following. Should have said, your PC/laptop will be clean and fresh with a new working BIP install.

In recent weeks I have been working with several customers on the migration utility. As those that have used will know, its not perfect by any means. But it does get you down the path of migration a fair way.

I spent half an hour scratch my hairless head (Ive run out of hair to pull out so resort to scratching now :) this morning. It was over a variable population error, the value was too large for the variable e.g. squeezing 'Larkspur' into a varchar(2) will not go! What was meant to happen was a code e.g. LK was supposed to go into the variable.

As you may know, BIP supports LOVs for parameters, you have the option of showing a user value but passing a code to the query. It seems that Oracle Reports works things the other way round i.e. pass the first and show the second ... grrrrr!

A quick look see at Oracle Reports reveals that yep, they use code, user_value whereas BIP uses user_vale, code ... dang!
A look at the XML rendition of the LOV shows that we might be able to get the converter to be a little more intelligent.

<istOfValues restrictToList="yes">

 <selectStatement hideFirstColumn="no">

  <![CDATA[select last_name, employee_id from employees]]>

 </selectStatement>

</listOfValues>

If nothing else, if there are two columns being retrieved then in the converter we need to swap them.

For now, its a manual job to switch them. You now know that rather then become bald, like me, check those LOV values and swap em if necessary ... now where is that bottle of Rogaine?

I'm happy to announce the 10.1.3.4.1 release of BI Publisher.

This release rolls up bug fixes and enhancements since 10.1.3.4.0 to include the following:

• Support for Oracle WebLogic Server 10.3
• Extended Support for Single Sign-On Providers
• Automatic Refresh of LDAP Cache
• Support for Siebel CRM Security
• Support for Secure File Transfer Protocol (SFTP) for Burst Reports
• Data Model Enhancements
• Web Service Data Set Type Enhancement: Specify Path to Data in SOAP Response
• SQL Query Data Set New Property: Use Default Schema Only
• Support for JNDI for Scheduler Connections
• Support for Expressions to Calculate Date Parameters
• RTF Template Enhancements
• Get List of BI Publisher Configuration Settings
• Enable Debug Mode for an RTF Template
• Number to Word Conversion in Report Output
• Remove Logos and Links from the BI Publisher Header
• Disable Access to Guest Page
• Updates to the BI Publisher Web Services

For more details on this release please see:

BI Publisher New Features Guide

Release Notes

Certification Information

Note: something is wrong with the doc pages at the time of posting. For the 3 docs above, if you see them dated January 2009 they are still the old 10.1.3.4.0 versions. The 10.1.3.4.1 versions are dated April 2009. I am working with the doc team to get this resolved.

BI Publisher download page

-- Mike

Apologies, Im getting more and more sporadic ... will try and do better!

Today I wanted to highlight some work Pieter has been doing on our behlf in support. He has a very useful note in metalink on debugging BIP issues under EBS. Check out

Note 364547.1 Troubleshooting Oracle XML Publisher For The Oracle E- Business Suite

Recently an enhancement has been made to the current capabilities, which Pieter will include into the note in due course but here's the details for now.

Currently, to request for XDO debug log, you have to create a file xdodebug.cfg, and copy it in to <JRE>/lib directory. This is sometimes very difficult to do this step because:
1) When there are multiple JDK/JRE installed in a system, it is sometime
difficult to know which one is being used.
2) The <JRE>/lib directory is locked, only writable by top admin person

To alleviate this issue the enhancement provides the ability to turn on debugging for a specific RTF template.

There is a new XDO propety that can be embedded into the template:
* Name: xdo-debug-level
* Default: OFF
* Allowable values: STATEMENT, ERROR, OFF

When it is set to any value but "OFF", it will turn in debug mode.
The debug log directory will be in
1) <system-temp-dir>/xdodebug
2) <Java java.io.tmpdir>/xdodebug (if system-temp-dir is not defined)

- The following comment added to the XSL template or FO file will also turn on XDO debug mode for that thread.

<!-- xdo-debug-level="STATEMENT" -->

The comment must reside within the first 512 bytes in the file ie get it into the top of the document.
Usage:
<?xdo-debug-level:"STATEMENT"?>
<?xdo-debug-level:"ERROR"?>
<?xdo-debug-level:"OFF"?>

The value of debug_level is not case sensitive,
and
<?xdo-debug-level:"statement"?> are the same.

should make life a whole lot easier for you EBS folks. Its being released to the 5.6.3 code line so if you want it you need to get to that level. Not available quite yet, just being tested but watch out on Metalink for the patch against # 8359991.

Ever wondered, in an idle moment, how Publisher got started? Maybe not, but if you head on over to Ashish's shiny new Oracle blog you'll find out. Ashish recently moved back to India and then to a new (or old) team in Oracle Financials. Those of you that have met me have probably heard, several times, where my back ground lies. Ashish's is very similar, I might have a few years on him, inside Oracle and out and he definitely has more hair than I do but we go back a long way, to a small project called 'Financials Reporting Strategy.'

We both worked on a prototype with the Oracle Reports team to provide some means for the 'average' user to customize a report layout. I still have it on a drive and go back and fire it up occasionally and reminisce about the old days. Soon after that we were helping to build Publisher, the rest they say is history.

Get on over to Ashish's blog read it then bookmark it. Ashish is on the other side of the fence now, he's a customer, albeit an internal one, but will be sure to have golden nuggets of info to share and maybe even a glimpse of Fusion or three.

A new patch for 10.1.3.4 hit the streets on Friday afternoon, seeing as I was still fighting my way across Los Angeles traffic to try and get to a flight that subsequently got canceled, I missed it until this morning.
Its packed full of fixes and a few enhancements, a couple of notables. 'Number to Words', a function most commonly sought after when it comes to check printing but I have seen other requirements.

The syntax of the to_check_number function is:

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

The following table explains the attributes.

Examples :

<?xdofx:to_check_number(12345.67, 2)?>
Twelve thousand three hundred forty-five and 67/100

<?xdofx:to_check_number(12345.67, 'USD')?>
Twelve thousand three hundred forty-five and 67/100

<?xdofx:to_check_number(12345, 'JPY', 'CASE_UPPER')?>
TWELVE THOUSAND THREE HUNDRED FORTY-FIVE

<?xdofx:to_check_number(12345.67, 'EUR', 'CASE_LOWER', 'DECIMAL_STYLE_WORDS')?>
twelve thousand three hundred forty-five and sixty-seven

The other larger enhancement is around single sign on - its been the bane of my life recently. Life has gotten just a little easier with this new functionality. You are no longer bound to Oracle's SSO. The patch delivers support for the following SSO solutions:

• Oracle Single Sign-On


• Oracle Access Manager


• CA SiteMinder

Along with these two there are a total of 31 bug fixes and ERs since 10.1.3.4.0c patch and a total of 69 bug fixes and ERs since 10.1.3.4.0 release.

Oh yeah, you might be interested in the patch number from metalink of course, it's 8284524.

Ah, finally, I have worked a character from Sponge Bob into a blog post. I get ribbed a little at work because rather than reveal my magnificent profile picture on our employee directory I have posted a picture of the sponge.
When we first moved to California in 1999, we were avid Sponge Bob fans - as the kids have grown up we only look out for the old episodes where the writing was awesome and Sponge Bob's voice was not so danged annoying - any episode with Mr Lawrence listed as a writer and you'll find us immersed in Bikini Bottom, hangin out with the 'yellow one' and Patrick. The 'hash slinging slasher' was a ghost story character used by Squidward to try and scare 'Bob' into shutting up. If I have completely lost you, apologies, just look out for older episodes on Nickelodeon.

The post title is referring to a problem that has been cropping up recently - not earth shattering, an easy fix, if you know what to do - so Im sharing.

Those of you on 10.1.3.4 standalone or using as part of BIEE might see an error similar to this:

[121508_062307356][][STATEMENT] BI Publisher can not read or
 write the repository: C:BIPHome3/XMLP

On first glance you think, hey that looks OK but look a little, there is a 'slash' missing in that path. Thankfully the boys and gals at HQ have found out what the problem was and there is a fix on the way.

In the mean time, you just need to either update the reports repository path through the user interface Admin > System Maintenance > Report Repository. Once you add the slash back in you'll need t osave and then bounce the server. Alternatively, you can update the config file directly. You'll find the 'xmlp-server-config.xml' file under the J2EE_HOME\home\applications\xmlpserver\xmlpserver\WEB-INF.

<?xml version="1.0" encoding="UTF-8"?>

<xmlpConfig xmlns="http://xmlns.oracle.com/oxp/xmlp">

  <resource>

    <file path="d:\BI\OracleBI\xmlp\XMLP"/>

  </resource>

</xmlpConfig>

Then bounce the server to pick up the change and you're done!

A freaky funky Friday fry up today, sorry my son has discovered aliteration and we are trying to out do each other. This nugget of BIP goodness comes from template miester, Hok-Min, on the development team.

When you are using @section in your template for the commands, 'for-each' or 'for-each-group' (e.g. <?for-each@section: ...?>), then an empty/invalid PDF can be generated if XML data file has no data for that for-each loop.

To workaround this issue, please follow these steps:

1) add a section-break at the end of RTF document.
2) On the last page, add XDO command <?if@section: not(//ID)?>No Data Found<?end if?>

Have a wonderfully wicked weekend!

Apologies to those that caught this post early and were wondering what 'alterations' had to do with a freaky funky Friday. Also managed to get the instructions corrected too. Thanks to Leslie the post actually makes some sense now. Thanks Leslie!

More charting goodness today with a cheesy title linking us to a cheesy song from big time brat packer, Dean Martin. I get shouted down by the rest of my family for belting out 'when the moon hits your eye ...' from the shower|kitchen|while driving|etc.

One of the gifts the boys got over the holidaze was 'Guitar Hero World Tour'. For the uninitiated, its a 'video game' whereby up to four players can play guitar, bass, drums or sing along to songs from nearly all genres. You are all in a band and have to play to audiences to earn money and to unlock more songs. Its all great fun, I get to strut my stuff in the basement while the boys simultaneously beat a poor drum kit to death and rip it up on a plastic guitar. Im pleased, for the most part, that they both play the 'real' instruments too, they just wont let me join their band!

The one drawback of the game, from the gamers perspective at least, is the lack of function to be able to load a song of choice and have its component parts i.e. drum, bass, etc, broken out for you. They have some funky, build your own tune thang but its not the same. My kids loved the idea initially, until they realized that they would need to break out the dickie bows and drum brushes while Dad crooned his way through Dean Martin's repertoire. I did try to cheer them up with the fact that I also loved The Smiths and they were more 'rock.' But then they have heard me singing that non-stop since a friend bought me a 'The Smiths' greatest hits CD for the holidays. According to one son, I'm apparently, an old fart but I still bashed out Dammit by Blink 182 last night with a 200 note streak on 'Hard', take that whippersnapper!

OK, back to charting ... got some data looking like this:

<TEST>
<LIST_G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>8</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>1</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Loans Outstanding</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>7.1</BALANCE>
<IS_ORDER>2</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4.6</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China, People'S Republic Of</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12.7</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
</LIST_G_HEADER>
<CS_TOTAL>10.8</CS_TOTAL>
</TEST>

Requirement is to have pie charts showing 'Undisbursed Effective Loans by Country' and 'No. of Outstanding Loans by Country'. That data is a little tricky with the loan type being stored in that horribly named, DECODE_HEADER_B_UNDISBURSED_EF element but we can do it.

Its back to our old friend XPATH for some help to 'filter' the records that we chart. If you remember, xpath is a way of limiting records using a 'where' clause in SQL speak. You can get very sophisticated but in this case is a simple clause ie filter records where DECODE_HEADER_B_UNDISBURSED_EF = 'Undisbursed Effective Loans' for one chart and DECODE_HEADER_B_UNDISBURSED_EF = 'No. of Outstanding Loans' for the other.

The chart dialog in the template builder can not currently help on adding the 'where' clause so you'll have to dig into the code but we can get you most of the way with the dialog.

Setup the chart like so:

Now we need to add the xpath expression, double click the image thats been embedded in the RTF to get the image properties dialog. Go to the Web tab to see the code. Now look for the three instances of './/G_HEADER' and add the following - [DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans'] so you have.

.//G_HEADER[DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans']

This will limit the records to those that are of the 'No. of Outstanding Loans' type. Now run the template to see the required chart. Repeat for the other loan type et voila! you will have the required charts. You can use XPATH expressions to help you build pretty much anything you want. Sample files here.

If you're in Colorado and want to catch me and der boyz rockin' out, stop by our basement, we're here all week!

Bit of a long hiatus or holiday since my last post. I recently moved to a new position and have been playing catch up ever since. Im now caught up (kinda), you won't get 4/5 entries a week but at least 2 or 3 :0)

To get back into the groove, we're back looking at charts. Essentially a colleague was looking for something like this:

Does not look that special but in the accompanying email the OWN_NUMBER column is meant to be a 'house' image. You'll also notice its laying on top of another column. Let me say right now, I dont think the 'house' can be laid on top of the column. Getting a house into the chart is doable thou.

There is a caveat, the charting engine can embed an image into the chart but it will stretch it based on the data. So be wary about what images you are going to use.

To add the images you are going to have to get your hands a little dirty in the chart code. You will need to add a new element to the XML.

<SeriesItems defaultBorderTransparent="true" borderUsingDefaults="true">
<Series id="0" color="#33ccff">
<SFX fillType="FT_TEXTURE" textureURL="file:d:\temp\house1.gif" textureDisplayMode="TDM_STRETCHED"/>
</Series>
<Series id="1" color="#cc33cc">
<SFX fillType="FT_TEXTURE" textureURL="file:d:\temp\house2.gif" textureDisplayMode="TDM_STRETCHED"/>
</Series>
</SeriesItems>

The SeriesItems has a couple of attributes, should be understandable. You then need to provide 'Series' items for the number of columns per data set you want to plot.

The SFX element is the one that pulls the images in, notice the textureURL attribute, it holds the path to the image you want to embed. The textureDisplayMode can take the following values TDM_STRETCHED | TDM_TILED. Ignore the second - the same SFX element can be used to add a chart background and you can stretch or tile it. Tiling is not going to be relevant here.

The final output looks something like this:

Bit stretched but you get the idea, sample files here. This will work with just about all versions of Publisher and BIBeans.

Ever been using BIP and had to step away for something only to come back to find you have been logged out ... again!

There is some help out there, you can control the time out for the system. In the web.xml located in WEB-INF folder (location varies with which app server is used to deploy BIP). With OAS, it is located in \applications\xmlpserver\xmlpserver\WEB-INF

Look for the tag:

30

You can change it as you wish, its measured in minutes. Thanks to Nikos and Kevin for the tip. Lots of other stuff in that file ... please don't touch, support folks will not be happy :)

I have mentioned the 'Iceman' Ike's blog in the past among others, its a veritable goldmine. Im not sure this is a good likeness I can not access his picture on his blog anymore, I dont remember him being so blue?

He dropped me a mail recently from the icy north where he hails from, on an article he put together on logging/debugging. Although not a flattering article for BIP its extremely useful - we need to get that metalink note updated, Ike whats the number? A Neat how-to on turning on the logging facility in BIP under EBS.

He links to another article in there on using BIP APIs with OA Framework - there are two in the series and both extremely useful. They highlight the IDE that Ike has come up with for folks needing to work in JDeveloper/OAF with BIP APIs - not the easiest task in the world to get the right supporting libraries in place. Ike walks you though it all - well worth checking out!

I have covered our MSWord shape support in various entries and there are some examples in the documentation. As you know we can change the shapes by skewing them, stretching them, squashing them, etc. We can also repeat them - the last is useful, if say, you want a visual representation of a rating for an employee.

As I mentioned, this can be achieved with shapes and that is documented, Jen (who watches our TARs/bugs among other fabulous activities) sent me a variation on the shape repeating. Gif repeating! The customer wanted to use an embedded image rather than a shape and have that repeat - it was a star gif which begged the question 'why not use a shape?' but anyhoo, we aim to please with BIP so here goes.

Some of you that have repeated shapes will be familiar with something like:

<?for-each@shape:xdoxslt:foreach_number($_XDOCTX,0,RATING,1)?>

This code is embedded in the shape to get it to repeat, we want to repeat an embedded image so we can not embed the code, we have to wrap it, so we get some thing like this:

A little different, but it generates the correct number of stars. You need to remove the @shape and use the @inlines to get them on a single row and then have the loop start at 1. Remember with shapes you can eliminate the star in the template with the offset but with the gif approach you cant so when RATING =1 we only want a single star so the loop needs to run from '1 to 1' to just get a single star, For rating =3 we get from 1 to 3 ie it adds two more stars to the row. If there is a situation where there should not be an image you will have to get imaginative and wrap an if around the whole thing so that nothing is shown.

If you are interested in the template, you can get it here.

Now people, I covered stars, what other images might you want to repeat on a report, I want business requirements here!

I got a good template last week that made me double take. Those of you that have seen enough Oracle themed powerpoints will remember the preponderance of red blocks on the slides. The problem was simple, the template was not working?

When I first opened it from my mail client, I did a double take thinking the emailer was trying to create a powerpoint template. Tough to see in the graphic below ... click to blow it up.

Imagine some one madly trying to answer mail, build a demo and answer a phone, having written or seen umpteen Oracle powerpoints and you'll be close to the state I was in when I opened it, double took and burst out laughing, someone thinking they could build a powerpoint template, bah!

After a third look I realized that it had opened in Word and it was an rtf file. With no margins and all that red it looked just like a powerpoint slide.

The reason it was not working properly came down to the shapes and the way the images were pasted in. We have some issues with the the shapes and getting them to sit in the right place. They 'float' above the regular word palette and its tough to pin them down for non PDF and RTF outputs.

A quick conversion to a table with colored cells and properly inserted images i.e. insert->picture->from file... and it renders great in powerpoint output. Template available here.

So to answer the Powerpoint template question, if you're asking it? No, its not supported but you can get some mighty fine ppt outputs from the RTF templates with a little ingenuity.

Apologies for the very patchy updates ... too danged busy at the moment.

Today, a PeopleSoft entry, Im looking for more non-EBS and standalone entries so get some questions or entries to me and I'll get them published. I got an email from Edward, one of our consultants.

At a client site where the client wants to utilize XML Publisher within PeopleSoft HCM 9.0. No issue here but ... the client wants to prompt for two date values that the user can embed into the Report Definition (template) - so the other fields in the template are extracted from PeopleSoft tables but the clients wants to supply 2 real-time values for dates.

...
How/where do I create two runtime prompts? Thanks.

Now, I freely admit, I dont know the PeopleSoft implementation as well as I might but I know folks that do ... thats important. In this case Jody and her tireless team, particularly Mikhail and Rabah, stepped up for me with answers.

You need to pass these as runtime Properties through PeopleCode. The ReportDefn class has setRuntimeProperties() method that can be used for this purpose. So the PeopleCode behind your page will need to get the values from the page and pass them to the ReportDefn object.

&asPropName; = CreateArrayRept("", 0);
&asPropValue; = CreateArrayRept("", 0);           
&asPropName.Push;("xslt.ParameterName");
&asPropValue.Push;("Parametervalue");

&oRptDefn.SetRuntimeProperties;(&asPropName;, &asPropValue;);

ReportID
ReportTitle
RunDate
RunTime

So now, I know and more importantly, you know!

I have been working on a lot of BIP/BIEE integrations lately. One thing that has come up which the Oracle by Example for BIEE/BIP training does not cover is the case where I want my BIP report to be driven by parameters from within a BIEE dashboard page.

Something like this:

Its a simple report requiring just a single parameter but how do I get a BIEE prompt to be passed through to the BIP server?
It's pretty simple really, first off you need to create the BIP report with the parameters defined, no need for LOVs, unless you want to run the report from the BIP interface too. Once you have the report defined it's then a case of setting up the dashboard page prompts.

The trick is to create a presentation variable associated with the prompt that exactly matches the BIP report parameter name, in this example its p_student_id.

You can create as many prompts as you have BIP parameters and of course you can create cascading values for the dashboard prompt list of values.
Now when the report renders you will have dashboard prompts for your users to select from and then hit Go to refresh the report data.

Following on from yesterday's intro to tree maps we now get into the nitty gritty of the Flex component and BIP.
The flex component is not that nitty really, its very easy to use, the main entry for it :

dataProvider="{dataXML.ROW.SCHOOLS.SCHOOL}" labelField="@NAME" weightField="@TOTAL_STUDENTS" x="0" y="0"
colorFunction="{itemToColor}" dataTipFunction="{itemToToolTip}"/>

is pretty simple. It needs to know your data source (dataProvider.) The labelField, what text to put in the rectangle, the weightField, this governs how big the rectangle is. The colorFunction - how to colour the rectangles and finally the dataTipFunction, this is a popup when folks hover over a rectangle to show them info about the underlying data.

The component download comes with a bunch of samples, I took the stock market example and bent it to my will, cos Im strong like that!

Both the color functions and dataTipFunctions are provided, they are easy enough to understand and can be modified easily. josh provides some good getting started doc.
All sounds very easy and the component is, its the data it needs or more importantly the data structure it needs thats a bit more tricky.

Those of you that know and use Publisher (standalone/BIEE) will know that if you give Publisher a piece of SQL it will return you XML. Its flat XML, we call it 'ROWSET/ROW' - the treemap component can not use it - it actually can, but you get some groovy results. The sample data Josh works with is simple enough:

<node label="Mail" data="100">
 <node label="Inbox" data="70"/>
 <node label="Personal Folder" data="10">
  <node label="Business" data="2"/>
  <node label="Demo" data="3"/>
  <node label="Saved Mail" data="5" />
 </node>
<node label="Sent" data="15"/>
<node label="Trash" data="5"/>
</node> 

But you'll notice that there are elements and attributes, you will also remember that Publisher does not generate attributes on its own. It appears the component needs a hierarchy with attribute values.
We can get Publisher to generate the attributes but we need some help, I had to use SQL XML. I have written about this in the past, its very powerful if a little fiddly to get the XMLELEMENT and XMLATTRIBUTES commands in the right spot. I managed to get a structure that worked with the tree map component with the following SQL XML.

select
 XMLELEMENT("SCHOOL",
  XMLATTRIBUTEs( e.SCHOOL_NAME as NAME,
   sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) as "ABSENT",
   count(e.stu_id) as "TOTAL_STUDENTS",
   round((sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "PCT_ABS",
   round(0.14 - (sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "DIFF_FROM_TARGET"
    )
    ).getClobVal() as SCHOOLS
from ENROLLMENT e,
SCHOOL_TYPE t
where e.sch_cd = t.sch_cd
and t.type = 'H'
and e.calendar_dt = to_date('10/24/2007','MM/DD/YYYY')
group by e.SCHOOL_NAME

Lots of heavy lifting going on in there but it basically generates:

<ROWSET>
<ROW>
<SCHOOLS>
<SCHOOL NAME="Dauphin High School" ABSENT="223" TOTAL_STUDENTS="1925" PCT_ABS=".116" DIFF_FROM_TARGET=".024">
</SCHOOLS>
</ROW>
. . .

We can not get rid of the ROWSET/ROW elements but we can live with that. All we need to do is specify the path to the SCHOOL level i.e.

dataProvider="{dataXML.ROW.SCHOOLS.SCHOOL}"

I have yet to investigate if we can use a data template to avoid the extraneous elements. The component will allow you to have multiple levels of data just like a tree so you can build outputs like this.

You can get the complete Flex project here and the Publisher report here.

More mails this week, its funny how you get a batch of requests for something all at the same time. This past week the proverbial hot potato has been Tree Maps and how to make em?

For the uninitiated, a tree map is a 2 dimensional graphical visualization of your data e.g. school size + absenteeism, maybe your debtors and the size of their debt and their delinquency or your organizations current project load, their relative size and how far behind or ahead they are compared to plan. For a large amount of data, say, listing on the stock market its very hard to compared whos up and whos down just by looking at tabular data. You can color code the data for sure but if you have masses of rows its tough to compare. A tree map gives you a easy visualization of common entries and they are color coded to make life even easier.

In the graphic above we can see schools in a school district, the size of the of the rectangle is affected by the school student population ie a bigger one for a larger school. The color of the rectangle is governed by the difference from an attendance target the school has for a given day.

Now you know what they are, so how do you build em?

I first looked at RTF templates, they can do so much and they can partially help but only for single dimension data. We can conditionally color a table cell based on some data but resizing that cell gets very complicated very quickly - I ended up in hard core XSL too quickly. I did find a brave soul out on the intertubes that had built several XSL style sheets to get the tree map - IMHO its just too expensive a development. If you only need a sing dimension then RTF templates are quick and easy.

You can get the template and data here.

Much more useful and altogether more cool from an output format standpoint is the good ol Flex template. I have not looked at Flex for a while, digging back into it I was amazed at the amount of open source development going on around building re-usable flex components. Don't want to build it yourself, go google for it. The Google Code site has masses of components and examples, just search for it. That's just what I did and came up with Josh's tree map component page. Its covered by an Open Source license from MIT so both kudos and thanks to Josh for it.

Seeing the MIT license got me digging through links to see what else might be out there from the MIT brainiacs. All you could ever want to know about tree maps.
Then I found this site, the MIT Simile Widgets - some awesome stuff going on in there, not Flex necessarily but some very rich interactive visualizations.

Getting back to Tree Maps and Flex - using the component in a project is easy enough, getting Publisher to generate data that will work with the component is a whole other kettle of fish ... I'll save that for tomorrow ...

Got a mail today asking for tips and tricks on how to take the following form and convert it to an RTF template?

Looks complicated right, the specs from the UK government get down to pixel positions for form objects - looks tough!

Hey people, wake up! Have you forgotten the RTFs long lost cousin, the PDF template. No it does not have fancy dynamic table formatting or conditional images. Its a little staid and lost in the past a bit but it still has its place. In this situation it can shine, no layout development, its all here for you, just add formfields or map them, generate your XML data and you're done!

PDF templates people, don't forget em!

There is some new content on OTN, around training and its looking pretty good.

http://www.oracle.com/technology/obe/obe_bi/bipub/index.html

• Getting Started with Oracle BI Publisher


• Exploring Advanced Features of Oracle BI Publisher



• Integration


• Integrating Oracle BI Publisher with Oracle Business Intelligence Enterprise Edition
  
• Oracle BI Publisher and BI Discoverer Integration



• BI Publisher for Applications


• Creating PeopleSoft Reports Using XML Publisher


• Creating Reports in Oracle E-Business Suite Using XML Publisher

There's some neat stuff in there, in particular the Advanced Features, not really really advanced but some very useful subjects get covered, drillable reports, multi data reports and finally some data templates. I have not tried the Disco integration OBE but fully intend to take it for a spin later this week. Enjoy!

I have been trawling through our documentation lately. Looking for something else I came across something that I have been asked for lately. The need to keep paragraphs of text together!
This functionality was actually delivered back in 10.1.3.3.3, here is Leslie's prose ...

Support for "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.

To enable "keep paragraph with the next paragraph" in Microsoft Word:

• Select the paragraphs you want to keep together on a page.
• From the Format menu, select Paragraph, and then click the Line and Page Breaks tab.
• Select the Keep with Next box.

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.

To enable "keep paragraph intact" in Microsoft Word:

• From the Format menu, select Paragraph, and then select the Line and Page Breaks tab.
• Deselect the Keep lines together check box.

So now you know ...

I have had a coupe of mails asking where I am? Im here, I took a week away from writing for the blog - needed a rest and some time to think about a bigger, paper based project :0)

I put together a viewlet last week for a colleague on how the Oracle Reports migration library can be used - its now residing in the demo catalog here or you can access it directly here.

I used a fairly simple Oracle Report but I think it covers the 'can' and 'can't' of the migrator. I'll say right here, its not magic, it will not transform all of your report pixel for pixel, format trigger for format trigger but it gets you a long way down the road.
For the BIP uninitiated I also put together a Oracle Reports to BI Publisher presentation that you might find useful. You can get that here.

Would be interested in feedback on the tool - there is a more 'souped up' version in the works that will turn your report to tomato or chicken noodle!

Over the past week I have received a few mails concerning a problem in some 10.1.3.4 upgrades. They all seem to be related to a full BIEE upgrade rather than a BIP upgrade. the dev team are still investigating as it seems to be hard to reproduce, but just in case you are out there suffering ... we have a solution.

If you have specifically chosen to upgrade in the installer process and find that you can not get into the BI Publisher server interface after upgrading to 10.1.3.4. Trying either from the More Products > BI Publisher link in BIEE or going directly to the BIP server you get a user security error. You will hopefully have hit an issue that was found today. The fix is simple enough, it looks as thou the /Admin/Security/security.xml file contains some incorrect entries. The sample file we saw today had the following:

<folderPermission>
  <allow path="/Business Intelligence" />
  <allow path="/Executive" />
  <allow path="/Financials" />
  <allow path="/HR Manager" />
  <allow path="/Marketing" />
  <allow path="/Sales Manager" />
  <allow path="/Supply Chain Management" />
  <allow path="/Training" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Business Intelligence" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Executive" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Financials" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/HR Manager" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Marketing" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Sales Manager" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Supply Chain Management" />
  <allow path="D:/OracleBI/xmlp/XMLP/Reports/Training" />
</folderPermission>

The entries in red are incorrect. Just simply remove them from the file, save it and bounce the server and all should be well. Investigations are ongoing to check why they entries were in there and as soon as I have some confirmation I will let you know.
If you have hit this issue, can you share the OS and platforms you upgraded on. The install.log file located at $BIHome/BIServer/log would be very useful along with the steps you took during the install, if you can remember.

I know, it's Monday, what happened on Thursday? A hectic morning on the demo grounds is what, lots of folks wanting to know more about BIP for Apps and lots of 'follow the leader' to get the folks interested in standalone or BIEE BIP through the demopod maze to get to the standalone pod and vice versa.

Here's what I mean, you can see the BIP for Apps pod on the right hand corner (great location, thanks Nadia) and the BIP Standalone pod circled on the left.

Do you take the easier route 1 or go for the more scenic route 2? Route 2 is more fun, but fraught with the imminent danger of losing the 4 people trailing behind you, as you move through the crowds of people around the intermediate pods. We should have had a sign on a pole that we could carry above our heads to ensure folks knew where we were and could not get lost. OK, I have beaten that train of thought to death but you get the picture - it was busy!

During the week, Wednesday I think, I bumped into Rick Pollina from Arrow Electronics. A hot topic recently has been getting BIP content to label printers, whether they be Zebra, Intermec or others. Im hoping to get an entry out on a more 'conventional' approach soon, but Rick came up with a neat solution - eText templates.

For those of you that have not come across eText templates yet, they are another template type, built in MSWord but are for 'electronic' output e.g. EDI messages or EFT - basically position or delimited based content. In recent months I have seen folks using them to generate flat file output - not what they were designed for, but they work.

I'll let Rick explain the problem and their final approach:

The first approach the team was working used BI Publisher loaded with the proper barcode fonts and would generate a PDF that looked exactly as the label would print. They were then going to use a PASTA driver to convert the image to postscript and send it to the ZEBRA printer that accepts postscript.) Basically this approach eliminated the need for ZPL coding and just send an image to the printer. For some reason they were not able to get this to work, not sure why as it appears the old ZEBRA printers seem to accept postscript. (Tim: this is the conventional approach, I had not heard that Zebra could accept PS, the solution we have worked on finally generates a Raster image of the label that Zebra supports)

The alternative approach and the one we ultimately chose was to build the template with the ZPL code's embedded in the template and send the RAW text image to the ZEBRA printer. On a Linux/Unix platform you define the printers as RAW and you can run it through CUPS or local define the printer. On windows you can probably setup the printer as Text, although I did not try this.

Attach find the sample template I built using etext format supported by BI Publisher. I choose this over native XSL-FO template (also supported by BI Publisher), because the development team was already using ms-word for the development of all their other templates.

To view the template as it would be created you need to install the BI Publisher desktop (free download from Oracle) and use the template viewer--but you probably new this already :)

The advantage to this approach is that the new ZEBRA printers are Accepting XML and the old ones like we have still require ZPL so you can account for both types with the same SQL and a different Template.

I also noticed that ZEBRA got an award here this week along with presenting. It looks like they already have a nice design tool that they could probably extend to produce the XSL-FO that can be used for the template. (looks like they already support SAP through there smartform client tool to design labels.

You can get the sample files Rick kindly provided here.

To fill in the gap you might have spotted, 'how do I know the ZPL format?'
Rick used the label creation application that Zebra provide with their printers to build a label layout, more information available from Zebra - I'll try and get more info on this for you. He then exported it to the ZPL format and then built the eText template to create the skeleton of the ZPL and filled in the data items. not for everyone I know but it was a quick and straightforward solution and still leverages the one data source, multiple layout templates mantra that I keep pushing to all of you.

Another busy day on the demogrounds, or at least a morning for me. I got to slope off for 2 hours at the Meet the Experts session at the Applications Lounge. Apparently, Im an expert, and folks value my input - thanks to those of you that came. Apologies, to Helga from Deloitte, whose notepad I drew all over trying to explain how Publisher worked inside Siebel CRM. I think it helped.

After the MTE session and break for some food and a quick visit to the JDE and PeopleSoft reporting demopods. I got to see Mikhail from the PeopleSoft dev team in full flight, sadly he spotted me and tolds the folks he was talking to, to emial me if they had questions.Then it was off to the Unconference session, where I stumbled through a more complex template. Apologies to all - after the session I realized I had opened a completely mangled Invoice template - hence the confusion on all the for-each's.

The day wrapped up back in the demogrounds and on the phone to try and procure a wrist band for the evenings entertainment. That in hand I got over to Treasure Island to check out the music. Seal was great, took me back to my youth.

Bit blurry but we were dancing!

Then to UB40, these were/are a reggae band from the 80s, I think alot of folks were a little bemused by the beat. When they opened their set they had a great Publisher back drop.

The first PDF template, probably around before PDFs had even been thought of. The UB40 form was a form you filled out every week to explain that you had not worked and could rightfully claim unemployment benefit in the UK.

If the form were around today, it would a prime candidate for either using as a template or for data entry!