Tutorial 17: Integrating Oracle Reports into Formspider using htmlRenderer component (Revised 04/09/2012)

This is tutorial shows how to use the htmlRenderer component to display Oracle Reports.

1- Creating Reports using Oracle Reports Designer

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 with the given “dep_id” parameter as “department_id” of the employees in a 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 inside it. You may change the path of this folder by editing the web.xml file in your Tomcat installation.

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

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

Open Formspider IDE and create an application named “OracleReports”. And set “HR” as datasource schema name.

2a- Creating Datasources

Create a new datasource definion based on a query;

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.

Enter “OUTPUTFORMAT” as datasource definition name and select “Query” radio button from “Based On” section. Next, select “Query” node from the tree and type the following query;

select 'pdf' as outputFormat_cd, 'PDF' as outputFormatName_tx from dual
union all
select 'htmlcss' as outputFormat_cd, 'HTMLCSS' as outputFormatName_tx from dual
union all
select 'html' as outputFormat_cd, 'HTML' as outputFormatName_tx from dual

A datasource based on this query will be used by a comboBox to select the output format of the report to be shown. Notice that “PDF”, “HTMLCSS” and “HTML” options will be listed in the comboBox.

Next, create a new datasource definition again; this time select “Table” from “Based On” section and “Departments” table from the combobox below. This will be used as a datasource of a combobox so that users can select the department of the employees to be shown in “employeesOfDepartments” report.

2b- 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 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>

Notice that we set maximizeOnStartUp attribute as “Y” and title as “Oracle Reports Demo”.

Select the “Panels” node and click the “+” button from the toolbar of the accordion panel for creating a new panel. Enter “menuPanel” as panel name and click “OK”. Edit panel XML as follow:

<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="100">
      <textLabel label="Output Format:" text-align="Right" />
    </cell>
    <cell x="370" y="5" width="80">
      <comboBox value="pdf" name="outputformat" displayColumn="OUTPUTFORMATNAME_TX" valueColumn="OUTPUTFORMAT_CD" listDataSource="OUTPUTFORMAT1"/>
    </cell>
    <cell x="500" y="5" width="120">
      <button name="report" label="Run Report"/>
    </cell>
  </xyLayout>
</panel>

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

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

Here, we added the “menuPanel” to the north of this panel with a height of “30”. Reports will be shown inside the htmlRenderer named “renderer” and it will cover most of the screen.

2c- Creating the Database Procedures

In this step, create a package named “ORACLEREPORTS” as follows:

Package Spec:

CREATE OR REPLACE
PACKAGE ORACLEREPORTS is
  procedure showReport;
end;

Package Body:

CREATE OR REPLACE
PACKAGE BODY ORACLEREPORTS is

type t_reportParameters is record(keyword_tx   varchar2(4000)
                                 ,value_tx varchar2(4000));

type tt_reportParameters is table of t_reportParameters index by binary_integer;
--------------------------------------------------------------------------------
procedure addParameter(in_parameterList_tt in out nocopy tt_reportParameters
                      ,in_keyWord_tx       varchar2
                      ,in_value_tx         varchar2) is
begin
  in_parameterList_tt(in_parameterList_tt.count + 1).keyword_tx := in_keyWord_tx;
  in_parameterList_tt(in_parameterList_tt.count).value_tx := in_value_tx;
end;
--------------------------------------------------------------------------------
procedure deleteParameter(in_parameterList_tt in out nocopy tt_reportParameters
                         ,in_keyWord_tx       varchar2) is

  v_parameterDeleted_bn boolean := false;
  i                     number;
begin
    i := in_parameterList_tt.first;
    loop
      if upper(in_parameterList_tt(i).keyword_tx) = upper(in_keyWord_tx) then
        in_parameterList_tt.delete(i);
        v_parameterDeleted_bn := true;
      end if;
      exit when v_parameterDeleted_bn or i = in_parameterList_tt.last;
       i := in_parameterList_tt.next(i);
    end loop;
end;
--------------------------------------------------------------------------------
function buildReportUrl(in_domainName_tx            varchar2
                       ,in_portNumber_tx            varchar2            -- OC4J Forms/Reports Instance port number
                       ,in_reportName_tx            varchar2            -- report file path
                       ,in_reportServerName_tx      varchar2 := null    -- report server name
                       ,in_destinationType_cd       varchar2			-- cache, file, localfile, mail, printer... Use "cache" value to display reports
                       ,in_destinationFormat_cd     varchar2            -- report output file format(PDF, HTMLCSS, HTML, XML...)
                       ,in_destinationName_tx       varchar2 := null    -- report output destination path
                       ,in_reportDBUserName_tx      varchar2 := null    -- report db schema name
                       ,in_reportDBUserPassword_tx  varchar2 := null    -- report db schema password
                       ,in_reportDBName_tx          varchar2 := null    -- report db name
                       ,in_otherReportParameters_tt tt_reportParameters -- other report parameters
                       ) return varchar2 is

  v_reportUrl_tx varchar(32767);
  i              number;
begin

  if upper(in_destinationType_cd) = 'CACHE' then

    v_reportUrl_tx := 'http://' || in_domainName_tx || ':' || in_portNumber_tx || '/reports/rwservlet?report=' || in_reportName_tx;

    -- report server parameter
    if in_reportServerName_tx is not null then
      v_reportUrl_tx := v_reportUrl_tx || '&server=' || in_reportServerName_tx;
    end if;

    -- destype parameter
    if in_destinationType_cd is not null then
      v_reportUrl_tx := v_reportUrl_tx || '&destype=' || in_destinationType_cd;
    end if;

    -- desformat parameter
    if in_destinationFormat_cd is not null then
      v_reportUrl_tx := v_reportUrl_tx || '&desformat=' || in_destinationFormat_cd;
    end if;

    -- userid parameter
    if in_reportDBUserName_tx is not null
      and in_reportDBUserPassword_tx is not null
        and in_reportDBName_tx is not null then
      v_reportUrl_tx := v_reportUrl_tx || '&userid=' || in_reportDBUserName_tx || '/' || in_reportDBUserPassword_tx || '@' || in_reportDBName_tx;
    end if;

    -- add other parameters
    i := in_otherReportParameters_tt.first;
    loop
      v_reportUrl_tx := v_reportUrl_tx || '&' || in_otherReportParameters_tt(i).keyword_tx || '=' || in_otherReportParameters_tt(i).value_tx;
      exit when i = in_otherReportParameters_tt.last;
       i := in_otherReportParameters_tt.next(i);
    end loop;
  end if;

  return v_reportUrl_tx;

end;
--------------------------------------------------------------------------------
procedure showReport is
  -- report url
  v_reportUrl_tx              varchar(32767);

  -- report parameters
  v_domainName_tx             varchar2(255) := 'formspideronline.com';	-- domain name
  v_portNumber_tx             varchar2(255) := '8891';					-- OC4J Forms/Reports Instance port number
  v_reportName_tx             varchar2(4000);							-- report name
  v_reportServerName_tx       varchar2(255) := 'reportserver';			-- report server name
  v_destinationFormat_cd      varchar2(255);							-- report output file format(PDF, HTMLCSS, HTML, XML...)
  v_reportDBUserName_tx       varchar2(255) := 'HR';					-- report db schema name
  v_reportDBuserPassword_tx   varchar2(255) := 'HR';					-- report db schema password
  v_reportDBName_tx           varchar2(255) := 'formspider';			-- report db name
  v_reportParameters_tt       tt_reportParameters;						-- other report parameters (reg_id, dep_id...)
begin

  v_destinationFormat_cd := api_component.getvaluetx('menuPanel.outputformat');

  v_reportName_tx := 'C:\reports\employeesOfDepartments.rdf';
  -- get dep_id parameter value from "Department" combobox'
  addParameter(v_reportParameters_tt
              ,'dep_id'
              ,api_component.getvaluenr('menuPanel.dep'));

  -- build report url
  v_reportUrl_tx := buildReportUrl(in_domainName_tx            => v_domainName_tx
                                  ,in_portNumber_tx            => v_portNumber_tx
                                  ,in_reportName_tx            => v_reportName_tx
                                  ,in_reportServerName_tx      => v_reportServerName_tx
                                  ,in_destinationType_cd       => 'cache'
                                  ,in_destinationFormat_cd     => v_destinationFormat_cd
                                  ,in_reportDBUserName_tx      => v_reportDBUserName_tx
                                  ,in_reportDBuserPassword_tx  => v_reportDBuserPassword_tx
                                  ,in_reportDBName_tx          => v_reportDBName_tx
                                  ,in_otherReportParameters_tt => v_reportParameters_tt);

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

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

This procedure named “showReport” will build report URL according to selected parameters and modify the URL of the htmlRenderer component.
Notice that buildReportUrl, addParameter and deleteParameter are utility functions to construct report request URL. For more details about running report requests and servlet parameters;

http://download.oracle.com/docs/html/B10314_01/pbr_run.htm#1008445
http://download.oracle.com/docs/html/B14048_02/pbr_cla.htm

You may need to update v_domainName_tx, v_portNumber_tx, v_reportServerName_tx, v_reportDBUserName_tx, v_reportDBuserPassword_tx and v_reportDBName_tx variables in this package with appropriate values in your environment.

2d- Creating Actions and attaching to the Components

In this step, an action will be created to call the procedure we created in the last step and attached to the associated Formspider component.

Create an action named “showReport” with following procedure:

oraclereports.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 you application, select a department, choose the ouput format and press “Run Report” to execute your report.

Oracle Reports successfully integrated into Formspider Application