Creating Excel Template for Oracle Applications

                                       Hello everyone this is my first post on my own blog(very excited), might not be the best one but it might help our technical friends to get a hang of using excel template for oracle reports. I am not an  expert in oracle whereas I would say that I just started working for apps but its pretty descent and kept it very simple as well.I am assuming that most of them know how to create a word template and register in application so I will just jump on to showing you a step by step process of how to create a template for oracle apps using microsoft excel.

But before we start why do we even want to create an excel template???
 
Here are some few pointers...
•  When we insert too many columns in word(RTF)  it just becomes very difficult to handle all of them  which takes a lot of efforts and we ultimately end up screwing up the report.With excel it becomes very easy to manage all the columns
•   Data can be distributed in different tabs within the same file which again makes it simple for calculations and data browsing.
•   We can put conditional formatting (we can do in report builder as well) but with excel its just a piece of cake.
•   There are few more advantages that are listed in this http://docs.oracle.com/cd/E21764_01/bi.1111/e13881/T527073T571887.htm but frankly I never understood the true meaning of it ;p
 
Enough of monkey business and lets get on to the main event

Template Creation in excel is a three step process
1.     Load the XML file in excel using BI publisher. 
2.     Insert the desired fields.
3.     Group them together… Ta..da and we are done.

Step 1 . Once you have successfully compiled ur query in report builder, Go to File>Generate to File

  











Click on the XML button

And Save it in a desired folder.

Go to Start>Oracle BI Publisher Desktop> Samples

Now click on Blank Excel Template. (Never tried using a normal excel file). This file will be provided by default in Oracle BI Publisher folder.

The file will contain a tab which says XDO_METADATA. In this tab you can state the below mentioned fileds. They are optional. And whenever you insert fields in this file the cells below the data constraints gets filled. These are basically Metadata information of all the blocks and groups that you include within this file.
 

So to explain this senario I have created a table named employee which contains name, department,profile and salary information. I am going to split this information  in two tabs one which will contain personal information and other with official information.

So lets begin  by creating two tabs and renaming them as “Employee general Information” and another as “Salary” 

Step 2. Load the saved XML file by clicking on “Sample XML “

Fill the blocks by column header.I have just created three fields as shown below.

Step 3. Now click on “Fields” a popup window will appear with all the data group that you have created in oracle Builder. Select the desired field and click on insert.You can notice a small window which says XDO_?XDOFIELD1?

This will  get created automatically the moment u insert any fields in excel. 

Likewise do for the remaiming fields.

 You can notice that the XDO_METADATA tab will get contain the same entries.

Now rename the fileds so as to map the fields from xml to excel. Rename even the group or the excel file will be generated without any output.

To rename click on Formulas Tab and click on Name Manager

Lets Start Renaming them.
Click on the field and then the Edit button.Make sure that you don’t change the “XDO_?” at the begning of every field.Just rename it as it is in the XML file so that the mapping can be done properly.

Step 4. Now select al the fields and click on “Repeating Group”
This is done so that the group gets created and the data will be displayed in tabular format otherwise all the data will get populated within a single cell.

Two groups will get created one with the name same as in the xml file and another with the prefix “LIST_" 

So that’s it we are done with our first Tab of showing the general information.

Now let’s do the same process for the salary information. Its becoming monotonous but the basic idea is to show you how data can be distributed across different tabs.


Step 1. Create column header for the table.

Step 2. Insert fields from the “Fields” window.

Step 3. Rename the fields same as in XML File.

Step 4. Select all the dields inserted (Not the header).

 

And assign a group.

Make sure that u hide the XDO_METADATA tab so that when the actual report is run users won’t have to read that annoying unwanted data.

So we are finally done with the creation of template. You can click on the preview button to check how the file will be generated during the actual report submission.

First Tab

And Second Tab

So this is it. wasn't that difficult after all ..kept it very simple but have added more screen shots so that everyone follows it clearly.If anyone finds any issue that i may have mentioned in my blog please do let me know through your comments.

Will Try to post more in coming days