Thursday Dec 13, 2012

All hail the Excel Queen

An excellent question this past week from dear ol Blighty; actually from Brian at Nextgen Clearing Ltd in the big smoke (London). Brian was developing an excel template and wanted to be able to reference the data fields multiple times inside the Excel template. Damn good question and I of course has some wacky solutions, from macros and cell referencing in Excel to pre-processing the data with an XSL stylesheet to copy the data multiple times so it could be referenced multiple times. All completely outlandish, enter our Queen of Excel, Shirley from the development team. Shirley is singlehandedly responsible for the Excel templates, I put her through six months of hell a few years back, with a host of Excel template requirements. She was more than up to the challenge and has developed some great features. One of those, is the ability to use the hidden XDO_METADATA sheet to map the data to custom named fields so they can be used multiple times in the template. So simple and very neat!

Excel template and regular Excel users will know that you can only use the naming function once ie the names have to be unique across the workbook so you can not reuse a cell/group name. To get around this you can just come up with as many cell names as you want and map them in the XDO_METADATA sheet to the data columns/fields in your XML data set:. For example:

XDO_GROUP_?G_D_DETAIL? <xsl:for-each-group select=".//G_D" group-by="./DEPTNO">

As you can see DEPTNO has been referenced twice and mapped to different named values in the left hand column. These values can then be used to name individual cells in the Excel template. You'll also notice a mix of Publisher <? ...?> and native XSL commands. So the world is your oyster on the mapping and the complexity you might need for calculations or string manipulation.

Shirley has kindly built out a sample Excel template, data and result here so you can see how it all hangs together. the XDO_METADATA sheet is hidden, just right click on the sheet names and use the Unhide command to show it.

Wednesday Dec 07, 2011

Excel 2007 Warnings!

As many of you have found out, everything is not as it seems with the Excel output that gets generated from an RTF template. It has non-Excel like limitations and the files are quite big. Those of you using Excel 2007 will have noticed another niggle. When you open an 'Excel' output you get a warning along the lines of:

The file you are trying to open, '*.XLS', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

This is down to BIP actually generating HTML and setting the mime type and extension to get Excel to open it. This is the cause of the limitations and the cause of the warnings. The limitations can be addressed by using an Excel template more on those in the user docs and elsewhere in this blog. Addressing the warning is going to require a modification to your users' registries.

Huge thanks to Abhishek Gupta for digging up a note from Oracle Support on it and thanks to whoever wrote the original. The note number is 1077728.1. I have uploaded Abhishek's snippet form the note here.

Update: Leslie has reminded me; for she is my Jimney Cricket to my Pinocchio, constantly prodding my conscious :0)
As an additional option in Excel 2007 is a new output type from RTF that generates .xlsx.
Excel 2007 is a new output type available for reports. When selected, BI Publisher generates the output in Excel XML format (.xlsx). If you have Excel 2007 or later installed, this option provides the best preservation of layout and formatting.

As noted in the comments, the Excel templates generate binary xls output not xlsx format and therefore have xls limitations in their abilities.

Friday Sep 02, 2011

Blog Post Bites Man!

Its funny how a post you wrote a few years ago can come back and bite you on the bum, rather hard. Im not even going to link to the post. Suffice to say that, it covered how you might write an XSL template to generate Excel output that was better and more robust and offered more features than the Excel generated by the RTF templates. Please, dont go and look for it, it will end in tears. In fact I ought to delete it except it still has some merit, if only in the fact that it covers a little XSL code that is still useful these days in Publisher.

But that post came and bit me, a customer found it and not knowing about the newer; more shiny and altogether more friendly Excel templates, gave it a whirl. They struggled to get it working and consequently logged an SR with support. Who found me and asked the obvious, I of course did not 'click' as to what context they were running the report in and came back with the glib reply that 'it works for me!' Not very helpful I know but I did follow up.

You see in the small print of that post is a caveat about being able to run and then view the output. The XSL created XML not an xls file so the user needed to download the file and force Excel to open it. Trying to view it from the report viewer interface resulted in misery for the user ... lesson for me, make my small print larger!

Once we had clarified what was actually needed, we turned the customer on to the all singing, all dancing lead on a Broadway show, Excel templates. Now I'll be the first to admit, we still do not have any formal doc out there on them, it's coming I promise.
Update: Leslie has has slapped me upside the head and reminded me that the 11g doc is available here. For you 10g'ers, they are documented in the new new features guide here.

In lieu of that, Robear (super support person) has put together a really great Note on building multisheet Excel templates. Packed full of info and more importantly examples - check it out on Oracle support site here.  Yes its on the flash site but it will come up eventually I promise, if not look for

Note: 1352000.1 : BI Publisher: How to create Multisheet in Single Excel file using Excel Template

If per chance, you are in Boulder, CO this Saturday (9/3) and want to see a 'not quite so fat as he used to be guy' swim (Im not bad), ride (I'm awesome) and run (I know I'll be crawling again) around Boulder Reservoir drop by. Not the most inviting image I know but we can have a laugh and joke about it afterwards and I promise to wear a shirt. We hit the water at 2pm! If you come, please bring, oxygen, beer and a spanking new Cervelo P4 .... I'm dribbling! I'll be the bald guy, trying to look slim in lycra shorts, looking even sillier in a swim cap. Hang on, that's what most of us look like ... just shout TIMMMMM!

Wednesday Feb 06, 2008

FSG Excel Templates Tomorrow

[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!


« March 2017