Multi-Tab Excel Report Output in Oracle Applications

By MaheshnathBabu. G

Introduction

Oracle Reports, a sophisticated concept and high fidelity reporting tool from Oracle, which gives an instant access of business information to the user in the variety of formats (PDF, HTML, Spreadsheet, and XML etc.). It also enables user to send this information/report to multiple destinations (email, fax, printer, Oracle portal, FTP and Web browser etc.)

It is a very typical business scenario to generate excel output with the single tab in oracle reports using XML publisher. However, sometimes there are certain critical business requirements to generate a multi-tab excel report output in Oracle.

Tab 1: Employee Information

Tab 2: Department Information

To achieve this no need to go for XML publisher, which is the typical technical process. This could be achieved by writing a PL/SQL API and respective code within.

The sequential steps guides you to generate a multi tab excel output in Oracle reports

Step 1: Create the sample layout in all tabs as per requirement in Microsoft Excel.

Step 2: Save the Microsoft Excel file with XML Spreadsheet 2003 (*.xml) in local Machine with sample name.

Step 3: Create a package with a procedure in it as follows:

i) Create procedure for passing all the program parameters including errbuff and retcode.

ii) Create one cursor for each excel tab with respective SQL query in a procedure.

iii) Open the previously saved XML (Book1.xml) file in Editor and copy the complete code from it.

iv) Paste it in the package in fnd_output. Excel code should be in single quotes as shown below:

v) Go to the piece of code where sample data is printing in the code for Employee Information page.

vi) Delete the existing sample data and add a loop with the employee cursor data in fnd_output.

Note: Remaining code will be printed on another fnd_output statement.

vii) Go to the piece of code where sample data is printing in the code for Department Information page. Remove sample data and add looping through department cursor as follow steps for employee page.

Before

After

viii) End the API.

Step 3: Create an Executable in Oracle Applications as Executable type ‘PL/SQL Procedure’ and Executable Method as ‘Package.Procedure’.

Step 4: Create a Concurrent Program with xxdummy_exec Executable and Format type as ‘FO’, department number as parameter

Step 5: Attach the Concurrent request in required Request group

Step 6: Run the Concurrent Program in respective responsibility with Department number as Parameter.

Step 7: Open the output file in Excel.

Output as follows:

Tab 1: Employee Information

Tab 2: Department Information


About the Author

MaheshnathBabu. G is working as Technical Consultant in Trinamix Inc. He is specialized in Oracle applications.


For more Information Contact Us