Oracle BI Publisher Blog

More charting goodness today with a cheesy title linking us to a cheesy song from big time brat packer, Dean Martin. I get shouted down by the rest of my family for belting out 'when the moon hits your eye ...' from the shower|kitchen|while driving|etc.

One of the gifts the boys got over the holidaze was 'Guitar Hero World Tour'. For the uninitiated, its a 'video game' whereby up to four players can play guitar, bass, drums or sing along to songs from nearly all genres. You are all in a band and have to play to audiences to earn money and to unlock more songs. Its all great fun, I get to strut my stuff in the basement while the boys simultaneously beat a poor drum kit to death and rip it up on a plastic guitar. Im pleased, for the most part, that they both play the 'real' instruments too, they just wont let me join their band!

The one drawback of the game, from the gamers perspective at least, is the lack of function to be able to load a song of choice and have its component parts i.e. drum, bass, etc, broken out for you. They have some funky, build your own tune thang but its not the same. My kids loved the idea initially, until they realized that they would need to break out the dickie bows and drum brushes while Dad crooned his way through Dean Martin's repertoire. I did try to cheer them up with the fact that I also loved The Smiths and they were more 'rock.' But then they have heard me singing that non-stop since a friend bought me a 'The Smiths' greatest hits CD for the holidays. According to one son, I'm apparently, an old fart but I still bashed out Dammit by Blink 182 last night with a 200 note streak on 'Hard', take that whippersnapper!

OK, back to charting ... got some data looking like this:

<TEST>
<LIST_G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>8</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>No. of Outstanding Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>1</BALANCE>
<IS_ORDER>1</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Loans Outstanding</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>7.1</BALANCE>
<IS_ORDER>2</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Undisbursed Effective Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>4.6</BALANCE>
<IS_ORDER>3</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>Afghanistan</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
<G_HEADER>
<COUNTRY_OF_EXPOSURE_RISK_DESC>China, People'S Republic Of</COUNTRY_OF_EXPOSURE_RISK_DESC>
<DECODE_HEADER_B_UNDISBURSED_EF>Total Loans</DECODE_HEADER_B_UNDISBURSED_EF>
<BALANCE>12.7</BALANCE>
<IS_ORDER>4</IS_ORDER>
</G_HEADER>
</LIST_G_HEADER>
<CS_TOTAL>10.8</CS_TOTAL>
</TEST>

Requirement is to have pie charts showing 'Undisbursed Effective Loans by Country' and 'No. of Outstanding Loans by Country'. That data is a little tricky with the loan type being stored in that horribly named, DECODE_HEADER_B_UNDISBURSED_EF element but we can do it.

Its back to our old friend XPATH for some help to 'filter' the records that we chart. If you remember, xpath is a way of limiting records using a 'where' clause in SQL speak. You can get very sophisticated but in this case is a simple clause ie filter records where DECODE_HEADER_B_UNDISBURSED_EF = 'Undisbursed Effective Loans' for one chart and DECODE_HEADER_B_UNDISBURSED_EF = 'No. of Outstanding Loans' for the other.

The chart dialog in the template builder can not currently help on adding the 'where' clause so you'll have to dig into the code but we can get you most of the way with the dialog.

Setup the chart like so:

Now we need to add the xpath expression, double click the image thats been embedded in the RTF to get the image properties dialog. Go to the Web tab to see the code. Now look for the three instances of './/G_HEADER' and add the following - [DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans'] so you have.

.//G_HEADER[DECODE_HEADER_B_UNDISBURSED_EF='No. of Outstanding Loans']

This will limit the records to those that are of the 'No. of Outstanding Loans' type. Now run the template to see the required chart. Repeat for the other loan type et voila! you will have the required charts. You can use XPATH expressions to help you build pretty much anything you want. Sample files here.

If you're in Colorado and want to catch me and der boyz rockin' out, stop by our basement, we're here all week!