Oracle BI Publisher Blog

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

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

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

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

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

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

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

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

Update on versions ...

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

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

BI EE & Standalone

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

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

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

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

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

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

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

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

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

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

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

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

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

The Data

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

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

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

The Template

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Reset your Head

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

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

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

Spaced Out

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

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

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

Next, page totals and last page only ...

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

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

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

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

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

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

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

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

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

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

The final template, data and output are available here.

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

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

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

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



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

When the output renders your text will follow the line.

Samples here.

I wrote yesterday about all of the great content at Collab07 there will be around XMLP/BIP ... I also mentioned that we would not be there :-(

There has been a reprieve, XMLP will be represented by yours truly. We, thats me and my laptop chock full of demos and 'intersting slides' (I kid you not) will be in South Seas F on Wednesday @4:30 ... looks like there will be plenty of room so join me for an hour of information, demos and news.

Details below, please excuse the meglomaniacal title and over the top session blurb ... we wanted to jump out at readers and pique their interest to find out who the heck is Tim.

Title: 'The Greatest of the Latest Oracle XML Publisher from Tim'

Abstract: Oracle XML Publisher, has fast become a key component of the EBS, Peoplesoft Enterprise and JD Edwards E1 reporting architectures reducing the high costs associated with the development, customization, and maintenance of business documents, while increasing the efficiency of reports management. Oracle XML Publisher is now being implemented in many other of the Oracle Applications family of products such as Siebel and Oracle Retail allowing a new group of Oracle customers to take advantage of this innovative approach to reporting.
This session explores the current and future features and benefits of XML Publisher within each of these applications showing how customers can take advantage of this innovative approach to reporting, packed full of demos, information and news you can use.

Time and Place: Wednesday April 18th @4.30pm in South Seas F

See you there.

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

Simple Barcodes

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

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

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

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

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

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

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

but I want this

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

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

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

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

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

For those of you using EBS either 11i or R12 there is now patch available for the Template Builder for MS Word.

5887917      ORACLE XML PUBLISHER DESKTOP PATCH 5.6.3

You can download it from metalking for the Windows 32 bit platform. I covered some of the new functionality in a previous post.

For those of you using Peoplsoft or JD Ewards you should be OK using it, there is nothing in the builder that would not work once deployed to the server. If you folks do not have access to metalink you can get the builder from the OTN download page.

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

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

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

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

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

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

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

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

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

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

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

Config Manager - EBS (5.6 and above)

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

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

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

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

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

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

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

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

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

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

package pdftester;


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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

FontFamilyName:Size e.g. Arial:12

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

We can generate something like

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

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

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

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

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

The other changes are hidden in the fields.

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

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

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

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

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

    Same for the bottom right cell:

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

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

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

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

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

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

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

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

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

1. 
   

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

   
   

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

   
   
2. 
   

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

   
   
   
   
3. 
   

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

   
   

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

   
   

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

   
   
4. 
   

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

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

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

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

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

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

  to_number(TO_CHAR(SYSDATE,'JSSSSS'))

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

You may have read last month about the great BIP Mind Share project, the plan was to provide the ability for development, consultants and customers across all flavors of publisher to share their templates - one big happy family sharing our toys.  

Well, I have had a great meeting recently with the folks that are implementing this wee beasty, one of those folks is Jake of AppsLab and Web2.0 evangelist. Its coming together very quickly and it looks like it is going to be really useful. Once loaded with its meta data to describe said layout or data template you are going to be able to search for that elusive template for that obscure concurrent program that ships with European Localizations. Just as a tease I even have grainy screenshots stolen from the locked cube (yes Oracle's cubes are lockable:0) where developers have been feverishly bashing this out.

Initial release rollout will support EBS and PeopleSoft, but JD Edwards, Siebel, <<put you app name here>> and even standalone publisher templates will be supported in furture releases.

There are a few operational things to iron out such as having a librarian or some means of ensuring the quality of the content, where its going to get hosted, behind Metalink somewhere is currently on the cards. Nothing major to sort out but here's where you come in.

This is only going to be as good as the content that is posted into it. You can expect all of the templates from development teams in EBS and PeopleSoft to be in there. But we need your templates in there too - I know theres a whole question of intellectual property, you have worked hard to get your templates where they are today and why should you share them? But its a give and take process, Im sure that there is going to be a template in there that you need, so if you put one in when taking one out the whole concept is going to be great - if I or any of the development team have helped you with a template I expect to see it in there, got it :0)

You're going to get to see the application at OpenWorld in November and we are looking for volunteer templates to be loaded to our demo system - we need layout templates and a little meta data - what report name and application it's associated and we'll do the rest. We can not guarantee you'll get into the session but we'll try. If you're up for it, mail me your templates, zip em and change the extension to .zipped otherwise our mail server will spit it back at you.

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

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

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

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

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

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

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

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

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

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

       Compile error in hidden module: Module_starter

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

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

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

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

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

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

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

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

• 
  

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

  
  
• 
  

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

  
  
• 
  

  Transparent and half-tone renderings are not supported.

  
  
• 
  

  DeviceN color space is not supported.

  
  
• 
  

  Shading patterns are not fully supported.

  
  
• 
  

  Vertical writing is not supported.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

A few weeks back I made a call for templates for a demo of the new Reports Repository demo at OpenWorld. As I mentioned this is going to be a repository for us and hopefully you to share your templates with the Publisher community at large. I got a few templates but I'd love to get more.

Doug Fortnam at Protege Software Services contacted me with a great example of a sharable template - its not for an invoice report but actually a custom report but its very useful. I'll share his insights here.

This XML Publisher Report is a report to document any Forms Personalizations present in Oracle Apps 11.5.10. I presented it at the New England OAUG in Massachusetts in June of this year. I think it is a good example of a useful, shareable template because it documents the setup of Forms Personalizations, either select Personalizations or all Personalizations in the database. The sql query to generate the flat file was developed in TOAD and then used in the Report Wizard to create the data template. This example makes extensive use of re-grouping and conditional logic to display the data in a fashion similar to what is displayed in the Oracle Forms. If I had to do it over again, I would develop a data template with grouping to simplify the rtf template. Not sure if there is a performance hit because of all the re-grouping.

Doug shared the template and you can get it here.

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

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

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

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

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

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

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

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

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

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

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

Down on the demo grounds this morning ... a slow start but boy did it get going. We are right next to the concourse - not buried at the back like last year so there were lots of folks coming by. Im very impressed by what some of you are getting up to with publisher - you have moved on from getting that invoice listing out and are now attacking the bursting engine with a vengeance. I heard today of a customer, sorry I can not remember your name, doing 'double bursting' - burst once and then take the results and burst those again - I did not catch the business problem we were solving but solving it we were and thats what matters. So bursting is hot - just about every one I spoke to wanted to know how to burst-  other than to drink 3 gallons of water and see if you can hang on!

For the initiated, bursting is a term used to describe the taking of a set of batch data, splitting them in to individual documents and then delivering them to recipients. A good example was quoted at the SIG yesterday - a company generates commission statements for all of its sales force every month. Rather than run 100s of individual reports, just run one for all of the sales force and then burst each report to the appropriate employee - simple n'est-ce pas? Thanks Jean Pierre - my 20 year old French is more than a little rusty :)

Publisher offers a little more, what if each employee had a delivery preference, or some wanted HTML while others PDF? what if some wanted it formatted using Layout A and others Layout B? Publisher can do that - as long as those values around layout, format and delivery option are available at run time then the bursting engine will dynamically apply templates, format layouts and deliver according to preferences.

We go a stage further thou, for real low level control, at each stage of the burst e.g. data split, format, generate, etc we have created a 'listener' - you can subscribe to any listener in your own code so that as a stage is reached in the burst you can interrupt the flow, step in, execute some logic and then let the burst continue. Let's say you wanted a copy of each document sent to your content management system - in the case of partner documents such as invoice, you can not just dump the documents into the repository, you need to add some attributes to it so that its searchable later on. Use the listener functionality - subscribe to the 'generate' event - once the document has been generated, get a copy of it and its XML data - now parse out what you need from the XML data and create your meta data file - now push this into your content manager et voila. The customer gets their invoice and you get an indexed copy of it for retrieval and record if needed later.

There are versions of bursting for EBS, PS and Standalone and I believe JDE will deliver a solution next year.

Go forth and burst! 

Hey P-Softers, there is a new OU course for you folks to get on the BIP train. Its only a single day but it looks jammed packed with useful content.

Technology Overview

• Describing XML Publisher
  
• Describing XML Publisher Phases
  
• Describing Optional Features

Setting Up XML Publisher

• Defining XML Publisher Settings
  
• Setting Up XML Publisher Permissions
  
• Setting Up Report Manager
  
• Defining Report Categories
  
• Downloading Design Plug-ins

Creating and Registering Data Sources

• Describing Data Sources
  
• Identifying or Creating the Source of Your Report Data
  
• Registering Data Sources

Creating Templates

• Describing Template Types
  
• Creating RFT Templates
  
• Designing RTF Templates
  
• Creating and Maintaining Reusable Sub-Template Definitions
  
• Working with PDF Files

Defining XML Publisher Reports

• Describing Report Definitions
  
• Creating a Report Definition
  
• Creating a Report Definition Based on PDF Template
  
• Bursting Reports
  
• Using Content Library
  
• Maintaining Template Translations

Running and Viewing Reports

• Running Reports in Query Report Viewer
  
• Scheduling PS Query Reports
  
• Locating and Viewing XML Publisher Reports
  
• Running Reports Using PeopleCode
  
• Running Reports from Application Engine

Get more information on the course and scheduling here. 

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

Here is sample XML:

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

Using Table Wizard, I am getting this report:

A1 2112
 7985 
B1 8452 
C2 2159 

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

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

xdoxslt:distinct_values(Node name)

and returns a numeric value.

In Vadim's case

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

I've mentioned numerous times on this blog that I'm no PeopleSoft expert, hence that part of our community suffers. Im not one for writing about a subject that I dont know enough about - well thats what I would have you believe. Its getting tougher, there are some very knowledgeable folks out on the forums these days that know things better than I do.

To fill the PeopleSoft void in my brain that I really must try and fill. We have the 'PeopleSoft Tipster' aka Duncan Davies from the UK - Im guessing Duncan has a 'mixed' heritage and I mean that in the nicest way, a Scottish first name and a Welsh last name. We have done this to one of our sons too - Douglas Lewellyn Dexter - his great grandfather has roots in Wales and his grandfather is from Scotland - not sure where 'Dexter' originates thou - other than cows and an association with particularly dexterous people.

Back to Duncan - lots of PeopleSoft tips but of particular interest to folks here will be his posts relating to XMLP as it sits in the PS Tools stack. Rather than list the current posts here which will not be useful over time Im just going to hook into Duncan's XMLP category. 

http://peoplesofttipster.com/category/xml-publisher/

The latest post is a cool workaround for the limitations of PSQuery and its XML datasets.

Well its that time of year again ... and I dont mean taxes. Saying that you better have got them done by the time Collaborate rolls around again this April. There are lots of opportunities to check out BIP/XMLP related content.

This year it's in my home'ish town - so rather than stare at the four walls of a non descript hotel room. I get to help my kids with their homework face to face rather than down a phone and I get sleep in my own bed every night. The drawback is I have to get my sorry backside up to Denver everyday but the upside is that there is loads to see and listen to when it comes to Publisher.
Here's what I have found so far:

I'll be digging around a bit more in coming weeks but there are some great looking sessions in there and if time allows I'll be at every one. Best laid plans and all that, it will no doubt snow and the trains will fail and I'll miss them but I'll being doing my damndest to get there. 

If you're coming, look out for me lurking at the back of sessions. Apart from mine of course, on Thursday @8.30am, the morning after the party - I'll try and not be too energetic and loud!

We had a customer issue a few weeks back ... 'really Tim, I never would have guessed' :0) Yeah, we get some issues sometimes. This was a JD Edwards customer that had developed an encoding class for a barcode. Lost? Check out the Advanced Barcode Support article. Basically, some barcodes need the data encoded, calculating stop bits and such like, prior to applying the barcode font. To support this, we allow you to create a java class that implements a Publisher interface to encode the data values. This can then be called from the RTF template layer using commands - check the user guide people!

The encoding class needs to be in the classpath on the server be it JDE, EBS or standalone - this is straightforward enough but how about being able to test on the Template Builder for Word interface?

It can be done, no support directly in the tool but with a little jiggery pokery we can get the class tested. The list of jar files that the builder will load at runtime is limited but the main one we can highjack is the xdocore.jar. You'll find it under the jlib directory under the Template Builder install directory.

You can open the jar file using something like WinZip, with it you can see the structure of the contents,

The class you developed will no doubt have a package declaration in it e.g. oracle.apps.xx.xmlp.barcode - this needs to map to the path in the jar file. To get it into the jar in the right place just get the class into the directory path on your local drive.

Then, in the case of Winzip, open the jar, then drag and drop the class and ensure the 'Use Full Path Info' option is set to get the class into the correct path that will map to your package specification.

Now, you can test to your hearts content in the comfort and relative speed of MSWord.

Starting to actually feel human again - Im amazed how 'flu' here in the US is so debilitating. I dont remember feeling this bad since I caught some 'bug' in N Africa when I spent far too long traveling when I was younger. My brain is still trying to burst out of the front of my head but the hacking cough that makes you wince, it hurts so much is not so bad. One great side effect, for my kids at least, is that my voice has almost disappeared so they really can claim they did not hear me calling them.

The error in the title is not a nice error to see! It evokes lots of questions, how much data are you processing, how complex is the template the list goes on. Now, Im not a great fan of metalink - there is a mass of useful information in their but getting at it is nigh on impossible.

I was in our Ohio office last week meeting some of our new team Noelle, Dave and Jen. Noelle had installed BIEE and BIP and was getting an error when she tried to connect - I had not seen it before, googling it brought nothing useful so it was on to Metalink. When I usually resort to metalink I try and get as specific a string as possible cos no matter what you enter you get 100s of hits. I was amazed when we entered our string in the advanced search to only get 4 hits - excellent! Then we realized that the 4 hits were nothing to do with OBIEE or BIP ... dang! Noelle ended up falling back on the age old solution - re-installing - it worked like a charm.

If you can get past the search engine on Metalink there are some real document gems. For the error mentioned in the title go to

                            Note:315477.1 : XML Report Publisher Fails With java.lang.OutOfMemoryError

Its a good solid document with some useful information. I think Pieter from the support team wrote, thanks Pieter!

Theres a new version of the Enterprise release training now available, its a 3 day instructor led course, looks pretty good if you're just starting out with BIP.

Oracle BI Publisher 10g R3: Fundamentals (Data Warehousing and Oracle Business Intelligence)

There is also the 5 day E Business Suite course being run all over the place, fancy a trip to Alaska? I hear Anchorage is a great place to visit. Alternatively pack the kids and get to Orlando, they can check out the mouse while you use your nouse!

11i Oracle XML Publisher Fundamentals (NAMER) (E-Business Suite)

There is also the PeopleSoft training course for XMLP

XML Publisher for PeopleSoft Enterprise PeopleTools Rel 8.48 (PeopleSoft Enterprise)

Its a one day affair but boy do they pack stuff in for it - I mentioned it in more detail here. You can also study from the comfort of an armchair at home using the CD-ROM option.

Last but not least is the JD Edwards  training

EnterpriseOne Embedded BI Publisher Rel 8.97 - RWC (JD Edwards EnterpriseOne)

Its a self study course rather than instructor led but again looks to be good. I have also heard rumor of hands-on labs for JDE XMLP at OpenWorld this year - more news as I get it.

All the courses are introductions to the various flavors of XMLP/BIP, I have no doubt that there will be a Siebel CRM course coming soon. But thats it, introductions to get from a 'noob' to an intermediate but no further, after that its time to hit the docs and the forums for answers. I did enquire as to the possibility of an Advanced class but its a no no ... historically they have not been worth it for OU, just could not get the numbers of folks to fill the classes.

I have talked about doing a regular 'public' session on XMLP/BIP where we can take things a bit further and look at more advanced features, case studies, etc but you guys are just too quiet - I got maybe one or two emails saying 'yes please!' - any one else out there interested?

If you cant email then get over the mix.oracle.com and click - they are currently allowing you to vote for or suggest sessions for OOW08. I have posted the idea of providing an 'Advanced Publisher Techniques' session but its currently languishing in 50-60th place - get over there and vote for it - https://mix.oracle.com/ideas/27984-advanced-techniques-for-bi-publisher

Even if we dont get to the top we can maybe do a session in the 'unconference' on some of the more advanced stuff or maybe one of you could, I'll stand at the back and clap and cheer loudly!

Quick one today, Im looking for feedback/information - I have written about check printing here on numerous occasions, the one piece we as a product struggled with was signatures and their security. I wrote about some of the 'soft' alternatives to securing the signatures in a previous post. It has always niggled me thou, that we could not help those folks that had invested in a 'hard' security solution.
By that I mean that their check printer has a removeable memory module that contains the signature images. To get them out on to the page the printer is passed the appropriate escape sequences in the document and low and behold the signatures appear as long as a the memory 'stick' is there.

The key to supporting this 'hard' solution is that these printers use the PCL file format for printing - as many of you know, we dont natively support PCL, just PDF and PS, that is until now! We are working feverishly on our own PCL output (at last) and we are not far away from completing it. Once we have it we then have complete control over the PCL generation that is sent to the printer. Yeah, so what?

Well this means that we can allow you to embed you signature escape sequences into your RTF templates that will be preserved all the way through to the printer and that means you can wrap conditional code around them too and that means we will have a complete 'hard' signature solution for check printing. A dream come true ... OK, maybe not a dream but a nightmare I and a some customers I have been working with, can wake up from soon.

So, its coming but I need some help. Ideally we need a check printer that we can test with - its a long shot but if anyone in the Bay Area can let us borrow their check printer and a couple of hundred pages of fresh check stock, along with their routing and account numbers well be in touch soon ... from Tahiti :0) Seriously thou, if we can get a printer that would be fabulous.

Failing that, some details around what the escape sequences are that you need to pass to your printers would help. Without a check printer to test with, we are working along the lines that if we can put an escape sequence in a template that contains say 'Hello World' and have it come out at the printer. Then we can extrapolate that you'll be able to embed your escape sequences into your RTF and have your signatures come out the other end - a safe assumption I think but we need more information. If you have the info and would may be like to become a Publisher Guinea Pig testing the solution and helping us out, drop me a mail.

Ok, so I said it was a quick one today, I lied, those that know me know, that when a 'yes' or 'no' answer will do I cant just say yes or no. Im the opposite of a bolshy teenager who manages to answer the most open question, inviting a diatribe of verbose verbiage, with a one word answer. See there I go again ... Im shutting up now, mail me!

I mentioned the new demos that were being built a while back. We have now got enough of them to make them available. We are starting off with about 10 of them - they cover 'templating' for the most part. Its been a team effort, kudos to Noelle, Dave and Jen for putting the demos together.

There are some of the newer features we have in the Template Builder for Word, PDF template building and other tips and tricks. Its going to be a growing collection of demos over the coming weeks and months covering all things Publisher. I'll also be begging our PeopleSoft, JD Edwards and Siebel brethren for content to mount. If you have requests let us know.

You can get at the catalog here:

http://www.oracle.com/technology/products/xml-publisher/demoshelf/Shelf.html

For those that are interested in the delivery vehicle - its good ol Adobe Flex - we have to thank Ely Greenfield from Adobe and www.quietlyscheming.com for the Flex Shelf component - its a great component that we leverage to get away from the boring HTML formatted list.
Make sure you visit the catalog frequently to get the latest and greatest.

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!

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.

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)

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!

... 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