Oracle BI Publisher Blog

Date formatting in XML Publisher is very powerful, it can provide a variety of formats from simple 03/12/99 to 'Friday, December 31, 1999 6:15 PM GMT' ... notice the timestamp and the timezone components. All very neat but how do you get there?

Get the right format ...

Well the first hurdle is to get your dates in the XSD date-time format:
                  YYYY-MM-DDThh:mm:ss+HH:MM
Looks straightforward and you ought to be able to get the database 'to_char' function to serve it up no problem, but there is a wrinkle in there in the form of that 'T' character its used as a separator between date and time components. Try and use that format mask with the to_char and the database will choke ... the format is not recognised. There is hope, even for Oracle Reports under EBS users :)

1. If you are not interested in the time and zone info then just use the 'YYYY-MM-DD' format mask, no need for the 'T'. Oracle Reports and the db support this mask. XMLP will then format the date appropriately. any timestamp is going to be 12:00:00 AM and the timezone will default to GMT
2. Use the XMLP extraction engine - the engine will extract all dates using the XSD mask - simple
3. If you're using Oracle Reports or your own plsql/sql extraction routine and want the timestamp and zone then only way I have found to construct the mask is to use something like:

    to_char(sysdate,'YYYY-MM-DD')||'T'||to_char(sysdate,'hh:mm:ss+HH:MM')

this will at least get the date into the right format.

I stand corrected on this thanks to an anonymous comment, I was playing with masks in the 
db while writing and could not get the format to work, however our anonymous friend has:
to_char(sysdate, 'YYYY-MM-DD"T"hh:mm:ss+HH:MM') which works perfectly ... apologies!

Date formatting and calculations

Now you have date in the right format you can apply masks in the layout very easily. You can either use the MSWord formats or XMLP provides a format-date function ... there is a complete section devoted to the formatting in the user guide. Page 118 is the start and covers all XMLP flavors.
The other advantage of the mask is that calculations on dates become possible with XSLT 2.0, prior to this all dates were treated as strings not much use when it comes to calculating the number of days between two dates. Thats another article in itself ... there are plenty of resources out there now.

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!
 

Happy New Year everyone ... back on the horse again, I'll try and stay on this time!

I have had several requests on how to handle HTML formatted XML in their templates, to save you time finding my email and me answering here's the solution. When there is HTML formatted data in the source XML it needs to be converted to the XSLFO format prior to being put into the final document otherwise the raw HTML will be inserted so something like

<B>This is bold text</B>

needs to be converted to its XSLFO equivalent

<fo:inline font-weight="bold">This is bold text</fo:inline>

We can do this reasonably simply but their is one caveat. The HTML must be in the XHTML format i.e. <BR> is not going to work it needs to be <BR></BR> for a new line. So, if you have HTML in your data you'll need to clean it up to get XHTML using something like JTidy or Tidy.  Once you have the cleaned up XHTML you're ready to convert it to the XSLFO equivalent.

To do this, the easiest way is to use a sub template, Im advocating that because you are likely to need to use the functionality across multiple templates. The sub template is going to be written in XSLFO, not the easiest language in the world but I have posted a sample template that handles much of the common HTML for you. Its basically a set of functions that accept the input and convert it if necessary to XSLFO. Take a look at the template and you'll see a series of functions with a similar format:

<xsl:template match="STRONG|B|b">
 <fo:inline font-weight="bold">
  <xsl:apply-templates /> 
 </fo:inline>
</xsl:template>

<?import:file:///c:temphtmlmarkup.xsl?>

<xsl:apply-templates select="PROJECTSCOPE"/>

Sadly we have to resort to XSL as the XMLP aliases do not support calling a template on a data element yet. This command tells the processor to apply all templates to the value of the element PROJECTSCCOPE. It will then cycle through the sub template functions looking for a match. You need to do this for all the fields that may have HTML markup in the XML data.

Now when you run the template against the XML the HTML markup will be converted to XSLFO markup et voila you have correctly formatted text in your output. You can get the complete example here.

Happy Templating

I have had a few emails and the forum has been hot lately with questions about sourcing an image from the OA_MEDIA directory. the more popular question is for an RTF template but there are those of you trying to update the purchasing template which is sadly written in XSL-FO and not in RTF format.

RTF Templates

The user guide covers this and its now correct :o! You can either place a dummy image in your template and then in the Web tab of the image properties put in the following:

url:{'${OA_MEDIA}/IMAGE_FILE'}

For example

url:{'${OA_MEDIA}/FNDSSCORP.gif'}

Notice the use of curly braces rather than rounded and the position of quotation marks. Its very important, otherwise you are going to be pulling hair out at a rapid rate. If you have an image name embedded in your XML you can use that too, just need to use the concat command to bind the values together:

url:{concat(${OA_MEDIA},'/',.//IMAGE_NAME'}

the other option is to embed the necessary XSLFO code into a field in the template:

<fo:external-graphic src="url('{'${OA_MEDIA}/FNDSSCORP.gif'}')" />

XSLFO Templates

IF you're updating that PO template and just want your own logo, then you'll find that the PO team have provided a placeholder in their template. Just uncomment it and add in the OA_MEDIA reference

src="url('{'${OA_MEDIA}/fwkhp_folder.gif'}')

Be aware the format is subtly different to the RTF format ... sorry not much we could do there. Just be careful to avoid that hair loss :)

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.

One of the lesser known and probably even lesser used features we have provided is to support MSWord shape and clipart support ... you know those shapes you use mainly in Powerpoint but sometimes where a simple diagram is needed in Word or you want a goofy piece of clipart in your document.
Its a cool feature but how can you use it in a reporting context? Well the extension of the simple shape support is the ability to manipulate the shapes, you can dulplicate along x and y axes, stretch, skew, rotate, shrink and stretch them ... so what? Well you could think about building your own data visualizations using shapes to build up a 'chart' to represent the data. As an example I have picked on the 'hot' gauge or speedometer ... admittedly more of a BI type object but its a good illustration and will hopefully spark your interest to think of others.

Heres a fairly common sight in reports these days.

Normally requiring a charting engine to render this output ... you can use your RTF template ... you could even develop a set of objects that could be re-used across the organization.  

Getting in Shape

Apologies, a poor pun ... but you just need to put together a set of MSWord shapes, data enable them and bind them together as a 'group'. At runtime the shapes are then driven by the incoming data in the case of the gauge the amount of rotation of the arrow and the upper, middle and lower measures. In the attached RTF you can see the gauge object, just ungroup it to see how its made up. The only shapes that are manipulated are the arrow and the text areas for the measures and the grouped shape. We manipulate them by putting the controls into the Web tab of the image properties.

We're using the following XML for demo purposes here:

<speeds>
<speed>0</speed>
<speed>20</speed>
<speed>40</speed>
<speed>60</speed>
<speed>80</speed>
<speed>100</speed>
</speeds>

1. Background 'ring', this is made up of 3 'donut' rings where I have reduced the angle using the yellow carat in MSWord and then colored and rotated them as appropriate to create the desired layout. You could of course play with this and used graduated shading from red to yellow to green or even just use a single half ring and shade it. No controls here, its just a static object.
   
2. The arrow, this is an arrow drawn from the bottom center of the ring - this is important because we are going to rotate it and the engine needs to know about which point to rotate the image ie center- bottom. The code to drive the amount of rotation:
   
   

   <?shape-rotate:number(.) * 1.8;'c/b'?> 

    - shape-rotate is strangely enough the command to rotate the shape
    - number(.) * 1.8 - this the calculation to work out the rotation in degrees. You can see there is no element name '.' refers to the current element. 
   - c/b - is the shortened version of center/bottom ie the rotation point. Now you see how important it is to start the arrow from the bottom.
   
3. The text boxes have some sample test but it is replaced at runtime with a minimum, mid and maximum value for the gauge.
   
   

   <?shape-text:$lowVal?>
   <?shape-text:$midVal?>
   <?shape-text:$highVal?>

    - low, mid and highVal variables are calculated earlier in the template to minimize processing
   
4. Finally we have the command to repeat the entire shape down the page for as many members of the XML as are available ie 6. This command is added to the grouped properties, you need to group the members of the gauge, right click, Format Object.
   

   <?for-each@shape:speed?>
   <?shape-offset-y:(position()-1)*150?>
   <?end for-each?>
   
   <?for-each@shape:speed?>

   The for-each should be familiar, the at shape just tells the engine we're dealing with shapes and looping over the 'speed' group.
   <?shape-offset-y:(position()-1)*150?>
    - this offsets the shape along the y-axis ie down the page. The sneaky bit to ensure that we get a gauge at the position we placed it on the page is yo use the position()-1 expression. Position() returns the record number we are at, first record is 1 and so on. Without the '-1', setting the value to '0' we would not get the gauge showing in the position we initially set it on the template, it would immediately be offset by 150 pixels.

So thats the gauge, not that tough to do and with a little time you can build a much better looking gauge than I did but you get the idea. You could even just put in a background image of your choice so you get something more realistic.

Heres the sample and Happy Driving !

Here's an interesting problem that hit my inbox this morning that had me scratching my head for a while. I need to create the following output:

easy  I hear you say, but heres the catch - no tables allowed!

Ahhh ... you have me ... well at least for a while. BIP has a little know feature that allows you to show data inline, 'for-each@inlines:XXXXX', the user guide goes some way to explain, the best way to find out whats going on is to experiment. Just using the @inlines command I got

Employee:  Ted Higgins Jon Swanson Ivor Problem || 
Helen Back

Assuming the || is the edge of the page. Using:

Employee: <?for-each@inlines:G_EMP?><?NAME?><?end for-each?>

So the next step was to start looking at MSWords indenting capabilities and just how far BIP goes in supporting them :o) So I highlighted the whole line above, right click -> paragraph and on the resulting dialog selected a hanging indent, set it it to 1in to get things sitting under the word Employee correctly. But after generating the output I was left with:

Employee:  Ted Higgins Jon Swanson Ivor Problem || 
               Helen Back

Almost there but no cigar! I needed to have a carriage return after each name. After a little fiddling and googling ... for 'xslfo inserting new line characters' ... which yielded very little .. I tried adding an fo:block after each name et voila it worked. Not very scientific maybe but thats how things are discovered much of the time. Now I have the list as I wanted ... Im sure there are other ways to acheive the same result and if you find them please share. Sample files here ... 

Apologies for the tenuous title, I went looking for a synonym for today's subject and came up with this, anyway, moving on ...

Have you ever created a summary column, run your template against some data only to find it either falls over or you get that friendly 'NaN' entry in your output frustrating isnt it? Just to clear up one thing, 'NaN' means surprisingly enough, 'Not a Number', if you want to know more check out the definition here.

The most common cause of the issue is you have a null value in your XML ie <AMOUNT></AMOUNT> and sadly the XSLT engine does not know how to handle it. 
So how to handle nulls? We now have a decent section in the user guide on handling nulls buts here's a couple.

Lets assume we have the following XML:

<AMOUNTS>
<AMOUNT>100</AMOUNT>
<AMOUNT></AMOUNT>
<AMOUNT>200</AMOUNT>
<AMOUNT>300</AMOUNT>
<AMOUNT></AMOUNT>
</AMOUNTS> 

If we just do a simple summary to add up the amounts sum(.//AMOUNT) we would get the NaN output. We have two options to get around this:

1. Check for the Null first

We can use an XPATH expression to test the value before adding it:

<?sum(.//AMOUNT[.!=''])?>

2. Use the BIP Sum function

A little less effort, we can remember to use the BIP built in function xdoxslt:sum. This will check for null values for you.

<?xdoxslt:sum(AMOUNT)?>

Both options work of course, of the two for simple summing either will work fine, if you have a more complex calculation then the second option is going to be better. Thats not to sat you could not use the XPATH but things start to get tedious checking all your values for nulls when the BIP sum function will do it for you. Before you ask, yes the Template Builder should give you the option and insert it for you ... I have logged the ER. Samples here.

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.

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.

We had a question come up on an internal mailing list yesterday that got me thinking. Can BIP/XMLP add OMR marks to documents? For the uninitiated, OMR, or Optical Mark Recognition, in one of its simpler forms is the series of 'slugs' (technical term) you see on the right hand side of your paper phone bill. They are short 1/2 inch (1.2cm ... sorry Europe Im all inches now :) lines that tell a paper folding machine where to fold the paper prior to sticking the sheets into an envelope.

This is not the only application of OMR, the routing and account numbers on checks are of the same ilk i.e. machine readable. I have covered checks in other postings and there is a white paper on the BIP OTN pages. Those of you in Europe and maybe the rest of the world that have taken 'multiple guess', sorry 'multiple choice' (MC) exam papers and have had to fill in the appropriate ellipse shape corresponding to what you think is the correct answer are using OMR technology.
I always thought these were the easiest exams until I got to university. My Applied Chemistry professor announced to a greatly relieved class that our mid and final term papers would be in a multiple choice format ... fan-tas-tic! Oh how wrong we were! My experience of MC thus far had been of the four choices, you could throw out one as being completely ridiculous. As long as you knew something on the subject, another two were 'dodgy' so that left the correct answer - easy right. Our professor put a twist on the format:

• We had 200 questions
  
• 5 choices not 4 - none of which were obviously wrong
  
• Get a question incorrect and you lost 2 points, dont answer and lose 1 point - this was done to put off the student who hedged their bets and relied on the 20% chance that they would get questions right if they picked ABCDEABCDEABCDE .... we had some real high achievers in our classes :o)

Anyway, I digress ... badly. So, just how can you add those marks to your output? 
There are commercial OMR fonts out on the market but for the purposes of marking paper for folding we have found that you can either use the '-' or '_' characters or a simple line drawing to creat the slug. All of the 'folding' manufacturers publish the dimensions of the the slug they need so its just a case of changing the font size or the line thickness/length to the desired size. There are a couple of options both work but it depends on your requirements.
a. Some machines just require marks to tell it where to fold, maybe 2 or 3 per page or
b. Requirements are more sophisticated, checksum slugs that need to appear only on the last page or on eavery X number of pages.
Options:

• Use line drawings - I have covered elsewhere how you can manipulate drawings in the RTF templates. You can use the same technique to create the necessary 'slugs' and then repeat them down the page on every page unless you put in a section break. This method can and does work for 'a'
  
• Use PDF APIs - assume your PDF has been generated. You can now use the PDF APIs, taking advatage of the watermarking method you can add the slug to specific x,y positions on particular pages. We have customers using this method combined with the page counting API and the bursting engine.
  
  
  
  1. Burst a set of documents and subscribe to the BurstListener,
     
  2. as the document is generated, grab it, count the pages - FormProcessor.getPageNumber() 
     
  3. calculate where and then apply the 'slugs' to each page appropriately - PDFDocMerger.setTextXXXX - where XXXX are the methods to set text, font and position. 
     
  4. deliver document to paper folder

You do not have to use the bursting engine to add the marks. You can add them to any PDF document, you could create a printer driver that called the API prior to sending the document to the printer so you do not have to interrupt or meddle with the concurrent manager - I covered how to do this in the EBS Delivery series.

Of the two, 1 is simpler but of course only meets the simpler requirements. Option 2 requires java (simple java mind you) and a little custom work but its well within your means.
Happy Slugging!

.. dats cos I god a code and my dose is bunged dup.
No I did mean 'a date addition', not a 'late addition'. Calculating dates has been tought with XSL until 2.0 arrived when things got much easier. Sadly our XSLT engine does not yet support that part of the 2.0 standard so we can not take advantage of the date manipulation functionality. We have provided some help for you thou to carry out simple date caluclations.

We have a funtion that will take an XSD date, remember them, a number of days or an offset and an operator. It takes the following format: 

<?xdoxslt:ora_format_date_offset(date, offset, 双perator?)?>

Using it in anger we might have:

<?xdoxslt:ora_format_date_offset(INVOICE_DATE, INVOICE_TERMS, ?+?)?>

This would take the invoice date and add the number of days in the invoice terms element to arrive at a payment due date. We do not have native abilities to subtract one date from another, or add them. To do that right now will require you to treat dates as strings or format them to a julian format, perform the calculation and then reformat ... all very messy. We'll make it better in the near future I hope.

Another great cartoon from the past ... so to something a little more useful. A nice real world problem from a custmer today. Say you needed an output that showed a chart and a table beneath it ... you had no idea how much data was going to hit your report at runtime but you wanted an output looking something like this.

Notice the chart has no column labels or legend, you would add this to the table rows, the idea is that the table columns line up with the chart columns and data points. Pretty nifty eh? It can be done with publisher relatively easily using its support for dynamic data. It requires a little 'alignment' to get table columns lined up with chart columns but thats just a little jiggery pokery.

Whats going on in the template then?

Well the chart is pretty standard we have covered multiple y-axis entries in previous entries.  So the only tough bit we have is the table. In the first column we have fixed row headers, we could handle dynamic data here if we wanted. To get the second column to handle the dynamic data we set its width to that required, in this case the width of the plot are of the chart, just use your mouse to drag the table border. Notice the red arrows in the following image marking the table column alignment.

It might take a little adjustment to get things aligned exactly but its only a few iterations with the template builder.

In the fields we are using, for Time

<?split-column-header:G_MEASUREMENT?><?FINISH_DATE_DISP?>

The split-column-header just tells the rendering engine to split the available space qually between the number of ocuurences of the G_MEASUREMENT group, then the FINISH_DATE is inserted.

The next rows handle the specific data elements:

<?split-column-data:G_MEASUREMENT?><?PROJECTED_FORECAST_AMOUNT?>

They work just the same way. The whole split column functions can be a little more sophisticated if needed. In this case we wanted equal columns across a given width, we could also specify widths for each column either as a percentage of the total width. Just note the limiting factor to the number of columns shown in the table is going to be how small you can make that font and still make it readable for your users and I guess how messy that chart is going ot get.

The other case we did not need to handle here but publisher is up to it, is something along the lines of the 'Z' printing format in Excel. Let's assume we had a report with 50 columns in it, ordinarily publisher would reach the edge of the page and truncate the report. With the dynamic column support we can generate reports to say PDF that behave like Excel when it prints i.e. print columns across the page until we can not fit anymore, drop to the next page and continue, repeating the row header if we wish, we continue like that until the columns are exhausted. If we had too many rows on a page then we would start at column 1 and go through again until all the rows had been rendered to the page.

This technique is pretty well covered in the user guide and the Template Builder install provides another example showing how to specify the width. You can get the sample RTF, XML and output here. 

I have posted about the new server side versions of BIP (10.1.3.2.x) and EBS (5.6.3) being available now ... what I neglected to mention was the availability of a new version of the Template Builder for Word. There are some new enhancements for both BIP and EBS user alike:

• Crosstab Wizard




• Multiple Levels


• Level and report Totals


• Enhanced Chart Dialog




• Multiple Line Charts


• 3D Charts with gradient fills


• Real time preview


• Table wizard


• Translation testing support


• Template Validation Checker

Those of you using BIP Server you get a few more bells and whistles. Rather than download sample data to your desktop and manually load it to the template builder, you can now connect to the BIP server, find a report, create a new template or modify an existing one, test and then upload to the server from the builder ... makes the whole development process much smoother. 

You can get the new version from the OTN download page, http://www.oracle.com/technology/software/products/publishing/index.html

Get Templating !

 

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.

Question on the forum this week ...

Hi,

I need to build a template like below, in my sql query i have employee details.
i need to display every five employee details in table then 5 in subsequent tables.

so how to split columns..
help me soon.

see below template:
--------------------------------------------------------------------------------------
Description emp1 emp2 emp3 emp4 emp5
--------------------------------------------------------------------------------------
empno 1 2 3 4 5
empname a b c d e
salary 100 200 500
---------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
Description emp6 emp7 emp8 emp9 emp10
--------------------------------------------------------------------------------------
empno 6 7 8 9 10
empname a b c d e
salary 100 200 500
---------------------------------------------------------------------------------------

A quick point on questions on the forum, please please please let us know the following:

1. BIP/XMLP version being used
2. What flavor? EBS, PS, JDE, Standalone or using APIs
3. A snippet of XML data if appropriate to the question
4. Template layout - its tough I know but it helps
5. Logs, config files, etc - post the contents of them

Im closing in on 1000 replies and I wonder how of those are 'Hi XXX, Please supply info A, B and C then I can help you. Regards, Tim' format. Lastly, please provide a forum name, its so much better replying 'Hi John' or 'Hi 60 Sausages' - yes we have a Mr. 60 Sausages on the forum ... than replying to User00000 ... who are you?

To the question, were I feeling mean I'd reply, check the user guide, there is enough in there to work out how to do it but it makes for a nice demo so Naresh ... you're lucky :o)

We need to use the @cell command to get the rows of data to display as columns, I assumed we had a data set like this:

<ROWSET>
   <ROW num="1">
      <EMPNO>7810</EMPNO>
      <ENAME>Jo Bloggs</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW num="2">
      <EMPNO>7800</EMPNO>
      <ENAME>Jane Doe</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </ROW>
 ...
</ROWSET>

All we need to do is loop over each member of the ROW group using the @cell command

<?for-each@cell:ROW?>

this will get us the row values rendering as column values, However the requirement was to show 5 at a time and then break to a new table.
Introducing an XPATH command can achieve this for us:

<?for-each@cell:ROW[position()<6]?>

this means that we will loop over the first 5 ROWs and then stop. You can then introduce a second instance of the template where the XPATH expression presents a range of ROWs to loop over.

<?for-each@cell:ROW[position()>5 and position() <11]?>

this will loop over 6 through 10.

So our template will look something like

and generate output like

Now, we have hardcoded things here and here are the sample files. But you could quite easily wrap a loop around the table that repeats if for as many rows as is needed based on the incoming data. Im not going to do it just yet ... have a go.

Short and sweet today but important none the less ...

We finally got to the bottom of the issues some of you were experiencing with the Template Builder when trying to connect to the BIP server in 10.1.3.2.x. There was a problem in the .NET compile environment we were using, got that fixed and rebuilt the plugin and things are now fine. There is a new download available from the download page on OTN, please hold off on the download until after 1pm PDT when the new file will hit the streets:

http://www.oracle.com/technology/software/products/publishing/index.html

You must uninstall the original version you have and install this one afresh but the connectivity issues will be fixed.

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.

Question on the forum today asking how you might use some meta information stored in the incomng XML data to format the final output i.e. override any formatting you might have created in the RTF template. I covered how to do this if the XML is actually HTML e.g.

<TEXT><B>Report Title</B></TEXT> 

You can check that out here.

What to do if you have something like the following:

<?xml version='1.0'?> 
<POEM>
 <TITLE format="bold">The Crazy Dog</TITLE> 
 <TEXT format="italic">I've seen many strange dogs but this is the one.
you should see some of the things hes done.

I've seen him jump and try to eat sand.
he had a mouth full of dirt as soon as he would land.

if you saw him you'd probably giggle.
if you do you have probably seen him squiggle.

the strange thing is hes never snappy
but he is always happy!

every one knows him now
and he says to you "bow wow!"
 </TEXT>
</POEM>

Notice the format attributes specifying how the text shuld be formatted.
We need to use some of the techniques from the HTML formatting article to specify the formatting required using the XSLFO syntax. Keeping things simple we can just use simple conditional 'if' statements to check the value of the 'format' attributes. Using the TITLE element we can use:

<?if:@format='bold'?>
 <xsl:attribute xdofo:ctx="block" name="font-weight">bold</xsl:attribute>
<?end if?>

Similar approach for the italic or any other attribute. You could build a subtemplate to handle all of the possible 'formatting' options to simplify your coding and making it more dynamic. Get ya example files here.

In case you're wondering, the poem is from my son about our new puppy.

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.

Got this great question today ... 

Is it possible to check whether a document generated by BI publisher fits 1 page?
If it's more than one page then we want to reduce the font until it fits on a single page.

Its a nice question to which I can quickly answer 'yes - potentially' - but before I could really say 'yes' thou, I needed to put my money where my mouth was and prove it. Finally, I have put together 10mins here and there to get something that works. Its a simple approach and will probably need some more work for production but the solution shows off some of the APIs we have and how they can be used to solve this problem.

From 10,000 feet

Getting the answer to the first part can be done quite simply but only after the final document has been created. Sadly, you can not use a nice 'if' statement in the template to check if you have more than one page and if so then reduce the font size until it fits. Page numbers can not be determined until the rendering engine has done its stuff and laid the data on the page. So it has to be a post generation check.

We need a flow such as

1. Set font size to X
        |
2. Generate Output
        |
3. Test page numbers
        |
4. If page number > 1 
        |
5. Set font size  X=X - 2 or some other number
        |
6. Goto 2

7. else End

Its a nighmarish BASIC program from my distant youth ... arrrrgggghhhh!

From about 12 inches

Lets get step 3 out of the way first cos its the easiest - there is an API we can use to count the number of pages in a PDF document.

Under the FormProcessor API there is a method getPageNumber(). We are going to use it on a completed document, we need to use the setTemplate method and pass it our completed document.

    int numPages;
    FormProcessor fp = new FormProcessor();
    fp.setTemplate("c:\\temp\\1.pdf");
    try {
            numPages = fp.getPageNumber();
            System.out.println("Number of pages: "+ numPages  );
       
        } catch (Exception e) {
            e.printStackTrace();
        }

Straightforward stuff really but as I said thats the easy piece. The other part to this is to change the font size in the template until it fits on a page. I have been playing with a sample template but have put it aside for now. If we had written an XSLFO template by hand we could easily use a variable in the template for the font size and pass that each time. But we are using RTF templates, so we need some logic to update/override the font-size attribute, remember it will have been set when you create the layout in the template. Im not even sure a template can be written that updates itself during processing ... if there are any real XSLT experts out there let me know and I'll post the solution. It's going to be simpler ...

The other steps ...

For now I have dodged the issue and use a parser to look for the relevant font-size attribute and update it to its current value -2 in the generated XSLFO template file from the RTF template.

So our java logic for the whole process will be :
1. RTF -> XSL
2. XSL+XML data - > PDF
3. Count pages
4. If > 1 page then use a parser to find instances of 'font-size' and 'height'. Assign initial value found to a variable, then reduce this by 2 points for all values.
5. With the new XSL+XML -> PDF
6. Retest page numbers and repeat as necessary.

The java class I have written is not perfect but I think you can easily use it as a start for a full solution. Once the intial XSL has been generated and the resulting PDF document tested for page numbers it then parses the XSL template using a DOM parser. This looks for the 'font-size' and 'height' attributes under the 'inline' elements and knocks them down by 2 pts.

There are surely going to be templates where reducing the font-size and height are not going to be enough but if you keep things simple you can do it. You could even get into scaling images as well so everything remains in proportion. My template is simple and heres where you may need to modify the class to handle some of the other 'height' and 'font-size' attributes if present. Here's the code with some annotation:

package xdotestbed;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
 
import java.io.OutputStream;
 
import oracle.apps.xdo.XDOException;
import oracle.apps.xdo.template.FOProcessor;
import oracle.apps.xdo.template.FormProcessor;
import oracle.apps.xdo.template.RTFProcessor;
 
import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.parser.v2.XMLElement;
 

import org.w3c.dom.Document;
 
import org.w3c.dom.NodeList;
 
import org.xml.sax.SAXException;
 
public class FitSinglePage {
    //declare class variables
    String rtfTemplate;
    String xslFile;
    String pdfFile;
    String xmlData;
    XMLDocument newDoc;
    
    public FitSinglePage(String rtfF,String xslF,String xmlF,String pdfF) {
        //Assign program parameters
        rtfTemplate = rtfF;
        xslFile = xslF;
        xmlData = xmlF;
        pdfFile = pdfF;
        
        //Initial RTF -> XSL conversion
        processTemplate(rtfTemplate);
        // Initial PDF generation using XSL above
        generateOutput(xslFile,xmlData,pdfFile);
        
        // Wrapping a loop to stop the process running away
        //if it can never fit on a single page
        
        for (int x=0; x<11; x++){
        //Test the number of pages of the resulting PDF,
        //keep going until it fits 1 page
        
        while (countPages(pdfFile) != 1) {
            // Assign the updated xslFile to an XMLDocument instance
            newDoc = parseTemplate(xslFile);
            // Write out the template to the same file name
            writeTemplate(newDoc);
            // regenerate the PDF document with the adjusted
            //font size and height settings
            generateOutput(xslFile,xmlData,pdfFile);
           }
        System.out.println("Success!");
        break;
    }
    
} 
    
public void generateOutput(String xslFileLoc,String xmlData,String pdfFile){
    // This method will generate the PDF output each time
    
    FOProcessor fop = new FOProcessor();
    fop.setData(xmlData);
    fop.setTemplate(xslFileLoc);
    fop.setOutput(pdfFile);
    fop.setOutputFormat(FOProcessor.FORMAT_PDF);
    try {
        fop.generate();
    } catch (XDOException e) {
        e.printStackTrace();
    }
 
}
public void processTemplate(String rtfFile) {
    // Only called once to create the initial XSLFO template
    // from the RTF template
        try {
            RTFProcessor rtfP = new RTFProcessor(rtfFile);
            rtfP.setOutput(xslFile);
            // this prevents the processor generating attribute sets
            // You could allow it but it would require changes to the
            //the parser code
            rtfP.setExtractAttributeSet(RTFProcessor.EXTRACT_DISABLE);
            rtfP.process();
        } 
        
        catch (XDOException e) {
            e.printStackTrace();
        }
        catch (IOException ioe){
            ioe.printStackTrace();
        }
    
    }
 
    public void writeTemplate(XMLDocument newTemplate){
    
    //Write the updated XMLDocument to the XSLFO template file
    
    OutputStream os;
    try {
        os = new FileOutputStream(xslFile);
        newTemplate.print(os);
        os.close();
         } 
    catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    catch (IOException ioe){
        ioe.printStackTrace();
    }
       
}
 
public static void main(String[] args) {
        
FitSinglePage fitSinglePage = new FitSinglePage(args[0],args[1]
                                                        ,args[2],args[3]);
    }
 
public XMLDocument parseTemplate (String templFile){
       // Parse the XSLFO template method
        DOMParser dp = new DOMParser();
        try {
            InputStream inp = new FileInputStream(templFile);
            dp.parse(inp);
            inp.close();
        } 
        catch (SAXException e) {
            e.printStackTrace();
        }
        catch (IOException e) {
             e.printStackTrace();
        }
        XMLDocument tDoc = dp.getDocument();
        //Grab all instances of the 'inline' element and their children
        NodeList ns = tDoc.getDocumentElement().getElementsByTagNameNS
                             ("http://www.w3.org/1999/XSL/Format","inline");
        XMLElement attrVal;
       //Loop thru the inline elements
        for (int i = 0; i < ns.getLength(); i++)
    {
        attrVal = (XMLElement)ns.item(i);
      //Change the font Sizes
       if (attrVal.getAttribute("font-size").indexOf("pt") != -1)
       { 
        //System.out.print("Number: "+i +"::" 
                         //+ attrVal.getAttribute("font-size")+"\n");
        //Get the font size value e.g. 12.0pt
 String fontSize = attrVal.getAttribute("font-size");
        
 //Strip out the 'pt' part to leave a number e.g. 12.0
 String fontVal = fontSize.substring(0,fontSize.indexOf("pt"));
        
 //Set the new value and add the 'pt' back in e.g. 10.0pt
 attrVal.setAttribute("font-size",
 (Double.parseDouble(fontVal)-2) +"pt");
        
 //System.out.print("Number: "+i +"::" 
 + attrVal.getAttribute("font-size")+"\n");
        } 
        // Change the row heights
        if (attrVal.getAttribute("height").indexOf("pt") != -1)
        { 
         //System.out.print("Number: "+i +"::" + attrVal.getAttribute("font-size")+"\n");
         String heightSize = attrVal.getAttribute("height");
         String heightVal = heightSize.substring(0,heightSize.indexOf("pt"));
         attrVal.setAttribute("height",(Double.parseDouble(heightVal)-2) +"pt");
         //System.out.print("Number: "+i +"::" + attrVal.getAttribute("height")+"\n");
         } 
        
    }    
 
    return(tDoc);
}
 
public int countPages (String outDoc) {
   //Count the number of pages in the generated PDF document
    int numPages = 0;
    FormProcessor fp = new FormProcessor();
    fp.setTemplate(outDoc);
    try {
            numPages = fp.getPageNumber();
            System.out.println("Number of pages: "+ numPages  );
        
        } catch (Exception e) {
            e.printStackTrace();
        }
    return(numPages);    
 }
}

You can also get the class here, both compiled and not along with the template and XML data. You can run the class from the command line passing in 4 parameters :

java xdotestbed.FitSinglePage rtfFileName xslFileName xmlFileName pdfFileName

you'll need to set your classpath accordingly, substitute your values for the parameters above e.g.

java xdotestbed.FitSinglePage 1.rtf 1.xsl 1.xml 1.pdf

 and you'll need the following libraries:

aolj.jar - EBS lib, required even for standalone
bicmn.jar - BIBEans for charting
bijdbc14.jar - BIBEans for charting
bipres.jar - BIBEans for charting
collections.jar - Needed in mailing
i18nAPI_v3.jar - internationalization lib
share.jar - general library
versioninfo.jar - anothe EBS lib
xdochartstyles.jar - for charting on the publisher side
xdocore.jar - core library
xdoparser.jar - publisher parser
xmlparserv2-904.jar - XML  lib
xmlpserver.jar - XML  lib

You may not need all of them depending on whats in your template but they are all easily grabbed either from the standalone server install or the Template Builder for MSWord install directory.

Summing Up

It seems quite a niche requirement but if you need this type of functionality then the APIs and an XML parser can help. I could even see the need to manipulate the template repeatedly for other requirements. I chose a DOM paerser because templates are not that big. It would not be a huge task to move this over to SAX.
Overall, I at least had some fun and frustration building the solution  and if nothing else you got to see a few more APIs. 

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

Those of you working with the E Business Suite will probably have come across the Financial Statement Generator (FSG) from the General Ledger application. Of course publisher can now be used to format XML output from the FSG engine. The Lance Reedy from Solution Beacon website has written up a good article on how to use FSG with publisher or vice versa of course. Its a great introduction to the solution.

http://solutionbeacon.blogspot.com/2007/07/simple-tutorial-for-publishing-fsg.html 

 Im sure you're going to want to take things a bit further with the output, just be aware that the FSG XML format is not the most friendly in the world. If you get stuck check the forum first, there have been several threads on the vagaries of the XML and how to manipulate it. Thanks for the article Lance. 

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. 

Apologies for the brief hiatus ... I have meeting my nemesis, our local Dentist. I have a somewhat irrational or maybe not for some of you, fear of that 'chair'. Im sitting here drinking lunch and I dont mean a beer it's due to the ache in my lower jaw after having a not so wise wisdom tooth removed - more here if you are so inclined, Im not and will not be for some time.

In the last article we had got as far as building the form and adding a button - those of you that want the dynamic insertion will be pleased to hear that I have logged enhancment requests to get that functionality into a release. Now we need to work on a server side object to accept the data, parse it and then insert it into a table. I chose a servlet but a jsp will work just as well, if not better if you are in some business flow, I assume you may even be able to build a page in APEX to accept the data - Im still a relative beginner with APEX so I'll bow to someone elses input on that subject.

The XFDF XML format needs to be handled on the server:

<?xml version="1.0" encoding="UTF-8"?>
<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
<fields>
 <field name="DEPT_ID">
  <value>50</value>
 </field>
 <field name="EMAIL">
  <value>tim.dexter@oracle.com</value>
 </field>
 <field name="FIRST_NAME">
  <value>Tim</value>
 </field>
 <field name="HIRE_DATE">
  <value>01jan2003</value>
 </field>
 <field name="JOB_ID">
  <value>ST_MAN</value>
 </field>
 <field name="LAST_NAME">
  <value>Dexter</value>
 </field>
 <field name="MANAGER_ID">
  <value>123</value>
 </field>
 <field name="PHONE_NUMBER">
  <value>343434343</value>
 </field>
 <field name="SALARY">
  <value>44444</value>
 </field>
 </fields>

Its a simple name-value pair format. We could use a SAX or DOM parser but why bother, Publisher has a parser specifically for XFDF. Again its not documented but it is public and we'll get it documented. For now you need the following:

        ServletInputStream in = request.getInputStream();
        FPXMLParser fpXMLParser = new FPXMLParser();
        try {
            fpXMLParser.parseXML(in);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Hashtable nameValuePairs = fpXMLParser.getSimpleMergingData();

The first line is standard servlet stuff to get the incoming data stream. Then we invoke the XFDF parser and initialize it with the input stream in the try-catch block. There are not too many methods to the parser but the most useful is the getSimpleMerging Data(), this drops the name/value pairs into a Hashtable object for you. Now as you might have realized we're working in memory with this parser, if you have a huge amount of data, and I mean 100megs to handle, think about using the SAXParser instead.

Now we have this object we can use it to populate a table in the data base, we'll need to enumerate through the hashtable and pull the fields and then the values to construct a sql insert statement. In this code you are not going to see any validation of the code - you will obviously need it. You could either put it into the PDF form, it supports javascript and there is ahost of web references on how to write javascript for PDF or you could of course code server side validation. The issue then becomes that if they have made a mistake you need to take their values and represent the form to the user to get it corrected - publisher can do that of course but its up to you.

        Hashtable nameValuePairs = fpXMLParser.getSimpleMergingData();
        StringBuffer sqlStmtBuffer = null;
        StringBuffer sqlColsBuffer = null;
        StringBuffer sqlValsBuffer = null;
        Enumeration fieldNames = nameValuePairs.keys();
        while(fieldNames.hasMoreElements())
        {
          String fieldName = (String)fieldNames.nextElement();
          if("btn_submit".equalsIgnoreCase(fieldName))
          {
            continue;
          }
          if(sqlStmtBuffer == null)
          {
            sqlStmtBuffer = new StringBuffer();
            sqlStmtBuffer.append("insert into employees(");
            sqlColsBuffer = new StringBuffer();
            sqlValsBuffer = new StringBuffer();
            sqlValsBuffer.append(" values(");
          }
          else
          {
            sqlColsBuffer.append(",");
            sqlValsBuffer.append(",");
          }
          sqlColsBuffer.append(fieldName);
          String value = (String)((Hashtable)nameValuePairs
 .get(fieldName)).get(Constants.ATTR_V);
          sqlValsBuffer.append("'");
          sqlValsBuffer.append(value);
          sqlValsBuffer.append("'");
        }
        sqlStmtBuffer.append(sqlColsBuffer.append(")").toString());
        sqlStmtBuffer.append(sqlValsBuffer.append(")").toString());

We take the hastable and assign it to an Enumeration object so we can step through the values. We create string buffers to hold the sql statement (sqlStmtBuffer), the column names (sqlColsBuffer) and the values (sqlValsBuffer).
We are not interested in the button value so we check for the button field, 'btn_submit' and if found, we step over it and continue. The string buffers are built up while looping through the name/value pairs. We need some code to get the value:

String value = (String)((Hashtable)nameValuePairs.get(fieldName))
                                                 .get(Constants.ATTR_V);

This grabs the value from the hashtable object so it can then be inserted into the string buffer.

At the end of the loop we then append the sqlColsBuffer and the sqlValsBuffer to the sqlStmtBuffer and we get the following:

insert into employees(DEPT_ID,MANAGER_ID,SALARY,EMAIL,LAST_NAME,
                       PHONE_NUMBER,JOB_ID,HIRE_DATE,FIRST_NAME) 
values ('50','123','44444','tim.dexter@oracle.com','Dexter','343434343',
                        'ST_MAN','01jan2003','Tim')

Now those of you that have taken a look at the EMPLOYEES table will have noticed that there is an EMPLOYEE_ID primary key on the table, so we would need to generate an employee number from the appropriate sequence.

Not to tease you but we'll pull it together in the next article ...

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

Finally I got a JDE related question yesterday, so I have some content for you JDE junkies out there. Its actually a pretty generic question so if your not a JDE-phile read on its an interesting problem.

Shafeek Abooubaker from a well know consulting firm contacted me with an issue, he had built a PO template for his client but was stuck right at the last hurdle, getting the tax and final total for the purchase order onto the template. I thought he was 'pulling my chain' at first - sorry an English expression. He had managed the tough stuff with the PO lines and I thought the totals were a cinch. Looking at the JDE XML revealed his problem.
Heres a snippet:

<R43500_UTIL0003>
 <Properties>
 </Properties>
 <PageHeaders>
 </PageHeaders>
 <Grand_Total_S50>
  <Variable_000001_ID1/>
  <Payment_Terms_Desc_ID5>NET 7</Payment_Terms_Desc_ID5>
  <Order_Total_Tax_Rate_ID7/>
  <Tax_Order_Amount_ID9>.00</Tax_Order_Amount_ID9>
  <Order_Total_ID10>1300.00</Order_Total_ID10>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Grand_Total_S50>
 </Grand_Total_S50>
 <Purchase_Order_Print_S42>
  <On_Ship_To_S45>
   <On_Ship_To_S48>
    <On_Line_Number_S47>
    </On_Line_Number_S47>
    </On_Line_Number_S47>
    <On_Line_Number_S47>
    </On_Line_Number_S47>
    <Order_Extended_Price_ID13>1300.00</Order_Extended_Price_ID13>
   </On_Ship_To_S48>

I have cut out alot of the data I just wanted to get across the structure. Notice there a series of Grand_Total_S50 nodes, one for each PO in the batch. The main loops for the PO are around On_Ship_To_S45 (header - red rectangle) and On_Line_Number_S47 (lines - green rectangle)

The SUBTOTAL value is in the Order_Extended_Price_ID13 element in the header loop, but the other two requied values are in the Grand_Total_S50 node. The problem is that there is not apparent connection between the On_Ship_To_S45 and the Grand_Total_S50 nodes. The grand totals do not store the relevant PO ID. The only way I could see to link the two was in the record numbers; there are 7 grand total nodes and that matches the number of purchase orders. So we can link on that using some publisher features and some XPATH.

To get to the Tax_Order_Amount_ID9 and Order_Total_ID10 values from the On_Ship_To_S45 (header) loop we use:

../../Grand_Total_S50/Tax_Order_Amount_ID9 or Order_Total_ID10

now we need to introduce the record component i.e. we grab the tax/total value in the relevant Grand_Total_S50 node based on the current iteration of the On_Ship_To_S45 (header) loop.

We can use XPATH's function position() for this:

<?../../Grand_Total_S50[x]/Order_Total_ID10?>

where x is the record position we want i.e. PO #1->x=1, PO#2->x=2 and so on. To get that PO record number we need to use a publisher updatable variable to keep track of the number.

 <?xdoxslt:set_variable($_XDOCTX, 'x', position())?>

we assign the record number to a variable 'x'.
Now in our reference to the tax and total values we need to fetch the current value of 'x'.

<?../../Grand_Total_S50[xdoxslt:get_variable($_XDOCTX, 'x')]/Order_Total_ID10?>

So we can now fetch what looks like an unrelated value into our template, modified template and XML here.

Im not sure if the disconnect between totals and POs was by design in the data extract or something that JDE does for you when you ask for a batch of POs. Ideally you want totals with headers, failing that, totals with references to headers and failing that - what we have just gone through i.e. position based references.

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 wrote about the coming Flex templates in 10.1.3.3 back in April - they are now here and I wrote some tutorial like documentation for the official doc. You can get that here or if you prefer PDF here - Chapter 9. The documentation covers the basics, how does publisher work with Flex templates, how do you build tables, charts and how do you wire up the objects to the data. To go with the tutorial are some Flex project files which are not with the documentation, they'll appear on metalink Im sure but for now I'll post them here.

http://www.oracle.com/technology/products/xml-publisher/demos/flexemployeereport.zip

Just unpack and use the Import feature of the Flex IDE to get them in. I have annotated the code so hopefully in conjunction with the doc you'll be able to make 'head and tail' of it all.

Before you EBS/PS and JDE folks ask - no, sorry, not yet, its a BIP Enterprise thang at the moment ... its coming thou.

I do not get into the 'Flex' of Flex templates but, I have been working with them again recently and if folks are interested I'll post 'tints & hips' as I work them out. There is a growing community of Flex developers building some amazing extensions to Flex that you can use in your templates. I have to mention the FlexLib project where there are a host of extensions - shout outs for Doug McCune , Ely Greenfield and Mrinal Wadhwa for providing such a wealth of info.  If you get stuck in some Flex related issue, try our forum but you are going to get better results by asking the FlexCoders mailing list. I have to admit the Flex documentation is overall, very good and failing those resources just goooogle for anything Flex ... its out there. 

There are truly some awesome things that you can do with Flex that your users are going to be screaming for and loving you for building, so get Flexing.

In 10.1.3.2 we shipped some functionality to allow you anlayze your data in real time - I blogged about it back in May. With 10.1.3.3 we have addressed one of the glaring holes in the 'Analyzer' - the ability to save the built analysis. So you can now save  the analyses you build and recall them as a template at a later date i.e. with fresh data.

There is a new toolbar providing the ability to Save, Save As, etc. You can load an existing template, modify it and save it back or save it as a new template entirely.

Once created, it will appear in the template drop list in an 'Analyzer' section. 

you can view it interactively as before or generate PDF or RTF outputs from it.



The 'paper' formats explode all levels or dimensions of the report. The format is fixed for this release but later you'll be able to modify. Of course the workaround is to create an RTF template where you can change the look and feel to your heart's content.

Get BIPnalyzing!

I have had a few enquiries recently asking why there is a dependency on MSOffice to create templates for publisher - actually there is no dependency. Yes, we have invested in Office by producing a plugin to MSWord to help you build templates, but there is nothing to stop you using an alternative editor to build templates. That said we have not certified on the alternatives but some early testing has shown some promise, there are some issues but we have pinned some of these down to the RTF version that we certify on not being supported in the alternative application.

The biggest difference is that the alternatives either do not have formfields or they have their own versions of them that are not compatible with MSWord's versions. Open Office is a prime example - they do have formfields but they do not have a 'help' tab in which to embed the code for the template thus you end up needing to write the command directly into the document. This leads to a very busy template but it will work. 

Of course, there are alternatives,

• PDF templates - these lack the conditional logic available in RTF templates but are great for static documents.
  
• Flex templates - a new and exciting template type - not great for paper but for building interactive reports its great.
  
• XSL-FO templates - under the publisher covers lies an XSLFO engine that conforms to the W3C standard.

There is another alternative on the horizon - the Online Template builder - not a greatly imaginative name at the moment but this is going to be an all singing, all dancing DHTML/JScript application that allows you to build templates. It's not yet production and its not quite to the level of the RTF template sophistication but the gap is closing fast. Its a drag and drop environment with Office 07-like toolbars - very friendly and we hope pretty intuitive - currently going through user testing as I write.  I think Im allowed to at least provide a couple of screen shots:

Based in the browser, first thing you'll get are some default layouts to work with.

Now, you can create objects - charts, tables, sub totals, etc - with the drag of a mouse. Notcie the ribbon tool bars and the property palettes for minute control.

You're gonna have to wait a little while longer for this ... it'll be worth it I promise.

Theres a long thread out on the forum concerning 'totals' or more accurately 'sub totals' and the fact that they are not showing or they are incorrect. I'll get to that but the crux of the problem is to first understand the structure of the data you are using. Back in the heady years of release 4.5 - not so long ago really, it was November 2004. We were like a teenager on a first date, a little awkward, full of energy and ready to impress - but we had a problem. You could build your layout templates in Word but there was no help i.e. no template builder. It meant that you had to build the templates by hand - by today's standards, tedious to say the least.

There was one advantage thou - it forced you to look at the structure of the data to understand how to construct the layout to 'walk' that data hierarchy. Today, we do our best to hide all that and for 'flat' data we provide the means to restructure the rendered data in the output. Becasue you dont spend some time on the knowing the structure then when you are starting out with the template builder or even creating totals from scratch its a little tough to understand. Let's deal with the easier structure to understand first:

Say we have the following:

<INVOICELIST>
 <G_VENDOR_NAME>
  <VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>
  <ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>
  <G_INVOICE_NUM>
   <INVOICE_NUM>981110</INVOICE_NUM>
   <INVOICE_DATE>10-NOV-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
   <ENT_AMT>122</ENT_AMT>
  </G_INVOICE_NUM>
  <G_INVOICE_NUM>
   <INVOICE_NUM>00s</INVOICE_NUM>
   <INVOICE_DATE>07-JUN-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
   <ENT_AMT>100</ENT_AMT>
  </G_INVOICE_NUM>
  <G_INVOICE_NUM>
   <INVOICE_NUM>FI1009</INVOICE_NUM>
   <INVOICE_DATE>10-MAY-04</INVOICE_DATE>
   <INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>
   <ENT_AMT>1220</ENT_AMT>
  </G_INVOICE_NUM>
 </G_VENDOR_NAME>
 ...
</INVOICELIST>

This layout will generate a vendor name/address followed by a table of its invoices. We want a total at the bottom of the invoice table for each vendor. Notice in the table we have a 'for-each:G_INVOICE_NUM' in the first cell and and 'end for-each' in the last cell. This will force the template to loop over the invoice section of the data and render one on each row of the table.
To add a total for each vendor we need to add a field before the closing field for the G_VENDOR_NAME - we can either add a row to the table and insert the total (1) or put a value directly under the table (2).

The contents of the 'sumENT_AMT' in both cases will be:

<?sum(ENT_AMT)?>

because the field position is inside the end G_VENDOR_NAME field then the total is only calculated for the current level ie at the vendor level. If we out the field outside of the vendor field then we would get a total for all the vendors.

As I mentioned earlier the template builder plugin will do this for you but hopefully you can now see what its actually doing. Check out the 'by Example' document and the samples folder under the template builder install directory. You can get the files for the above example here.

 Next we'll take a look at 'dynamic totals' when we re-group the data in the layout template ...

This was going to be a holiday posting but today I found Bill on the forum who I think might actually be able to use this post ... funny world. That said I may be completely 'off' and the following prose is completely useless.

Bill's question - I want to make the report colorful, the color should be same for same group, the color should be change for every other group. Kind of did some research but cannot figure out yet.

You'll see in my follow up question - do you want colored fonts or backgrounds - either are pretty straightforward. The background needs a trick but nothing like making an elephant disappear more like hiding a coin behind a 2 year olds ear.

Background

Font

If we take the font colors first, they are easy, just change the font color for each group you have in the template. So in my case the vendor group is red and the inner invoice group is green.

For the background, all I have done is put the whole template inside a table except for the Vendor group fields. Then just color the table first and then the inner grouping table so its kinda on the top of the first table.

and you're done. Of course if you have multiple groups, just color accordingly. Sample files here. Hope thats what you were after Bill ?

A Friday quickie from HQ - how do you default a template for EBS 11i or R12?

System Administration > Concurrent > Programs - notice its Administration not Administrator

and you're done. No you can not default an output format ... yet!
Told you it was quick.

An oft used term from my football (Americans read 'soccer') coach back when I was knee high to a grasshopper and we were doing 'header' practice. You dont need those brain cells just launch yourself at a football and get it in the net (goal) - there was a knack to not getting a blinding headache - my method, just avoid heading the ball altogether, just chest it down and volley it into the goal - I was that good, honest! OK, may be not that good and what the hell am I talking about I hear you ask?

Headers ... and footers for that matter in your templates! I was on a call yesterday with a JDE customer that has been implementing BIP and has been struggling with the headers in their templates. We have a command available in the templates, start|end body its a way to get headers and footers into your template without the need for the Word versions. The customer was using them and experiencing problems, now the command has its place but I have never needed it in all the templates I have built and I have built a few. The comment came up that its tough to work with Word headers and footers - this stemmed from the fact that Word does not let you put their Formfields in the header or footer areas. There are ways and means to get around this that the customer was not aware of ... I thought I'd share a couple of those with you readers out there.

So, no formfields allowed in the header or footer but you can put the clear text command in those regions. By that I mean the contents of the formfield e.g. <?CUSTOMER_NAME?>. I can therefore put as much as I want in the header/footer thus:

All well and good but not good looking and maybe a little intimidating to the business user who asked of IT,
   'Hey, I want to have the currency code column to appear after the invoice amount in that table'
to which the IT dept replied,
   'You got MSWord on your desktop?'
   'Yep'
   'Heres the template, get on with it!'

OK, maybe a little blunt but you get the idea. On a side note we are seeing the 'business' getting more and more involved in building layouts. Instead of it being a pure IT nightmare trying to keep everyone happy the business has the tools (MSWord) to make the changes they want. We now have a customer that has gone the whole way and the business is completely responsible for building, mainitaing and enhancing templates for their users - making for one very happy IT department!
Getting back on track, there is an alternative to the raw commands in the header you can create, a little confusingly a 'template' in your template - think of it as a function that will render output for you when called.

Notice the layout is created in the body of the main template and wrapped in <?template:XXXX?> commands - we then call that from the MSWord header of the template - simple stuff. Samples files here.

Now, you can get more sophisticated and completely externalize the header template into a separate RTF template and use it as a 'sub-template' - now you can share the header across multiple templates by just calling it. You can even pass parameters into the 'header' template, say the 'report title' - I have talked about that a little in this blogs second ever post back in 2006

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

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

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

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

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

You can get the template and data here.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

You will then be prompted for the source and target XSDs

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

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

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

<?template:Header1?>

... header 1 layout table, etc

<?end template Header1?>


<?template:Header2?>

... header 2 layout table, etc

<?end template Header2?>

<?template:Footer1?>

... footer 1 layout table, etc

<?end template Footer1?>

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

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

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

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

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

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

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)

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

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

<?for-each://*?>

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

<?end for-each?>

this pushed out

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

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

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

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

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

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

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

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

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

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

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

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

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

Modify it so that it's type is Date.

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

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

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

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