Oracle BI Publisher Blog

This posts assumes that you've looked at the new flex templates and have worked through the tutorial in the New Features Guide.  If you built the report in this tutorial, you may have noticed that your results were interactive.  You can click on a column heading to sort the rows by a column.  Or, you can drag and drop a column to a new location.  You may have also noticed that the data grid and the chart are not synchronized.  Meaning that if you sort the data grid, the data in the chart does not also sort.  Synchronizing them is pretty easy though.

1. Turn your XML into an XMLListCollection.  BIP sends data to the flex template as an XML Object.  Unfortunately, Flex doesn't have the best XML support.  If you need to sort, filter or refresh data, it is much easier to use an Array or a Collection.  The good news:  It's very easy to convert the XML into an XMLListCollection and the XMLListCollection is much easier to work with.  First, import the proper classes into your project by adding this line:
   
   import mx.collections.XMLListCollection;
   
   Tip: just type XMLListColl and type "CTRL - Space" and Flex Builder will complete the word and add the appropriate import statement.
   
   Second, create the XMLListCollection as a Bindable variable:
   
   [Bindable]
   public var filteredXML:XMLListCollection = new XMLListCollection(dataXML.ROW);
   
   Notice the the new variable called filteredXML is constructed from the dataXML variable that we used in the tutorial.
   
   
2. Modify your data grid and chart so that their data providers are the new variable called filteredXML:
   
   <mx:DataGrid x="10" y="160" width="476" height="152" dataProvider="{filteredXML}">
   
   <mx:ColumnChart x="10" y="10" id="columnchart1" width="476" height="142" dataProvider="{filteredXML}">
   
   
3. Create an init() function.  Our end goal here is to refresh the data behind the data grid and the chart.  The refresh method of a collection must be called from inside an init() function:
   
   public function init():void{
           filteredXML.refresh();
   }
   

If you worked through the last post on Flex Templates, you may have noticed that sorting doesn't work properly on numeric data.  This is the classic "sort a number as a number" rather than "sort a number as a string" problem.  This is not surprising if you think about how the data is getting to the flex template.  BIP issues a query for the data, converts the data extracted to XML and hands that off to the flex template.   In the process, the fact that the numbers in your data are actually a numeric data type gets lost.  Actually, this problem is nothing new to BIP developers: whenever you add a sort in a Word Template, you have to state whether the data being sorted is a String/Date or Number.  The same sort of thing needs to happen in your Flex template.  You need to tell flex that your numbers are really numbers and need to be sorted that way.  Unfortunately, it's not as simple as a radio button like it is in the Word Template.  In this post, I'll walk you through setting up the proper sorting.  I'll build off the template that was in my previous post.

To get a column in a data grid to sort properly, you have to add a sortCompareFunction to the definition of the column.  Here's an example:

<mx:DataGridColumn headerText="Monthly Salary" dataField="SALARY" sortCompareFunction="sortSalary"/>

So what is this function called sortSalary?  You have to write that.  The sortCompareFunction must have the following signature (takes in two objects and returns an integer):

sortSalary(obj1:Object, obj2:Object):int

The objects in this case are your rows of data.  Here's an example sortCompareFunction:

private function sortSalary(obj1:Object, obj2:Object):int {
            var value1:Number = (obj1.SALARY == "" || obj1.SALARY == null) ? null : new Number(obj1.SALARY);
            var value2:Number = (obj2.SALARY == "" || obj2.SALARY == null) ? null : new Number(obj2.SALARY);

            if (value1 < value2) {
                return -1;
            } else if (value1 > value2) {
                return 1;
            } else {
                return 0;
            }
        }

It's fairly straight forward.  Cast the column as a number and compare the two values.  The annoying part of this is that the function is specific to the column that you are sorting.  That means that you will have to create a sortCompareFunction for each column that you need to sort by.  Adobe actually recommends that you create a sortCompareFunction for every column where you do any formatting (topic for another post).  You'll notice in the source code below that I create two sortCompareFunctions: one for monthly salary and one for annual salary.  Here's the source code.  Here's the finished BIP report.  Thanks to Adobe Flex 2 - tips and tricks and my brother-in-law (you can just image how much fun the family conversations are) for helping me figure this out.

I have had a few enquiries recently asking why there is a dependency on MSOffice to create templates for publisher - actually there is no dependency. Yes, we have invested in Office by producing a plugin to MSWord to help you build templates, but there is nothing to stop you using an alternative editor to build templates. That said we have not certified on the alternatives but some early testing has shown some promise, there are some issues but we have pinned some of these down to the RTF version that we certify on not being supported in the alternative application.

The biggest difference is that the alternatives either do not have formfields or they have their own versions of them that are not compatible with MSWord's versions. Open Office is a prime example - they do have formfields but they do not have a 'help' tab in which to embed the code for the template thus you end up needing to write the command directly into the document. This leads to a very busy template but it will work. 

Of course, there are alternatives,

• PDF templates - these lack the conditional logic available in RTF templates but are great for static documents.
  
• Flex templates - a new and exciting template type - not great for paper but for building interactive reports its great.
  
• XSL-FO templates - under the publisher covers lies an XSLFO engine that conforms to the W3C standard.

There is another alternative on the horizon - the Online Template builder - not a greatly imaginative name at the moment but this is going to be an all singing, all dancing DHTML/JScript application that allows you to build templates. It's not yet production and its not quite to the level of the RTF template sophistication but the gap is closing fast. Its a drag and drop environment with Office 07-like toolbars - very friendly and we hope pretty intuitive - currently going through user testing as I write.  I think Im allowed to at least provide a couple of screen shots:

Based in the browser, first thing you'll get are some default layouts to work with.

Now, you can create objects - charts, tables, sub totals, etc - with the drag of a mouse. Notcie the ribbon tool bars and the property palettes for minute control.

You're gonna have to wait a little while longer for this ... it'll be worth it I promise.

Apologies to regular readers may be wondering where I have been. I took a week off after OAUG to sit on a San Diego beach to get toasted to a crisp and to avoid any sharks. We were on that same beach the day before it happened! Needless to say my wife was constantly telling me to watch the kids in the surf, checking for any big gray fins in the water and if seen to run or swim like hell!

While I was away, Noelle has been working on some Flex template goodies. We got a requirement a while back to produce a report similar to this:

Its nothing to 'write home about' standard table stuff but the interesting feature is the 'percentage bar' embedded in the table. Two issues to solve here:

1. Building the 'bar' component - its not an out of the box feature.
2. Getting the bar to render inside the table

Noelle, being very cunning, solved both!

Its not exactly the same look and feel but we were after the functionality - the look can be tweaked quite easily.

Tackling the 'bar' component first, Noelle first needed to build the 'percentage bar'. To do this, Noelle used an ActionScript class to create and render the bar. It basically takes a label object and extends it to render the text value and a rectangle shape, calculates the percentage fill to be used based on the data.

Here's the script

package {

// Need these packages for rendering and drawing

import flash.display.*;
import flash.geom.*;
import flash.text.TextField;
import mx.controls.Label;

// We are going to extend the label object

public class PercentageBar extends Label {
 override protected function updateDisplayList(unscaledWidth:Number, unscaledHeight:Number):void

 {
  super.updateDisplayList(unscaledWidth, unscaledHeight);

  // Create rectangle shape and width

  var rndedRect:Shape = new Shape();
  var rectWidth:Number=100;
  var rectHeight:Number=15;
  // Fill for the rectangle is derived from value in the main.mxml ie the parent application

  var rectFill:Number=this.parentApplication.percentCalculated;

  //Set the fill type, colors, alphas and ratios

  var fillType:String = GradientType.LINEAR;
  var colors:Array = [0xFF0000,0xFFFFFF];
  var alphas:Array = [1, 1];
  var ratios:Array = [0, 255];

  // matrix - not required but allows you to specify the fill
  var matr:Matrix = new Matrix();
  matr.createGradientBox(rectFill, rectHeight, 0, 0, 0);
  var spreadMethod:String = SpreadMethod.PAD;
  //Start rendering the rectangle
  rndedRect.graphics.beginGradientFill(fillType,colors,alphas,ratios,matr,spreadMethod);
  rndedRect.graphics.lineStyle(1,0x000000,1,false);
  rndedRect.graphics.drawRoundRect(2, 0, rectWidth, rectHeight, 10, 20);
  rndedRect.graphics.endFill();
  
  // Add the rectangle to the label object
  this.addChild(rndedRect);

  //Create the label for the rectangle
  // Value drawn from the percentCalculated value in the main.mxml

  var label:TextField = new TextField;
  label.width = 26;
  label.text = (this.parentApplication.percentCalculated+ "%");
  label.x = 105;
  label.y = 0;
  //Add the text field to the label
  this.addChild(label);
    }
  }
}

Those familiar with java should not be too scared of the code - if you spend some time with it its not that tough to understand. Its code yes! but it adds a huge layer of flexibility to the flex reports.

Thats the 'bar' component, so how to bring it into the main table layout. Thats pretty easy too ...

Remember that the bar component needed the 'percentCalculated' value from the parent application ie the main flex template? Well, we need a function to calculate that:

[Bindable] public var percentCalculated:Number;

// determine the percent of the target number - round before passing back
private function calcPercent(row:Object, column:DataGridColumn ):void
{
  var a:Number;
  var t:Number;
  a = row.actual;
  t = row.target;
  percentCalculated = Math.round((a/t) * 100);
}

Notice we need to declare the 'percentCalculated' variable as public and bindable so the action script class can access it. Its a simple calculation to get the percentage based on the rendered table data.

To get the 'bar' to render we just need to set the last column to use its own renderer ie the PercentageBar AS class that was created.

<mx:DataGrid id="myDatagrid" width="500" height="300" dataProvider="{dataXML.product}" editable="false" enabled="false">
 <mx:columns>
  <mx:Array>
   <mx:DataGridColumn dataField="name" headerText="Product Category"/>
   <mx:DataGridColumn dataField="actual" headerText="Units Sold Actual" id="actual"/>
   <mx:DataGridColumn dataField="target" headerText="Units Sold Planned" id="target"/>
   <mx:DataGridColumn itemRenderer="PercentageBar" width="135" headerText="% of Target" labelFunction="calcPercent"/>
  </mx:Array>
 </mx:columns>
</mx:DataGrid>

You can get the complete source to the flex project here. Thanks again to Noelle for the research and code.

So, a little effort but hopefully you get an idea of how you can take existing Flex objects and extend them to create your own visualizations of the data. You can now tell your users that the 'world is their oyster' when it comes to reporting ... or may be not!   

More mails this week, its funny how you get a batch of requests for something all at the same time. This past week the proverbial hot potato has been Tree Maps and how to make em?

For the uninitiated, a tree map is a 2 dimensional graphical visualization of your data e.g. school size + absenteeism, maybe your debtors and the size of their debt and their delinquency or your organizations current project load, their relative size and how far behind or ahead they are compared to plan. For a large amount of data, say, listing on the stock market its very hard to compared whos up and whos down just by looking at tabular data. You can color code the data for sure but if you have masses of rows its tough to compare. A tree map gives you a easy visualization of common entries and they are color coded to make life even easier.

In the graphic above we can see schools in a school district, the size of the of the rectangle is affected by the school student population ie a bigger one for a larger school. The color of the rectangle is governed by the difference from an attendance target the school has for a given day.

Now you know what they are, so how do you build em?

I first looked at RTF templates, they can do so much and they can partially help but only for single dimension data. We can conditionally color a table cell based on some data but resizing that cell gets very complicated very quickly - I ended up in hard core XSL too quickly. I did find a brave soul out on the intertubes that had built several XSL style sheets to get the tree map - IMHO its just too expensive a development. If you only need a sing dimension then RTF templates are quick and easy.

You can get the template and data here.

Much more useful and altogether more cool from an output format standpoint is the good ol Flex template. I have not looked at Flex for a while, digging back into it I was amazed at the amount of open source development going on around building re-usable flex components. Don't want to build it yourself, go google for it. The Google Code site has masses of components and examples, just search for it. That's just what I did and came up with Josh's tree map component page. Its covered by an Open Source license from MIT so both kudos and thanks to Josh for it.

Seeing the MIT license got me digging through links to see what else might be out there from the MIT brainiacs. All you could ever want to know about tree maps.
Then I found this site, the MIT Simile Widgets - some awesome stuff going on in there, not Flex necessarily but some very rich interactive visualizations.

Getting back to Tree Maps and Flex - using the component in a project is easy enough, getting Publisher to generate data that will work with the component is a whole other kettle of fish ... I'll save that for tomorrow ...

Following on from yesterday's intro to tree maps we now get into the nitty gritty of the Flex component and BIP.
The flex component is not that nitty really, its very easy to use, the main entry for it :

dataProvider="{dataXML.ROW.SCHOOLS.SCHOOL}" labelField="@NAME" weightField="@TOTAL_STUDENTS" x="0" y="0"
colorFunction="{itemToColor}" dataTipFunction="{itemToToolTip}"/>

is pretty simple. It needs to know your data source (dataProvider.) The labelField, what text to put in the rectangle, the weightField, this governs how big the rectangle is. The colorFunction - how to colour the rectangles and finally the dataTipFunction, this is a popup when folks hover over a rectangle to show them info about the underlying data.

The component download comes with a bunch of samples, I took the stock market example and bent it to my will, cos Im strong like that!

Both the color functions and dataTipFunctions are provided, they are easy enough to understand and can be modified easily. josh provides some good getting started doc.
All sounds very easy and the component is, its the data it needs or more importantly the data structure it needs thats a bit more tricky.

Those of you that know and use Publisher (standalone/BIEE) will know that if you give Publisher a piece of SQL it will return you XML. Its flat XML, we call it 'ROWSET/ROW' - the treemap component can not use it - it actually can, but you get some groovy results. The sample data Josh works with is simple enough:

<node label="Mail" data="100">
 <node label="Inbox" data="70"/>
 <node label="Personal Folder" data="10">
  <node label="Business" data="2"/>
  <node label="Demo" data="3"/>
  <node label="Saved Mail" data="5" />
 </node>
<node label="Sent" data="15"/>
<node label="Trash" data="5"/>
</node> 

But you'll notice that there are elements and attributes, you will also remember that Publisher does not generate attributes on its own. It appears the component needs a hierarchy with attribute values.
We can get Publisher to generate the attributes but we need some help, I had to use SQL XML. I have written about this in the past, its very powerful if a little fiddly to get the XMLELEMENT and XMLATTRIBUTES commands in the right spot. I managed to get a structure that worked with the tree map component with the following SQL XML.

select
 XMLELEMENT("SCHOOL",
  XMLATTRIBUTEs( e.SCHOOL_NAME as NAME,
   sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) as "ABSENT",
   count(e.stu_id) as "TOTAL_STUDENTS",
   round((sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "PCT_ABS",
   round(0.14 - (sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "DIFF_FROM_TARGET"
    )
    ).getClobVal() as SCHOOLS
from ENROLLMENT e,
SCHOOL_TYPE t
where e.sch_cd = t.sch_cd
and t.type = 'H'
and e.calendar_dt = to_date('10/24/2007','MM/DD/YYYY')
group by e.SCHOOL_NAME

Lots of heavy lifting going on in there but it basically generates:

<ROWSET>
<ROW>
<SCHOOLS>
<SCHOOL NAME="Dauphin High School" ABSENT="223" TOTAL_STUDENTS="1925" PCT_ABS=".116" DIFF_FROM_TARGET=".024">
</SCHOOLS>
</ROW>
. . .

We can not get rid of the ROWSET/ROW elements but we can live with that. All we need to do is specify the path to the SCHOOL level i.e.

dataProvider="{dataXML.ROW.SCHOOLS.SCHOOL}"

I have yet to investigate if we can use a data template to avoid the extraneous elements. The component will allow you to have multiple levels of data just like a tree so you can build outputs like this.

You can get the complete Flex project here and the Publisher report here.