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.

Wednesday Sep 18, 2013

BI Publisher Trial Edition News

Whoooo hoooo! Theres finally a new version of the BI Publisher Trial Edition available for download from OTN. 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!

Wednesday Aug 07, 2013

Siebel BIP Tuning

Another brain dump from John, the product manager for the Siebel-BIP integration.

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.

Siebel CRM BI Publisher Integration Performance[Article ID 1466709.1]

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

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

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!

Wednesday Jul 03, 2013

Siebel BIP Integration

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 ( [ID 1425724.1]

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.

Tuesday Jul 02, 2013

Working the Chart Percentages

Charting in BIP is such fun, well sometimes it is. Not so much today, at least not for Ron in San Diego. He needed a horizontal bar chart showing values plotted for various test areas with value labels at the end of the bars. Simple enough right? The wrinkle, they were percentage values so he needed to see '56%' not '56'!

Still, it should be simple enough but the percentage formatting has a requirement for your values to be in a decimal format i.e. 0.56 not 56.0. 56.0 gets formatted as 5600%. OK, so either pull the values out as decimals or use the div function to divide the values in the chart by 100 e.g.

<xsl:value-of select="myval div 100)" />

Now I can use the following the chart XML to format the percentages as I need them:


<Graph ... >
<MarkerText visible="true">
<ViewFormat numberType="NUMTYPE_PERCENT" decimalDigit="0" numberTypeUsed="true" 
        leadingZeroUsed="true" decimalDigitUsed="true"/>


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


Now I have a chart that's showing the percentage values and formatting axis scale correctly for me too.

You can of course mess with the attributes above to get more decimal points on your labels, etc.

Happy Charting!

Tuesday Jun 04, 2013

Integrating BI Publisher and Forms 11g via web services

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

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

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)


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