How to develop BI Publisher reports with mulitple spread sheets Excel Layout Outputs

       BI Publisher reports  Multiple spread sheet Excel Layout Outputs

Below are the steps to develop the Excel Layouts for BI Publisher report Excel outputs.

1. First open a new Excel sheet.

2. Design the Data XML with Groups and data elements as you create for any other BI Publisher outputs.

3. In the Excel sheet, Name the first spread sheet as 'Customers',the second spread sheet as 'Sites','Site Uses' and Contacts.

4. Navigate to Tool Bar of Excel sheet and click on the Menu Formulas -> Name Manager. Click on New and provide the Name field as 

"XDO_?ACCOUNT_NUMBER?.The name of the element in the data xml should be exactly same as the Name field you give.

In the example: The DATA XML element should be

<element name="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER" />

In Excel sheet field it should be "XDO_?ACCOUNT_NUMBER?

The "Refers to" field will point to the field in the excel sheet where you want the data to appear. You can manually change it to refer to any field in excel sheet.

Note: You should provide a dummy name in the field where you are expecting data to be populated .

5. Once you define all the elements in the group in Excel, now you should define which group all the elements defined in the excel belongs to by clicking Name Manager in tools menu -> New -> Name Field ='XDO_GROUP_?G_CUST_ACCT?' and the Refers to field should define the all the fields from first field to last field.

In the below screenshot it shows that all the elements defined from cell A18 to AM18 belongs to group G_CUST_ACCOUNT.

6. Similarly create another spread sheet called as "Sites", repeat steps 4 and 5 to define new site elements and group which belong to sites.  

Note: The group names and element names should always be same as data xml element names and group names.

7.Finally by default you should create one spreadhseet called as "XDO_METADATA"

7.Save the file as Microsoft Excel 97-2003 Worksheet (.xls)