X

An Oracle blog about BI Publisher

Multi Sheet Excel Output

Im on a roll with posts. This blog can be rebuilt ...

I received a question today from Camilo in Colombia asking
how to achieve the following.

‘What are my options to deliver excel files with multiple sheets? I know we can split 1 report in multiple
sheets in with the BIP Advanced Options, but what if I want to have 1 report /
sheet? Where each report in each sheet has a independent data model ….’

Well, its not going to be easy if you have to have
completely separate data models for each sheet. That would require generating
multiple Excel outputs and then merging them, somehow.

However, if you can live with a single data model with
multiple data sets i.e. queries that connect to separate data sources.
Something like this:


Then we can help. Each query is returning its own data set
but they will all be presented together in a single data set that BIP can then
render. Our data structure in the XML output would be:

<DS>
<G1>
...
</G1>
<G2>
...
</G2>
<G3>
...
</G3>
</DS>

Three distinct data sets within the same data output.

To get each to sit on a separate sheet within the Excel
output is pretty simple. It depends on how much native Excel functionality you
want.

Using an RTF template you just create the layouts for each
data set on a page(s) separated by a page break (Ctrl-Enter.) At runtime, BIP
will place each output onto a separate sheet in the workbook. If you want to
name each sheet you can use the <?spreadsheet-sheet-name: xpath-expression?> command. More info here. That’s as
sophisticated as it gets with the RTF templates. No calcs, no formulas, etc.
Just put the output on a sheet, bam!

Using an Excel template you can get more sophisticated with
the layout.



This time thou, you create the layout for each data model on
separate sheets. In my example, sheet 1 holds the department data, sheet 2, the
employee data and so on. Some conditional formatting has snuck in there.

I have zipped up the sample files here.

FIN!

Join the discussion

Comments ( 4 )
  • Paramita Tuesday, October 21, 2014

    Hello

    We are facing some issues with time format in excel template.

    TO_CHAR("User_USR_RESP_LOGIN_O2C1_G3"."LAST_USED",'Mon DD, YYYY HH24:MI:SS')

    This creates a problem in BIP Data set as it takes everything after a colon as parameter.

    Can you please advise how this can be handled.

    Thanks


  • guest Saturday, January 24, 2015

    Hello Tim,

    I have an excel template with 4 sheets, where the first three sheets are the static sheets and I want to split sheet 4 dynamically into multiple sheets.

    I am using

    Xdo_sheet_name? <sheet name to be kept> <the sheet from where the data burst should start i.e sheet4>

    This is working fine when I am running offline using bi publisher add ins

    But it is not splitting the sheet when I am using it after registering the template into EBS. I am using 12.1.3 EBS version.

    Please help!!

    ekta


  • Yury Monday, February 16, 2015

    Hello. I try to convert excel to xsl sub template.

    But i have error

    org.xml.sax.SAXException: element Workbook is not supported yet.

    How to resolve this issue ?


  • guest Tuesday, September 1, 2015

    This works very well when you preview using Excel 2007 in BI Publisher 11.1.1.5 but when you preview using Excel 2000, it shows all the data in single sheet one after another


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.