Quick Start Guide to Oracle Reports Server 9.0.4

Mark Rittman, July 2005

I've been working with Oracle Reports for the past couple of days, and whilst the product is good the documentation is pretty poor. The problem with Reports is that it's a developer tool, and the documentation is correspondingly technical and very, very longwinded. As I finally managed to get reports up and running and produce some output, I thought I'd put some notes on the blog in case anyone else has to quickly get to grips with the product.

For the purposes of this posting I've been working with Oracle Application Server 9.0.4 running on Solaris. When I started with it, Reports hadn't had any special configuration and I was keen to just get up and running as soon as possible. Therefore what I'm going to go through now is just the bare minimum to get some reports up in a browser. You'll have to refer to the online docs for more detailed notes and configuration options.

Configuring Reports Security

First of all then, before you can run some reports, you need to configure security. You can either run Reports outside of SSO (single sign-on) or you can configure it to run with SSO support.

If you don't want to use Single Sign-On:

  1. Edit $ORACLE_HOMEreportsconfrwservlet.properties

Change #SINGLESIGNON=YES to SINGLESIGNON=NO

  1. Log in to the Enterprise Manager website at http://<server_name>:1810
     
  2. Navigate to the OC4J_BI_Forms > Reports page. Click on the "Edit Configuration File" link.

  1. Delete the section that reads

    <security id="rwSec" class="oracle.reports.server.RWSecurity">
    <!--property name="securityUserid" value="%PORTAL_DB_USERNAME%/%PORTAL_DB_PASSWORD%@%PORTAL_DB_TNSNAME%" confidential="yes" encrypted="no"/-->
    <property name="oidEntity" value="reportsApp_markr_F90769F006885105E0340003BA110550"/>
    </security>
     
  2. Press “OK” and let EMWebsite restart the reports server.

If you require SSO support

  1. Obtain the password for the infrastructure PORTAL schema

    To do this, start up the OID Admin console, log in to OID as ORCLADMIN and then navigate to (note this was tested on 9ias 9.0.2, might have changed with later versions)

    Entity Management
    + cn=OracleContext
      + cn=Products
        + cn=IAS
          + cn=IAS Infrastructure Databases
            + cn=orclReferenceName
              + OrclResourceName=PORTAL

    Write down the PORTAL schema password.
     
    1. Edit $ORACLE_HOMEreportsconfrwservlet.properties

      Change #SINGLESIGNON=YES to SINGLESIGNON=YES
       
  2. Start up the EMWebsite and navigate to the page where you can edit the Reports configuration file as before.

    Change the value of the property named securityUserid to portal/<portal_password>@<tnsnames_infrastructure_entry>

    Where <portal_password> is the password for the PORTAL schema in the infrastructure database, and <tnsnames_infrastructure_entry> is the connect string that the mid-tier uses to connect to the infrastructure database.

    Set the encrypted attribute to NO. For example:

    <security id="rwSec" class="oracle.reports.server.RWSecurity">
    <!--property name="securityUserid" value="portal/UxdE3wQ@inf904" confidential="yes" encrypted="no"/-->
    <property name="oidEntity" value="reportsApp_markr_F90769F006885105E0340003BA110550"/>
    </security>
     
  3. Restart the reports server (using the EMWebsite)

    If you check the configuration file again, the password you supplied for the portal user will now be encrypted, and the encrypted attribute will be YES.

You will now be run reports.

Default Places to Put Reports Files (So That You Can Run Them Using AS 9.0.4)

With Reports, you can save report definitions as either JSP or RDF files. JSP files (Java Server Pages) can support both web and paper layouts, whilst RDF files support only paper layouts. JSPs are generally the way reports are saved now, with RDFs for backwards compatability, and the JSPs are run through the servlet (as this give us more format, distribution, scheduling options).

Note that $ORACLE_HOME is where you have installed the BI & Forms mid-tier.

You can also specify a directory for Reports to look for your report definitions by editing the configuration using the Enterprise Manager website, and providing a value for SourceDir and TempDir.

Displaying a Report using Application Server 9.0.4

To display a simple JSP report

  1. Start up Reports Builder, build a new report using the wizard, select Paper + Web, build the report and save as a JSP.
     
  2. Place the JSP file in the directory

    $ORACLE_HOMEj2eeOC4J_BI_Formsapplicationsreportsweb
     
  3. Run the report, passing across the username, password and connect string

    http://<server:port>/reports/<report_name>?userid=<userid>/<password>@<connect_string>

    e.g.

    http://markr:7779/reports/products.jsp?userid=video5/password@markr_10g

To display a JSP report or an RDF report using the Reports servlet

Note that according to metalink note 258376.1 – reports can only use SMTP servers where authentication is disabled.

Administration Screens

Farm > App_Server_Mid_Tier > OC4J_BI_Forms > Reports


Distributing and Bursting Reports

Whilst most aspects of reports building is straightforward, the process of building a report for bursting and distribution is more complicated, (a) because the documentation doesn't make any sense and (b) even then you'd never work it out unless someone showed you how. Here's how you do it (the example uses the scott/tiger schema)

Scenario – you need to produce a report that lists out, for each department, the employees for that department. The report needs to be “burst” on department name and individual reports produced for each department, showing just their employees.

  1. Build your report using Reports Builder, use the wizard. Select the “Group Above” option.


     
  2. Enter the SQL as normal, make sure one of the columns is the column you will burst on (in our case DNAME).


     
  3. Pick the field that you would like to appear at the top of each report.


     
  4. Then bring across all the other columns


     
  5. Complete the rest of the wizard as normal. At the end of the process, the report layout should look like:


     
  6. Using the Object Navigator, click on the “Main Section” node under the Page Layout node, then change the “Repeat On” property to the field you wish to burst on (in our case, DNAME)


     
  7. Double-click on the “Paper Layout” icon in the Object Navigator. The screen should look like:


     
  8. Expand the “Main Section” node under the “Paper Layout”, expand out the “Body” section fully. Find the node that starts with “R_” and click on it. Click on the right-hand pane (the Report Editor) whilst still leaving the “R_” object selected in the Object pane.


     
  9. Ensuring that you have the Report Editor pane current, not the Object Navigator pane, press Delete. The screen should now look like:


     
  10. To check that it works, run the Paper version of the report. Your report should be on multiple pages like this:


     
  11. To set up distribution, you can do this in two ways. The first is to use Reports Builder to distribute the report (for dev purposes only) and to do this, double-click on the “Main Section” part of the Paper Layout to bring up the properties screen:


     
  12. Click on the Distribution button. Enter a distribution ID, then the report filename and format. You can embed a field name in the filename by surrounding it with &< and >


     
  13. Highlight the report in the object navigator, then run the report distribution using File > Distribute.




     
  14. 14. Alternatively, you can set up a Distribution XML file, save it along with the report as "dist_sales.xml" and use that to specify the distribution. Examples of a distribution file are:

    <destinations>
    <!-- Generate a file for each city -->
    <foreach>
    <file id="MyFiles" name="/home/markr/Rep_&amp;&lt;city&gt;.pdf" format="pdf" instance="this">
    <include src="report"/>
    </file>
    </foreach>
    </destinations>

    And an example that emails attachments

    <!-- Send a mail with an attachment for each warehouse -->
    <mail id="ex1"
    to="<DestinationEmailAddress>"
    from="<OriginEmailAddress>"
    subject="This mail contains one attachemnt per city">
    <body srcType="text">
    This is the body of the message.
    </body>
    <foreach>
    <attach format="pdf" name="report_&amp;&lt;city&gt;.pdf" srcType="report" instance="this">
    <include src="mainSection"/>
    </attach>
    </foreach>
    </mail>

    The report is then run using the distribution file from a URL

    http://<server:port>/reports/rwservlet?report=<report_name>&userid=<userid>/<password>@<connect_string>&distribute=YES&destination=<distribution_file>

    e.g.

    http://markr:7779/reports/rwservlet?server=markr&&report=sales_report.rdf&userid=video5/password@markr_10g&DISTRIBUTE=YES&destination=dist_sales.xml

More information on Oracle Reports can be found at Oracle® Application Server Reports Services Publishing Reports to the Web, Oracle Reports Building Reports, Volumes 1 and 2 and Oracle Reports - Oracle by Example.

Comment on this article or back to Mark Rittman's Oracle Weblog