Oracle EBS: Linking Directly to SQL Server Reporting Services from Oracle e-Business Suite

This blog post covers (with a fairly simple example) a way of having a menu item in Oracle "punch out" to a SQL Server Reporting Services Report (or any other URL).

The reasoning behind doing this is that if you have a process driven by Oracle and need a report users don't want to then have to bring up Internet Explorer, browse to the Report website, and finally select their report. Using this process makes the join practically seemless to the end user.


This process requires three parts. The first is setting up and deploying a SQL Server 2008 report (I've included a small, and fairly pointless, example which just displays Invoices/Day from the AP_INVOICES_ALL table in e-Business Suite). Of course if you already have your own report just substitute your existing report for the example and proceed to the "Configuring e-Business Suite" stage.

The second stage is adding a simple HTML redirect script to the Apache webserver that comes with Oracle.

And the final step is to configure Oracle so that wen you click on a menu item it takes you to a SQL Server Reporting Services Report (SSRS).

Stage 1: Setting Up And Deploying a SQL Server 2008 Report
In order to speed things along I've created a report and a Shared Data Source (that you will need to re-point to your database). In order to use these files you need to open Visual Studio (I'm using 2008 but I don't see any reason why other versions wouldn't work), go to File > New > Project and select "Report Server Project".

At the right-hand side of the screen (in the treeview) you will see Shared Data Sources. Right-click this and select "Add > Existing Item" and then download the EBUSINESSSUITE.rds file (from Google) into your project directory and select it.

Double click the EBUSINESSUITE data source and change the connection string so that it points to your server. Click on "Credentials" and enter a username/password that has access to the AP_INVOICES_ALL table (the APPS account will, as will the AP account - if you are using something else then make sure that if you login using SQL * Plus and enter the SQL "select * from ap_invoices_all where rownum < 10" and make sure this works).

Next right-click the "Reports" node in the same tree view and select "Add > Existing Item" and then download the Invoice Totals By Day.rdl file (also from Google) into your project directory and select it.

Your Project should now look something like this;

Figure 1: Visual Studio 2008 Project Setup

Open the "Invoice Totals By Day" report and select "Preview". After a few seconds the report should display some data. You will get something back similar to;

Figure 2: Invoice Totals By Day (Sample Output)

Next you need to deploy the report to your SQL Server. Choose "Project > Properties" from the menu and enter the URL for your server. You can type this URL straight into a bowser if you want to check it's correct.

Next deploy your solution by choosing "Build > Deploy Solution".

Finally when you visit the SQL Server and go into your deployment folder you will see something like;

Figure 3: New Report Visible on SQL Server

Clicking on the report will show you the same result as you saw in Figure 2.

Deploying a SQL Server 2008 Example report is now complete.

Stage 2: Adding a Simple HTML Re-direct Script to Oracle
By far the most complicated part of this process is actually finding the correct directory into which to deploy the script. I'm not a UNIX person, I do my work in Windows and Oracle. I have a nice friendly UI and when that doesn't work I have the familiarity of PL/SQL ... Under UNIX I'm afraid I get a little lost.

The directory you're looking for contains lots of files. I created a simple text file called "test.txt" which contained the word "test" and then went to the URL;

http://XXXX:9999/OA_HTML/test.txt (XXX is the name of your server, and 9999 is the port number)

And confirmed that the word "test" appeared in my web browser.

I then created a small text file called verysimpleredirect.html containing this code (via Google Drive);

As you can see I took the "gup" function from from another website (I think I did a simple Google search for "javascript redirect" and picked out one of the top entries). To test the file is in position correct visit the URL;

http://XXXX:9999/OA_HTML/verysimpleredirect.html?redirect=www.google.co.uk (XXX is the name of your server, and 9999 is the port number)

You should end up at Google.

Once you have copied this file to the server (and tested it) this stage is complete.

Stage 3: Configuring Oracle to Punch-Out to the Report
Log into Oracle and select the "Application Developer" responsibility.
Scroll down the list and under "Application" select "Function" (everything will slow down while Java starts up and the screens load). Eventually the "Form Functions" dialog will appear;

Figure 4: Oracle "Form Functions" Dialog

Despite the name this dialog can be used for any kind of function - like the web page links we need to create.

Complete the form as follows;

Tab	Field	Value
Description	Function	SSRS_INVTOTBYDAY
	User Function Name	SSRS: Invoice Totals By Day Report
	Description	SSRS: Invoice Totals By Day Report
Properties	Function	SSRS_INVTOTBYDAY (Default)
	Type	SSWA jsp function
	Maintenance Mode Support	None (Default)
	Context Dependence	Responsibility (Default)
Form	Function	SSRS_INVTOTBYDAY (Default)
	Form	Blank (Default)
	Application	Blank (Default)
	Parameters	Blank (Default)
Web HTML	Function	SSRS_INVTOTBYDAY (Default)
	HTML Call	**
	MDS Reference Path	Blank (Default)
Web Host	Function	SSRS_INVTOTBYDAY (Default)
	Host Name	Blank (Default)
	Agent Name	Blank (Default)
	Icon	Blank (Default)
	Secured	Blank (Default)
	Encrypt Parameters	Blank (Default)
Region	Function	SSRS_INVTOTBYDAY (Default)
	Object	Blank (Default)
	Region Application	Blank (Default)
	Region Code	Blank (Default)

**- In this field you need to enter the URL of your report. This will look something like;
 verysimpleredirect.html?redirect=https://XXX/Reports/Pages/Report.aspx?ItemPath=%2fOracleToSSRSExample%2fInvoice+Totals+By+Day
 (where XXX is the name of your SQL Server)
I have listed all the fields for completeness but for the huge bulk you can just accept the default.

Now this is where things become a little fiddlie. For whatever reason the Type > SSWA jsp function doesn't appear to have saved correctly into the database (if anyone knows why, or what I'm doing wrong!) please let me know via the comments bit below!

Anyway running the following SQL will "fix" the problem;

update applsys.fnd_form_functions t
   set type = 'JSP'
 where function_name in ('SSRS_INVTOTBYDAY')

For some reason the type stored in the table is "UNKNOWN" . Until you have specified the type correctly you'll not be able to see your function anywhere.

Now switch responsibility to "System Administrator".

Under "Application" select "Menu" and the "Menus" form will be displayed. For the purposes of this example we'll be adding the report as a menu option to the "Application Developer" user (simply because this is likely to interfear less with other users using the system).

Figure 5: Oracle Menus Dialog

Using "Find" select the main menu for the Application Developer Responsibility (it's called "Navigator Menu - Application Developer GUI" - just search for %App%Dev% and you get a small enough list to pick it from).

For the purposes of this new menu I'll just add new item at the bottom;

The values are;

Key	Value
Seq	9
Prompt	Reports
Submenu	(blank)
Function	SSRS: Invoice Totals By Day Report
Description	Invoice Totals By Day Report
Grant	(Checked)

You should end up with something like this;

Figure 6: Completed Menus Dialog (for Application Developer GUI)

Save the changes, a job will be submitted to rebuild your menus. While that's happening you need to Clear the Cache on the Application Tiers (so that your change will be picked up).

Now if you switch back to the "Application Developer" responsibility you will see a new option on the menu called "Reports";

Figure 7: New "Reports" Item for "Application Developer" Responsibility

Clicking on "Reports" will bring up your default browser and point it at the Reporting Services Report;

Figure 8: Clicking on an Oracle Menu Items Triggers a SSRS Report