By Tim Dexter-Oracle on Dec 20, 2013
This is a great overview of the Financial Statement Generator (FSG) engine from GL in EBS and how Publisher fits into the picture.Thanks to Helle Hellings on the Financials PM team.
How can you conditionally turn cells borders on and off in Publishers RTF/XSLFO templates? With a little digging you'll find what appears to be the appropriate attributes to update in your template. You would logically come up with using the various border styling options:
border-top|bottom|left|right-width border-top|bottom|left|right-style border-top|bottom|left|right-color
Buuuut, that doesnt work. Updating them individually does not make a difference to the output. Not sure why and I will ask but for now here's the solution. Use the compound border formatter border-top|bottom|left|right. This takes the form ' border-bottom="0.5pt solid #000000". You set all three options at once rather than individually. In a BIP template you use:
<?if:DEPT='Accounting'?> <?attribute@incontext:border-bottom;'3.0pt solid #000000'?> <?attribute@incontext:border-top;'3.0pt solid #000000'?> <?attribute@incontext:border-left;'3.0pt solid #000000'?> <?attribute@incontext:border-right;'3.0pt solid #000000'?> <?end if?>
3pt borders is a little excessive but you get the idea. This approach can be used with the if@row option too to get the complete row borders to update. If your template will need to be run in left to right languages e.g. Arabic or Hebrew, then you will need to use start and end in place of left and right.
For the inquisitive reader, you're maybe wondering how, did this guy know that? And why the heck is this not in the user docs?
Other than my all knowing BIP guru status ;0) I hit the web for info on XSLFO cell border attributes and then the Template Builder for Word. Particularly the export option; I generated the XSLFO output from a test RTF template and took a look at the attributes. Then I started trying stuff out, Im a hacker and proud me! For the users doc updates, I'll log a request for an update.
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.
Being some what follicly challenged, and to my wife's utter relief, the comb over is not something I have ever considered. The title is a tenuous reference to a formatting feature that Adobe offers in their PDF documents.
The comb provides the ability to equally space a string of characters on a pre-defined form layout so that it fits neatly in the area. See the numbers above are being spaced correctly. Its not a function of the font but a property of the form field.
For the first time, in a long time I had the chance to build a PDF template today to help out a colleague. I spotted the property and thought, hey, lets give it a whirl and see in Publisher supports it? Low and behold, Publisher handles the comb spacing in its PDF outputs. Exciting eh? OK, maybe not that exciting but I was very pleasantly surprise to see it working.
I am reliably informed, by Leslie, BIP Evangelist and Tech Writer that, this feature was introduced from version 10.1.3.4.2 onwards.
Official docs and no mention of comb overs here.
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; end BIREPORTS;
create or replace PACKAGE BODY BIREPORTS AS FUNCTION beforeReportTrig return boolean AS BEGIN whereCols := ' and d.department_id = 100'; RETURN true; END beforeReportTrig; END BIREPORTS;
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, "OE"."DEPARTMENTS" d where d."DEPARTMENT_ID"= e."DEPARTMENT_ID" &whereCols
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.
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 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.
Whoooo hoooo! Theres finally a new version of the BI Publisher Trial Edition available for download from OTN.
184.108.40.206.1 is the imaginative release name. Nevermind your iOS7's get some blazingly fast BIP '.7.1'!
I'll be digging into some of the new features in the coming weeks!
These docs focus on the out of the box integration. Where Siebel holds the reins and just sends the BI Publiser server publishing requests. For this you use Integration Objects to generate the data. This support doc contains a white paper on performance testing of the IOs and provides some standard tests that you can compare your system to.
I have pulled the white paper out of support to save you some time (cos Im kind like that.) Of course the latest and greatest will be on http://support.oracle.com
The following support doc covers the tuning of said objects to handle larger data sets.
Improving the performance of Siebel BI Publisher Report Generation (Doc ID 1392449.1)
I have linked a converted PDF of the doc as of today, access http://support.oracle com and search via the Doc Id for the latest and greatest.
For completeness of the post, heres a link to post on the Siebel-BIP Business Service Integration.
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.
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:
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!
This post is more of a bookmark for me so that I stop bugging the brown stuff out of the John the Siebel-BIP product manager. I have had multiple customers over the past two weeks asking for help around the integration. What's its capable of? How can I allow my users to click a button to run a BIP report? How can I kick off a report from a Siebel workflow?
Start right here - this is a great white paper explaining whats now available with the integration using, the Siebel Report Business Service. Once you have consumed that from start to finish.
Get on over to Oracle support and look for the following note that has code samples and lots of other good stuff!
Siebel BI Publisher Reports Business Service (220.127.116.11+)
The Reports Business Service enables BI Publisher reports to be executed from the Siebel application via a Workflow Process, or through scripting. The report is generated in the background by connecting to the BI Publisher server. The report output is stored in the Siebel File System and accessed from the My BI Publisher Reports view. Alternatively using appropriate methods, the report can be attached to an entity or sent to a particular delivery channel.
Charting in BIP is such fun, well sometimes it is. Not so much today, at least not for Ron in San Diego. He needed a horizontal bar chart showing values plotted for various test areas with value labels at the end of the bars. Simple enough right? The wrinkle, they were percentage values so he needed to see '56%' not '56'!
Still, it should be simple enough but the percentage formatting has a requirement for your values to be in a decimal format i.e. 0.56 not 56.0. 56.0 gets formatted as 5600%. OK, so either pull the values out as decimals or use the div function to divide the values in the chart by 100 e.g.
<xsl:value-of select="myval div 100)" />
Now I can use the following the chart XML to format the percentages as I need them:
<Graph ... > ... <MarkerText visible="true"> <Y1ViewFormat> <ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" numberTypeUsed="true" leadingZeroUsed="true" decimalDigitUsed="true"/> </Y1ViewFormat> </MarkerText> ... </Graph>
That gets me the values shown the way I want but the auto axis formatting gets me from 0 >> 1.
I now need to go in and add the formatting for the axis too.
<Graph ...> ... <Y1Axis axisMinAutoScaled="false" axisMinValue="0.0" axisMaxAutoScaled="false" axisMaxValue="1.0" majorTickStepAutomatic="true"> <ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" scaleFactor="SCALEFACTOR_NONE" numberTypeUsed="true" leadingZeroUsed="true" decimalDigitUsed="true" scaleFactorUsed="true"/> </Y1Axis>
Now I have a chart that's showing the percentage values and formatting axis scale correctly for me too.
You can of course mess with the attributes above to get more decimal points on your labels, etc.
A freshly updated white paper on how to integrate BI Publisher 11g reports into an Oracle Forms 11g application is now available from the BI Publisher OTN page along with sample code and a video:
Integrating BI Publisher with Oracle Forms | Download Sample Code | Video
Thanks to Axel and Florin from PITSS and Juergen and Rainer from Oracle Germany
The new release of BI Publisher 18.104.22.168 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)
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)
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.
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
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)
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:
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.
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.
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.
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">|
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.