Monday Nov 25, 2013

Conditional Borders

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

 

border-top|bottom|left|right-width
border-top|bottom|left|right-style
border-top|bottom|left|right-color

 

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

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

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

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


Tuesday Nov 05, 2013

Comb Over

Being some what follicly challenged, and to my wife's utter relief, the comb over is not something I have ever considered. The title is a tenuous reference to a formatting feature that Adobe offers in their PDF documents.

The comb provides the ability to equally space a string of characters on a pre-defined form layout so that it fits neatly in the area. See the numbers above are being spaced correctly. Its not a function of the font but a property of the form field.

For the first time, in a long time I had the chance to build a PDF template today to help out a colleague. I spotted the property and thought, hey, lets give it a whirl and see in Publisher supports it? Low and behold, Publisher handles the comb spacing in its PDF outputs. Exciting eh? OK, maybe not that exciting but I was very pleasantly surprise to see it working.

I am reliably informed, by Leslie, BIP Evangelist and Tech Writer that, this feature was introduced from version 10.1.3.4.2 onwards.

Official docs and no mention of comb overs here.


Happy Combing!

Tuesday Jul 02, 2013

Working the Chart Percentages

Charting in BIP is such fun, well sometimes it is. Not so much today, at least not for Ron in San Diego. He needed a horizontal bar chart showing values plotted for various test areas with value labels at the end of the bars. Simple enough right? The wrinkle, they were percentage values so he needed to see '56%' not '56'!

Still, it should be simple enough but the percentage formatting has a requirement for your values to be in a decimal format i.e. 0.56 not 56.0. 56.0 gets formatted as 5600%. OK, so either pull the values out as decimals or use the div function to divide the values in the chart by 100 e.g.

<xsl:value-of select="myval div 100)" />

Now I can use the following the chart XML to format the percentages as I need them:

 

<Graph ... >
...
<MarkerText visible="true">
<Y1ViewFormat>
<ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" numberTypeUsed="true" 
        leadingZeroUsed="true" decimalDigitUsed="true"/>
</Y1ViewFormat>
</MarkerText>
...
</Graph>

 

That gets me the values shown the way I want but the auto axis formatting gets me from 0 >> 1.

I now need to go in and add the formatting for the axis too.

 

<Graph ...>
...
<Y1Axis axisMinAutoScaled="false" axisMinValue="0.0" axisMaxAutoScaled="false" 
    axisMaxValue="1.0" majorTickStepAutomatic="true">
<ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" scaleFactor="SCALEFACTOR_NONE" 
    numberTypeUsed="true" leadingZeroUsed="true" decimalDigitUsed="true" scaleFactorUsed="true"/>
</Y1Axis>

 

Now I have a chart that's showing the percentage values and formatting axis scale correctly for me too.



You can of course mess with the attributes above to get more decimal points on your labels, etc.

Happy Charting!


Friday May 11, 2012

Beyond the Conditional Dialog

Interesting question today, asking how to conditionally underline and align text in an RTF template?

Your first thought, the conditional dialog box in the Template Builder for Word right?
Mine too, but I know that the dialog is limited to setting the background color, font color and font style. However, the XSLFO standard has a bunch of attributes that can be set. There is going to an intersect of what the standard offers and what BI Publisher has implemented. Ots also going to depend on what version of BIP you are running too as the boys and gals in the back room constantly add to the list of supported attributes for give objects.

If you're just getting to grips with the language and want to know what attributes are available, the W3c Schools site is a good place to start - http://www.w3schools.com/xslfo/xslfo_reference.asp.  There you can find the object you want to change and its attributes.

The easiest way to create the conditional code is to go ahead and create a condition using the dialog and choosing one of the supported attributes. Note that the dialog only works when you are inside a table. Thats jus tthe dilog box talking, you re not limited to changing attributes only inside tables. Just use the dialog to get the code.

All you then need to do is substitute in the attribute name and the value you want it to be into the code. So:

<?if:@Name='Tim'?><?attribute@incontext:color;'red'?><?end if?>

 

can then be changed to

 

<?if:@Name='Tim'?><?attribute@incontext:text-align;'right'?><?end if?>

 

and of course you have make multiple changes inside the 'if' statement.

One thing to note here, the @incontext might need to be changed to get the desired changes to be applied. Check the documentation for details on the various @ levels you can use. Don't be scared, play a little until it does what you want it to do. Its useful to export the RTF to XSL:FO and see where your code is being applied if the output is not what you were expecting.

Wednesday Feb 29, 2012

Conditional Charting II

A follow up post on yesterdays efforts. After pinging a few colleagues Klaus came up with a much much neater approach that appeals to my sense of easy, straightforward and neat, when it comes to code anyway. A by product of the approach, it can handle, none, one or multiple conditonal bars.

So, no variables ... a big plus, some chart xml editing, not quite so good but the benefits far outweigh the costs. Its a case of digging into the chart code and maybe Klaus (who is in charge of the template builders) will one day, provide this conditionality via the chart dialogs ... pleeeease Klaus :0)

Heres the relevant snippet of the chart code:

<Graph seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="false" />
 <ExceptionalRisers>
  <xsl:for-each select=".//Row"  xmlns:xsl="
http://www.w3.org/1999/XSL/Transform">
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
  </xsl:if>
  </xsl:for-each>
 </ExceptionalRisers>
 <LocalGridData>
...
</Graph> 

We essentially, build the multiple ExceptionalRiser entries in the graph code itself rather than try and build out a concatentated string variable. The string approach was my first thought but 1. XSL does not allow XML strings to be built dynamically and 2. the chart does not break if there are no conditional columns to be rendered.
Dealing with the code a line at a time
  <xsl:for-each select=".//Row"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
Loop over the data as we did before
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
Look for percentages less than a specified value. I externalized this value into a variable (pLim) to make things easier to test, you can play with it in the template.
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 
If we find a row of data that meets the criteria we create an ExceptionalRiser entry and use the current record number (position()) minus 1 because out bars are numbered from zero.
Then we just close out the if, the for-each and we're done.
If the if statement does not return true at all i.e. no bars need to be colored; the chart just renders as normal, nice!

Klaus took his example a little further than mine. The one thing that my approach suffers from is that it requires the engine to loop over the data twice for the chart. Once to search for ExceptionalRisers and again to render the chart itself. Klaus took an aproach to create a variable to hold the chart data first and then  run the riser code and chart rendering against that. Check that out in the second template (ConditionalChart2-1.rtf) please note, that its an 11g template and will need the 11g plugin to see it running. Both available and with sample datasets here.

You can now conditionally color your bar charts, horizontal or vertical. I have tried the same with pie charts but no banana so I'm assuming its only going to work for bar charts. I have tested on 10.1.3.4.x and 11g releases. 

Tuesday Feb 28, 2012

Conditional Charting

Something I have never been asked for with BIP until recently, conditional charts. Not whether they appear or not but being able to highlight a specific bar on a bar chart if it meets some certain criteria. The chart to the right is simple enough showing sales by month. The April bar is being highlighted in red because the value is falling outside of some limit.

So how can you do it? Its not documented in the BIP docs, it's not in the BIBeans (charting engine) docs, so until I finish this post you will need to know and have friends in high places at Oracle. Thank you Klaus and especially Katia for pointing me in the right direction.

I have tested this approach with 10.1.3.4.x and 11g, I have not tested with 5.6.3 thou.
To get the bar to change color you are going to have to dig into the chart XML and there will be a bit of effort to identify the column to change color but its not that onerous to do or manage. Note thou, if you re-open the chart dialog with the 10g Template Builder it will over write you conditional code.

The new object you need to add into your graph XML is an ExceptionalRiser, you can add multiple risers but more on that later. It needs to appear somewhere inside the Graph tag.

chart:
<Graph seriesEffect="SE_NONE" graphType="BAR_HORIZ_CLUST">
<ExceptionalRisers>
<ExceptionalRiser
series="0" group="9" borderColor="#0fffff" fillColor="#ff0000" />
</ExceptionalRisers>
...
</Graph>

The attributes can be described as: 
 - series - the data series that contains the data point to be re-colored. For the majority of charts this will be "0" ie only a single data series
- group - the number of the data point or bar to be re-colorded. Starting from 0
- borderColor - line around outside color
- fillColor  - errr ... the fill color.

If you know the value thats erroneous up front then you can hard code the group attribute as above but thats going to be rare. I guess you could work it all out in the data extract and create an element to hold the exception data row number but you can equally work out the value in the template layer.

I have struggled a bit with this and have had to resort to my nemesis updatable variables. My first use case is where I know I only want to color a single bar on my chart.
Here's my data:

<Graph>
<Sales>
    <Row> <Month>Jan</Month> <Value>450</Value> <Target>420</Target></Row>
    <Row> <Month>Feb</Month> <Value>500</Value> <Target>550</Target></Row>
    <Row> <Month>Mar</Month> <Value>490</Value> <Target>490</Target></Row>
    <Row> <Month>Apr</Month> <Value>400</Value> <Target>520</Target></Row>
    <Row> <Month>May</Month> <Value>680</Value> <Target>650</Target></Row>
</Sales>
</Graph>

Simple stuff as usual but I hope easy to understand. I can create a calculation looking for Values that miss the Target value and build an 'if' statement oround it to highlight values in red.

<?if:(number(Value) div number(Target))<number(0.8)?><?attribute@incontext:color;'red'?><?end if?>

This is looking for Values less than 80% of their Target, of which I know there is only one the %Delta for Apr.


Looking back at the ExceptionalRiser, XML I should be able to pass a value to the group attribute. Here's where I have wasted some time trying to use native variables, for some reason I can not set the value crrectly for the chart to pick the variable value up. So I have resorted to updateable variables instead. It works, it just not appeal to my sense of right and wrong in XSL i.e. no native support for updatable variables. Seeing as Im conditionally formatting the percentage values I could createa variable in that cell. But if the chart appears before the table, its not going to work.

So I have created a field at the beginning of the template to loop through the values seraching for my erroneous vlaue:

<?for-each:Row?><?if:number(number(Value) div number(Target))<0.8?><?xdoxslt:set_variable($_XDOCTX,'val3',position())?><?end if?><?end for-each?>

If it finds the value, it sets the variable val3 with the current record pointer, note, this starts at 1.
In my chart code I have:

<ExceptionalRisers>
<ExceptionalRiser series="0" group="{xdoxslt:get_variable($_XDOCTX,'val3')-1}"  borderColor="#000000" fillColor="#ff0000" />
</ExceptionalRisers>


Notice the curly braces to get the XSLT engine to evaluate my variable value first and remember the group value starts from zero hence the '-1'.
This gets me my chart correctly showing the Apr bar in red.


Next time, multiple conditional bars, heres the RTF template and data so far.

Monday Sep 12, 2011

HTML in XML Support

I luckily still get to see some internal emails from the development team. I saw one today making mention of an upcoming 11.1.1.5 roll up patch set that is coming. This piqued my interest as I thought, was there one for August? Indeed there was or is, check out patch 12831433. Digging into the patch read me I found a nugget of gold that many folks have been looking for ... out of the box HTML >> FO formatting.

I have written about this subject elsewhere in the blog and you can find em using the search box. This is new, this is out of the box support to convert you stored XHTML to the required format for Publisher's underlying language, XSL-FO. What do I mean by that? Being able to take

<B>This is bold text</B>
and convert to its XSLFO equivalent

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

There are some restrictions to what's available right now but its a big step forward. Here's the details.

This patch supports HTML embedded in XML. The following new layout command is added to retain HTML format from data in the final output.
<?html2fo: xpath?>
The HTML code you want converted of course needs to be within the report's xml data so that the template processor can work on it. Further, the html2fo command needs the HTML to be inside a CDATA section in the report data. The term CDATA is used to describe or store text data that should not be parsed by the XML parser. For example the < and > characters denote the opening and closing characters for an XML element tag. In our case we want them to denote the opening and closing of HTML commands. Without the CDATA section the XML parser would attempt to parse and process the HTML commands resulting in unexpected results to say the least. 
In the example below the HTML code is embedded in the field RTECODE.
<?xml version="1.0" encoding="UTF-8"?>
<RTECODE>
<![CDATA[
<font style="font-style: italic; font-weight: bold;" size="3"><a
href="http://www.oracle.com">oracle</a></font> <br/>
<font size="6"><a href="www.oracle.com">www.oracle.com</a></font><br/><br/>
]]>
</RTECODE> 

The next question is how do you get the CDATA sections wrapped around your HTML code?  Your HTML does not need to be stored into the database with the CDATA sections already attached. Well, you can do it in your SQL query but you need to use a very specific way to achieve it:

select '<![CDATA' || '['|| RTECODE || ']' || ']>' as "RTECODE"
from table x

You'll be thinking, hey, I can get rid of a set or two of those concatenation pipes, don't do it! Please use the example above and stick to it. If you do remove some of the || then the CDATA section will not be preserved correctly by the extraction engine.

Sample usage in rtf template (assume the data.xml is as above).

<?html2fo:RTECODE?> 

Supported html formats:

  • Paragraph Font style ( bold, italic, plain, underline, subscript, superscript and strikes-through)
  • Font size
  • Font family
  • Background color
  • Foreground color
  • Paragraph alignment (center, left, right and justify)
  • Paragraph indent
  • URL link
  • List
  • Bullet List
  • Number List

Non-Supported formats:

  • Nested List (List with Indent)
  • Some HTML tags/attributes which manually inserted like Table, Image, etc... in stead of HTML editor
Pretty cool, if you are on 11.1.1.5 and not up to the August patch set, give it a try!


Wednesday Aug 27, 2008

Formatting Currencies

[Read More]
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today