Tutorial 30: Integrating Oracle Reports into Formspider using htmlRenderer component – 2

Since version 1.0, Formspider has a servlet that makes it easier for developers to render reports produced by Oracle Reports. This is tutorial shows how to use this new feature.

1- Creating Reports using Oracle Reports Designer

If you already created the report file in “Tutorial 17: Integrating Oracle Reports into Formspider using htmlRenderer component“, please skip to step 2.
As the first step, create a report that shows data from Human Resources (HR) schema tables. Open Reports Designer and create a new report using Report Wizard. Select “Create both Web and Paper Layout” as layout type;


Select “Create both Web and Paper Layout” option as layout type

Then, select “Group Above” as report style;

Select “Group Above” as report style

This report queries “Employees” and “Departments” tables using the “dep_id” parameter and shows employees working in a particular department. Type the following query as the report’s data source definition:

select e.employee_id
      ,e.first_name
      ,e.last_name
      ,e.phone_number
      ,e.salary
      ,d.department_name
      ,m.first_name as manager_name
      ,m.last_name as manager_surname
  from employees e, departments d, employees m
 where e.department_id = d.department_id
   and d.manager_id = m.employee_id
   and d.department_id = :dep_id

Datasource definition query

Select department_name field as a group field;


Group field selection

And remaining fields as display fields;


Display field selection

Use the “Wine” template in this report;


“Wine” template selected

Save the report with “employeesOfDepartments” as file name and Reports Binary(rdf) as file type.

You may also download this report file from the following link:

employeesOfDepartments.rdf

Finally, create a “reports” folder under “C:\” and place the report file under it. Also create a “temp” folder under the newly created “C:\reports\” folder. You may change the paths of these folders by editing the web.xml file in your Tomcat installation.

2- Setting Connection Parameters

Formspider has a built-in Oracle Reports servlet. You just need to set connection parameters and path of the Oracle Reports files. Open the web.xml file in “\webapps\formspider\WEB-INF” folder in your Tomcat installation and edit the following connection parameters.

<context-param>
       <param-name>reportCommand</param-name>
       <param-value>rwrun.exe</param-value>
</context-param>
<context-param>
       <param-name>fileDirectory</param-name>
       <param-value>c:\reports\</param-value>
</context-param>
<context-param>
       <param-name>tempDirectory</param-name>
       <param-value>c:\reports\temp\</param-value>
</context-param>
<context-param>
	<param-name>databaseTNSName</param-name>
	<param-value>YOUR_TNSNAME</param-value>
</context-param>
<context-param>
	<param-name>databaseUsername</param-name>
	<param-value>YOUR_USERNAME</param-value>
</context-param>
<context-param>
	<param-name>databasePassword</param-name>
	<param-value>YOUR_PASSWORD</param-value>
</context-param>

The “reportCommand” parameter is path of the “rwrun.exe” file. The rwrun command runs a report using the Reports Server. You may start the Reports Server as a stand alone server with rwserver server=server_name command from the Command Line. The “fileDirectory” is the directory where the Oracle Reports files reside. “tempDirectory” is the place that Formspider uses to create PDF files. The “databaseTNSName”, “databaseUsername”, and “databasePassword” parameters are database connection parameters.

Once the correct settings are made, you must see the “Welcome.. Please select a report and press run.” message, when you go to the “[ApplicationServerURL]/formspider/oraclereportsservlet” webpage.

3- Creating a Formspider application that displays reports in htmlRenderer component

This sections explains creating a simple Formspider application that shows reports inside an htmlRenderer component.

Open Formspider IDE and create an application named “OracleReports_ReportsServer”. Set “HR” as datasource schema name.

3a- Creating Datasources

Create a new datasource definion based on a table;

To achieve this, expand the “Datasource Definitions” accordion Panel, select the “Datasource Definitions” root node and click the “+” button from the toolbar of the accordion panel. The “New Datasource Definition” dialog shows up.

Select “Table” from the “Based On” section and “Departments” table from the combobox. This datasource definition is used as the datasource of a combobox so that users can select the department of the employees to be shown in “employeesOfDepartments” report.

3b- Creating GUI of the application

Expand the “Containers” accordion Panel, expand “Windows” node and then “Mainframe” node from the tree, double-click “mainFrame” node and edit the XML as the following;

<mainframe title="Oracle Reports Demo" width="1280" height="800" maximizeOnStartUp="Y">
 <borderLayout>
    <cell docking="Center">
     <include panelName="mainPanel"/>
    </cell>
 </borderLayout>
</mainframe>

Note that maximizeOnStartUp attribute is set as “Y” and the title as “Oracle Reports Demo”.

Select the “Panels” node and click the “+” button from the toolbar of the accordion panel to create a new panel. Enter “menuPanel” as panel name and click “OK”. Edit panel XML so that it looks like this:

<panel>
  <xyLayout>
    <cell x="5" y="5" width="80">
      <textLabel label="Department:" text-align="Right"/>
    </cell>
    <cell x="100" y="5" width="150">
      <comboBox name="dep" displayColumn="DEPARTMENT_NAME" valueColumn="DEPARTMENT_ID" listDataSource="DEPARTMENTS1"/>
    </cell>
    <cell x="260" y="5" width="120">
      <button name="report" label="Run Report"/>
    </cell>
  </xyLayout>
</panel>

Next, open and edit the XML of the “mainPanel”:

<panel>
 <borderLayout>
    <cell docking="North" height="30">
     <include panelName="menuPanel"/>
    </cell>
    <cell docking="Center">
     <htmlRenderer name="renderer"/>
    </cell>
 </borderLayout>
</panel>

Add the “menuPanel” to the north of this panel with a height of “30”. The Reports are displayed inside the htmlRenderer named “renderer” and cover most of the screen.

3c- Creating the Database Procedures

At this step, create a package named “ORACLEREPORTS_REPORTSSERVER”:

Package Spec:

CREATE OR REPLACE
PACKAGE ORACLEREPORTS_REPORTSSERVER is
 procedure showReport;
end;

Package Body:

create or replace
PACKAGE BODY ORACLEREPORTS_REPORTSSERVER is
--------------------------------------------------------------------------------
procedure showReport is
 -- report url
 v_reportUrl_tx              varchar(32767);
begin
 v_reportUrl_tx := '[ApplicationServerURL]/formspider/oraclereportsservlet?';

 v_reportUrl_tx := v_reportUrl_tx || 'rep_name=employeesOfDepartments.rdf';

 v_reportUrl_tx := v_reportUrl_tx || '&application_id=' || api_application.getid;

 -- get dep_id parameter value from "Department" combobox'
 v_reportUrl_tx := v_reportUrl_tx || '&dep_id=' || api_component.getvaluenr('menuPanel.dep');

 -- show report
 api_component.seturl('mainPanel.renderer', v_reportUrl_tx);

end;
--------------------------------------------------------------------------------
end;

Note that “application_id ” is a mandatory parameter for Formspider’s Oracle Reports servlet.

The “showReport” procedure builds the report URL according to the selected parameters and modifies the URL of the htmlRenderer component.
You may need to update v_reportUrl_tx variable in the package with appropriate values for your evironment.

3d- Creating Actions and attaching to the Components

Create an action named “showReport” pointing to the procedure ORACLEREPORTS_REPORTSSERVER.showReport

Open the XML of the “menuPanel” and add a buttonPress event to the button named “report”:

<button name="report" label="Run Report">
 <events>
    <buttonPress action="showReport"/>
 </events>
</button>

Running reports

Run the application and see the report output showing in the application after you select a department.

Oracle Reports successfully integrated into Formspider Application

  • Io Mitu

    Running a report may take some time. How could we set the cursor to a “wait shape” and reverse this change when report is presented to the user?