Oracle Business Intelligence

December 2, 2011

Creating Excel Templates

Filed under: BI Publisher — Aravind Darla @ 9:11 PM

An Excel template is a report layout that you design in Microsoft Excel for retrieving and formatting your reporting data in Excel.
Prerequisites
Following are prerequisites for designing Excel templates:
1.Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
2.To use some of the advanced features, the report designer will need knowledge of XSL and XSLT.
3.The report data model has been created.

Design the layout in Excel

First create a data model and export the xml to your local directory.
Let assume the xml structure like below

Open a new excel file and save it with (Excel 97-2003 .xls) extension.
• Create a new sheet in your Excel Workbook and name it “XDO_METADATA”.
• Create the header section by entering the following variable names in column A, one per row, starting with row 1:
Version
ARU-dbdrv
Extractor Version
Template Code
Template Type
Preprocess XSLT File
Last Modified Date
Last Modified By
• Skip a row and enter “Data Constraints:” in column A of row 10.
• In the header region, for the variable “Template Type” enter the value: TYPE_EXCEL_TEMPLATE

Create one more sheet and insert a table as like below and enter the NAME BOX entry as XDO_?MALE? for the male count in the table, here MALE represents the xml tag in our data model.

Applying a Defined Name to a Cell
1. Click the cell in the Excel worksheet.
2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: C6.
3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?MALE?
4. Press Enter.

Similarly do it for female as well by giving XDO_?FEMALE?
For the Total cell use the native excel function to sum up the above two values by giving the formula as =SUM(C6:C7)
For calculating percentages also we can use excel native functionality.
Note:
BI Publisher defined names
To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:
Data elements: XDO_?element_name?
where
XDO_ is the required prefix and
?element_name? is either:
the XML tag name from your data delimited by “?”
a unique name that you will use to map a derived value to the cell
For example: XDO_?MALE?
Data groups: XDO_GROUP_?group_name?
where
XDO_GROUP_ is the required prefix and
?group_name? is the XML tag name for the parent element in your XML data delimited by “?”.
a unique name that you will use to define a derived grouping logic
For example: XDO_GROUP_?DEMO?
Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

Creating Charts:
We can use the excel charts to embed graphs in the template, To do this just select the required cells which you want to show in chart and goto insert tab then click on any chart type ,here I am clicking PIE chart

Upload this template to BI Publisher and run the report then we will get the output like

Note:About the XDO_METADATA Sheet
Each Excel template requires a sheet within the template workbook called “XDO_METADATA”. Use this sheet to identify your template to BI Publisher as an Excel template. This sheet is also used to specify calculations and processing instructions to perform on fields or groups in the template. BI Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.
It is recommended that you hide the XDO_METADATA sheet before you upload your completed template to the BI Publisher catalog. This will prevent report consumers from seeing it in the final report output.

About these ads

5 Comments »

  1. Hi,
    can you please share your thoughts if in case instead of static values(male,female) , we have dynamic values like countries. The problem I’m facing with this is that I’m not able to define the range for chart as the number of countries could change.

    Comment by AJ — February 25, 2014 @ 7:46 AM | Reply

  2. Try to create chart by selecting columns instead of selecting rows because rows selection will become fixed number.If you select whole column then it will update chart as per available values in that column.

    Comment by Aravind Darla — February 25, 2014 @ 8:51 AM | Reply

  3. Hello just wanted to give you a quick heads up. The words in your article seem to be running off the screen in
    Safari. I’m not sure if this is a formatting issue or
    something to do with web browser compatibility but I thought I’d post to let you know.

    The style and design look great though! Hope you get the problem resolved soon.
    Cheers

    Comment by www.zakladpogrzebowybielany.pl — February 25, 2014 @ 10:26 AM | Reply

  4. indesign product catalogue

    Creating Excel Templates | Oracle Business Intelligence

    Trackback by indesign product catalogue — June 11, 2014 @ 7:33 AM | Reply

  5. Very shortly this web page will be famous amid all blog users, due to it’s
    nice content

    Comment by Christena — August 6, 2014 @ 4:34 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: