rasti.hil@hilandco.com +41 79 367-9677

Wednesday, 2 June 2010

Oracle BI Publisher Enterprise and Multi-sheet Excel Output

1) Create new report in Oracle BI Publisher Enterprise
Home -> My Folders -> Click on "Create a new folder" link on left side -> add a name for folder (for example: first_sample) -> Click on the "Create" button -> click on new folder which was created by you
-> Click on "Create a new report" on left side -> add a name for report (for example: multisheet_excel) -> click on "Create" button
-> click on "Edit" link

-> click on "Data Model" -> New ->
Name: first_query
Type: Sql Query
Data Source: choose you data schema
SQL Query:

select EMP.JOB as JOB,
EMP.SAL as SAL,
EMP.HIREDATE as HIREDATE,
EMP.EMPNO as EMPNO,
EMP.ENAME as ENAME,
EMP.COMM as COMM,
EMP.MGR as MGR,
EMP.DEPTNO as DEPTNO
from ZSUZSI.EMP EMP

-> Click on "Save" on left side and top of page

-> Click on "View" on right side and top of page
-> Click on "View" button
If you see the XML data then you can start multi-sheet Excel template.
2) Create multi-sheet Excel template
->Start MS Excel -> create new excel file like the following pictures:



3) Convert Excel file to XSL file
-> Save As your excel file and change your file type to XML Spreadsheet type -> use the XLS Processor Engine for Oracle program for convert the xml file to xsl file
4)Upload excel template
In Oracle BI Publisher Enterprise edit your report ->Click on "Layout" -> Manage Template Files ->Upload Template: browse your xsl file and click on Upload button -> click on "New" on top of side bar (create a new layout)->
Name: first_layout
Template: your uploaded XSL file
Template Type: XSL Stylesheet(XML)
Output Format: All Formats

-> Click on "Save"
-> Click on "View" link on right side and top of page
-> select "first_layout" and "XML" for template and click on View button, after that click on Export button and save the XML file. -> change this file's extension (XML) to XLS and open it.

7 comments:

Илья Малышев said...
This comment has been removed by the author.
Ilya Malyshev said...

Hi Zsuzsi,

We have found a link to our product from your blog. We are interested in feedback about our product, so we offer you the cooperation - personal license (or discount on a corporate license, your choice) in exchange for a detailed review of our product.

If you are interested in this offer, please contact us (http://xlspe.com/contact.php).

Regards,
Ilya Malyshev
XLS PE product manager

Unknown said...

Hi Zsuzsi and Llya, I just convert the excel and I have this error:

Validation error: Incorrect close tag in element xsl:for-each

I follow the example of the point 2)Create multi-sheet Excel template

Maybe something is wormg. Please can you give me a hand.

Thank you very much
best regards
Luz

Zsuzsi Vadasz said...

Hi,
You should put this text the begin of the row: <?for-each:ROWSET/ROW?>
and the end of the row: <?end for-each?>
(See the picture about first sheet of excel file)

Unknown said...

Hi Zsuzsi, first thak you for answering.

I did that and I have the error that I told in before comment.

Validation error: Incorrect close tag in element xsl:for-each

what might be going on?

Thank you very much

Unknown said...

one more thing, the picture of the point 4 , where is it, i didn't find it, so i can´t Manage Template Files.

Where can I send you images???. I'm very interested for make a report with Multi-Sheet Excel Ouput. My email is luzfloresg@gmail.com if you want to answer me there

Thank you very much.

Илья Малышев said...

Just try to ask your questions on the official product forum http://xlspe.com/e107_plugins/forum/forum_viewforum.php?2