Friday Feb 28, 2014

Waterfall Charts

Great question came through the ether from Holger on waterfall charts last night.

"I know that Answers supports waterfall charts and BI Publisher does not.
Do you have a different solution approach for waterfall charts with BI Publisher (perhaps stacked bars with white areas)?
Maybe you have already implemented something similar in the past and you can send me an example."

I didnt have one to hand, but I do now. Little known fact, the Publisher chart engine is based on the Oracle Reports chart engine. Therefore, this document came straight to mind. Its awesome for chart tips and tricks. Will you have to get your hands dirty in the chart code? Yep. Will you get the chart you want with a little effort? Yep. Now, I know, I know, in this day and age, you should get waterfalls with no effort but then you'd be bored right?

First things first, for the uninitiated, what is a waterfall chart? From some kind person at Wikipedia, "The waterfall chart is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns. The columns are color-coded for distinguishing between positive and negative values."

We'll get back to that last sentence later, for now lets get the basic chart working.

Checking out the Oracle Report charting doc, search for 'floating' their term for 'waterfall' and it will get you to the section on building a 'floating column chart' or in more modern parlance, a waterfall chart. If you have already got your feet wet in the dark arts world of Publisher chart XML, get on with it and get your waterfall working.

If not, read on.

When I first starting looking at this chart, I decided to ignore the 'negative values' in the definition above. Being a glass half full kind of guy I dont see negatives right :)

Without them its a pretty simple job of rendering a stacked bar chart with 4 series for the colors. One for the starting value, one for the ending value, one for the diffs (steps) and one for the base values. The base values color could be set to white but that obscures any tick lines in the chart. Better to use the transparency option from the Oracle Reports doc.

<Series id="0" borderTransparent="true" transparent="true"/> 

Pretty simple, even the data structure is reasonably easy to get working. But, the negative values was nagging at me and Holger, who I pointed at the Oracle Reports doc had come back and could not get negative values to show correctly. So I took another look. What a pain in the butt!

In the chart above (thats my first BIP waterfall maybe the first ever BIP waterfall.) I have lime green, start and finish bars; red for negative and green for positive values. Look a little closer at the hidden bar values where we transition from red to green, ah man, royal pain in the butt! Not because of anything tough in the chart definition, thats pretty straightforward. I just need the following columns START, BASE, DOWN, UP and FINISH. 

START 200
BASE 0
UP 0
DOWN 0
FINISH 0
START 0
BASE 180
UP 0
DOWN 20
FINISH 0
START 0
BASE 150
UP 0
DOWN 30
FINISH 0

 Bar 1 - Start Value
 Bar 2 - PROD1
 Bar 3 - PROD2

and so on. The start, up, down and finish values are reasonably easy to get. The real trick is calculating that hidden BASE value correctly for that transition from -ve >> + ve and vice versa. Hitting Google, I found the key to that calculation in a great page on building a waterfall chart in Excel from the folks at Contextures.  Excel is great at referencing previous cell values to create complex calculations and I guess I could have fudged this article and used an Excel sheet as my data source. I could even have used an Excel template against my database table to create the data for the chart and fed the resulting Excel output back into the report as the data source for the chart. But, I digress, that would be tres cool thou, gotta look at that.
On that page is the formula to get the hidden base bar values and I adapted that into some sql to get the same result.

Lets assume I have the following data in a table:

PRODUCT_NAME SALES
PROD1 -20
PROD2 -30
PROD3 50
PROD4 60

The sales values are versus the same period last year i.e. a delta value.  I have a starting value of 200 total sales, lets assume this is pulled from another table.
I have spent the majority of my time on generating the data, the actual chart definition is pretty straight forward. Getting that BASE value has been most tricksy!

I need to generate the following for each column:

PRODUCT_NAME

STRT

BASE_VAL

DOWN

UP

END_TOTAL

START
200
0
0
0
0
PROD1
0
180
20
0
0
PROD2
0
150 30 0
0
PROD3
0 150 0 50 0
PROD4
0 200
0 60 0
END
0 0 0 0 260

Ignoring the START and END values for a second. Here's the query for the PRODx columns:

 SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
      OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)

The inner query is breaking the UP and DOWN values into their own columns based on the SALES value. The LAG function is the cool bit to fetch the UP value in the previous row. That column is the key to getting the BASE values correctly.

The outer query just has a calculation for the BASE_VAL.

200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME))

The SUM..OVER allows me to iterate over the rows to get the calculation I need ie starting value (200) + the running sum of LAG_UP - DOWN. Remember the LAG_UP value is fetching the value from the previous row.
Is there a neater way to do this? Im most sure there is, I could probably eliminate the inner query with a little effort but for the purposes of this post, its quite handy to be able to break things down.

For the start and end values I used more queries and then just UNIONed the three together. Once note on that union; the sorting. For the chart to work, I need START, PRODx, FINISH, in that order. The easiest way to get that was to add a SORT_KEY value to each query and then sort by it. So my total query for the chart was:

SELECT 1 SORT_KEY
, 'START' PRODUCT_NAME
, 200 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, 0 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
UNION
SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) 
      OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
       OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)
UNION
SELECT 3 SORT_KEY 
, 'END' PRODUCT_NAME
, 0 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, SUM(SALES) + 200 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
GROUP BY 1,2,3,4,6
ORDER BY 1 

A lot of effort for a dinky chart but now its done once, doing it again will be easier. Of course no one will want just a single chart in their report, there will be other data, tables, charts, etc. I think if I was doing this in anger I would just break out this query as a separate item in the data model ie a query just for the chart. It will make life much simpler.
Another option that I considered was to build a sub template in XSL to generate the XML tree to support the chart and assign that to a variable. Im sure it can be done with a little effort, I'll save it for another time.

On the last leg, we have the data; now to build the chart. This is actually the easy bit. Sadly I have found an issue in the online template builder that precludes using the chart builder in those templates. However, RTF templates to the rescue!

Insert a chart and in the dialog set up the data like this (click the image to see it full scale.)

Its just a vertical stacked bar with the BASE_VAL color set to white.You can still see the 'hidden' bars and they are over writing the tick lines but if you are happy with it, leave it as is. You can double click the chart and the dialog box can read it no problem. If however, you want those 'hidden' bars truly hidden then click on the Advanced tab of the chart dialog and replace:

<Series id="1" color="#FFFFFF" />

with

<Series id="1" borderTransparent="true" transparent="true" />

and the bars will become completely transparent. You can do the #D and gradient thang if you want and play with colors and themes. You'll then be done with your waterfall masterpiece!

Alot of work? Not really, more than out of the box for sure but hopefully, I have given you enough to decipher the data needs and how to do it at least with an Oracle db. If you need all my files, including table definition, sample XML, BIP DM, Report and templates, you can get them here.

Monday Feb 24, 2014

Wildcard Filtering continued

I wrote up a method for using wildcard filtering in your layouts a while back here. I spotted a followup question on that blog post last week and thought I would try and address it using another wildcard method. 

I want to use the bi publisher to look for several conditions using a wild card. For example if I was sql it would look like this:

if name in ('%Wst','%Grt')

How can I utilize bi publisher to look for the same conditions.

This, in XPATH speak is an OR condition and we can treat it as such. In the last article I used the 'starts-with' function, its a little limiting, there is a better one, 'contains'. This is a much more powerful function that allows you to look for any string within another string. Its case insensitive so you do not need to do upper or lowering of the string you are searching to get the desired results.
Here it is in action:

For the clerks filter I use :

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk')];./JOB_TITLE?>

and to find all clerks and managers, I use:

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk') or contains(JOB_TITLE,'Manager')];./JOB_TITLE?>

Note that Im using re-grouping here, you can use the same XPATH with a regular for-each. Also note the lower case 'or' in the second expression. You can also use an 'and' too.

This works in 10 and 11g flavors of BIP. Sample files available here.

Monday Feb 10, 2014

Alternate Tray Printing

Since we introduced support for check printing PCL escape sequences in 11.1.1.7 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 Feb 03, 2014

Memory Guard

Happy New ... err .. Chinese Year! Yeah, its been a while, its also been danged busy and we're only in February, just! A question came up on one of our internal mailing lists concerning out of memory errors. Pieter, support guru extraordinaire jumped on it with reference to a support note covering the relatively new 'BI Publisher Memory Guard'. Sounds grand eh?

As many a BIP user knows, at BIP's heart lives an XSLT engine. XSLT engines are notoriously memory hungry. Oracle's wee beastie has come a long way in terms of taming its appetite for bits and bytes since we started using it. BIP allows you to take advantage of this 'scalable mode.' Its a check box on the data model which essentially says 'XSLT engine, stop stuffing your face with memory doughnuts and get on with the salad and chicken train for this job' i.e. it gets a limited memory stack within which to work and makes use of disk, if needed, think Windows' 'virtual memory'.

Now that switch is all well and good, for a known big report that you would typically mark as 'schedule only.' You do not want users sitting in front of their screen waiting for a 10,000 page document to appear, right? How about those reports that are borderline 'big' or you have a potentially big report but expect users to filter the heck out of it and they choose not to? It would be nice to be able to set some limits on reports in case a user kicks off a monster donut binge session. Enter 'BI Publisher Memory Guard'!

It essentially lets you set those limits on memory and report size so that users can not run a report that brings the server to its knees. More information on the support web site, search for 'BI Publisher Memory Guard a New Feature to Prevent out-of-memory Errors (Doc ID 1599935.1)' or you can get Leslie's white paper covering the same here.

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.

http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index.html

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

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 (8.1.1.7+) [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.

Friday May 10, 2013

Building on Subject Areas

The new release of BI Publisher 11.1.1.7 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)



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 :)

Tuesday Sep 18, 2012

Ubiquitous BIP

The last number I heard from Mike and the PM team was that BIP is now embedded in more than 40 oracle products. That's a lot of products to keep track of and to help out with new releases, etc.
Its interesting to see how internal Oracle product groups have integrated BIP into their products. Just as you might integrate BIP they have had to make a choice about how to integrate.

1. Library level - BIP is a pure java app and at the bottom of the architecture are a group of java libraries that expose APIs that you can use. they fall into three main areas, data extraction, template processing and formatting and delivery. There are post processing capabilities but those APIs are embedded withing the template processing libraries.

Taking this integration route you are going to need to manage templates, data extraction and processing. You'll have your own UI to allow users to control all of this for themselves. Ultimate control but some effort to build and maintain. I have been trawling some of the products during a coffee break. I found a great post on the reporting capabilities provided by BIP in the records management product within WebCenter Content 11g. This integration falls into the first category, content manager looks after the report artifacts itself and provides you the UI to manage and run the reports.

2. Web Service level - further up in the stack is the web service layer. This is sitting on the BI Publisher server as a set of services, runReport and scheduleReport are the main protagonists. However, you can also manage the reports and users (locally managed) on the server and the catalog itself via the services layer.

Taking this route, you still need to provide the user interface to choose reports and run them but the creation and management of the reports is all handled by the Publisher server. I have worked with a few customer on this approach. The web services provide the ability to retrieve a list of reports the user can access; then the parameters and LOVs for the selected report and finally a service to submit the report on the server.

3. Embedded BIP server UI- the final level is not so well supported yet. You can currently embed a report and its various levels of surrounding  'chrome' inside another html based application using a URL. Check the docs here. The look and feel can be customized but again, not easy, nor documented. I have messed with running the server pages inside an IFRAME, not bad, but not great.

Taking this path should present the least amount of effort on your part to get BIP integrated but there are a few gotchas you need to get around.

So a reasonable amount of choices with varying amounts of effort involved. There is another option coming soon for all you ADF developers out there, the ability to drop a BIP report into your application pages. But that's for another post.

About

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!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today