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:

xdoxslt:minimum
xdoxslt:maximum

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

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

Happy Charting!


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
« July 2013 »
SunMonTueWedThuFriSat
 
1
4
5
6
7
8
9
10
11
12
13
14
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
   
       
Today