Oracle BI Publisher Blog

When you create a simple report that contains for example a single table, many users would like to have a single template that gets the column by which the table is sorted as a parameter - so that the end user can easily change it. Lately, I got this functionality working  and thought I share it.First you will need to define a parameter:

<xsl:param name="order" select="'SALARY'" xdofo:ctx="begin"/>

Next you will create a table that is sorted for example by name This can be accomplished easily with the table wizard. The resulting for-each and sort statements are:

<?for-each:ROW?>
<sort:NAME;'ascending';data-type='text'?>
?

So in a first attempt we would replace NAME with $order to use the parameter to sort:

<?for-each:ROW?>
<?sort:$order;'ascending';data-type='text'?>
?

This does not work? Why? Well, $order contains a string, while the <?sort:$order ?> statement which is translated into a <xsl:sort select=?$order?/> expect a node as a parameter and not a name. So how do we get the SALARY node from a variable that contains the text 鉄ALARY??

We can get the node by adding a predicate using the [] syntax. We first use the expression that matches any childnode: ./*. Now we add the condition that the name of the child node equals the content of the variable $order. The resulting expression is:

./*[name(.) = $order].

So we replace the NAME not with $order but with ./*[name(.) = $order] and achieve

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='text'?>
?

This worked fine, with one caveat: The sort always sorts alphanumerical and not numerical ? meaning 1000 comes before 2. You will need to change the data-type to achieve correct sorting. You could achieve that with an if statement to set the data-type depending on the field name and run into some other issues ? or pass the type as a parameter.

If you want to use a parameter (e.g. with the name type) in for the data-type, you need to know that this field expects a string and not a node. If you write:

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='$type'?>
?

your sorting will fail, because it will use the name of the variable $type as the data type and not its content. So you will need to user {} around the variable to substitute an XPATH expression or a variable inside the text. The final expression is:

<?for-each:ROW?>
<?sort:./*[name(.) = $order];'ascending';data-type='{$type}'?>
?

and your dynamic sorting should work like a charm? Unless you want to use an if statement to set the data-type depending on the parameter name ? but that again is a different story to be told at a different time.

KF

Dave M followed up with a question on the regrouping totals post from a day or two ago.

Would it be possible to reference the group sum values from outside of the loop? For instance, if you wanted to have a separate table at the bottom of the report that contained the department totals for each department.

Of course the answer is 'Yes', but of course there are several ways you could tackle it. You could use our updatable variables to track totals against each department as you move through the XML tree. Not ideal and probably expensive.

You could loop through everything again in the summary section.

<?for-each-group:ROW;DNAME?> 
 <?DNAME?> <?sum(current-group()/SAL)?> 
<?end for-each-group?>

Its another pass thru the data which could be expensive on a very large report. The fastest way to get the summary is going to be to have the summarization at the extract layer. Another alternative might be to build a dynamic XML tree and then use that to render the values. I think this would be just as expensive as the solution above thou - I might be wrong thou.

Through streaming eyes and a nose that wont stop running, its a doozy of a cold/flu. I found a nice question yesterday on the forum from Chris (chrish6):

Hi All

I have just spent the last couple of hours trying to do something which I was hoping would be fairly simple - the only thing I have figured out is that it is not simple.

Heres my problem, I have data structured like so

<HORSE>
 <HORSE_NAME>Chris One</HORSE_NAME>
</HORSE>
<HORSE>
 <HORSE_NAME>Chris Two</HORSE_NAME>
</HORSE>
<HORSE>
 <HORSE_NAME>Chris Three</HORSE_NAME>
</HORSE>
<HORSE>
 <HORSE_NAME>Chris Four</HORSE_NAME>
</HORSE>

I want the data to come out in RTF data like this

Chris One Chris Two
Chris Three Chris Four

I really want to avoid changing the data file.
So I don't come across as a lazy boy I have looked into a couple of things....
Tims blog on Rows and Columns - this is the direction I need to go but I don't get how to force a new line - I tried it and got all records on one line.
I've read various forum posts a blog by another guy doing invoice stuff - which seems over complicated for what I need.
Tim - any chance you could do a follow up to your rows and columns blog??
I'm running latest BIP,
my data can have x amount of records and may be an odd or even amount of records.

Not sure on the horse reference, maybe Chris has a race horse stable and wants to generate address labels for them. I came up with a solution that requires you to specify the number of columns you want in the table. In Chris' case its 2, it relies on a small piece of XPATH code to get specific records or horses to show up.

All we have in the template is a 2 celled table, inside each we have a complete for-each loop with the HORSE_NAME and an end loop.

The contents of the F fields are :

<?for-each:HORSE[position() mod 2=1]?>   -   <?for-each:HORSE[position() mod 2=0]?>

the [position() mod 2 =1] expression tests the result of dividing the current record number by two and check the remainder. This puts the odd records into the first column and even records into the second column.

Now your not tied to two columns, just add a column and adjust the 'mod' calculation to have

<?for-each:HORSE[position() mod 3=1]?> 
     - <?for-each:HORSE[position() mod 3=2]?> 
            - <?for-each:HORSE[position() mod 3=0]?>

to get this output

Sample template and data here.
You can keep going as long as you wish, just need to work out the 'mod' calcs and of course if you dont want horses but maybe address labels, just add the fields you need and you're 'off the races' as it were.

I knew I had jumped the gun ... the devil is in the detail as it were - Bill followed up yesterday on the forum with a little more detail on what was needed.

Thank you for your fast response, actually I should have made my question clearer. Let's take your sample data, what I need is like this:

For group 1 Nuts and Bolts Limited, make background yellow,
981110 10-NOV-04 Standard EUR 122
100000 28-MAY-04 Standard FIM 122
100001 28-MAY-04 Standard FIM 200
1 03-JUN-04 Standard FIM 400
100004 28-MAY-04 Standard FIM 100
00s 07-JUN-04 Standard FIM 100
FI1009 10-MAY-04 Standard FIM 1220
Stop make background yellow.

For group 2 Tick Tock Clocks, make background blue,
100002 28-MAY-04 Standard DEM 235
100003 28-MAY-04 Standard DEM 100
2001 01-JUN-04 Standard EUR 1000
2002 01-JUN-04 Standard EUR 1500
2003 01-JUN-04 Standard DEM 1000
2004 01-JUN-04 Standard DEM 2000
3001 04-JUN-04 Standard EUR 1000
FI1011 25-MAY-04 Standard DEM 2000
Stop make background blue.

For group 3, make background back to yellow
Big Bike Motorsports
3001 01-JUN-04 Standard SEK 1000
FI1013 02-JUN-04 Standard USD 25000
Stop make background back to yellow

For group 4, make background back to blue
Second Cars
FI1010 20-MAY-04 Standard FIM 2440
Stop make background back to blue

We're mixing 'Groups' and 'records' here, in my original post I colored fonts and backgrounds based on the group or 'for-each'. What Bill is decribing above is to alternate the coloring of records within a group. Still doable, still not 'disappearing an elephant' but maybe 'pulling a bunch of flowers out of your sleeve' - OK OK I'll stop with the magician analogies.

Heres the template, looks similar to the ones from yesterday but without the shading.

The only addition is the IFColor field - inside there is some code to manipulate the table fill color based of some boolean expression.

<?choose:?>
 <?when:position() mod 2=0?>
 <?attribute@incontext:background-color;'Yellow'?>
 <?end when?>
 <?otherwise:?>
 <?attribute@incontext:background-color;'Red'?>
 <?end otherwise?>
<?end choose?>

This relies on the position() and 'mod' functions to allow us to, in this case alternate between two colors we can increase the mod value to alternate between more if we wish. If you are going to stick with just 2 then we can simplify further.

Just set a color background on the table and in the formfield use:

<?if:position() mod 2=0?><?attribute@incontext:background-color;'Red'?><?end if?>

So by default the color will be yellow but when the 'mod' condition is satified ie every even row you'll get a red background. Of course standard HTML color names are supported along with RGB HEX values.

Good question from Remc0 on the forum recently.

When the following in a group:
<?sum(current-group()/_saw_8_[.!=鋳])?>

It returns a 0 when all values are null. But 0 means smaller then 0,5 and null means it is not there. Now my question is: how can I sum the values and make a different between 0 and null?

Its a good question and there is a reply to use the updateable variables - I have to admit an aversion to updateable variables - they are expensive and are not a feature of native XSL - there are other ways around using the 'variables'

Remco's basic problem is that the code above that checks and eliminates null values in a sum function returns a zero value when all values are null. For example with the following XML.

<AMOUNTS>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="CM"></AMOUNT>
<AMOUNT type="CM">100</AMOUNT>
</AMOUNTS>

The sum of the amounts of the type 'CM' using the XPATH null elimination:

<?sum(AMOUNT[@type='CM' and .!=""])?>

as an aside, notice we're combining XPATH expressions with an 'and' - we want to sum all AMOUNT values where the type attribute is CM and the values are not 'null'. In this case we get '100' returned - thats good. If we sum the INV types:

<?sum(AMOUNT[@type='INV' and .!=""])?>

we get a zero returned - which as Remco notes is not actually correct - well not for his requirement, he wants to see an empty string.

My approach was to use our inline 'if' statement.

<?xdoxslt:ifelse(sum(AMOUNT[@type='CM' and .!=""]) = 0,'',sum(AMOUNT[@type='CM' and .!=""]))?>

The if statement tests the returned value and if its a zero ie the XML values are null then an empty string (two single quotes) is inserted otherwise it returns the calculated amount. This is fine if all the values are null but if the returned sum value happens to be '0' then an empty string will be returned.

In the interest of audience participation, here's a challenge. Can anyone come up with the XPATH necessary to return the real zero values and handle the null case too?

There is a good and apparently simple question on the forum today from mysterious user613190 - dont be a number, be a free person to mangle a phrase. It was actually posted yesterday but a man has to rest a little.

Hi,

In my output I am getting page numbers like

1 of 3 (when output has 3 pages)
2 of 3
3 of 3

But if output has only one page then I need to show like below

1

Could you help me in this?

Its a simple question, how do I conditionalize (is that a new word I just made up?) the page numbers? Seems straightforward to the uninitiated XSLFO template creator.
'Why cant I count the number of pages in the template and then render one string or another?'
'Well, you can't !'
'Why Not?'
'Because I said so !'
Hang on, hang on, this is sounding like a conversation between my son and I; except it would be about why he can not go to the movies, or hang out at the skate park or maybe ride his dirtbike - it's a power thang and I get irrational sometimes OK!

Getting back to a rational answer ... you still can't and its not because I said so, its because the XSL standards say so. Well that and the fact that the rendering engine at runtime decides how much data/information can fit on a page. To understand the process a little more, when we apply a template against some data, the flow (for the case of PDF) and assume your RTF template has been converted to XSLFO is as follows.

XML + XSLFO  -------> FO ------> PDF
__________             ____
          |                        |
 XSLT Engine          FO Renderer

That FO stage is the Formatting Object - its a big piece of XML that looks similar to extremely detailed HTML with pixel perfect placement of objects on the resulting page. To understand why you can not place 'page' logic in the template you need to know the processes going on. To get to the formatting object its an XSL transformation - at this stage all thats happening is the format and placement of data is applied to the XML data but there is no notion of a 'page' or how much data will fit on that page. The second stage, taking the FO to the PDF output is carried out by the FO renderer - at this point the engine will start to lay the data out on pages, calculating what will fit and then rendering it.

So, you see that any logic around pages in the template is not going to work, its lost and can not be executed in the FO layer, by then all calculations and conditional formatting will have been executed and because there is no notion of what will fit on a page or how many pages there are going to be - by then its a matter of fitting the data onto the page.

What are the options then I hear you ask? Well, we already have some extensions to the standard we have put in to our engine around page rendering. Those of you using the dynamic column solution to get a Z printing format on your PDF output are unwittingly using one of those extensions. We are therefore looking into building a page calculation extension to allow such functionality as is being asked for above - it's coming, not in the next release but some time after that.
What about now, I need a solution! - well one option to is to control the number of lines you want to show per page. If you have that in place then you can comfortably calculate in your template whether you are going to get 1 page or 100 pages of output and then act accordingly. I have covered fixed number of lines per page before and that article should get you on your way. If folks out there have found others, please feel free to share.

Arrived in California today to a beautiful warm and sunny day, great to see the team. With the morning's meetings out of the way it was off to the 600 cafe to get a much missed veggie burrito - its not so much the burrito (although it was as good as ever) it was the fantastic lady who serve's you. Sadly, I have forgotten her name, but she looks so gruff and barks questions at you, what kind of tortilla do you want, beans? black or refried? jalapanos? salsa - hot? Its a fantastic tongue lashing setting you up to savor the flavor of the burrito even more ... forget Qdoba get over to the Oracle cafe in the bottom of 600!

So to todays rather strangely titled entry - hard core sorting. I say hard core purely because of the requirement. Here's the XML:

<REP> 
 <G_1> 
  <NAME_1>Peter</NAME_1> 
  <SORT_1>6</SORT_1> 
 </G_1> 
 <G_1> 
  <NAME_1>Anna</NAME_1> 
  <SORT_1>3</SORT_1> 
  <G_2> 
   <NAME_2>Paul</NAME_2> 
   <SORT_2>1</SORT_2> 
  </G_2> 
  <G_2> 
   <NAME_2>Mary</NAME_2> 
   <SORT_2>11</SORT_2> 
  </G_2> 
 </G_1> 
 <G_1> 
  <NAME_1>John</NAME_1> 
  <SORT_1>2</SORT_1> 
 </G_1> 
 <G_3> 
  <NAME_3>Jim</NAME_3> 
  <SORT_3>4</SORT_3> 
 </G_3> 
 <G_3> 
  <NAME_3>Frank</NAME_3> 
  <SORT_3>12</SORT_3> 
 </G_3> 
</REP> 

Looks innocuous enough until you see the requirement on how it should render ...

Paul 1
John 2
Anna 3
Jim 4
Peter 6
Mary 11
Frank 12

Hmmmm ... well Klaus and Hok Min rose to the challenge and came up with the following:

<?for-each://*[starts-with(name(.),'G_')]?> 
 <?sort:./*[starts-with(name(.),'SORT_')];'descending';data-type='number'?> 
 <?./*[starts-with(name(.),'NAME_')]?>, <?./*[starts-with(name(.),'SORT_')]?> 
<?end for-each?>

Why am I sharing this ? Couple of reasons:

1. Its a neat solution to a tough problem - that, I would have pushed back on before answering. Why not get the extraction routine to do all the heavy lifting. I've said many a time, sorting in the template comes at a price. This type of sorting comes at a bigger price, you are sorting on a partial string comparison ... not good! If you can get the query to do the work, get it to do the work!

2. If we ignore the sorting and focus on the 'for-each'. You can see that you are not looping over a given element in the XML but you are looping over a partial string match on the element name. Tres Cool!

  starts-with(ELEMENT, string ) is an XSL function
  name(.) - another XSL function that returns the element name itself, not its contents.

So we are matching on all element names that start with 'G_' - this enables us to treat the nested 'G_2's as if they were at the same level as the G_1 and G_3 elements.
Again, I would question the XML structure and ask why its so badly formed, but I suspect we did not get to see all of it.

I'll try and expand on this theme of not just 'element looping' in more articles, you can create some damn fine reports with an Oracle burrito inside you!

Welcome back .... new look, same old (hopefully) useful articles - will talk about the new look another day. We're back with a bang, at least for those of you struggling with the Purchase Order Oracle Report. Dave summed up the problem on the BIP forum last week:

I have the following XML:

<PO_DATA>
 <LINES>
  <LINES_ROW>
   <LINE_DESCRIPTION>LINE 1</LINE_DESCRIPTION>
   <PO_LINE_ID>2467</PO_LINE_ID>
  </LINES_ROW>
  <LINES_ROW>
   <LINE_DESCRIPTION>LINE 2</LINE_DESCRIPTION>
   <PO_LINE_ID>2468</PO_LINE_ID>
  </LINES_ROW>
  <LINES_ROW>
   <LINE_DESCRIPTION>LINE 3</LINE_DESCRIPTION>
   <PO_LINE_ID>2469</PO_LINE_ID>
  </LINES_ROW>
 </LINES>
 <LINE_ATTACHMENTS>
  <TEXT>Detailed item description</TEXT>
  <ID>2467</ID>
  <TEXT>Detailed item description2</TEXT>
  <ID>2468</ID>
  <TEXT>Detailed item description3</TEXT>
  <ID>2469</ID>
 </LINE_ATTACHMENTS>
</PO_DATA>

Is this possible?

Notice the line attachment section? We have a link back to the LINE_ROW section but no nice structure to loop over!

We have to get into some more serious XSL/XPATH expressions to get the description to appear with the rest of the line information.

With some help from a relatively new member of the BIP dev team, Yaoguang, I managed to come up with a solution.

Where you are looping over the LINES_ROW, just inside the for-each field add a new field:

<?variable@incontext:PoLnID;'PO_LINE_ID'?>

Every time the loop goes over it assigns the current PO_LINE_ID to a local variable 'PoLnID'

Where ever you want the description to appear on the row create a field that contains

<?/PO_DATA/LINE_ATTACHMENTS/ID[child::text()=$PoLnID]/preceding-sibling::TEXT[1]?>

Its a bit of a mouthful but its basically looking for a child element that matches the PoLnID value - then its looking for the first [1] occurrence of the TEXT value that is a sibling to that ID.

Works real nice!

Of course it would be better to get the PO team to restructure the XML but this will do nicely. Example template here

I have been helping a JD Edwards customer over the last few days. Nothing too tough but the XML that JDE spits out can have its own 'issues'. In JDE you can use their App engine to generate reports, a UBE. It has been modified to spit out XML too but the structure can be a bit daunting.

Here's a PO XML structure:

<R5543500>
<Properties>
<Purchase_Order_Print_S42_Column_Headings>
<PageHeaders>
<Purchase_Order_Print_S42_Group>
<On_Ship_To_S45>
<OLN_S47>
<Purchase_Order_Print_S42>
<Grand_Total_S50>
<Buyer_S51>

Not too bad on the surface but the XML is generated in 'pages' - the engine in its former use generates the layout of the report in a page fashion for the rendering engine to generate the output.
This means things can get a little funky in the template because we dont care about pages in the data - the BIP rendering engine will handle all that for you.

Mac at GW Electronics has been getting to grips with RTF templates and the XML, he had also started down the path of using XPATH to navigate around the XML to get the data he needed for various parts of the report.
He had also alighted on the use of our updatable variables to help get the right element. He had things like:

<?/R5543500/Purchase_Order_Print_S42_Group/On_Ship_To_S45/OLN_S47[xdoxslt:get_variable($_XDOCTX, 'x')]/DescriptionLine2_ID24?>

where the variable was being updated as the template looped over the XML groups. It was not working very well. It's nasty and will come at a cost, albeit a small one.

Im not a fan of our updatable variables - talk to an XSL purist and they will tell you we have committed blasphemy for providing them in our engine. My beef with them is that they are too verbose and expensive in terms of resources.

Looking at Mac's template and having the benefit of having a decent XML editor (XMLSpy) on my machine I was able to spot that he could use.

<?../DescriptionLine2_ID24?>

To get to the same data item - much simpler no? The takeaway from this? Arm yourself with a decent XML editor - and learn the data structure you are working with. XMLSpy will cost you but there are others out there that can help with working out navigation paths through the XML and stay away from those updateable variables if you can

I have seen quite a few templates recently that are using our updatable variables to keep a track of the current record being processed. Regular readers will know my dislike for the updatable variables. Don't get me wrong, they have their place ... sometimes. It might be the way you set the dang things up that annoys me so much and I should take some blame for not chasing the dev team up to simplify the declaration and retrievel. Here's the code to define and assign a value to a variable x:

<?xdoxslt:set_variable($_XDOCTX, ’x’, 10)?>

now lets update the variable, adding 10 and then show it

<?xdoxslt:set_variable($_XDOCTX, 'x', xdoxslt:get_variable($_XDOCTX, 'x')+10)?>
<?xdoxslt:get_variable($_XDOCTX, 'x')?>

Too much right? Imagine using them to track the number of the current record. Folks are doing this in their templates. There is no need - the underlying standard for RTF templates is XSL and it has some neat native functions that can be used to track the current record and the last record.

position()

last()

For some examples I indebted to Chris from UK Oracle Consulting. Chris will be out at Oracle World again this year so if you would like to hear how the Queen's English should be spoken drop by the demo grounds. I seem to have developed some mid-atlantic drawl - my family in the UK think Im lost to those danged Yankees! I do still pronounce butter with t's rather than d's but my kids are struggling to understand me when I ask for a 'glass of water' rather than 'wadder'. Enough language mangling already! Back to Chris' examples. He came up with the following XML data.

<ROOT>
  <LIST_G_ORDER_HEADERS>
    <G_ORDER_HEADERS>
      <HEADER_ID>123</HEADER_ID>
    </G_ORDER_HEADERS>
    <G_ORDER_HEADERS>
      <HEADER_ID>456</HEADER_ID>
    </G_ORDER_HEADERS>
    <G_ORDER_HEADERS>
      <HEADER_ID>789</HEADER_ID>
    </G_ORDER_HEADERS>
  </LIST_G_ORDER_HEADERS>
</ROOT>

and some examples of position() and last()

.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[HEADER_ID = '123']/position() RETURNS '1'
//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[position() = last()]/HEADER_ID RETURNS '789'
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[last()]/HEADER_ID RETURNS '789'
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[position() != last()]/HEADER_ID RETURNS '123' and '456'
.//LIST_G_ORDER_HEADERS/G_ORDER_HEADERS[2]/HEADER_ID RETURNS '456' 

You'll have noticed the [ ] - these contain what are know as XPATH expressions. They are very powerful and well worth taking a look at - I'll try and cover XPATH more than we do in the documentation.
Update
===========
Leslie, our fab doc writer has just pointed out that I should have stated:
I will work with the doc writer to get some more in the documentation

I'll also try and get some more in here in the meantime :)

You can of course use them both in a conditional statement too:

<?if:position() = last()?>
 Last Record!
<? end if ?>

Chris' third example is doing something similar but its using the condition within an XPATH expression, G_ORDER_HEADERS[position() != last()] - inside those square braces you do not need the 'if'.

So, throw out those updatable variable whenever you can and get native!

Got a request to come up with a list of all the extension functions we provide in Publisher. I finally got around to generating some javadoc for the XSLTFunctions.java.

You can get the zipped java doc for the 5.6.3 release here.
You can get the zipped java doc for the 10.1.3.4 release here

Should be enough in there for you to work out whats needed in terms of parameters.

To use the functions you need to prefix them with

<?xdoxslt: function_name(parameters) ?>

Enjoy!

Got a good internal question from Nikos one of our Product managers this week, it was a sorting question but a tough one. He had the following data:

<ROWSET>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Audio</PRODUCT>
   <SALES>75241.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>143054.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>West</MARKET>
   <PRODUCT>Audio</PRODUCT>
   <SALES>108053.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>West</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>171242.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Total Year</YEAR>
   <MARKET>South</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>110312.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Qtr1</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Audio</PRODUCT>
   <SALES>18132.0</SALES>
  </ROW>
  <ROW>
   <YEAR>Qtr1</YEAR>
   <MARKET>East</MARKET>
   <PRODUCT>Visual</PRODUCT>
   <SALES>32847.0</SALES>
  </ROW>
  ...
  </ROWSET>

You get the picture, unsorted sales data with funky values for the YEAR. What he wanted to do was get an output that sorted by Market then Product then by the YEAR value ie Jan, Feb,Mar, Qtr1 ... Total Year. He then wanted to highlight the Qtr and Year rows with other background colors. Something like this:

The first two criteria are easy, just use the for-each-group functionality. The last is a bit tougher, you could hard code the YEAR values into a table, that would get what he wanted but not very satisfying from a 'Templaters' point of view.

A quick Google and I had a plan - hat tips to XSL miesters, Michael Kay and Jeni Tennison.Still a little hard coding but nothing like the other hard coding option and a bit more satisfying. It relies some string calculations to come up with the sort order. Here's the sort command:

 <?sort: string-length(

   substring-before ('JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year'

  ,YEAR));

    'ascending';

     data-type='number'?>

then you sort by the string length ... coool! Now you just plug that into the template.

<?for-each:current-group()?>
<?sort: string-length(
         substring-before ('JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year'
          ,YEAR))
        ;'ascending';data-type='number'?>

Hok Min from the dev team made a good point here. To avoid that ugly and potentially unmanageable (if you wanted to use it again in the template) sort string. Just create a variable to hold the string and then reference it in the sort command:

<xsl:variable name="mnthtbl">JanFebMarQtr1AprMayJunQtr2JulAugSepQtr3OctNovDecQtr4Total Year</xsl:variable>

Add to the sort by:

<?sort:string-length(substring-before($mnthtbl, ./YEAR));'ascending';data-type='number'?>

For the conditional highlighting of the 'Qtr' and 'Total Year' rows we can use the string manipulation again.

For the QtrX

<?if@row:substring(YEAR,1,1)='Q'?><?attribute@incontext:background-color;'#80FFFF'?><?end if?>

For the Total Year

<?if@row:substring(YEAR,1,1)='T'?><?attribute@incontext:background-color;'#00CACA'?><?end if?>

All Im doing is testing the first character of the YEAR value and looking for a 'Q' or a 'T'. Using the @row gets the formatting applied to the whole row. If you want the source files you can get them here.
You can of course apply this sort to any set of strings, its going to come at a small price thou. Its far far better to sort in the XML extract if at all possible. So what have we learned? this XSL stuff is cool! Google is a 'Templaters' friend and there is always an easier way!

Im not talking about filling your reports with fluff. I can remember, mistakenly filling my high school essays with fluff and padding. Why use 5 words when 10 can say it more eloquently and fill the word count more quickly. Sadly, there was no pulling the wool over my teacher's eyes - just had to learn more facts!

I have just been helping out on a demo for a customer proof of concept. They wanted the following report output.

Nothing fancy there on the surface, building the report is straightforward enough. We can get this output very quickly.

But have you noticed in the original, there is some space after each set of categories.

We can achieve this by manipulating the 'padding' in the first cell of the row to effectively increase the height of the whole row. This increases the space between the last row of the group and the beginning of the next.

Just insert the following in a form field in the first cell:

<?if:LAST='Y'?>
<?attribute@incontext:padding-bottom;'10.0pt'?>
<?end if?>

The LAST element was in the XML to define whether the current member of a group is the last. You could equally use a position() based if statement, you just need to have some means of identifying the last member of a give grouping. The next line just manipulates the 'padding-bottom' attribute - here we are making it 10pts. This effectively provides us the row height we need to provide the spacing between the rows we need. There are of course other padding attributes:

• padding-top
• padding-bottom
• padding-start
• padding-end

XSL uses 'end' and 'start' rather than 'right' and 'left' for internationalization purposes. With the extra conditional field we can now generate the required output.

this post kinda begs the question, 'how the heck do you know what you can manipulate?' Well there is the XSL-FO spec available, if you want to wade through it. Personally, I dont very often. My best friend is the Template Builder for Word -> Tools > Export > XSL-FO Stylesheet. this will dump your current RTF template into the browser. There you can check out whats going on in the template. Its not that tough - it's bit like complex HTML, there are tables, rows, cells, etc and each have attributes that you can override to your hearts content. Yes, you have to write some code at the moment, but the extra power in the formatting is worth it I think. I have not touched Oracle Reports (in anger) in a while but I can not think how I would achieve the layout above, not off the top of my head anyway. So get padding!