Tuesday Nov 18, 2014

Bordering Text

A tough little question appeared on one of our internal mailing lists today that piqued my interest. A customer wanted to place a border around all data fields in their BIP output. Something like this:


Naturally you think of using a table, embedding the field inside a cell and turning the cell border on. That will work but will need some finessing to get the cells to stretch or shrink depending on the width of the runtime text. Then things might get a bit squirly (technical term) if the text is wide enough to force a new line at the page edge. Anyway, it will get messy. So I took a look at the problem to see if the fields can be isolated in the page as far as the XSLFO code is concerned. If the field can be siolated in its own XSL block then we can change attribute values to get the borders to show just around the field. Sadly not.

This is an embedded field YEARPARAM in a sentence.

translates to

 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" 
  xml:space="preserve">This is an embedded field <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> in a sentence.</fo:inline>


If we change the border on tis, it will apply to the complete sentence. not just the field.
So how could I isolate that field. Well we could actually do anything to the field. embolden, italicize, etc ... I settled on changing the background color (its easy to change it back with a single attribute call.) Using the highlighter tool on the Home tab in Word I change the field to have a yellow background. I now have:

 This gives me the following code.

<fo:block linefeed-treatment="preserve" text-align="start" widows="2" end-indent="5.4pt" orphans="2"
 start-indent="5.4pt" height="0.0pt" padding-top="0.0pt" padding-bottom="10.0pt" xdofo:xliff-note="YEARPARAM" xdofo:line-spacing="multiple:13.8pt"> 
 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" xml:space="preserve">This is an embedded field </fo:inline>
  <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
   font-family-generic="swiss" font-family="Calibri" background-color="#ffff00">
    <xsl:attribute name="background-color">white</xsl:attribute> <xsl:value-of select="(.//YEARPARAM)[1]" xdofo:field-name="YEARPARAM"/> 
  </fo:inline> 
 <fo:inline height="0.0pt" style-name="Normal" font-size="11.0pt" style-id="s0" white-space-collapse="false" 
  font-family-generic="swiss" font-family="Calibri" xml:space="preserve"> in a sentence.</fo:inline> 
</fo:block> 

Now we have the field isolated we can easily set other attributes that will only be applied to the field and nothing else. I added the following to my YEARPARAM field:

<?attribute@inline:background-color;'white'?> >>> turn the background back to white
<?attribute@inline:border-color;'black'?> >>> turn on all borders and make black
<?attribute@inline:border-width;'0.5pt'?> >>> make the border 0.5 point wide
<?YEARPARAM?> >>> my original field

The @inline tells the BIP XSL engine to only apply the attribute values to the immediate 'inline' code block i.e. the field. Collapse all of this code into a single line in the field.
When I run the template now, I see the following:

 


Its a little convoluted but if you ignore the geeky code explanation and just highlight/copy'n'paste, its pretty straightforward.

Wednesday Mar 05, 2014

Internal Links

Another great question today, this time, from friend and colleague, Jerry the master house re-fitter. I think we are competing on who can completely rip and replace their entire house in the shortest time on their own. Every conversation we have starts with 'so what are you working on?' He's in the midst of a kitchen re-fit, Im finishing off odds and ends before I re-build our stair well and start work on my hidden man cave under said stairs. Anyhoo, his question!

Can you create a PDF document that shows a summary on the first page and provides links to more detailed sections further down in the document?

Why yes you can Jerry. Something like this? Click on the department names in the first table and the return to top links in the detail sections. Pretty neat huh? Dynamic internal links based on the data, in this case the department names.

Its not that hard to do either. Here's the template, RTF only right now.


The important fields in this case are the ones in red, heres their contents.

TopLink

<fo:block id="doctop" />

Just think of it as an anchor to the top of the page called doctop

Back to Top

<fo:basic-link internal-destination="doctop" text-decoration="underline">Back to Top</fo:basic-link>

Just a live link 'Back to Top' if you will, that takes the user to the doc top location i.e. to the top of the page.

DeptLink

<fo:block id="{DEPARTMENT_NAME}"/>

Just like the TopLink above, this just creates an anchor in the document. The neat thing here is that we dynamically name it the actual value of the DEPARTMENT_NAME. Note that this link is inside the for-each:G_DEPT loop so the {DEPARTMENT_NAME} is evaluated each time the loop iterates. The curly braces force the engine to fetch the DEPARTMENT_NAME value before creating the anchor.

DEPARTMENT_NAME

<fo:basic-link  internal-destination="{DEPARTMENT_NAME}" ><?DEPARTMENT_NAME?></fo:basic-link>

This is the link for the user to be able to navigate to the detail for that department. It does not use a regular MSWord URL, we have to create a field in the template to hold the department name value and apply the link. Note, no text decoration this time i.e. no underline.

You can add a dynamic link on to anything in the summary section. You just need to remember to keep link 'names' as unique as needed for source and destination. You can combine multiple data values into the link name using the concat function.

Template and data available here. Tested with 10 and 11g, will work with all BIP flavors.

Monday Jul 15, 2013

Minning and Maxing in Pivots

A tricksy question from a hobbiteses this past week or so. How can I use minimum or maximum in an RTF template pivot table?

Using the pivot table dialog box, you get sum or count. So, how to get a min or max? You need to understand the pivot structure a bit to understand how to get the min|max. I wrote about the pivot table format a few years back here.

 Its the C field that holds the calculation as the last parameter.

<?crosstab:c8949;"//G_1";"DEPARTMENT_NAME{,o=a,t=t}";"HIRE_YEAR{,o=a,t=t}";"JOB_ID";"sum" ?>

I was not sure if we could simply swap out the sum|count function for our min, max functions. But, Im a hacker at heart, so I gave it a whirl. It worked, I used the BIP min and max functions:

xdoxslt:minimum
xdoxslt:maximum

They both work nicely!

So, the C field would look like:

<?crosstab:c8949;"//G_1";"DEPARTMENT_NAME{,o=a,t=t}";"HIRE_YEAR{,o=a,t=t}";"JOB_ID";"xdoxslt:maximum" ?>

If you do not need the default totals (that use the functions you define.) You can just delete them from the table.

Sample template and data here.

Now, the average values need cracking!


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!


Wednesday May 01, 2013

Get yourself Organized!

A request from Leslie today to help her out on the user docs. In them we state that we support the MSWord organization charts but we do not give any detail.

Use the organization chart functionality in the templates and the chart that is rendered in the output. Figure 4-18 shows an example of an organization chart.

Figure 4-18 Sample Organization Chart

Description of Figure 4-18 follows

Its been a  while since I have looked at them but we mean just that. You build an org chart with names in the boxes, BIP will render it, simple.

Oh, you wanted it to load the names into the chart dynamically from the dataset? Sorry, no dice, at least not with the MSWord Org Chart object.

However, you can create your own org chart structure using MSShapes and use BIP's ability to fill those shapes with text from your data. Thats documented pretty well and is very easy to do. Taking it to obvious final step; completely data driven org chart structure and text. Thats a bit tougher. It can be done with the shape copy and move commands but its going to take some planning. You need to think about how wide your 'page' is, what to do when you reach the edge and need to continue with the same level in the hierarchy, etc.

To get you started, I have created a sample template and data for the first two scenarios. They will work with all releases of BIP and XMLP. The third will take me a little longer :0)

Thursday Apr 11, 2013

Variable Numbers to Words

Satyender posted a comment to the Numbers to Words post asking:
How can I store the result of &lt;?xdofx:to_check_number(TOTAL_INV_AMOUNT,'USD','CASE_UPPER','DECIMAL_STYLE_WORDS')?&gt; inside a variable.

Checking this out, BIP chokes on the assigning to the variable with a nice error:

 Namespace prefix 'xdofx' used but not declared

Turning to BIP RTF template guru in residence Hok-Min, he suggested avoiding the xdofx: wrapper altogether in this case and calling the function more directly. The underlying function in java is:

public static String toCheckNumber(String locStr, String amount, String preOrCurCode, String caseStyle, String decimalStyle)

Applying that to Satyender's needs we end up with:

<?variable@incontext:salval; xdoxslt:toCheckNumber($_XDOLOCALE,.//SALARY,'USD'
,'CASE_UPPER','DECIMAL_STYLE_WORDS')?>

We still need the xdoxslt prefix but we can now assign the value to a variable. There is a caveat from Hok Min.

Note that the amount has to be in string format.  If it is not a string, it has to be converted to a string, e.g. string($CALCULATED_SALARY).  If you use XML element name directly (like in this case SALARY), then it is already a string, so no need to do conversion.

I know this raises the question of why do we need the xdofx: prefix at all? Im discussing that with Hok Min as I write and will get back to you.


Thursday Nov 29, 2012

Spring Cleaning

I recently got a shiny new laptop; moving my shiz from old to new, was not the nightmare it used to be. I have gotten into the habit of using a second hard drive in the media bay where the CDROM normally sits. That drive contains my life's work with BIP. I can pull it out and plug it into another machine very easily. I have been sorting through some old directories and files, archiving some, sharing others with colleagues.

For instance, a little dated but if you were looking for a list of Publisher reports available in EBS R12.1, here it is. Im trying to track down a more recent R12 instance and will re-post the document.

I also found another gem; its a little out there in terms of usefulness but Im sharing it none the less. You can embed, locally or remotely reference SVG graphics (in XML format) and bring the images into the BIP outputs. Template and sample data here.

A nice set of templates showing page number control and page suppression - they will need some explanation, so I'll save them for another post.

The list goes on but I'll save them for later. Back to the clean up!

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.

Wednesday Oct 26, 2011

Tricky Grouping

A week off trying to catch up with all the stuff that builds up was not enough but it was pretty fruitful. You clear the decks the week before you leave, you even answer a few mails in the first few days of vacation, until your manager slaps your wrists. But there is always a pile of work waiting when you get back. It was not too bad this time; I at least deleted the crap mail during the week via my phone.

How the heck did we survive without smart phones? I took a trip to north Denver last week to pick up a part for our bear ravaged pop up camper. I think he wanted a cold beer from the fridge just wish he'd used the front door rather than slicing the camper open. That gaping hole is meant to be covered!
I guess I could have checked a map before we left but my phone got us through the mess of roads up there. An 80 mile round trip for a $10 louvered cover! Still, we found a great Indian place for lunch, delicious curry!

Today's tidbit (titbit for my British brethren) is a bit tough to describe. It comes from Charlotte in New Zealand. On the surface it looks simple and it is when you know how :0) But there's a wrinkle in to smooth out.
Here's the data:

<DATA_DS>
 <G_1>
  <SERIALNUMBER>HSS0000156</SERIALNUMBER>
  <LOCATION>1 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Lettable Date</ATTRIBDESC>
  <ATTRIBVALUE>15/JUN/11</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0000156</SERIALNUMBER>
  <LOCATION>1 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Status Reason</ATTRIBDESC>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0000276</SERIALNUMBER>
  <LOCATION>11 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Lettable Date</ATTRIBDESC>
  <ATTRIBVALUE>18/MAY/11</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0000276</SERIALNUMBER>
  <LOCATION>11 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Status Reason</ATTRIBDESC>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0001046</SERIALNUMBER>
  <LOCATION>21 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Lettable Date</ATTRIBDESC>
  <ATTRIBVALUE>21/JUN/11</ATTRIBVALUE>
  </G_1>
 <G_1>
Heres the desired output:

Serialnumber Status Reason Date
HSS0000156 Planned Major Repair/Upgrade 15/JUN/11
HSS0000276 Planned Major Repair/Upgrade 18/MAY/11
HSS0001046 Planned Major Repair/Upgrade 21/JUN/11
HSS0001303 Planned Major Repair/Upgrade 19/MAY/11
HSS0001403 Planned Major Repair/Upgrade 14/JUN/11

Hok-Min, Zen Master of the RTF template, jumped on this for me and nailed it first time. On first look you think, ah this is just a simple re-group left, using some xpath to repeat the ATTRIBVALUE element twice in the table based on the ATTRIBDESC. Along the lines of:

<?ATTRIBVALUE[../ATTRIBDESC='Planned Major Repair/Upgrade']?> and <?ATTRIBVALUE[../ATTRIBDESC='Lettable Date']?>

But you oh so very easily end up with this:

Serialnumber Status Reason Date
HSS0000156
15/JUN/11
Planned Major Repair/Upgrade
HSS0000276
18/MAY/11
Planned Major Repair/Upgrade
HSS0001046
21/JUN/11
Planned Major Repair/Upgrade
HSS0001303
19/MAY/11
Planned Major Repair/Upgrade
HSS0001403
14/JUN/11
Planned Major Repair/Upgrade

and start scratching your head? With the 10.1.3.4.1 and 11g template builders there is a nice Group Left feature that removes the need for nasty nested tables but for this slightly corner case, it drops you into a world of misery. You need to take a step back from what the wizard has given you.

Yes, you need to group by serial number but you do not need to loop over the remaining data (current-group().) Rather, you just need to drop into that current-group() and bring the values you need up to the level of the serial number group. So you just need to modify your XPATHs thus:

<?current-group()/ATTRIBVALUE[../ATTRIBDESC='Status Reason']?>

i.e. provide a complete path to the element you want within the serial number re-group level. Neat!

Template and sample data available here.


Sunday Oct 09, 2011

BIP and Mapviewer Mash Up III

This is the third installment of the BIP and Mapviewer Mashup, for the previous entries:

BIP and Mapviewer Mash Up I

BIP and Mapviewer Mash Up II

BIP and Mapviewer Mash Up III

Its been a hectic couple of weeks which has included all things mapviewer and integration. I have finally got my piece de resistance in mapping integration working but more on that next time. Its very cool in a geeky BIP, kinda way; my wife was completely fascinated when I told her all about it over dinner last night. Either that, or she has become very adept at nodding and saying 'that's nice honeycakes' at appropriate times. I hope the former but suspect the latter :0(

The next part of this mashup series, I said we would parameterize the map call. That's actually pretty easy to do, just a bit more effort with the parameters and the concat functions.

1. Set up your parameters in the data model. These do not have to be tied to the query. We support 'template ' only parameters. they just all have to be defined in the datamodel. Note the name you give to the parameter(s)

2. In your RTF template you need to declare 'interest' in the parameters. Its the same as the CURRENT_SERVER_URL format.

<?param@begin:name;defaultvalue?>

3. In the fields where you are building the encoded request string you can use the concat function to drop the parameter values into the string. In this case we are changing th map title with a parameter called 'title'

 <xsl:param xdofo:ctx="begin" name="pMapRq">concat
("%3Cmap_request%20title%3D%22",$title,
"%20basemap%3D%22world_map%22%20datasource%20%3D%20%22
obiee_navteq_sample%22%20width%3D%22640%22%20height
%3D%22480%22%20bgcolor%3D%22%23a6cae0%22%20antialiase
%3D%22false%22%20format%3D%22JPG_STREAM%22%3E")
     </xsl:param>

Its not tough but boy is it a bit of a nightmare to manage and keep track of everything in that encoded URL.

As I mentioned in my last post its a good idea to maintain the parts of the URL as un-encoded text in the template surrounded by an 'if:1=2' if statement to keep it hidden at runtime.

Next post, we get to a much more robust, easier to manage and as I mentioned cooler solution ... enter the mapping servlet. It acts as a map request broker between the BIP template and the mapviewer server. The RTF template is not full of encoded URLs but just a simple URL call to the servlet that will call the mapviewer server and stream the image back to BIP for rendering. I have built in a couple of tricks but more on that next time.

Thursday Sep 29, 2011

BIP and Mapviewer Mash Up II

Quite some time ago now I wrote the first of what I thought were going to be at least a couple of articles on getting BIP to render maps via Oracle Mapviewer. It was a real HelloWorld example with no 'World' just the 'Hello' bit.

I like to think it was like Kevin Costner's 'Waterworld', a nice idea but poorly executed and a flop at the blog box office but it would have been a perfect map for the movie. I have to admit, I think Dennis Hopper was awesome as the bad dude.

Well, I recently needed to show something more than an area of blue ocean, we needed some land. As you'll see if you go back to the original post, I have dabbled, I know how to render a map view in OBIEE but I knew I needed to lean on our resident map meister David to take this forward. David lives and breathes maps and probably knows the Oracle GIS solutions better than the folks that wrote them.

After a conversation, I was on the right track with the original post, mapviewer has an XML API that is accessible via a URL. Its just a case of building the URL and calling mapviewer and getting the result rendered by BIP. One thing that David noted was to not use the XML data that the template had access to. You can create your set of name/value pairs from the XML and pass it on to the URL. But imagine doing that for even a map showing data across all 50 states of the US or countries across the EU or APAC. It's going to get large very very quickly. The XML API does support passing a query to the mapviewer server for it to execute fetch and format the data into a map. Yep, we're breaking one of Publisher's cardinal rules, going back to the db for more data but the benefits far out weigh the costs. Armed with this information I got going. We have been working with a customer that needs this functionality (Hi Wilson :-) so I had a sample XML request to play with:

<?xml version="1.0" standalone="yes"?> <map_request  title="US Renal Disease Rates"  basemap="world_map"  datasource = "obiee_navteq_sample"  width="640"  height="480"  bgcolor="#a6cae0"  antialiase="false"  format="PNG_STREAM">  <center size="45">   <geoFeature>    <geometricProperty typeName="center">     <Point srsName="SDO:8307">     <coordinates>-96, 34</coordinates>     </Point>    </geometricProperty>   </geoFeature>  </center>  <legend bgstyle="fill:#ffffff;stroke:#ff0000" profile="MEDIUM" position="SOUTH_WEST">   <column>    <entry text="Number of Renal Disease Cases:" />    <entry style="V.POPULATION_COUNTY" tab="1" />   </column>  </legend>  <themes>   <theme name="theme1" min_scale="5.0E7" max_scale="0.0">   <jdbc_query     datasource="obiee_navteq_sample"    jdbc_srid="8307"     spatial_column="geometry"     render_style="OBIEE_NAVTEQ:V.POPULATION_COUNTY"> SELECT geometry,sqkm from obiee_state where iso_country_code='USA'</jdbc_query>   </theme>  </themes> </map_request> 

That's a hunk of XML to pass right, there is a lot going on in there. It basically sets the base map, size, center point, themes (or layers) to be added. The format is important, when you hop on over to the mapviewer doc you'll see that the format can take multiple values in our case we need 'XXX_STREAM' where XXX is the image format name e.g. PNG, JPG, etc. If you just request XXX then you get a URL string to the image on the server (that will come in handy in an upcoming post but not here) Finally the query that needs to be executed; this is the important bit for us; it needs to marry a map related column to some measure in this case 'sqkm'. Once the data set is returned, mapviewer can then 'map' the data. Our XML gets us this map to the left. Big prizes for the first one to spot whats wrong with the map? and why? Answers can be placed on the down tube of a shiny new 58cm Cervelo S5 and sent to the usual address.

For those of you that have gone back to part I of this post, you'll remember that we need to encode all of the XML before we can use it. Otherwise BIP gets all upset and reports a problem. When I started on this example I started to encode it all by hand, not a good plan and much shouting at the monitor ensued. Being lazee or smart, take your pick, I jumped on Google to look for some kind soul that had provided some web page that would do the encoding for me. Deepest thanks go out to the owner of http://meyerweb.com/eric/tools/dencoder/ you saved me from pulling out what hair I have left. For someone that has very little hair; I sure do talk about it on this blog a lot; maybe I need to talk to someone about that or get a toup?

Encoding nightmare solved but its going to be a big ugly piece of text to manage if I just encode the lot and assign it to a parameter. I decided to break it up a bit into sections and then use a concat function to bring it all back together.

<?param@begin:mReq;concat($mURL,$pXMLStr,$pMapRq,$pCenterOp,$pGeo,$pCentCl,$pLeg,$pTheme,$pMapRqCl)?>

The individual pieces make the string more manageable in terms of needing to make changes. I would recommend embedding the actual XML string into the template and noting which section belongs to which parameter and then surrounding it with an IF statement to hide it at runtime. The concat generated a big ol URL that I can test in a browser to ensure its going to return the map I want. You'll see in the template that you can test on the desktop too, as long as you have access to the mapviewer server.

Once you have the URL correct its just a case of dropping it into a form field:

<fo:external-graphic src="url({$mReq})"/>

At runtime the URL is resolved, called and the map returned to BIP for rendering. 

For those of you on 11g (I have tested this on 11.1.1.5 BIP) you can download the complete report here. You just need to upload it and probably change the data connection on the data model. It relies on the obiee_navteq db user that is installed with the BIEE sample app.

For those of you on 10g, heres the template and some sample data to play with.

Next for this series, parameterizing the XML so that users can set various features at runtime. Imagine being able to ask for 'Renal disease cases by state' for one request and then 'Liver disease cases by country' for another using the same report/template.
Following that, after my somewhat 'hit it with a big hammer until it submits' (we're good at that in our house :) approach I have plans for something a bit more sophisticated.

Wednesday Aug 03, 2011

Crispy Charts!

It's been so long ... since I last post Kan has been stepping up and providing content. You may know I love weather and have a not so secret desire to see a tornado up close and personal. Since my last post I have come close, I got trapped in deepest darkest Mississippi and hot and sultry Montgomery, AL due to the most fantastic electrical storms. St Louis nearly kept me for an extra night but in spite of tornado sirens our plane snuck out just in time to avoid the worst.

I have also dropped quite a lot of poundage and last week took part in my first Triathlon, just a 'sprint' you understand. 1/2 mile swim, 11 mile ride and a 5K crawl, sorry I mean run. My dear wife joined me and we did not finish last! I even managed to come 8th in the bicycle ride!

To ease back into the blogging world, a bit of a corner case solution that could be applied to other issues and I wanted to get it down on 'paper' before I forget it. We have a customer that is having issues with the charting engine in 10g. As many of you know, you can customize the charts beyond what the dialog box in MSWord offers. Said customer was hitting an alignment issue in one of their customizations. When generating the chart to RTF all was well but with HTML and more particularly PDF the alignment was off. The first think to remember is that by default BIP will try and generate SVG outputs for the charts if it can. In the case of RTF it can not so you get an embedded image. In HTML and PDF, it can, hence the difference in alignment.

The difference in the chart quality is surprising:

 

The image format above and the svg below

I have had to shrink the images a little but you get the idea. Your svg charts are crisp and clear, even if you zoom in to them. The image formats, not so good.

We needed to log and enhancement/bug against the chart engine but was there a workaround? 

If you have ever used the export feature in the Template Builder to take a look at the XSL, you may have noticed a bunch of parameters the engine drops in. One of those controls the chart format, _XDOCHARTTYPE. The actual line is:

<xsl:param name="_XDOCHARTTYPE">image/svg+xml</xsl:param>

So we need a way to override this parameter for the HTML and PDF outputs. Well the HTML is easy, in the user preferences you can specify that you want chart images in your HTML output, simple. For PDF, you need to do a little more work.

Hopefully you know that you can have a set of config properties either at site level or at report level. There are a bunch you can change via the UI. We need to add one manually.

For Report Level only
1. Navigate to the report folder in the BIP catalog
2. Open the xdo.cfg file. If there is not one present, go back to the UI for the report and click the Config link, then just change any property to the opposite of the site level and Apply. This will create a cfg for you back in the report directory. You an change it back later. Now open it.
3. In the properties section add the following line

<property name="xslt._XDOCHARTTYPE">'image/png'</property>

4. Save the file and re-run the report to PDF output and you will get charts embedded as images rather than SVG and the alignment issue will be gone. There is a price to pay in clarity of the charts but its a work around that works.

If you want this for all reports you can put the same property into the 'master' cfg file in Admin/Configuration. Again, you may have to change a value to get the cfg file created.  

Definitely a corner case but the technique can be applied to any parameter settings in the generated XSLFO file. The xslt. prefix also allows you to set your own paraemter values in the template that might be needed ... I'll save that explantion for another time.

Wednesday May 11, 2011

Dynamic Report Titles

Couple of mails recently on being able to externalise the header and footer for a set of templates in 10g. 11g has the whole new style templates that include not only externalised headers and footers but also the look and feel for your templates.

In 10g, you can get the headers and footers with a little effort and I have written about them in the past. The recent mails had a small wrinkle in the requirement - they wanted dynamic report titles (or content) passed to the header template. So I could have a template looking like this:

Dynamic Header

With a place holder for the report title, this could be passed at runtime, either as a runtime report parameter or embedded inside the XML data for the report. The Report Name field holds a reference to the passed parameter <?$ReportName?>. The Template Header field contains a reference to the expected inbound report name as a parameter.

<?template:Header?> <?param:ReportName;string('')?>

The 'param' command declares the name of the parameter and sets a default value i.e. in this case an empty string.

We can two approaches to getting the dynamic value into the output.

XML Data Source

Firstly, we can pull a value from the report's XML data and pass it to the report header sub-template.

We have a standard Import Template command to bring in the sub template. The clear text in the header is a standard call-template command with the added with-param command to pass the report title. You need the @inlines if you want to pass a parameter value.

<?call@inlines:Header?><?with-param:ReportName;.//DEPARTMENT_NAME?> <?end call?>

In this case we are passing the department name from the XML data. I have added a bit more to this template by using the @section in the main grouping statement (group ROW by DEPARTMENT_NAME) This has the effect of re-setting the header each time the DEPARTMENT_NAME changes and thus the 'report title' changes to the new department name.

Report Parameter Source

This is a similar approach.

We just need to declare the report parameter in the main template. The 'Report Param' field contains the parameter declaration.

<?param@begin:val1;'"Employee Report"'?>

That's double quotes surrounding the inner string with single quotes.  The header then contains:

<?call@inlines:Header?><?with-param:ReportName;$val1?> <?end call?>

'with-param' now passes the val1 parameter value to the sub template as $val1. This time the report title will be 'Employee Report' in spite of the @section command i.e, its a fixed constant value that will show on all pages.

You can find the sample templates, data and outputs here.




Friday Sep 10, 2010

Anatomy of a Template Reprise

[Read More]

Friday Jul 09, 2010

Cant see the Woods for the Trees

[Read More]

Monday Apr 12, 2010

Collapsing Bookmarks

[Read More]

Thursday Apr 01, 2010

Dynamic Grouping and Columns

[Read More]

Tuesday Mar 02, 2010

Number-Upper-Lower Sorting

[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