Thursday Jan 15, 2015

OAUG BIP SIG ... we're getting the band back together

 Today's post comes to you from Brent at STR Software. If you could help out, it would be greatly appreciated, read on ...

First off, if you are not familiar with the term SIG, it stands for Special Interest Group. OAUG facilitates a number of SIGs to bring users together that share common or industries concerning certain Oracle products.

Unfortunately, the BI Publisher SIG has been offline for a number of years and has not been given the attention it needs to be a useful resource for members of OAUG. Well... I'm getting the band back together and I need your help!

The SIG itself was formed to specifically focus on BI Publisher embedded in Oracle EBS, Peoplesoft and JD Edwards. I have put together a survey that is being emailed out to previous members of the SIG to get thoughts on how the SIG can be of service. That list is pretty old and YOU may not be on it, so if you are interested in participating in the SIG (or even if you are not), have a look at the link below and let me know your thoughts. Our first official meeting will be at Collaborate 15 in Las Vegas, hope to see you there!

Take the survey -> 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.

Thursday Oct 02, 2014

Multi Sheet Excel Output

Im on a roll with posts. This blog can be rebuilt ...

I received a question today from Camilo in Colombia asking how to achieve the following.

‘What are my options to deliver excel files with multiple sheets? I know we can split 1 report in multiple sheets in with the BIP Advanced Options, but what if I want to have 1 report / sheet? Where each report in each sheet has a independent data model ….’

Well, its not going to be easy if you have to have completely separate data models for each sheet. That would require generating multiple Excel outputs and then merging them, somehow.

However, if you can live with a single data model with multiple data sets i.e. queries that connect to separate data sources. Something like this:

Then we can help. Each query is returning its own data set but they will all be presented together in a single data set that BIP can then render. Our data structure in the XML output would be:


Three distinct data sets within the same data output.

To get each to sit on a separate sheet within the Excel output is pretty simple. It depends on how much native Excel functionality you want.

Using an RTF template you just create the layouts for each data set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIP will place each output onto a separate sheet in the workbook. If you want to name each sheet you can use the <?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s as sophisticated as it gets with the RTF templates. No calcs, no formulas, etc. Just put the output on a sheet, bam!

Using an Excel template you can get more sophisticated with the layout.

This time thou, you create the layout for each data model on separate sheets. In my example, sheet 1 holds the department data, sheet 2, the employee data and so on. Some conditional formatting has snuck in there.

I have zipped up the sample files here.


Tuesday Feb 11, 2014

Filtered Charts

A customer question this week regarding filtering a chart. They have a report with a bunch of criteria with monetary values but, rather than show all of the criteria in a pie chart, they just want to show a few. For example:

 This ...
 rather than this

 There are a couple of ways to tackle this:

1. Filter the chart data in the chart definition. Using an XPATH expression you can filter out all of the criteria you do not want to see. Open the chart definition and update the definition. You will need to update the RowCount, RowLabels and DataValues attributes in the chart definition. Adding in the following XPATH expression:

    [DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive']

so the DataValues value becomes:

    <DataValues><xsl:for-each-group select=".//G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                        DEPARTMENT_NAME='Executive']" ...

2. Create a variable in the template to hold just the values you want to chart.

    <?variable: filterDepts; /DATA_DS/LIST_G_1/G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 

Then update the chart definition with the variable for the same three attributes above, the RowCount, RowLabels and DataValues. For example:

    <DataValues><xsl:for-each-group select="$filterDepts" ...

These both work admirably, but they both require some manual updating of the chart definition which can get fiddly and a pain to maintain. I'm also just filtering for three departments, when you get up to 5 or 6 then the XPATH starts to become a pain to maintain. Option 2 alleviates this somewhat because you only need to define the filter once to create the filtered variable.
A better option may be ...

3. Force the effort down into the data layer. Create another query in the report that just pulls the data for the chart.

LIST_G2/G_2 holds the data for the chart. Then all you need do is create a vanilla chart on that particular section of the data.

Yes, there is some overhead to re-fetch the data but this is going to be about the same if not less than the extra processing required in the template with options 1 and 2. This has another advantage, you can parametrize the criteria for the user. You can create a parameter to allow the user to select, in my case, the department(s) they want to chart.

Its simple enough to create the multi-select parameter and modify the query to filter based on the values chosen by the user.

Sample report (including data model and layout template here) just un-archive into your catalog.
RTF Template plus sample data available here.

Monday Feb 10, 2014

Alternate Tray Printing

Since we introduced support for check printing PCL escape sequences in i.e. being able to set the micr font or change the print cartridge to the magnetic ink for that string. I have wanted to test out other PCL commands, particularly, changing print trays. Say you have letter headed paper or pre-printed or colored paper in tray 2 but only want to use it for the first page or specific or for a separator page, the rest can come out of plain ol Tray 1 with its copier paper.

I have had a couple of inquiries recently and so, I finally took some time to test out the theory. I should add here, that the dev team thought it would work but were not 100%. The feature was built for the check printing requirements alone so they could not support any other commands. I was hopeful thou!
In short, it works!

I can generate a document and print it with embedded PCL commands to change from Tray 1 (&l4H) to Tray 2 (&l1H ) - yep, makes no sense to me either. I got the codes from here, useful site with a host of other possibilities to test.

For the test, I just created a department-employee listing that broke the page when the department changed. Just inside the first grouping loop I included the PCL string to set Tray 1.

<pcl><control><esc/>&l4H </control> </pcl>

Note, this has to be in clear text, you can not use a formfield.
I then created a dummy insert page using a template and called it from just within the closing department group field (InsertPAGE field.) At the beginning of the dummy page I included the PCL string to get the paper from Tray 2:

<pcl><control><esc/>&l1H</control> </pcl>

When you run this to PDF you will see the PCL string. I played with this and hid it using a white font and it worked great, assuming you have white paper :)

When you set up the printer in the BIP admin console, you need to ensure you have picked the 'PDF to PCL Filter' for the printer.

If you dont want to have PCL enabled all the time, you can have multiple definitions for the same printer with/with out the PCL filter. Users just need to pick the appropriate printer instance. Using this filter ensures that those PCL strings will be preserved into the final PCL that gets sent to the printer.

Example files here. Official documentation on the PCL string here.

Happy Printing!

Monday Nov 25, 2013

Conditional Borders

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




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

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

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

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

Thursday Nov 21, 2013

Desktop Testing XSL

Bit of a corner case this week but I wanted to park this as much for my reference as yours. Need to be able to test a pure XSL template against some sample data? Thats an XSL template that is going to generate HTML, Text or HTML. The Template Viewer app in the BI Publisher Desktop group does not offer that as an option. It does offer XSL-FO proccesing thou.

A few minutes digging around in the java libraries and I came up with a command line solution that is easy to set up and use.

1. Place your sample XML data and the XSL template in a directory
2. Open the lib directory where the TemplateViewer is installed. On my machine that is d:\Oracle\BIPDesktop\TemplateViewer\lib
3. Copy the xmlparserv2.jar file into the directory created in step 1.
4. Use the following command in a DOS/Shell window to process the XSL template against the XML data.

java -cp ./xmlparserv2.jar oracle.xml.parser.v2.oraxsl fileX.xml fileY.xsl > fileX.xls

The file generated will depend on your XSL. For an Excel output, you would instruct the process to generate fileX.xls in the same folder. You can then test the file with Excel, a browser or a text editor. Now you can test on the desktop until you get it right without the overhead of having to load it to the server each time.

To be completely clear, this approach is for pure XSL templates that are designed to generate text, html or xml. Its not for the XSLFO templates that might be used at runtime to generate PDF, PPT, etc. For those you should use the Template Viewer application, it supports the XSLFO templates but not the pure XSL templates.

If your template still falls into the pure XSL template category. This will be down to you using some BIP functionality in the templates. To get it to work you'll need to add in the Publisher libraries that contain the function e.g. xdo-core.jar, i18nAPI_v3.jar, etc to the classpath argument (-cp.)

So a new command including the required libraries might look like:

java -cp ./xmlparserv2.jar;./xdo-core.jar;./i18nAPI_v3.jar 
                            oracle.xml.parser.v2.oraxsl fileX.xml fileY.xsl > fileX.xls

 You will need to either move the libraries to the local directory, my assumption above or include the full path to them. More info here on setting the -cp attribute.

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!

Friday May 10, 2013

Building on Subject Areas

The new release of BI Publisher has a very nice new feature for those of you wanting to build reports on top of the BI Server data model. In previous releases you would need to either write the logical sql yourself or build an Answer request and copy the SQL from the Advanced tab and paste it into the BIP data modeler.

With the new release comes the ability to create reports without the need for a data model at all. You have the option when creating a new report to use a subject area directly.

 Once you have selected the subject area you are interested in you can decide on whether to continue into the wizard to help you build the layout. Or to strike out on your own and build the layout yourself.

If you go for the latter and load up the layout editor, you get to see all of the data items you would see in the Answers builder in the data tree. Its then a case of dragging and dropping the columns into the layout, just as you would normally with a sample data source.

Once you are back to the report editor, the final step is to add some parameters. 

This is a little different to a conventional BIP report. There is no data model definition per se i.e the logical SQL is not stored but rather, the columns you added to the layout and the subject area(s) you pulled them from. Yes' you can go across subject areas, but you need to know if its going to make sense or even work before you add more. You add more subject areas click on the subject area name where the data model name normally resides. You'll then get a shuttle dialog that lets you add more subject areas. You can then add columns in the layout builder.
Getting back to the parameters, on the report editor page, click the Parameters link (top right.) This will open the parameters dialog.

You can add parameters and set how they will be displayed; whether folks can select all; do they see check boxes, a drop box or text box; whether other parameters should be limited by the choice made for this box. Everything you get with a regular BIP parameter.

Finally, the report rendered with the parameters.

If you have a need to build a more highly formatted report on the BI Server data then this is definitely the way to go. This approach really does open up BIP reporting to business users. No need to write SQL, just pick the columns you want and format them in a simple to use interface.

Before you ask, you can not build report layouts in MSWord or Excel for this type of data source, not yet anyhoo :0)

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)

Wednesday Nov 14, 2012

Chart Filtering

Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data?

In an RTF template, you need to do a little finagling in the chart definition. In an online template, a couple of clicks and you're done.


Build your chart as you would normally to include all the data to start with.

Now flip to the Advanced tab to see the code behind the chart. Its not very pretty but with a little effort you can get it looking a little more friendly. Here's my chart showing employees and their salaries.

<Graph depthAngle="50" depthRadius="8" seriesEffect="SE_AUTO_GRADIENT">
 <LegendArea visible="true"/>
 <Title text="Executive Department Only" visible="true" horizontalAlignment="CENTER"/>
 <LocalGridData colCount="{count(.//G_2)}" rowCount="1">
   <xsl:for-each select=".//G_2">
    <Label><xsl:value-of select="EMP_NAME"/></Label>
    <xsl:for-each select=".//G_2">
     <Cell><xsl:value-of select="SALARY"/></Cell>

Note the emboldened text. Its currently grabbing all values in the G_2 level of the data. We can use an XPATH expression to filter the data to the set we want to see. In my case I want to only see the employees that are in the Executive department. My  data is structured thus:


            <JOB_TITLE>Public Accountant</JOB_TITLE>
            <EMP_NAME>William Gietz</EMP_NAME>

So the XPATH expression Im going to use to limit the data to the Executive department would be .//G_2[../DEPARTMENT_NAME='Executive'] Note the ../ moves the parser up the XML tree to be able to test the DEPARTMENT_NAME value. I added this XPATH expression to the three instances that need it ColCount, ColLabels and RowData. Its simple enough to do. Testing your XPATH expression is easier to do using a table of data. Please note, as soon as you make changes to the chart code. Going back to the Builder tab, you'll find that everything is grayed out. I recommend you make all the changes you can via the chart dialog before updating the code.

Online Template

Implementing the filter is much simpler, there is a dialog box to help you out. Add you chart and fill out the various data points you want to show. then hit the Filter item in the ribbon above the chart. That will pop the filter dialog box where you can then add a filter to the chart.

  You can add multiple filters if needed and of course you can use the Manage Filters button to re-open and edit the filters.

Pretty straightforward stuff!

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.

Tuesday May 08, 2012

Port 80 Goodness for Publisher

Do you ever tire of remembering what port BIP is running on when you enter the URL? Ever wish you could just type:


and be done with it? Well its really not that hard to do. Its documented how to front the web logic server with a web server whether that be Microsoft's IIS or maybe Apache. Well with my success at getting BIP on to my Ubuntu laptop I thought I would give Apache a whirl being as I dont think IIS would run on my machine :)

I did start off with the docs but as usual Googling got me faster, more concise instructions with some help if things went awry. Of course, BIP has nothing to do with the configuration other than to tell Apache where the xmlpserver app is running and on what port. So its really just an exercise in installing Apache and then configuring it.

Rather than re-invent the wheel, check out Vikas' blog post here -  Yes, its specific to Ubuntu but can be applied to any flavor of Linux in this case. For you 'windowers' out there, this will get you on the road to non port typing bliss,

For linux folks, just follow Vikas' excellent post and just replace the references to the 'medrec' application with 'xmlpserver' and change the port from 7011 to 7001. You can of course add the /console app to the list as well.

I have covered web logic server installs ie 11.x. If you re running earlier versions on OC4J the Oracle AS documentation is pretty good on how to get BIP on port 80.

In no time you'll have forgotten that BIP even runs on a specific port.

Monday May 07, 2012

Running BIP on Ubuntu!

I am a complete Ubuntu (Linux) convert ... my work laptop runs 64 bit Ubuntu and Im very happy with it. Yes I do still need windblows for a few things and for testing but I would say 80-90% of my time is spent in Ubuntu. Not so good is Oracle's support for Ubuntu, some of that I guess, is around Ubuntu being seen primarily for the desktop and not for servers and also Ubuntu's interesting at times, implementation of Linux.

Case in point, the recent release of the BI Publisher Trial Edition (see here) we state support for Linux but to be fair, we narrow that down to Oracle Linux Redhat and SLES. OK, but as its so lightweight it would be great to be able to fire it up on my laptop without the need for a winddoze VM. Trying the installer or more appropriately the 'configurer' it falls at the first hurdle complaining about the script.
Thats easily fixed, the pain point in question is the -s switch for the password entry for the admin user you create. Just open the and remove the -s references (x2).

read -s -p "Enter a password for the Administrator user: " WLS_ADMIN_PWD
echo ""
read -s -p "Confirm password: " WLS_ADMIN_PWD2

Rerunning and it falls over a little further into the configuration.
At this point I want to thank Juergen in Germany for digging into the sub scripts and figuring out what to do. I have to admit, I had put the investigation on to the back burner while I have been traveling. Juergen found the problem in the ../server/wls/ file. It checks for the MW_HOME and JAVA_HOME environment variables and even if you have set them it still complains. The easiest fix is to just comment out the checks for the variables.

# Users must set $MW_HOME variable
if [[ -z "$MW_HOME" || ! -d $MW_HOME ||  ! "$(ls -A $MW_HOME)" ]]; then
  echo "ERROR: You must set MW_HOME and it must point to a directory".
  echo "       where an installation of WebLogic exists. Ensure you point"
  echo "       this variable to the extract location of the zip distribution."
  exit 1;

# Users must set $JAVA_HOME variable if [[ -z $JAVA_HOME || ! -d "${JAVA_HOME}/bin" ]]; then   echo "ERROR: You must set JAVA_HOME and point it to a valid location"   echo "       of where your JDK has been installed"   exit 1; fi

Just comment out the red text with a # at the beginning of the line.

Now when you run the main configureBIP script it completes and successfully starts the WLS server and consequently BIP. Its real fast to start and fast to run and best of all I have another reason I can avoid windblows!

Yes I have boring windows colors but what the heck, its not windows!

Thursday May 03, 2012

Proxy Images in EBS

Gareth Roberts, founder of Virtuate, a consulting outfit based in New Zealand, came up with a doh! moment for me this week. He has been working with Publisher and trying to get images into his reports that are sitting behind a proxy server. This is all inside E Business Suite so no means to use the JVM proxy settings when the concurrent manager starts up. The support folks found the solution that I had completely forgotten about ... I have been away from EBS too long :(

When the concurrent manager (CM) starts a java based job (concurrent program) such as a Publisher one. It fires up a new JVM instance for it. In their wisdom the CM development folks left an 'options' field on the concurrent program definition where you can add JVM start up parameters.

So to allow Publisher to reach the proxied images adding the following to the options field:

-Dhttp.proxyHost=<proxyhost> -Dhttp.proxyPort=<proxyport>

did the trick. Why a Doh! moment? Because we also used the same field when we knew a report was going to be BIG and wanted to increase the memory heap available to that particular report at runtime i.e. -Xmx, -Xmn, etc. You can catch Gareth over on his blog waxing lyrical about all things EBS.

Pieter from support pointed out that if the application requires a proxy server then it can be done at a more global level, that is via the context file using these CP context variables:


[vispb12@pbreugel4 ~]$ grep s_afjsmarg $CONTEXT_FILE
            <AFJSMARG oa_var="s_afjsmarg" osd="Linux">-server -Xmx384m -XX:NewRatio=2 -XX:+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc</AFJSMARG>
[vispb12@pbreugel4 ~]$ grep s_afjcparg $CONTEXT_FILE
            <AFJCPARG oa_var="s_afjcparg" osd="Linux">-client</AFJCPARG>

More on that approach here - 

Wednesday May 02, 2012

BI Publisher has hit the gym

As Pieter reminded me this morning a sleek, slim, new installer is available for BIP 11g. The Oracle BI Publisher 11g Trial Edition has been released on OTN. In comparison to the fatty monster that is, OBIEE Suite, tipping the scales at more than 7Gb; BIP Trial Ed is a svelte, muscular beauty a tad under 600 Mb. A fully functional, free server provided for evaluation purposes. It is not intended for production deployments thou. For that you have to go start the BIEE download and go for a cup of joe.

The installer includes the Derby database and BIP is configured out of the box with a bunch of new demos and samples so just download, unpack, run the config script and you're ready to roll. You can of course hook it up to your own data sources and get building. Check the links below.

BI Publisher Trial Edition 11g for Windows 64 bit (587 MB)

BI Publisher Trial Edition 11g for Windows 32 bit (582 MB)

Windows installers include Java SE 6 Runtime Environment

BI Publisher Trial Edition 11g for Linux 32 and 64 bit
(533 MB)

Linux installer requires Java SE 6 Runtime Environment (download)

Install Guide | Quick Start Guide | Getting Started Tutorial

BIG NOTICE ... right here, read this!

BI Publisher Trial Edition is not supported for production deployments. 
You can save any reports developed on the Trial Edition and upload to your production environment.

Tuesday May 01, 2012

Fusion Middleware Innovation Awards 2012

Is your organization using Oracle BI  to deliver unique business value? If so, submit a nomination today for the Oracle Fusion Middleware Innovation Awards. These awards honor customers for their cutting-edge solutions using Oracle Fusion Middleware. Winners are selected based on the uniqueness of their business case, business benefits, level of impact relative to the size of the organization, complexity and magnitude of implementation, and the originality of architecture. The awards will be presented during Oracle OpenWorld 2012 (September 30-October 4) in San Francisco.

Thats the official blurb out of the way, I know you are out there.. I have seen and heard about some very cool uses of Oracle BI. I'm not limiting things to BI Publisher here, nor even just Oracle BI but any use of Oracle middleware. Maybe you have integrated BIP into your ERP solution, or are allowing users to run reports via web center or retrieve BIP documents from a content management system. Maybe, you have built a kick ass billing system that integrates with BIP for invoice generation and OBIEE to do the ad-hoc reporting on payments, debtors, etc.

Anything qualifies, just get yourself over to the Awards site and fill out an application. You have until July 17th 2012 to get your solution into the spotlight. Good luck!


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