The simplest way of turning SQL queries to Excel reports in Oracle EBS

This article is obsolete. For the new and free version of Excel Out check here New Excel Out

Over the years of working with Oracle EBS I was highly frustrated with its sluggish reporting. Oracle is a great transactional system but it is often underutilized when it comes to aiding decision making. Throughout most of my career if one needed a report, Oracle Report Builder was required. This tool provides good flexibility but is extremely cumbersome to work with, and most of the time needed to write a report is used for servicing the need of the Oracle Report Builder rather than focusing on the data itself. Anybody who encountered the ‘rep-1212 object is not fully enclosed by its enclosing object’ error can fully appreciate that. To create a report was always a big project that usually took several days of development and then the output was provided in a text format which was not easy to read or manipulate.

With XML publisher things become a little easier at least in the way of the output. One could finally create a report that provides a nice graphical format or that opens directly in Excel. Unfortunately the XML still has to be generated by Oracle Reports or some other tool, which usually requires creating and placing the RDF report file on the server, developing a template in RTF with a special MS add-on, then uploading it trough the front end and associating it with a report. Lots of servicing the needs of development tools and way too complex for me, and very annoying if I don't have the UNIX access. When the sales manager asks me for a report that shows sales figures broken down by sales territories and time periods, I need to give him that report in 2 hours rather than tell him that:

- it is a week long development project

- we can only submit a budget for its approval next week

- if it gets approved in the next 2 weeks we can start development and testing

- after a month or so he can have his already outdated report and he can ask for a change request to add some additional columns he didn't think about in the first place ....

If you are on a hosted platform it may be significantly more difficult than that.

How can a company be successful if they can't instantly react to the changing business environment?

Simply, I needed an easy and quick way to create required reports in Excel, right out of Oracle. There are tools out there that enable that, such as Discoverer or OBIEE, but then they require a few months long development project to put them into place and create underlying data objects, servicing them when they are installed, and most difficult of all – they require user adoption. I have seen companies, where despite of having those tools, users moved away from them towards the solution I describe below because it was so much more convenient. They don’t want to learn another application separate from EBS where they have to to go to each time they want their report. Ideally, it should be just another concurrent request that provides nicely formatted Excel report. And not a CSV file.

I looked for a tool like that for several years and found none, so decided to create one myself.

It is called Excel-Out, takes SQL query as an IN parameter to provide a nicely formatted output that opens directly in Excel. Creating a new report is as simple as copying the concurrent request and pasting a new query into it.

Here is a demonstration of my tool hosted on the more4apps web site

http://www.more4apps.com/Demonstrations/Video+Demonstrations/Excel-Out.html

Click on the below icon when viewing the demo, to go to full screen format. It is much easier to watch it that way.

The tool is really only a concurrent request based on a PL/SQL package and you should be able to install it in 5 minutes. The package parses the query and converts it into native Excel format, so all the report outputs open directly in Excel.

Now, new report development and deployment can take only as much time as it takes to develop the query, and other than having TOAD or other SQL tool, no development environment is required. No user learning curve either.

To install it you need to run 2 scripts.

The first one is different depending on whether you are on 11i or R12. It can be found here:

http://www.more4apps.com/installation-scripts/

Put all the installation files (all that end with sql, plbs and plbb) into the bin directory of your oracle home and then run the R12_install.sql (or install.sql for the 11i or earlier versions) script from your SQL client.

The second script is not release specific and it can be downloaded here:

http://www.more4apps.com/downloads?ProdID=5273

To get to the download page you need to register, which is to satisfy my curiosity on who is downloading it. Every time you register I get an e-mail. This is also the only way for me to email you back.

Like with the first installation script, put all the installation files into your bin directory of your Oracle home and run the eo_install.sql

This script will install the concurrent request Excel-Out.

You can find the concurrent request by going to

System Administrator -> Concurrent -> Program -> Define

Now you can add it to the System Administrator Reports request group and it is ready to run as a report.

When the report completes click on the Output and it will open directly in Excel

Now, depending on your operating system and the version of browser and Excel, the output window may not open at all, due to your security settings.

To fix this follow the 3 steps below:

1. Turn off all the pop-up blockers or hold CTRL key when opening the output to bypass them. If you are not sure on how to do that, check the online user manual.

2. Add the application URL to your trusted sites. Check the user manual for details.

3. Go to My Computer -> Folder options

Go go File Types tab, select file type XLS and click on Advanced

Different options are checked there depending on your Excel version.

Uncheck all checkboxes. This will cause Excel to open in a separate window,rather than embedded in the browser.

If you are on Excel 2007 you may get an annoying pop-up message that your file format does not match the extension. This message has no significance other than being annoying but if you want to suppress it check metalink notes 780817.1 and 759410.1 or the online user manual.

Now your report should be running smoothly. Keep the Excel-Out as your template but copy it and update the query sitting in parameter 991 to get a brand new report. When you copy this report into a new one do not change the application. It needs to stay as Application Object Library, even if you assign this report to request groups in different modules.

Watch the 5 minutes demonstration video or check the screenshots in the user manual on how to create query parameters, especially the section on dates. When creating dates as parameters you need to format them with fnd_date.date_to_cannonical. When you output dates they will work in your default format, but for user convenience it is better to format them with with to_char(date,'dd-mon-yyyy') or any other format the users are used to. Check out the dates in the seeded Excel-Out report.

Don't hesitate to ask me a question if you get stuck at any point.

Most of the issues are due to an invalid query. Check the concurrent request log, it displays your query after all the parameters were substituted for actual values Run it directly from SQL to see if it works.

This is a 45 days trial version. After that time it will still work but limit the output to 70 rows.