Wednesday May 20, 2015

BIP scheduleReport with Parameters

I have just spent an hour or so working on getting a sample scheduleReport web service working with parameter values. There are a lot of examples out there but none I have found have the parameters being set. Our doc is a little light on details on how to set them up :) In lieu of that, here's this!

        // Set the parameter values for the report. In this example we have
        // 'dept' and 'emp' parameters. We could easily query the params dynamically
        //Handle 'dept' parameter
        ParamNameValue deptParamNameVal = new ParamNameValue();
        deptParamNameVal= new ParamNameValue();
        // Create the string array to hold the parameter value(s)
        ArrayOfXsdString deptVal = new ArrayOfXsdString();
        // For individual values or multiples, add values to the 
        // string array e.g. 10,20,30
        // Asterisk used for a null value ie 'All'

        // add the array to the parameter object
        //Handle 'emp' parameter
        ParamNameValue empParamNameVal = new ParamNameValue();
        empParamNameVal= new ParamNameValue();
        ArrayOfXsdString empVal = new ArrayOfXsdString();
        // For individual values or multiples, add values to the string array 
        // empVal.getItem().add("Jennifer Whalen");
        // empVal.getItem().add("Michael Hartstein");

        // Asterisk used for a null value ie 'All'

        // add parameter values to parameter array        
        ArrayOfParamNameValue paramArr = new ArrayOfParamNameValue();
        //Now add array to values obj
        ParamNameValues pVals = new ParamNameValues();

 The pVals object can then be added to the report request object.


Hopefully, you can extrapolate to your code. JDev application available here, unzip and open the application.
Just the schedule report class is available here.

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

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.


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


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


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

Friday Feb 28, 2014

Waterfall Charts

Great question came through the ether from Holger on waterfall charts last night.

"I know that Answers supports waterfall charts and BI Publisher does not.
Do you have a different solution approach for waterfall charts with BI Publisher (perhaps stacked bars with white areas)?
Maybe you have already implemented something similar in the past and you can send me an example."

I didnt have one to hand, but I do now. Little known fact, the Publisher chart engine is based on the Oracle Reports chart engine. Therefore, this document came straight to mind. Its awesome for chart tips and tricks. Will you have to get your hands dirty in the chart code? Yep. Will you get the chart you want with a little effort? Yep. Now, I know, I know, in this day and age, you should get waterfalls with no effort but then you'd be bored right?

First things first, for the uninitiated, what is a waterfall chart? From some kind person at Wikipedia, "The waterfall chart is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns. The columns are color-coded for distinguishing between positive and negative values."

We'll get back to that last sentence later, for now lets get the basic chart working.

Checking out the Oracle Report charting doc, search for 'floating' their term for 'waterfall' and it will get you to the section on building a 'floating column chart' or in more modern parlance, a waterfall chart. If you have already got your feet wet in the dark arts world of Publisher chart XML, get on with it and get your waterfall working.

If not, read on.

When I first starting looking at this chart, I decided to ignore the 'negative values' in the definition above. Being a glass half full kind of guy I dont see negatives right :)

Without them its a pretty simple job of rendering a stacked bar chart with 4 series for the colors. One for the starting value, one for the ending value, one for the diffs (steps) and one for the base values. The base values color could be set to white but that obscures any tick lines in the chart. Better to use the transparency option from the Oracle Reports doc.

<Series id="0" borderTransparent="true" transparent="true"/> 

Pretty simple, even the data structure is reasonably easy to get working. But, the negative values was nagging at me and Holger, who I pointed at the Oracle Reports doc had come back and could not get negative values to show correctly. So I took another look. What a pain in the butt!

In the chart above (thats my first BIP waterfall maybe the first ever BIP waterfall.) I have lime green, start and finish bars; red for negative and green for positive values. Look a little closer at the hidden bar values where we transition from red to green, ah man, royal pain in the butt! Not because of anything tough in the chart definition, thats pretty straightforward. I just need the following columns START, BASE, DOWN, UP and FINISH. 

UP 0
BASE 180
UP 0
BASE 150
UP 0

 Bar 1 - Start Value
 Bar 2 - PROD1
 Bar 3 - PROD2

and so on. The start, up, down and finish values are reasonably easy to get. The real trick is calculating that hidden BASE value correctly for that transition from -ve >> + ve and vice versa. Hitting Google, I found the key to that calculation in a great page on building a waterfall chart in Excel from the folks at Contextures.  Excel is great at referencing previous cell values to create complex calculations and I guess I could have fudged this article and used an Excel sheet as my data source. I could even have used an Excel template against my database table to create the data for the chart and fed the resulting Excel output back into the report as the data source for the chart. But, I digress, that would be tres cool thou, gotta look at that.
On that page is the formula to get the hidden base bar values and I adapted that into some sql to get the same result.

Lets assume I have the following data in a table:

PROD1 -20
PROD2 -30
PROD3 50
PROD4 60

The sales values are versus the same period last year i.e. a delta value.  I have a starting value of 200 total sales, lets assume this is pulled from another table.
I have spent the majority of my time on generating the data, the actual chart definition is pretty straight forward. Getting that BASE value has been most tricksy!

I need to generate the following for each column:







150 30 0
0 150 0 50 0
0 200
0 60 0
0 0 0 0 260

Ignoring the START and END values for a second. Here's the query for the PRODx columns:

, UP
,  0 AS STRT

The inner query is breaking the UP and DOWN values into their own columns based on the SALES value. The LAG function is the cool bit to fetch the UP value in the previous row. That column is the key to getting the BASE values correctly.

The outer query just has a calculation for the BASE_VAL.


The SUM..OVER allows me to iterate over the rows to get the calculation I need ie starting value (200) + the running sum of LAG_UP - DOWN. Remember the LAG_UP value is fetching the value from the previous row.
Is there a neater way to do this? Im most sure there is, I could probably eliminate the inner query with a little effort but for the purposes of this post, its quite handy to be able to break things down.

For the start and end values I used more queries and then just UNIONed the three together. Once note on that union; the sorting. For the chart to work, I need START, PRODx, FINISH, in that order. The easiest way to get that was to add a SORT_KEY value to each query and then sort by it. So my total query for the chart was:

, 200 STRT
, 0 UP
, 0 DOWN
, UP
, 200 + (SUM(LAG_UP - DOWN) 
,  0 AS STRT
, 0 STRT
, 0 UP
, 0 DOWN
GROUP BY 1,2,3,4,6

A lot of effort for a dinky chart but now its done once, doing it again will be easier. Of course no one will want just a single chart in their report, there will be other data, tables, charts, etc. I think if I was doing this in anger I would just break out this query as a separate item in the data model ie a query just for the chart. It will make life much simpler.
Another option that I considered was to build a sub template in XSL to generate the XML tree to support the chart and assign that to a variable. Im sure it can be done with a little effort, I'll save it for another time.

On the last leg, we have the data; now to build the chart. This is actually the easy bit. Sadly I have found an issue in the online template builder that precludes using the chart builder in those templates. However, RTF templates to the rescue!

Insert a chart and in the dialog set up the data like this (click the image to see it full scale.)

Its just a vertical stacked bar with the BASE_VAL color set to white.You can still see the 'hidden' bars and they are over writing the tick lines but if you are happy with it, leave it as is. You can double click the chart and the dialog box can read it no problem. If however, you want those 'hidden' bars truly hidden then click on the Advanced tab of the chart dialog and replace:

<Series id="1" color="#FFFFFF" />


<Series id="1" borderTransparent="true" transparent="true" />

and the bars will become completely transparent. You can do the #D and gradient thang if you want and play with colors and themes. You'll then be done with your waterfall masterpiece!

Alot of work? Not really, more than out of the box for sure but hopefully, I have given you enough to decipher the data needs and how to do it at least with an Oracle db. If you need all my files, including table definition, sample XML, BIP DM, Report and templates, you can get them here.

Monday Feb 24, 2014

Wildcard Filtering continued

I wrote up a method for using wildcard filtering in your layouts a while back here. I spotted a followup question on that blog post last week and thought I would try and address it using another wildcard method. 

I want to use the bi publisher to look for several conditions using a wild card. For example if I was sql it would look like this:

if name in ('%Wst','%Grt')

How can I utilize bi publisher to look for the same conditions.

This, in XPATH speak is an OR condition and we can treat it as such. In the last article I used the 'starts-with' function, its a little limiting, there is a better one, 'contains'. This is a much more powerful function that allows you to look for any string within another string. Its case insensitive so you do not need to do upper or lowering of the string you are searching to get the desired results.
Here it is in action:

For the clerks filter I use :


and to find all clerks and managers, I use:

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk') or contains(JOB_TITLE,'Manager')];./JOB_TITLE?>

Note that Im using re-grouping here, you can use the same XPATH with a regular for-each. Also note the lower case 'or' in the second expression. You can also use an 'and' too.

This works in 10 and 11g flavors of BIP. Sample files available here.

Wednesday Oct 23, 2013

BIP 11g Dynamic SQL

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime.

With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny.

If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db.

Here's a simple plsql package with the 'beforedata' function trigger.


create or replace PACKAGE BIREPORTS AS 

 whereCols varchar2(2000);
 FUNCTION beforeReportTrig return boolean;




  FUNCTION beforeReportTrig return boolean AS 
      whereCols := ' and d.department_id = 100';
      RETURN true;
   END beforeReportTrig;


you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example.

I have my package compiling successfully in the db. Now, onto the BIP data model definition.

1. Create a new data model and go ahead and create your query(s) as you would normally.

2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols.


select     d.DEPARTMENT_NAME,
 from    "OE"."EMPLOYEES" e,


Note that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code.

3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.
In my case I get to see the following:

Select the BEFOREREPORTTRIG function (or your name) and shuttle it across.

4. Save your data model and now test it. For now, you can update the where clause via the plsql package.

Next time ... parametrizing the dynamic clause.

Thursday Oct 10, 2013

Mobile App Designer

Back in August a new Oracle mobile solution jumped out of the gate, the Mobile App Designer (MAD). I seem to have been on the road every week for the last, goodness knows how many weeks. I have finally found some time this week to get down and work with it. Its pretty cool and above all, its capable of providing a mobile platform independent reporting solution.

But you already have a mobile application! Yep, and I think they both sit quite comfortably together. The Oracle BI Mobile Application is available from the App Store for Apple users. Its a great app, build reports, dashboards and BIP reports for your browser based users and your Apple app users can take advantage of them immediately.

MAD takes the next step forward. Maybe you don't use or can not use Apple mobile devices? Maybe you need to build something more specific around a business area that provides users with a richer experience, beyond what Answers and Dashboards can offer. However, you do not want to have to rely of the tech folks to build the mobile application, thats just piling more work on them. You also want to be platform agnostic, you might have a mix of mobile platforms. MAD can help.

For those of you that have already used the Online Template layout editor with BI Publisher, you already know how to build a mobile application. The MAD interface is essentially the online template builder user interface, tweaked for a mobile destination ie a phone or tablet.

You build your data model as you would normally including the newer direct data model build on a subject area from OBIEE.

Then start to create the 'pages' of your application and the content to sit on those pages. All the normal stuff, tables, pivot tables, charts, images plus accordians, filters and repeating objects. On top of that is the ability to then extend the visual objects that are available to users. Maps (google or oracle), D3 visuals, gantt charts, org charts, if you can either write the code or leverage an existing javascript library, MAD has the extension framework to support it.

You can build and test in a browser and then deploy to your own BI App Store. Users, on their mobile devices, can then subscribe to an application. They can open and interact with your app using their phone or tablet's interactive features just as they would with a native application.  As you update your app and add new features the changes will be picked up the next time your users open the application.

Interested? Want to know more? The Oracle MAD home page has a ton of content including tutorials, etc. We are planning to dig into MAD in forthcoming posts. The geek in me wanted to be able to build plugins using the D3 and other visuals. I have been working with Leslie on some of the documentation and we'll be sharing some of that 'plugin' doc and how tos in the coming weeks.

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:


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!

Thursday May 23, 2013

BI Publisher 11g Training - Jul 1-3

For those of you still sitting on the 10g fence ... or if you're new to Publisher 11g, take advantage of this educational opportunity:

Oracle BI Publisher 11g R1: Fundamentals

Learn To:

    Create data models by using the Data Model Editor.
    Create BI Publisher reports based on data models.
    Create report layouts by using the Layout Editor (online).
    Create reports based on OBI EE data sources.
    Publish the reports on OBI EE Dashboards.
    Schedule reports and burst these reports.

Date: 01-JUL-13
Duration: 3 days
Location: Online

Click here for more details and to enroll

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'

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 Sep 27, 2012

Quick Quips on QR Codes

Yes, I'm an alliterating all-star; I missed my calling as a newspaper headline writer.
I have recently received questions from several folks on support for QR codes. You know them they are everywhere you look, even here!

How does Publisher handle QR codes then? In theory, exactly the same way we handle any other 2D barcode font. We need the font file, a mapping entry and an encoding class. With those three pieces we can embed QR codes into any output.

To test the theory, I went off to IDAutomation, I have worked with them and many customers over the years and their fonts and encoders have worked great and have been very reliable.
They kindly provide demo fonts which has made my life so much easier to be able to write posts like this. Their QR font and encoder is a little tough to find. I started here and then hit the Demo Now button. On the next page I hit the right hand Demo Now button. In the resulting zip file you'll need two files: >> Automation2DFonts >> TrueType >> IDAutomation2D.ttf
 Java Class Encoder >> IDAutomation_JavaFontEncoder_QRCode.jar - the is useful to see how to call the encoder.

The font file needs to be installed into the windows/fonts directory, just copy and paste it in using file explorer and windows will install it for you. Remember, we are using the demo font here and you'll see if you get your phones decoder to looks a the font above there is a fixed string 'DEMO' at the beginning. You want that removed? Go buy the font from the IDAutomation folks.

The Encoder

Next you need to create your encoding wrapper class. Publisher does ship a class but its compiled and I do not recommend trying to modify it, you can just build your own. I have loaded up my class here. You do not need to be a java guru, its pretty straightforward. I'd recommend a java IDE like JDeveloper from a convenience point of view. I have annotated my class and added a main method to it so you can test your encoders from JDeveloper without having to deploy them first. You can load up the project form the zip file straight into JDeveloper.

Next, take a look at IDAutomation's example java class and you'll see:

QRCodeEncoder qre=new QRCodeEncoder();
 String DataToEncode = "IDAutmation Inc.";
 boolean ApplyTilde = false;
 int EncodingMode = 0;
 int Version = 0;
 int ErrorCorrectionLevel = 0;
 System.out.println( qre.FontEncode(DataToEncode, ApplyTilde,
                        EncodingMode, Version, ErrorCorrectionLevel) );

You'll need to check what settings you need to set for the ApplyTilde, EncodingMode, Version and ErrorCorrectionLevel. They are covered in the user guide from IDAutomation here. If you do not want to hard code the values in the encoder then you can quite easily externalize them and read the values from a text file. I have not covered that scenario here, I'm going with IDAutomation's defaults and my phone app is reading the fonts no problem.

Now you know how to call the encoder, you need to incorporate it into your encoder wrapper class. From my sample class:

      Class[] clazz = new Class[] { "".getClass() };  
      ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));
      ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));
      ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));
      ENCODERS.put("qrcode",mUtility.getClass().getMethod("qrcode", clazz));

I just added a new entry to register the encoder method 'qrcode' (in red). Then I created a new method inside the class to call the IDAutomation encoder.

/** Call to IDAutomations QR Code encoder. Passing the data to encode
     Returning the encoded string to the template for formatting **/
public static final String qrcode (String DataToEncode)
  QRCodeEncoder qre=new QRCodeEncoder();
   boolean ApplyTilde = false;
   int EncodingMode = 0;
   int Version = 0;
   int ErrorCorrectionLevel = 0;
  return qre.FontEncode(DataToEncode, ApplyTilde, EncodingMode, Version, ErrorCorrectionLevel);

Almost the exact same code in their sample class. The DataToEncode string is passed in rather than hardcoded of course.

With the class done you can now compile it, but you need to ensure that the IDAutomation_JavaFontEncoder_QRCode.jar is in the classpath.
In JDeveloper, open the project properties >> Libraries and Classpaths and then add the jar to the list. You'll need the publisher jars too. You can find those in the jlib directory in your Template Builder for Word directory.

Note! In my class, I have used

package oracle.psbi.barcode;

As my package spec, yours will be different but you need to note it for later.

Once you have it compiling without errors you will need to generate a jar file to keep it in.
In JDeveloper highlight your project node >> New >> Deployment Profile >> JAR file. Once you have created the descriptor, just take the defaults. It will tell you where the jar is located. Go get it and then its time to copy it and the IDAutomation jar into the Template Builder for Word directory structure.

Deploying the jars

On your windows machine locate the jlib directory under the Template Builder for Word install directory. On my machine its here, F:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib. Copy both of the jar files into the directory.

The next step is to get the jars into the classpath for the Word plugin so that Publisher can find your wrapper class and it can then find the IDAutomation encoder. The most consistent way I have found so far, is to open up the RTF2PDF.jar in the same directory and make some mods.

First make a backup of the jar file then open it using winzip or 7zip or similar and get into the META-INF directory. In there is a file, MANIFEST.MF. This contains the classpath for the plugin, open it in an editor and add the jars to the end of the classpath list. In mine I have:

Manifest-Version: 1.0
Class-Path: ./activation.jar ./mail.jar ./xdochartstyles.jar ./bicmn.jar ./jewt4.jar 
./share.jar ./bipres.jar ./xdoparser.jar ./xdocore.jar ./xmlparserv2.jar 
./xmlparserv2-904.jar  ./i18nAPI_v3.jar ./versioninfo.jar 
./barcodejar.jar ./IDAutomation_JavaFontEncoder_QRCode.jar
Main-Class: RTF2PDF

I have put in carriage returns above to make the Class-Path: entry more readable, make sure yours is all on one line. Be sure to use the ./ as a prefix to the jar name. Ensure the file is saved inside the jar file 7zip and winzip both have popups asking if you want to update the file in the jar file.
Now you have the jars on the classpath, the Publisher plugin will be able to find our classes at run time.

Referencing the Font

The next step is to reference the font location so that the rendering engine can find it and embed a subset into the PDF output. Remember the other output formats rely on the font being present on the machine that is opening the document. The PDF is the only truly portable format.

Inside the config directory under the Template Builder for Word install directory, mine is here,
F:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config.
You'll find the file, 'xdo example.cfg'. Rename it to xdo.cfg and open it in a text editor.
In the fonts section, create a new entry:

       <font family="IDAutomation2D" style="normal" weight="normal">
             <truetype path="C:\windows\fonts\IDAutomation2D.ttf" /> 

Note, 'IDAutomation2D' (in red) is the same name as you can see when you open MSWord and look for the QRCode font. This must match exactly. When Publisher looks at the fonts in the RTF template at runtime it will see 'IDAutomation2D' it will then look at its font mapping entries to find where that font file resides on the disk. If the names do not match or the font is not present then the font will not get used and it will fall back on Helvetica.

Building the Template

Now you have the data encoder and the font in place and mapped; you can use it in the template. The two commands you will need to have present are:

<?register-barcode-vendor:'ENCODER WRAPPER CLASS'; 'ENCODER NAME'?> 

for my encoder I have:

<?register-barcode-vendor:'oracle.psbi.barcode.BarcodeUtil'; 'MyBarcodeEncoder'?>

Notice the two parameters for the command.
The first provides the package 'path' and class name (remember I said you need to remember that above.)
The second is the name of the encoder, in my case 'MyBarcodeEncoder'. Check my full encoder class in the zip linked below to see where I named it. You can change it to something else, no problem.
This command needs to be near the top of the template.

The second command is the encoding command:


for my command I have

DATATEXT is the XML element that contains the text to be encoded. If you want to hard code a piece of text just surround it with single quotes.

qrcode is the name of my encoder method that calls the IDAutomation encoder. Remember this.
MyBarcodeEncoder is the name of my encoder. Repetition? Yes but its needed again.

Both of these commands are put inside their own form fields.

Do not apply the QRCode font to the second field just yet. Lets make sure the encoder is working. Run you template with some data and you should get something like this for your encoded data:


Grooovy huh? If you do not get the encoded text then go back and check that your jars are in the right spot and that you have the MANIFEST.MF file updated correctly.
Once you do get the encoded text, highlight the field and apply the IDAutomation2D font to it. Then re-run the report and you will hopefully see the QR code in your output. If not, go back and check the xdo.cfg entry and make sure its in the right place and the font location is correct.

That's it, you now have QR codes in Publisher outputs. Everything I have written above, has been tested with the 5.6.3, codelines. I'll be testing the 11g code in the next day or two and will update you with any changes.

One thing I have not covered yet and will do in the next few days is how to deploy all of this to your server. Look out for a follow up post.

One note on the apparent white lines in the font (see the image above). Once printed they disappear and even viewing the code on a screen with the white lines, my phone app is still able to read and interpret the contents no problem.

I have zipped up my encoder wrapper class as a JDeveloper project here. Just dig into the src directories to find the file if you just want the code. I have put comments into the file to hopefully help the novice java programmer out.

Happy QR'ing!

Friday May 18, 2012

Secrets Revealed to Advanced Charting

We get a lot of emails and questions here at Publisher Tower concerning charts and how to do X. I write about some of the solutions here if I think they could be useful to a wider audience but its tough to document everything for everyone's specific features.

The chart dialog in the template builder gets you so far but there are cases where you are going to have to get into the code to make things work the way you want them to. I have documented a bunch which I have pulled together as links below. But if you do venture into the chart code, where do you start?

I have re-documented the location of the chart DTD document recently as it disappeared from OTN and having bugged a few people about it, its still not there, c'est la vie. But those of you with the Template Builder for Word (TB) have your own copy you can refer to. Just dig into your TB install directory and look for the dvt-jclient.jar (11g) or bipres.jar (10g) files open them with a zip utility a dig down through the directories to oracle\dss\graph\.
There you will find the fabled and rare, graph.dtd ... this is the golden fleece of the BIP charting world. In it, you will find secrets beyond your imagination, treasures beyond compare ...  OK, its not that exciting but there is a lot of charting info to be gleaned. There is not much in the way of comments but you can at least look up features and then see what attributes they will need to achieve your needs.

Just remember, Word has almost unlimited undo's, just get stuck in a try stuff out you are not going to break anything!

Some blogged chart solutions via google.

Friday Mar 23, 2012

Updating a database connection password using a script

An interesting customer requirement that I thought was worthy of sharing today. Thanks to James for the requirement and Bryan for the proposed solution and me for testing the solution and proving it works :0)

A customers implementation of Sarbanes Oxley requires them to change all database account passwords every 90 days. This is scripted leveraging shell scripts today for most of their environments. But how can they manage the BI Publisher connections?

Now, the customer is running 11g and therefore using weblogic on the middle tier, which is the first clue to Bryans proposed solution. To paraphrase and embellish Bryan's solution a little; why not use a JNDI connection from BIP to the database. Then employ the web logic scripting engine to make updates to the JNDI as needed? BIP is completely uninvolved and with a little 'timing' users will be completely unaware of the password updates i.e. change the password when reports are not being executed.

Perfect! James immediately tracked down the WLST script that could be used here, (thanks Ravish)

Now it was just a case of testing the theory. Some steps:

  1. Create the JNDI connection in WLS
  2. Create the JNDI connection in BI Publisher pointing to the WLS connection
  3. Build new data models using or re-point data sources to use the JNDI connection.
  4. Create the WLST script to update the WLS JNDI password as needed.
  5. Test!

Some details.

  1. Creating the JNDI connection in web logic is pretty straightforward.
    1. Log into hte console and look for Data Sources under the Services section of the home page and click it
    2. Click New >> Generic Datasource
    3. Give the connection a name. For the JNDI name, prefix it with 'jdbc/' so I have 'jdbc/localdb' - this name is important you'll need it on the BIP side.
      Select your db type - this will influence the drivers and information needed on the next page. Being a company man, Im using an Oracle db.
      Click Next
    4. Select the driver of choice, theres lots I know, you can read about them I just chose 'Oracle's Driver (Thin) for Instance connections; Versions 9.0.1 and later'
      Click Next >> Next
    5. Fill out the db name (SID), server, port, username to connect and password >> Next
    6. Test the config to ensure you can connect. >> Next
    7. Now you need to deploy the connection to your BI server, select it and click Next.
      You're done with the JNDI config.
  2. Creating the JNDI connection on the Publisher side is covered here. Just remember to the connection name you created in WLS e.g. 'jdbc/localdb'
  3. Not gonna tell you how to do this, go read the user guide :0) Suffice to say, it works.
  4. This requires a little reading around the subject to understand the scripting engine and how to execute scripts. Nicely covered here. However a bit of googlin' and I found an even easier way of running the script.


Where is my script file, it can be in another directory. As part of the script your environment is set up for you so its very simple to execute.

The nitty gritty:

  1. Need to take Ravish's script above and create a file with a .py extension.
  2. Its going to need some modification, as he explains on the web page, to make it work in your environment. I played around with it for a while but kept running into errors.
    The script as is, tries to loop through all of your connections and modify the user and passwords for each. Not quite what we are looking for. Remember our requirement is to just update the password for a given connection.

    I also found another issue with the script. WLS 10.x does not allow updates to passwords using clear type ie un-encrypted text while the server is in production mode. Its a bit much to set it back to developer mode bounce it, change the passwords and then bounce and then change back to production and bounce again.
    After lots of messing about I finally came up with the following:

#  Update password for JNDI connections

print("*** Trying to Connect.... *****")
print("*** Connected *****")

print ("*** Encrypt the password ***")
en = encrypt('hr')
print "Encrypted pwd: ", en

print ("*** Changing pwd for LocalDB ***")
dsName = 'LocalDB'

print 'Changing Password for DataSource ', dsName


Its pretty simple and you can expand on it to loop through the data sources and change each as needed. I have hardcoded the password into the file but you can pass it as a parameter as needed using the properties file method. Im not going to get into the detail of that here but its covered with an example here.

Couple of points to note:
1. The change to the password requires a server bounce to get the changes picked up. You can add that to the shell script you will use to call the script above.
2. The script above needs to be run from the MW_HOME\user_projects\domains\bifoundation_domain directory to get the encryption libraries set correctly. My command to run the whole script was:


- where wlst.cmd is the scripting command line and was my update password script above.

I have not quite spoon fed everything you need to make it a robust script but at least you know you can do it and you can work out the rest I think :0)

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" />
  <xsl:for-each select=".//Row"  xmlns:xsl="">
  <xsl:if test="number(number(.//Value) div number(.//Target))&lt; number($pLim)">
   <ExceptionalRiser series="0" group="{position()-1}" fill border/> 

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="">
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 and 11g releases. 

Wednesday Dec 07, 2011

Excel 2007 Warnings!

As many of you have found out, everything is not as it seems with the Excel output that gets generated from an RTF template. It has non-Excel like limitations and the files are quite big. Those of you using Excel 2007 will have noticed another niggle. When you open an 'Excel' output you get a warning along the lines of:

The file you are trying to open, '*.XLS', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

This is down to BIP actually generating HTML and setting the mime type and extension to get Excel to open it. This is the cause of the limitations and the cause of the warnings. The limitations can be addressed by using an Excel template more on those in the user docs and elsewhere in this blog. Addressing the warning is going to require a modification to your users' registries.

Huge thanks to Abhishek Gupta for digging up a note from Oracle Support on it and thanks to whoever wrote the original. The note number is 1077728.1. I have uploaded Abhishek's snippet form the note here.

Update: Leslie has reminded me; for she is my Jimney Cricket to my Pinocchio, constantly prodding my conscious :0)
As an additional option in Excel 2007 is a new output type from RTF that generates .xlsx.
Excel 2007 is a new output type available for reports. When selected, BI Publisher generates the output in Excel XML format (.xlsx). If you have Excel 2007 or later installed, this option provides the best preservation of layout and formatting.

As noted in the comments, the Excel templates generate binary xls output not xlsx format and therefore have xls limitations in their abilities.

Thursday Dec 01, 2011

Dynamic Content using Sub Templates

I have written about sub templates in the past on a few occasions; the principle behind them is pretty simple. If you have common report components that can be shared across reports; be they blocks of text like standard contract clauses or maybe some common calculation or function, drop them into a sub template and share the love. Develop once, use everywhere!
A colleague was recently tasked with conditionally bringing into a report output, paragraphs of static text based on some user preferences. That’s an ideal candidate for a sub template approach; drop all of the paragraphs in to an RTF subtemplate and then just conditionally pull them in based on some boolean expressions.
You might, quite naturally think about conditionally importing a series of sub templates rather than defining one, with all the content. However, XSL does not allow the conditional import of sub templates so you must take the single template approach. You can of course, import multiple sub templates if you have a lot of content to bring in but in most cases I would expect a single sub template will suffice.

BIP does need to know what those paragraphs need to be for each user whether that’s as a set of parameter values or a data element in the incoming data set. For this example I have used both approaches and they work all flavors of BIP. Implementation of the sub template onto the servers is going to be a little different but the main principle is the same. I have mercilessly ripped out a nice graphic from Leslie’s (doc writer extraordinaire) documentation.

This is for the 11g version that supports loading sub templates into the report catalog as objects.  They can then be referenced in your main template using the import statement:


The subtemplate folder is going to be from the /SharedFolders  or /My Folders root. For instance, I have a sub template ‘paragraphs’ loaded into a ‘test’ folder under  Shared Folders. The import statement in my main template is ‘<?import:xdoxsl:///Test/ParaSubTemplate.xsb?>’

Update from Leslie

For those of you testing using your own My Folder area. The syn tax is

<?import:xdoxsl:///~username/path to subtemplate.xsb?> where username is your user name. For example: <?import:xdoxsl:///~tdexter/Subtemplates/Template1.xsb?>

Recommend you move them into the shared folder area in production.

For 10g you will either need to drop them into an accessible directory and use the file URI or mount them into the web server directory structure and access them via an http URI. I normally mount them in a directory under the ‘xmlpserver’ directory e.g J2EE_HOME\applications\xmlpserver\xmlpserver\subtemplates, a template is then accessible via the URI ‘http://server:port/subtemplates/template.rtf’

Make sure you set the Allow External References property to true for the report so that the sub template can be accessed.

The actual content of the sub template is pretty straight forward. It’s a series of paragraphs bounded by the ‘template’ command e.g.

<?end template?>
<?end template?>
<?end template?>
Now we have the dynamic content defined it’s a case of conditionally bringing it into the main template. For this example I have demonstrated two approaches; both rely on the required paragraph information to be in the main dataset:
1.    Using parameters to allow the user to select the appropriate paragraphs to be brought in. This means creating the parameters and ensuring that you have set the property on the data model to include the parameter values in the XML result set.

Once that’s done its just a simple case of using id statements to check if a given paragraph should be included:

<?if:.//PARA1='1'?><?call:para1?><?end if?>

This assumes my parameter is called PARA1 and that a ‘1’ means include it, it could easily be a ‘Y’ or ‘True’ value, you are just testing for it.

2.    Including a value in the data to define what paragraphs should be included. If you have stored what paragraphs should be included for a given entity i.e. customer, supplier, employee, etc. Then you can extract those values into the data set and test for them. For this demo I have a 5 character set of ‘1’s and ‘0’s to represent the paragraphs that need to be included e.g. 10110. I just use a substring command to find out if a particular paragraph needs to be included.

<?if:substring(.//PARAS,1,1)='1'?><?call:para1?><?end if?>

Where PARAS is the element holding the ‘1’s and ‘0’s string to be parsed.
You can of course use some other means of marking whether a paragraph needs to be included or not. It’s just a case of parsing out the values with a substring or similar command.
You should be able to generate dynamic content such as this:

 Notice that I have rendered the code so that I can see that the paragraphs are coming in i.e 10001 means we get paragraphs 1 and 5. I have built out demo files for the second method, available here.

Wednesday Oct 12, 2011

BIP and Mapviewer Mash Up IV

Dang folks, we're on part four already, things start to get really groovy now.
If you're jumping in at part four, here are the previous episodes:

The current approach to get the maps into the output using encoded concatenated URL strings works but if you need to make changes to the map request call it gets fiddly and annoying quite quickly and if you need to drop another map into another output you'll need to embed the code into the second RTF template. What if we could simplify all of this and create a 'map request broker' that sat between the template and the map server? Even better if that broker could handle any map request and support as many layout templates that I want. I have just the solution built and ready to roll.

Of course all three components might be running on the same physical server inside the same weblogic domain. Its a pretty simple concept and it makes life so much easier when embedding a map into an output. The servlet is called from the RTF template using a URL that is constructed on the fly; we can also pass parameters on the URL to influence the map. For my example those parameters include the mapviewer server and port (to make the template portable) the map title, what needs to be mapped and the map request file to be used. The servlet receives the request grabs the parameters off the URL. It then reads the map request files and substitutes the appropriate variable values and then makes the call to mapviewer requesting a map. Once it receives the map it streams the image back to the template for rendering by BI Publisher. No messy encoded URL's minimal concatenation in the template to create the servlet URL and I have fine control over the map request. Best of all any template can call the same servlet, we just need the base map request on the server.

The Breakdown

There are three components to consider, what's in the RTF template the servlet code and the map request. Lets look at the map request first.

<?xml version="1.0" encoding="UTF-8"?>
<map_request title="param1" 
 datasource = "obiee_navteq_sample" 
 <center size="45">
   <geometricProperty typeName="center">
    <Point srsName="SDO:8307">
     <coordinates>-96, 34</coordinates>
 <legend bgstyle="fill:#ffffff;stroke:#ff0000" 
          <entry text="Number of Renal Disease Cases:"/>
          <entry style="V.POPULATION_COUNTY" tab="1"/>
  <theme name="theme1" min_scale="5.0E7" max_scale="0.0">
    SELECT geometry, param3 
    from obiee_state 
    where iso_country_code='USA'</jdbc_query>

Its just a well formed XML file. This has loads going on and to find out more you're going to have to hit the books - the mapviewer documentation is here - check out the XML API section for some samples. I was playing with it last night and found that the 'size' attribute under the 'center' element controls the zoom level ... go figure. But combining that with the 'coordinates' value under geoFeature and some nifty LOVs in your BIP report and folks can move around a map and zoom in and out. More on that later.

 Testing your map requests is simple enough inside the mapviewer web console http://server:port/mapviewer >> Requests. Just copy, paste and submit.

The other features of note are my parameters highlighted in blue. Before I pass this request on to the mapviewer server I do a search and replace on the 3 parameters. You'll see that in the servlet code. Remember, this XML is not hard coded into the servlet, its a separate XML file in a 'resource' directory that is read by the servlet. Taking this approach I can re-use the servlet for multiple map requests.

Next, the servlet code, ready for my awesome java skills? Read on ...

package oracle.bipmapper;

//Read-write libs
//Handle the URL call
//Servlet libs
import javax.servlet.*;
import javax.servlet.http.*;

public class bipmap extends HttpServlet {
    private String CONTENT_TYPE = "";

    public void init(ServletConfig config) throws ServletException {

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response) throws ServletException,
                                                           IOException {

        doPost(request, response);

    public void doPost(HttpServletRequest request,
                       HttpServletResponse response) throws ServletException,
                                                            IOException {

        ((1)) - Fetch the URL params. Check that we have values, if not, assign defaults.
        String measure1 =
            (request.getParameter("a1").toString() == "") ? "sqkm" :
        String server =
            (request.getParameter("serv").toString() == "") ? "" :
        String port =
            (request.getParameter("port").toString() == "") ? "9704" :
        String title =
            (request.getParameter("title").toString() == "") ? "Default Title" :
        String format =
            (request.getParameter("format").toString() == "") ? "PNG_STREAM" :
        String filename =
            (request.getParameter("file").toString() == "") ? "mapreq.xml" :
            request.getParameter("file") + ".xml";

        // Needed objects for the mapviewer call
        HttpURLConnection huc = null;
        URL mapAddress = null;
        ((2)) Set the content format based on the format parameter value
        // The else value is just text, thats for the SVG support
        if (format == "PNG_STREAM") {
            CONTENT_TYPE = "image/png";
        } else if (format == "JPG_STREAM") {
            CONTENT_TYPE = "image/jpg";
        } else {
            CONTENT_TYPE = "text/javascript";


        ((3)) //Find the request file
        ServletContext context = request.getSession().getServletContext();
        String separator = System.getProperty("file.separator");
        final String TMPDIR =
        // With WLS the tempdir drops you into the _WL_user/APP_NAME/RANDOM_DIR/public
        // we need the 'war' directory to find the resource directory
        String reqFile =
            TMPDIR.substring(0, TMPDIR.lastIndexOf(separator)) + "/war/resource/" +

        ((4))// Construct the start of the URL map request
        String mapURL =
            "http://" + server + ":" + port + "/mapviewer/omserver?xml_request=";
        ((5)) // Load the XML request file
        String xmlReq = getMapReq(reqFile);
        ((6)) // Look for a replace the parameters in the XML request.
        xmlReq = xmlReq.replace("param1", title);
        xmlReq = xmlReq.replace("param2", format);
        xmlReq = xmlReq.replace("param3", measure1);

        InputStream is = null;

        ((7)) // Fetch the output stream from the mapviewer request
        try {
            mapAddress = new URL(mapURL + URLEncoder.encode(xmlReq));
            huc = (HttpURLConnection)mapAddress.openConnection();
            huc.setRequestMethod("POST"); //Use HTTP POST method.
            is = huc.getInputStream();
            // Write the image stream back to the caller ie BIP in the template layer
            OutputStream out = response.getOutputStream();
            byte[] buf = new byte[1024];
            int len = 0;
            while ((len = >= 0) {
                out.write(buf, 0, len);
        } catch (Exception e) {

    private static String getMapReq(String fname) throws {
        ((5.5)) // Fetch the XML request file and load it into a string object
        byte[] buffer = new byte[(int)new File(fname).length()];
        BufferedInputStream f = null;
        try {
            f = new BufferedInputStream(new FileInputStream(fname));
        } finally {
            if (f != null)
                try {
                } catch (IOException ignored) {
        return new String(buffer);

I have highlighted the important bits:

(1) Standard stuff here, pulling the parameters off the URL and assigning them to string variables. For the un-initiated each entry is an inline if statement.

(2) Im checking the requested output so that I can set the appropriate content type. In my example Im supporting PNG, JPG and SVG. The SVG is handled in the else condition. More on that in another post.

(3) Here I fetch the request XML file. Its in a 'resource' directory in the deployment. This was a bit of a pain, I expected the 'javax.servlet.context.tempdir' to return he root of the web application but in weblogic you end up in a 'public' directory under the root. My resource directory is actually under the 'war' directory under the root so this piece of code:

ServletContext context = request.getSession().getServletContext();
String separator = System.getProperty("file.separator");
final String TMPDIR = context.getAttribute("javax.servlet.context.tempdir").toString();
String reqFile =
            TMPDIR.substring(0, TMPDIR.lastIndexOf(separator)) + "/war/resource/" +

does some substringing and concatentation to build a path to the correct directory. Im sure there is an easier way but it works for now. Note the user of the 'separator' string so that the servlet can be deployed to windblows, unix or linux. I had a devil of a time catching that one. I developed on Linux but it would not run on windblows until I grabbed the appropriate separator ... grrr!

(4) Starting to construct the URL request for the XML map request

(5) Fetching the map request XML from the file in the resource directory

  (5.5) This is the function to read the map request file and load it int a string variable

(6) A simple replace for each of the parameters created in the map request. We could have gotten fancier here with an XML parser but its not a huge string and there are three replacements to do. Its not a slow process.

(7) Now we construct the full http request for the map. Note the encoding of the xml request string, thats a must. Once we get the result we just have the servlet write it back to the response object for BIP to render.

Nothing earth shattering, could it be better, sure, if you fail to pass enough parameters you get an ugly null pointer exception which could be handled better but just make sure you pass enough parameters :0)

Finally the RTF template, its a case of declaring parameters for the report parameters and then building the URL request to the servlet. You can see the fields in the graphic on the right. The only ones of note are:

  • The file parameter, allows you to point to any deployed request file
  • The mURL that constructs the URL. Its just a big long concat with the name|value pairs for the servlet parameters.

Then just use the external-graphic place holder as we have before to reference the mURL parameter. The curly braces {} get BIP to pre-process the value before processing the graphic.

Finally ...

Thats it, put the three pieces together et voila, you have a mapping solution that's more manageable and configurable. You can of course increase the number of parameterized values in the xml map request.

I have bundled up the JDeveloper ( project as it is ... its only really got the servlet and XML request in it. You can just copy and paste into your own project - get the project here. I have also zipped the 11g BIP report with the RTF template (Mapviewer4.rtf) get that here. For 10g'ers just unzip the xdoz file to get at the RTF template.

So, we can now get maps into our BIP outputs, the quality is pretty good but it could be better. Having spent some time digging around in the mapviewer docs I spotted that they support SVG outputs ... more on that next time.

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.


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

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


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

Friday Aug 26, 2011

Bursting and Hairloss

After last week's post on small successes after much pain and misery. Still pulling your hair out over minor things? You should be so lucky to have some to pull. I lost mine years ago, I blame my kids. In fact here's one of my kids unconsciously rubbing it in on a recent'ish diving expedition. He's the one on the right, in case you were wondering who the wrinkled old man was on the left ... yes, that's me :0( I've heard of sucking your gut in for a photograph; I now need to remember to pull the skin tight on the back of my head too :0)

However, I take comfort in the fact that:

1. He's mine
2. Both of his grandfathers and 3 of his great grandfathers are/were follicly challenged.
So he and his brother will be bald as coots by the time they hit their 20s ... ha, join the club boys!

The reason for my hair pulling? Getting bursting working in 11g. Now, Leslie, 'Super Documenter' to the rescue. It's there in black and white, just RTFM Tim! My words, not hers!

My bursting query in 10g for a simple demo starts:

select  d.department_name KEY,

But this throws an error in 11g along the lines of:

Syntax error: Encountered "KEY" at line 2, column 24

Simple fix and it is documented in the 11g user docs.

select  d.department_name as "KEY",

Not sure if this is a BIP or database choke, I'm suspecting the db. Anyhoo, change the select statement and my bursting is working again!


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!


« May 2015