Wednesday Oct 26, 2011

Tricky Grouping

A week off trying to catch up with all the stuff that builds up was not enough but it was pretty fruitful. You clear the decks the week before you leave, you even answer a few mails in the first few days of vacation, until your manager slaps your wrists. But there is always a pile of work waiting when you get back. It was not too bad this time; I at least deleted the crap mail during the week via my phone.

How the heck did we survive without smart phones? I took a trip to north Denver last week to pick up a part for our bear ravaged pop up camper. I think he wanted a cold beer from the fridge just wish he'd used the front door rather than slicing the camper open. That gaping hole is meant to be covered!
I guess I could have checked a map before we left but my phone got us through the mess of roads up there. An 80 mile round trip for a $10 louvered cover! Still, we found a great Indian place for lunch, delicious curry!

Today's tidbit (titbit for my British brethren) is a bit tough to describe. It comes from Charlotte in New Zealand. On the surface it looks simple and it is when you know how :0) But there's a wrinkle in to smooth out.
Here's the data:

  <LOCATION>1 The Street Anytown USA</LOCATION>
  <LOCATION>1 The Street Anytown USA</LOCATION>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  <LOCATION>11 The Street Anytown USA</LOCATION>
  <LOCATION>11 The Street Anytown USA</LOCATION>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  <LOCATION>21 The Street Anytown USA</LOCATION>
Heres the desired output:

Serialnumber Status Reason Date
HSS0000156 Planned Major Repair/Upgrade 15/JUN/11
HSS0000276 Planned Major Repair/Upgrade 18/MAY/11
HSS0001046 Planned Major Repair/Upgrade 21/JUN/11
HSS0001303 Planned Major Repair/Upgrade 19/MAY/11
HSS0001403 Planned Major Repair/Upgrade 14/JUN/11

Hok-Min, Zen Master of the RTF template, jumped on this for me and nailed it first time. On first look you think, ah this is just a simple re-group left, using some xpath to repeat the ATTRIBVALUE element twice in the table based on the ATTRIBDESC. Along the lines of:

<?ATTRIBVALUE[../ATTRIBDESC='Planned Major Repair/Upgrade']?> and <?ATTRIBVALUE[../ATTRIBDESC='Lettable Date']?>

But you oh so very easily end up with this:

Serialnumber Status Reason Date
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade
Planned Major Repair/Upgrade

and start scratching your head? With the and 11g template builders there is a nice Group Left feature that removes the need for nasty nested tables but for this slightly corner case, it drops you into a world of misery. You need to take a step back from what the wizard has given you.

Yes, you need to group by serial number but you do not need to loop over the remaining data (current-group().) Rather, you just need to drop into that current-group() and bring the values you need up to the level of the serial number group. So you just need to modify your XPATHs thus:

<?current-group()/ATTRIBVALUE[../ATTRIBDESC='Status Reason']?>

i.e. provide a complete path to the element you want within the serial number re-group level. Neat!

Template and sample data available here.

Sunday Oct 09, 2011

BIP and Mapviewer Mash Up III

This is the third installment of the BIP and Mapviewer Mashup, for the previous entries:

BIP and Mapviewer Mash Up I

BIP and Mapviewer Mash Up II

BIP and Mapviewer Mash Up III

Its been a hectic couple of weeks which has included all things mapviewer and integration. I have finally got my piece de resistance in mapping integration working but more on that next time. Its very cool in a geeky BIP, kinda way; my wife was completely fascinated when I told her all about it over dinner last night. Either that, or she has become very adept at nodding and saying 'that's nice honeycakes' at appropriate times. I hope the former but suspect the latter :0(

The next part of this mashup series, I said we would parameterize the map call. That's actually pretty easy to do, just a bit more effort with the parameters and the concat functions.

1. Set up your parameters in the data model. These do not have to be tied to the query. We support 'template ' only parameters. they just all have to be defined in the datamodel. Note the name you give to the parameter(s)

2. In your RTF template you need to declare 'interest' in the parameters. Its the same as the CURRENT_SERVER_URL format.


3. In the fields where you are building the encoded request string you can use the concat function to drop the parameter values into the string. In this case we are changing th map title with a parameter called 'title'

 <xsl:param xdofo:ctx="begin" name="pMapRq">concat

Its not tough but boy is it a bit of a nightmare to manage and keep track of everything in that encoded URL.

As I mentioned in my last post its a good idea to maintain the parts of the URL as un-encoded text in the template surrounded by an 'if:1=2' if statement to keep it hidden at runtime.

Next post, we get to a much more robust, easier to manage and as I mentioned cooler solution ... enter the mapping servlet. It acts as a map request broker between the BIP template and the mapviewer server. The RTF template is not full of encoded URLs but just a simple URL call to the servlet that will call the mapviewer server and stream the image back to BIP for rendering. I have built in a couple of tricks but more on that next time.

Thursday Sep 29, 2011

BIP and Mapviewer Mash Up II

Quite some time ago now I wrote the first of what I thought were going to be at least a couple of articles on getting BIP to render maps via Oracle Mapviewer. It was a real HelloWorld example with no 'World' just the 'Hello' bit.

I like to think it was like Kevin Costner's 'Waterworld', a nice idea but poorly executed and a flop at the blog box office but it would have been a perfect map for the movie. I have to admit, I think Dennis Hopper was awesome as the bad dude.

Well, I recently needed to show something more than an area of blue ocean, we needed some land. As you'll see if you go back to the original post, I have dabbled, I know how to render a map view in OBIEE but I knew I needed to lean on our resident map meister David to take this forward. David lives and breathes maps and probably knows the Oracle GIS solutions better than the folks that wrote them.

After a conversation, I was on the right track with the original post, mapviewer has an XML API that is accessible via a URL. Its just a case of building the URL and calling mapviewer and getting the result rendered by BIP. One thing that David noted was to not use the XML data that the template had access to. You can create your set of name/value pairs from the XML and pass it on to the URL. But imagine doing that for even a map showing data across all 50 states of the US or countries across the EU or APAC. It's going to get large very very quickly. The XML API does support passing a query to the mapviewer server for it to execute fetch and format the data into a map. Yep, we're breaking one of Publisher's cardinal rules, going back to the db for more data but the benefits far out weigh the costs. Armed with this information I got going. We have been working with a customer that needs this functionality (Hi Wilson :-) so I had a sample XML request to play with:

<?xml version="1.0" standalone="yes"?> <map_request  title="US Renal Disease Rates"  basemap="world_map"  datasource = "obiee_navteq_sample"  width="640"  height="480"  bgcolor="#a6cae0"  antialiase="false"  format="PNG_STREAM">  <center size="45">   <geoFeature>    <geometricProperty typeName="center">     <Point srsName="SDO:8307">     <coordinates>-96, 34</coordinates>     </Point>    </geometricProperty>   </geoFeature>  </center>  <legend bgstyle="fill:#ffffff;stroke:#ff0000" profile="MEDIUM" position="SOUTH_WEST">   <column>    <entry text="Number of Renal Disease Cases:" />    <entry style="V.POPULATION_COUNTY" tab="1" />   </column>  </legend>  <themes>   <theme name="theme1" min_scale="5.0E7" max_scale="0.0">   <jdbc_query     datasource="obiee_navteq_sample"    jdbc_srid="8307"     spatial_column="geometry"     render_style="OBIEE_NAVTEQ:V.POPULATION_COUNTY"> SELECT geometry,sqkm from obiee_state where iso_country_code='USA'</jdbc_query>   </theme>  </themes> </map_request> 

That's a hunk of XML to pass right, there is a lot going on in there. It basically sets the base map, size, center point, themes (or layers) to be added. The format is important, when you hop on over to the mapviewer doc you'll see that the format can take multiple values in our case we need 'XXX_STREAM' where XXX is the image format name e.g. PNG, JPG, etc. If you just request XXX then you get a URL string to the image on the server (that will come in handy in an upcoming post but not here) Finally the query that needs to be executed; this is the important bit for us; it needs to marry a map related column to some measure in this case 'sqkm'. Once the data set is returned, mapviewer can then 'map' the data. Our XML gets us this map to the left. Big prizes for the first one to spot whats wrong with the map? and why? Answers can be placed on the down tube of a shiny new 58cm Cervelo S5 and sent to the usual address.

For those of you that have gone back to part I of this post, you'll remember that we need to encode all of the XML before we can use it. Otherwise BIP gets all upset and reports a problem. When I started on this example I started to encode it all by hand, not a good plan and much shouting at the monitor ensued. Being lazee or smart, take your pick, I jumped on Google to look for some kind soul that had provided some web page that would do the encoding for me. Deepest thanks go out to the owner of you saved me from pulling out what hair I have left. For someone that has very little hair; I sure do talk about it on this blog a lot; maybe I need to talk to someone about that or get a toup?

Encoding nightmare solved but its going to be a big ugly piece of text to manage if I just encode the lot and assign it to a parameter. I decided to break it up a bit into sections and then use a concat function to bring it all back together.


The individual pieces make the string more manageable in terms of needing to make changes. I would recommend embedding the actual XML string into the template and noting which section belongs to which parameter and then surrounding it with an IF statement to hide it at runtime. The concat generated a big ol URL that I can test in a browser to ensure its going to return the map I want. You'll see in the template that you can test on the desktop too, as long as you have access to the mapviewer server.

Once you have the URL correct its just a case of dropping it into a form field:

<fo:external-graphic src="url({$mReq})"/>

At runtime the URL is resolved, called and the map returned to BIP for rendering. 

For those of you on 11g (I have tested this on BIP) you can download the complete report here. You just need to upload it and probably change the data connection on the data model. It relies on the obiee_navteq db user that is installed with the BIEE sample app.

For those of you on 10g, heres the template and some sample data to play with.

Next for this series, parameterizing the XML so that users can set various features at runtime. Imagine being able to ask for 'Renal disease cases by state' for one request and then 'Liver disease cases by country' for another using the same report/template.
Following that, after my somewhat 'hit it with a big hammer until it submits' (we're good at that in our house :) approach I have plans for something a bit more sophisticated.

Wednesday Aug 03, 2011

Crispy Charts!

It's been so long ... since I last post Kan has been stepping up and providing content. You may know I love weather and have a not so secret desire to see a tornado up close and personal. Since my last post I have come close, I got trapped in deepest darkest Mississippi and hot and sultry Montgomery, AL due to the most fantastic electrical storms. St Louis nearly kept me for an extra night but in spite of tornado sirens our plane snuck out just in time to avoid the worst.

I have also dropped quite a lot of poundage and last week took part in my first Triathlon, just a 'sprint' you understand. 1/2 mile swim, 11 mile ride and a 5K crawl, sorry I mean run. My dear wife joined me and we did not finish last! I even managed to come 8th in the bicycle ride!

To ease back into the blogging world, a bit of a corner case solution that could be applied to other issues and I wanted to get it down on 'paper' before I forget it. We have a customer that is having issues with the charting engine in 10g. As many of you know, you can customize the charts beyond what the dialog box in MSWord offers. Said customer was hitting an alignment issue in one of their customizations. When generating the chart to RTF all was well but with HTML and more particularly PDF the alignment was off. The first think to remember is that by default BIP will try and generate SVG outputs for the charts if it can. In the case of RTF it can not so you get an embedded image. In HTML and PDF, it can, hence the difference in alignment.

The difference in the chart quality is surprising:


The image format above and the svg below

I have had to shrink the images a little but you get the idea. Your svg charts are crisp and clear, even if you zoom in to them. The image formats, not so good.

We needed to log and enhancement/bug against the chart engine but was there a workaround? 

If you have ever used the export feature in the Template Builder to take a look at the XSL, you may have noticed a bunch of parameters the engine drops in. One of those controls the chart format, _XDOCHARTTYPE. The actual line is:

<xsl:param name="_XDOCHARTTYPE">image/svg+xml</xsl:param>

So we need a way to override this parameter for the HTML and PDF outputs. Well the HTML is easy, in the user preferences you can specify that you want chart images in your HTML output, simple. For PDF, you need to do a little more work.

Hopefully you know that you can have a set of config properties either at site level or at report level. There are a bunch you can change via the UI. We need to add one manually.

For Report Level only
1. Navigate to the report folder in the BIP catalog
2. Open the xdo.cfg file. If there is not one present, go back to the UI for the report and click the Config link, then just change any property to the opposite of the site level and Apply. This will create a cfg for you back in the report directory. You an change it back later. Now open it.
3. In the properties section add the following line

<property name="xslt._XDOCHARTTYPE">'image/png'</property>

4. Save the file and re-run the report to PDF output and you will get charts embedded as images rather than SVG and the alignment issue will be gone. There is a price to pay in clarity of the charts but its a work around that works.

If you want this for all reports you can put the same property into the 'master' cfg file in Admin/Configuration. Again, you may have to change a value to get the cfg file created.  

Definitely a corner case but the technique can be applied to any parameter settings in the generated XSLFO file. The xslt. prefix also allows you to set your own paraemter values in the template that might be needed ... I'll save that explantion for another time.

Wednesday May 11, 2011

Dynamic Report Titles

Couple of mails recently on being able to externalise the header and footer for a set of templates in 10g. 11g has the whole new style templates that include not only externalised headers and footers but also the look and feel for your templates.

In 10g, you can get the headers and footers with a little effort and I have written about them in the past. The recent mails had a small wrinkle in the requirement - they wanted dynamic report titles (or content) passed to the header template. So I could have a template looking like this:

Dynamic Header

With a place holder for the report title, this could be passed at runtime, either as a runtime report parameter or embedded inside the XML data for the report. The Report Name field holds a reference to the passed parameter <?$ReportName?>. The Template Header field contains a reference to the expected inbound report name as a parameter.

<?template:Header?> <?param:ReportName;string('')?>

The 'param' command declares the name of the parameter and sets a default value i.e. in this case an empty string.

We can two approaches to getting the dynamic value into the output.

XML Data Source

Firstly, we can pull a value from the report's XML data and pass it to the report header sub-template.

We have a standard Import Template command to bring in the sub template. The clear text in the header is a standard call-template command with the added with-param command to pass the report title. You need the @inlines if you want to pass a parameter value.

<?call@inlines:Header?><?with-param:ReportName;.//DEPARTMENT_NAME?> <?end call?>

In this case we are passing the department name from the XML data. I have added a bit more to this template by using the @section in the main grouping statement (group ROW by DEPARTMENT_NAME) This has the effect of re-setting the header each time the DEPARTMENT_NAME changes and thus the 'report title' changes to the new department name.

Report Parameter Source

This is a similar approach.

We just need to declare the report parameter in the main template. The 'Report Param' field contains the parameter declaration.

<?param@begin:val1;'"Employee Report"'?>

That's double quotes surrounding the inner string with single quotes.  The header then contains:

<?call@inlines:Header?><?with-param:ReportName;$val1?> <?end call?>

'with-param' now passes the val1 parameter value to the sub template as $val1. This time the report title will be 'Employee Report' in spite of the @section command i.e, its a fixed constant value that will show on all pages.

You can find the sample templates, data and outputs here.

Friday Sep 10, 2010

Anatomy of a Template Reprise

[Read More]

Friday Jul 09, 2010

Cant see the Woods for the Trees

[Read More]

Monday Apr 12, 2010

Collapsing Bookmarks

[Read More]

Thursday Apr 01, 2010

Dynamic Grouping and Columns

[Read More]

Tuesday Mar 02, 2010

Number-Upper-Lower Sorting

[Read More]

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 2015