Thursday May 09, 2013

Integrating BI Publisher and Forms, ADF and APEX

For those of you integrating or planning to integrate with Forms, ADF or APEX, a presentation from our friends at PITSS

Tuesday, 14.05.2013  (14-May-2013)
2:00 pm - 3:00 pm (CEST)

Free Webinar

Integrating Oracle BI Publisher with
Forms 11g, ADF and APEX

Have you already decided about how to integrate in the future your reports within your Oracle Forms, ADF or APEX applications? In view of the technical innovations in Oracle Forms 11g, we will take a closer look at Oracle BI Publisher and see, step by step, how can we reach an optimal integration of BI Publisher reports within existing Forms applications, as well as the co-existence with Oracle ADF and APEX.


Wednesday May 01, 2013

Get yourself Organized!

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

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

Figure 4-18 Sample Organization Chart

Description of Figure 4-18 follows

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

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

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

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

Thursday Apr 11, 2013

Variable Numbers to Words

Satyender posted a comment to the Numbers to Words post asking:
How can I store the result of <?xdofx:to_check_number(TOTAL_INV_AMOUNT,'USD','CASE_UPPER','DECIMAL_STYLE_WORDS')?> 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.

Tuesday Feb 26, 2013

E-Business Suite 12 - XML Publisher enabled reports

With some help from the EBS folks, I recently re-ran a report on the latest EBS environment that stores the seeded content that we ship to customers. The document here (sorry,I had to zip it), lists the reports and their publisher templates. The vast majority are still based on an Oracle Reports extract rather than a publisher extract. I can not say that 100% of the reports have a Publisher template but its close. Hopefully it'll be a useful reference.

For more information on the reports check the specific products' documentation.

Thursday Dec 13, 2012

Beat the Post-Holiday Blues with a dose of BIWA

You know its coming so why not plan ahead.  Come and join like minded professionals at the BIWA Summit 2013

Early Bird Registration ends December 14th for BIWA Summit 2013. This event, focused on Business Intelligence, Data Warehousing and Analytics, is hosted by the BIWA SIG of the IOUG on January 9 and 10, at the Hotel Sofitel, near Oracle headquarters in Redwood City, California.

Be sure to check out the many featured speakers, including Oracle executives Balaji Yelamanchili, Vaishnavi Sashikanth, and Tom Kyte, and Ari Kaplan, sports analyst, as well as the many other speakers. Hands-on labs will give you the opportunity to try out much of the Oracle software for yourself--be sure to bring a laptop capable of running Windows Remote Desktop. Check out the Schedule page for the list of over 40 sessions on all sorts of BIWA-related topics. See the BIWA Summit 2013 web site for details and be sure to register soon, while early bird rates still apply.

Klaus and Nikos will be presenting the ever popular Getting the Best Performance from your Business Intelligence Publisher Reports and Implementation and we will run 2 sessions of the BI Publisher Hands On Lab for building Reports and Data Models.

Hope to see you there.

All hail the Excel Queen

An excellent question this past week from dear ol Blighty; actually from Brian at Nextgen Clearing Ltd in the big smoke (London). Brian was developing an excel template and wanted to be able to reference the data fields multiple times inside the Excel template. Damn good question and I of course has some wacky solutions, from macros and cell referencing in Excel to pre-processing the data with an XSL stylesheet to copy the data multiple times so it could be referenced multiple times. All completely outlandish, enter our Queen of Excel, Shirley from the development team. Shirley is singlehandedly responsible for the Excel templates, I put her through six months of hell a few years back, with a host of Excel template requirements. She was more than up to the challenge and has developed some great features. One of those, is the ability to use the hidden XDO_METADATA sheet to map the data to custom named fields so they can be used multiple times in the template. So simple and very neat!

Excel template and regular Excel users will know that you can only use the naming function once ie the names have to be unique across the workbook so you can not reuse a cell/group name. To get around this you can just come up with as many cell names as you want and map them in the XDO_METADATA sheet to the data columns/fields in your XML data set:. For example:

XDO_GROUP_?G_D_DETAIL? <xsl:for-each-group select=".//G_D" group-by="./DEPTNO">

As you can see DEPTNO has been referenced twice and mapped to different named values in the left hand column. These values can then be used to name individual cells in the Excel template. You'll also notice a mix of Publisher <? ...?> and native XSL commands. So the world is your oyster on the mapping and the complexity you might need for calculations or string manipulation.

Shirley has kindly built out a sample Excel template, data and result here so you can see how it all hangs together. the XDO_METADATA sheet is hidden, just right click on the sheet names and use the Unhide command to show it.

Tuesday Dec 04, 2012

Interesting fact #123423

Question from a customer on an internal mailing list this, succintly answered by RTF Template God, Hok-Min

Q: Whats the upper limit for a sum calculation in terms of the largest number BIP can handle?

A: Internally, XSL-T processor uses double precession.  Therefore the upper limit and precision will be same as double (IEEE 754 double-precision binary floating-point format, binary64). Approximately 16 significant decimal digits, max is 1.7976931348623157 x 10308 .

So, now you know :)

Thursday Nov 29, 2012

Spring Cleaning

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

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

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

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

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

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!

Tuesday Oct 30, 2012

OpenWorld Presentations and Anatomy of an RTF Template w/ files

For those who missed it ... or those who made it and couldn't get enough, check out the presentations delivered at OpenWorld:

Overview and Roadmap

The Reporting Platform for Oracle Applications

Best Practices

and even though it wasn't presented at OpenWorld an updated version of

Anatomy of an RTF Template

to include documented example files  (RTF template, Sub-Template and sample XML data) so you can re-use and play with the code directly. 

Huge thanks to Tim and Hok-Min who did all the hard, original work on this example loaded with tips and tricks.



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!


« April 2014