14
OracleAS Reports Services and XML

This chapter contains the following sections:

• Introducing OracleAS Reports Services and XML

• Creating XML Output "On the Fly' Using OracleAS Reports Services

• Customizing Report Definitions at Runtime

• Performing Batch Report Modifications by Applying XML Report Definitions

• Creating Report Definitions in XML

• Using XML as a Datasource

• Reports Case Studies

• Frequently Asked Questions: Reports and XML

Introducing OracleAS Reports Services and XML

OracleAS Reports Services provides the following XML support:

• Reports can be output and customized as XML [Reports 6i and higher].
  • Create XML output 'on the fly', using OracleAS Reports Services. See "Creating XML Output "On the Fly' Using OracleAS Reports Services" .

  • Customize Report Definitions at runtime. See "Customizing Report Definitions at Runtime" .

  • Run Batch report modifications using XML Report Definitions. See "Performing Batch Report Modifications by Applying XML Report Definitions" .

• Read XML data source from Reports [Reports 9i and higher]. See "Using XML as a Datasource" .

B2B Data Exchange: Why Use XML in Reports?

Figure 14-1 shows the sharing of information with partners and how XML can be used to help send data in a more timely manner. With OracleAS Reports Server, you can automatically generate XML files. A URL is all that is required to invoke an Oracle Report on the Web. By defining a Report module to query the data, suppliers can stream information back to the calling eCommerce application in real time.

Figure 14-1 Why Use XML in OracleAS Reports Services?

Text description of the illustration repintro.gif

See Also: Oracle9i Case Studies - XML Applications, under the chapter, "Customizing Discoverer4i (9i) Viewer with XSL" "Oracle Reports Developer Release 6i: Building Reports" or later. "Oracle Reports Developer Release 6i: Publishing Reports" or later. "Oracle Reports Developer Release 6i Reference Manual" or later. Online help, accessed in several ways, for example from your desktop, go to START > "Oracle Forms and Reports Doc".

What's Needed to Run OracleAS Reports Services

Oracle Reports, or OracleAS Reports Service as it is called now, is part of Oracle Application Server. It seamlessly integrates in the environment and the other services provided by the product.

See Also: http://otn.oracle.com/products/reports/

Creating XML Output "On the Fly' Using OracleAS Reports Services

The data model drives the structure of XML output for a report. XML output is not dependent on the visual layout.

• You can change the structure of a report's XML output by editing the XML properties available in the Property Palette for data model objects.

• Report Builder does not currently support presentation information (color, font, physical layout, etc.) in XML output. You may provide your own presentation information using XSL, which may be specified in the prolog.

• From the command line, you can use the argument DESFORMAT=XML to generate XML output.

  See Also: "Oracle Reports Developer Release 6i Reference Manual", for more information on DESFORMAT.

XML as a Data InterChange Format

You may need to send a report to a B2B partner in XML. Figure 14-2 shows an example of a report in XML. This is obtained by changing one parameter that tells Reports Server to output the report in XML instead of HTML.

Figure 14-2 Example Report Shown in XML

Text description of the illustration rep5_2.jpg

Formatting XML Output Using XSL Stylesheets

Figure 14-3 shows the results of applying an XSL stylesheet to the same XML report shown in Figure 14-2. In this case, the example report of data is needed in HTML.

Figure 14-3 Example Report After Applying an XSL Stylesheet

Text description of the illustration rep5_3.jpg

Customizing Report Definitions at Runtime

OracleAS Reports Services allows modifications to be externalized into a separate 'customization' file, rather than having to create unique versions of each report. Report outputs can be customized for specific users or groups without changing the original report definition.

A customization file is a report definition that is applied to an existing report (.RDF or .XML). It can change certain characteristics of existing report objects, such as the field's date format mask or background color. A customization file can also be used to add entirely new objects to another report.

See Also: "Using XML as a Datasource"

Applying an XML Customization

To apply an XML report definition to an .RDF or .XML file at runtime, use either of the following:

• Runtime options. The CUSTOMIZE command line argument. CUSTOMIZE can be used with RWCLI60, RWRUN60, RWBLD60, RWCON60, and URL report requests.

• Built-In Options. The SRW.APPLY_DEFINITION built-in (a supplied PL/SQL package)

Applying One Customization File

The following command line sends a job request to OracleAS Reports Services that applies an XML report definition, emp.xml, to an .RDF file, emp.rdf:

rwcli60 report=emp.rdf customize=e:\myreports\emp.xml
  userid=username/password@mydb destype=file desname=emp.pdf desformat=PDF
  server=repserver

Reports Runtime.

If you were using OracleAS Reports Services Runtime, then the equivalent command line would be:

rwrun60 userid=username/password@mydb report=emp.rdf
   customize=e:\myreports\emp.xml destype=file desname=emp.pdf
   desformat=PDF

When testing your XML report definition, it is sometimes useful to run your report requests with additional arguments to create a trace file. For example:

tracefile=emp.log tracemode=trace_replace traceopt=trace_app

The trace file provides a detailed listing of the creation and formatting of the report objects.

Applying Multiple XML Customization Files

You can apply multiple XML report definitions to a report at runtime by providing a list with the CUSTOMIZE command line argument. The following command line sends a job request to OracleAS Reports Services that applies two XML report definitions, emp0.xml and emp1.xml, to an .RDF file, emp.rdf:

rwcli60 report=emp.rdf 
  customize="(e:\corp\myreports\emp0.xml,
  e:\corp\myreports\emp1.xml)"
  userid=username/password@mydb destype=file desname=emp.pdf desformat=PDF
  server=repserver

Reports Runtime.

If you are using OracleAS Reports Services Runtime, then the equivalent command line is:

rwrun60 report=emp.rdf 
  customize="(e:\corp\myreports\emp0.xml,
  e:\corp\myreports\emp1.xml)"
  userid=username/password@mydb destype=file desname=emp.pdf desformat=PDF

Applying an XML Customization File Using PL/SQL

To apply an XML report definition to an .RDF file in PL/SQL, use the SRW.ADD_DEFINITION and SRW.APPLY_DEFINITION PL/SQL supplied packages (built-ins) in the Before Form or After Form trigger.

• Applying an XML Definition Stored in a File. (Built-In) To apply XML that is stored in the file system to a report, you can use the SRW.APPLY_DEFINITION PL/SQL supplied package in the Before Form or After Form triggers of the report:

  SRW.APPLY_DEFINITION ('d:\orant\tools\doc60\us\rbbr60\cond.xml');
  

  When the report is run, the trigger executes and the specified XML file is applied to the report.

• Applying an XML Definition Stored in Memory. To create an XML report definition in memory, add the definition to the document buffer using SRW.ADD_DEFINITION before applying it using SRW.APPLY_DEFINITION.

Customizing Reports at Runtime with XML

Using Oracle Reports Developer, in Reports6i and higher, you can change the appearance and content of a report at runtime. To do this, merge the report definition files (RDFs), built from XML tags, with existing .RDF files at runtime, and then execute the combination. You can use XML report definitions for other tasks such as:

• Making batch modifications using XML report definitions with the Reports Conversion Tool (RWCON60). RWCON60 is a PL/SQL supplied package (Built-in function).

• Building a complete report definition in XML that can be run by itself, without an existing /RDF file.

The following examples show you how to modify reports using XML at runtime, including:

• Changing visual attributes of an item and changing its format-mask

• Modifying the attributes of non-database-fields

• Creation of multi-language reports from one RDF file

• Creation of a report without any definitions in the RDF file out of an XML file

Before you Start...

Before you can use the following examples 1 through 5, you must create a default report (a tabular report - "select * from emp", select all columns, "Corporate 1" template) that the XML customizations are applied to. You can call it anything you want, but in the following examples, we refer to it as emp_report.

To activate these modifications, it is best to create a file called modify.xml, and then copy the XML code of the current example into it. This way, you can always use the following same command line to run the report and modifications:

rwrun60 report=emp_report userid=scott/tiger customize=modify.xml

If you modify the scripts listed below, keep all XML specific restrictions in mind, especially the case-sensitivity of XML.

Customizing Reports with XML, Example 1: Modifying F_EMPNO and Setting Color

The following example modifies the field F_EMPNO and sets its color to red.

<report name="emp_report" DTDVersion="1.0">
 <layout>
  <section name="main">
   <field name="F_EMPNO" source="EMPNO" textColor="red"/>
  </section>
 </layout>
</report>

Customizing Reports with XML, Example 2: Changing Text Color of F_EMPNO

Example 2 changes the text-color of field F_EMPNO to red and sets the date-format of field F_HIREDATE to german notation.

<report name="emp_report" DTDVersion="1.0">
 <layout>
  <section name="main">
   <field name="F_EMPNO" source="EMPNO" textColor="red"/>
   <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/>
  </section>
 </layout>
</report>

Customizing Reports with XML, Example 3: Modifying Boilerplate Text Objects

This example shows you how to modify boilerplate text objects. Normal report layout elements are enclosed by the <layout>...</layout> tags, while the boilerplate and logic elements are enclosed by the <customize>...</customize> tags.

<report name="emp_report" DTDVersion="1.0">
 <layout>
  <section name="main">
   <field name="F_EMPNO" source="EMPNO" textColor="red"/>
   <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/>
  </section>
 </layout>
 <customize>
  <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Anst.Dat. </property>
   </properties>
  </object>
 </customize>
</report>

Customizing Reports with XML, Example 4: Replacing a SELECT * Query

As any other element of a report definition, the query is an element that can be customized by the XML file. In this example, the query used when creating the report (select * from emp) is replaced by one using a WHERE-clause (select * from emp where deptno = 10).

Note that you have to use the same datasource name as originally used in the report definition done by the wizard. Otherwise, there would be another datasource created but not used, as there is no association to any repeating frame.

<report name="emp_report" DTDVersion="1.0">
 <data>
  <dataSource name="Q_1">
   <select>
    select * from emp where deptno = 10
   </select>
  </dataSource>
 </data>
 <layout>
  <section name="main">
   <field name="F_EMPNO" source="EMPNO" textColor="red"/>
   <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/>
  </section>
 </layout>
 <customize>
  <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Anst.Dat. </property>
   </properties>
  </object>
 </customize>
</report>

Customizing Reports with XML, Example 5: Adding a Trigger to Field S_SAL

Besides changing the visual attributes of a report, you can easily modify its logic by customizing the PL/SQL code as well. In this example, the report we created does not contain format-triggers. By applying this XML file, a trigger is added to field, F_SAL, that hides the field if the value of :SAL is less than 2500.

<report name="emp_report" DTDVersion="1.0">
 <layout>
  <section name="main">
   <field name="F_EMPNO" source="EMPNO" textColor="red"/>
   <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/>
   <field name="F_SAL" source="SAL" formatTrigger="SAL_FORMAT"/>
  </section>
 </layout>
 <programUnits>
 <function name="SAL_FORMAT">
  <![CDATA[
    function sal_format return boolean
    is
    begin
     if :SAL > 2500 then 
        return (true);
     else
        return (false);
     end if;
    end;
  ]]>
 </function>
 </programUnits>
 <customize>
  <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Anst.Dat. </property>
   </properties>
  </object>
 </customize>
</report>

Performing Batch Report Modifications by Applying XML Report Definitions

Reports' ability to externalize modifications simplifies upgrading and the need to make an application site-specific. You can apply an XML definition and save the resultant definition as a new unique module. This also facilitates updates or upgrades without having to open each file in Reports Builder to make changes.

To update a large number of reports, you can use the CUSTOMIZE command line argument with RWCON60,the Reports Conversion Tool, to perform modifications in batch. Batch modifications are useful when making repetitive changes to a large number of reports, for example, when changing a field's format mask. From Oracle Report Builder, you can run RWCON60 once and make the same change to a large number of reports.

The following example applies two XML report definitions, translate.xml and customize.xml, to three .RDF files, inven.rdf, inven2.rdf, and manu.rdf.

It saves the revised definitions to new files:

• inven1_new.rdf

• inven2_new.rdf

• manu_new.rdf

  rwcon60 username/password@mydb
    stype=rdffile 
    source="(inven1.rdf, inven2.rdf, manu.rdf)" 
    dtype=rdffile 
    dest="(inven1_new.rdf, inven2_new.rdf, manu_new.rdf)" 
    customize="(e:\apps\trans\translate.xml, 
    e:\apps\custom\customize.xml)" batch=yes 
  
  

Creating Mutated RDFs Out of One Master

For example, ERP vendors may want each customer to individualize their own reports. This requires modifying fonts, colors, and so on. By using the Reports XML based customization, vendors can make changes like this to the entire application in one step.

To do so, simply apply the XML customization files using RWCON60 and create a new RDF or REP file out of it, like you an see in the example above. This way, you can create customized applications for each customer without the problem of maintaining different versions of a report. You only have one master-report containing your basic layout and maybe placeholder objects for customer-specific things like logos, company names. And then you apply a customization file for each customer building their own report.

Creating Multi-Version Reports Out of a Single RDF

Creating multi-language reports is always a challenging task. Developing a base report and then translating it for all supported languages can cause maintenance problems. With OracleAS Reports Services' XML customization feature, this is easily done.

You create your report in the base-language and then apply different XML customization files containing language specific settings, such as, label text, data-format, and numeric formats. This way you can create different language versions of your report easily.

Customizing Reports with XML Example 6: Creating Different Language Versions

This example shows how you can easily create different language versions from one report definition. You no longer have to do heavy coding or multiple versions of the same report. Simply create one layout and localize it by applying a "language-XML".

The example modifies emp_report to have a German boilerplate and date-formats. You can easily change this to your local language. Simply replace the German terms by ones used in your language and you have localized the report.

<report name="emp_report" DTDVersion="1.0">
 <layout>
  <section name="main">
   <field name="F_EMPNO" source="EMPNO" textColor="red"/>
   <field name="F_HIREDATE" source="HIREDATE" formatMask="dd.mm.yyyy"/>
   <field name="F_DATE1_SEC2" source="Current Date" formatMask="dd.mm.yyyy"/>
  </section>
 </layout>
 <customize>
  <object name="B_EMPNO" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Pers.No. </property>
   </properties>
  </object>
  <object name="B_ENAME" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Name </property>
   </properties>
  </object>
  <object name="B_JOB" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Pos. </property>
   </properties>
  </object>
  <object name="B_MGR" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Vorges. </property>
   </properties>
  </object>
  <object name="B_HIREDATE" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Anst.Dat. </property>
   </properties>
  </object>
  <object name="B_SAL" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Geh. </property>
   </properties>
  </object>
  <object name="B_COMM" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Prov. </property>
   </properties>
  </object>
  <object name="B_DEPTNO" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Abt. </property>
   </properties>
  </object>
  <object name="B_DATE1_SEC2" type="REP_GRAPHIC_TEXT">
   <properties>
    <property name="textSegment"> Stand vom </property>
   </properties>
  </object>
 </customize>
</report>

If your application requires dynamic switching of languages, you can also consider applying the customization at runtime.

Creating Report Definitions in XML

Another use of XML report definitions is to make an entire report definition in XML that can be run independently of another report. The advantage of this is that you can build a report without using the Oracle Report Builder. In fact, you could even use your own front end to generate the necessary XML and allow your users to build their own reports dynamically.

With OracleAS Reports Services you can also create a report in Report-Builder, save it in XML format so that you have a starting point. You can then modify the XML or use it as template for an application that creates the XML.

Customizing Reports with XML, Example 7: Report from XML Definitions Only

This example requires an empty RDF file. Just create an empty report, and save it as an empty .rdf. You then apply the following XML that contains the needed modifications to create a report out of XML definitions only.

rwrun60 report=empty userid=scott/tiger customize=modify.xml

This example creates a simple report that displays the columns EMPNO, ENAME, SAL, and COMM using the template corp1.tdf. This report looks exactly like one created using the report-wizard.

<report name="emp_report" DTDVersion="1.0">
<data>
 <dataSource name="Q_EMP">
  <select>
   select empno, ename, sal, comm from emp
  </select>
 </dataSource>
</data>
<layout>
 <section name="main">
  <groupLeft name="M_emp" template="corp1.tdf">
   <group>
    <field name="F_EMPNO" source="empno"/>
    <field name="F_ENAME" source="ename"/>
    <field name="F_SAL" source="sal"/>
    <field name="F_COMM" source="comm"/>
   </group>
  </groupLeft>
 </section>
</layout>
</report>

Running XML Report Definitions

Once you have created your XML report definition, you can use it in the following ways.

• "Applying an XML Customization". Apply XML report definitions to .RDF or other .XML files at runtime by specifying the CUSTOMIZE command line argument or the SRW.APPLY_DEFINITION built-in.

• "Running an XML Report Definition by Itself". Run an XML report definition by itself (without another report) by specifying the REPORT command line argument.

Running an XML Report Definition by Itself

To run an XML report definition by itself, send a request with an XML file specified in the REPORT argument. You can do this in the following ways:

• From the command line, to send a job request to OracleAS Reports Services to run report, emp.xml, by itself, use:

  rwcli60 userid=username/password@mydb 
    report=e:\corp\myreports\emp.xml
    destype=file desname=emp.pdf desformat=PDF
    server=repserver
  

• From OracleAS Reports Services Runtime, to send the equivalent job request, the command would be:

  rwrun60 userid=username/password@mydb
    report=e:\corp\myreports\emp.xml
    destype=file desname=emp.pdf desformat=PDF
  

  When running an XML report definition in this way, the file extension must be .XML. You could apply an XML customization file to this report using the CUSTOMIZE argument.

XML Used in JSP for Storing Report Definitions

In Reports 9i, besides using RDF and XML, you can also use Java Server Pages (JSP), as a format for saving a report. Inside the JSP, report elements such as data-model and paper-layout, are stored in XML format.

Depending on what you want to do with the JSP, it may contain only the data-model and the web-source, or it may also contain the paper-layout. In this case you can produce, for example, a PDF document from the same file.

Using XML as a Datasource

OracleAS Reports Services introduces the concept of a pluggable data source (PDS). PDS enables you to create interfaces to your own data sources, hence allowing reports to access this data and use it together with data from other PDSs in a single report.

Through a published interface, OracleAS Reports Services communicates with the PDS and uses it to fetch data from the specified source. Reports PDSs are transparent to the user. The Reports pluggable data sources (PDS) can be used side-by-side in the same data model and linked together.

The PDS is written in JAVA^TM and then linked into OracleAS Reports Services using the configuration files.

Pluggable Data Source, XML-PDS

The XML-PDS is one of the PDSs shipped with OracleAS Reports Services. It enables you to access XML-data from a file or live stream from the Internet. The structure of the XML data must follow a DTD (document type definition) or XSD (XML schema definition). The advantage of using the XML schema is:

• With XML Schema Definition, the columns can have different data types

• With a normal XML document using a DTD, only the structure and simple syntax-specific information is stored. All values are of type CHARACTER.

Using XML for OracleAS Reports Services Configuration Files

As the configuration-files for Oracle Application Server - Reports Service became more and more complex, their format has moved to XML for easier modification and readability.

Pluggable Data Source (PDS) Configuration

Needing to store configuration information, the XML-PDS and the JDBC-PDS both use XML-files for storing their preference settings.

Server Configuration

The configuration file for the server has also moved to XML format. When you migrate from an older version, such as, Reports 6i, the server will read the old configuration file and create a file in XML format for you.

Here is an example of a simple configuration file:

<?xml version = '1.0' encoding = 'ISO-8859-1'?>
<!DOCTYPE server PUBLIC "-//Oracle Corp.//DTD Reports Server Configuration 
9i//EN" "file:/d:/orawin70/report70/server/jasmine.dtd">
<server>
   <cache class="oracle.reports.cache.RWCache">
      <property name="cacheSize" value="50"/>
      <property name="cacheDir" value="d:\orawin70\report70\server\cache"/>
   </cache>
   <!--Please do not change the id for reports engine.-->
   <!--The class specifies below is subclass of _EngineImplBase and implements 
EngineInterface.-->
   <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1" 
maxEngine="1" minEngine="0" engLife="50" maxIdle="30" callbackTimeOut="60000">
      <property name="cacheDir" value="d:\orawin70\report70\server\cache"/>
   </engine>
   <job jobType="report" engineId="rwEng"/>
   <log option="noJob"/>
   <trace traceOpts="trace_all" traceFile="foo.txt" traceMode="trace_replace"/>
   <connection maxConnect="20" idleTimeOut="15">
      <orbClient id="RWClient" publicKeyFile="clientpub.key"/>
      <cluster publicKeyFile="serverpub.key" privateKeyFile="serverpri.key"/>
   </connection>
   <queue maxQueueSize="10000"/>
   <persistFile fileName="d:\orawin70\report70\server\demo-pc.c7.dat"/>
</server>

Distribution File

The distribution capabilities in OracleAS Reports Services have been enhanced dramatically. To fit these changes and make the management of the distribution file easier, it also now uses the XML format.

The new distribution files now look like the following:

<?xml version = "1.0" encoding = "UTF-8"?>
<destination>
 <file id = "1" name = "Testfile.html" section = "main" format = "htmlcss">
   <include src = ""/>
 </file>
<mail id = "2" to = "[e-mail]">
  <attach format = "pdf" srctype = "report">
    <include src = "test.pdf"/>
  </attach>
</mail>
</destination>

How Reports9i XML-PDS Supports XSQL Servlet

XML-PDS allows you to specify an XSQL Servlet file as the XML data source.

You can specify the URL (local or remote) of the XSQL file, as the data source URL for the XML- PDS. XML-PDS then sends the URL request to the Web Server. To process the XSQL file, the webserver noted in the URL, must be configured with XSQL Servlet. By identifying the specific extension of the file mentioned in the URL, such as .xsql in this case, (which can be configured in the Webserver), it invokes XSQL Servlet.

XSQL servlet has the information on the database connection in an XSQL Pages configuration file. It processes the given XSQL Page, sends the SQL query to the database through a JDBC interface, receives the resultset , and puts the resultset in XML format. XSQL Servlet sends this to XML-PDS. Hereafter XML-PDS treats this XML as any other XML data source and processes it inside the reports.

An XML Schema or DTD must be used as Data Definition when XSQL is used as Data Source.

Reports Case Studies

How to Become a Supplier of Live XML Streams

In the ever-growing B2B environment, time has become a vital factor. Many businesses rely on just-in-time delivery to avoid large inventories and the need for large amounts of fixed capital. Hence, it is vital for merchants to know, what their suppliers have in stock at any moment.

To offer such a service, suppliers could provide their inventory-information on their web-page. However, in most cases this information is needed in a format that can be used for further processing; a format such as XML. Even more useful would be a tool that can produce a report in HTML, PDF, and XML, out of the same report. So, merchants can either:

• Go to the web-page of their suppliers and look up the information.

• Use a URL and get the information in XML format to import it into their own system or even the system accessing the data at the moment of request.

In this case, Oracle Application Server - Reports service offers an ideal solution. You can design a good looking report for creating HTML or PDF output, and at the same time use this to produce an XML stream.

Figure 14-4 shows an example of a report produced by OracleAS Reports Services in HTML format.

Figure 14-4 Example Report of Inventory Data Producing HTML and PDF Output

Text description of the illustration rep5-34.jpg

Note: The link in the upper left corner of the report-output. It calls the report itself but with destination-type XML and produces an XML-format output from the same report.

Figure 14-5 shows an example report representing the same inventory data in Figure 14-4 but as an XML stream.

Figure 14-5 Example Report For Inventory Data as an XML Stream

Text description of the illustration rep5-36.jpg

How to Take Advantage of Supplied XML-Data

As merchants, you can use the inventory data provided by your suppliers and combine it with data your own inventory to create a virtual stock list.

OracleAS Reports Services now also enables you to use:

• SQL-PDS (SQL-Pluggable Data Source) module to access your inventory data

• XML-PDS (XML-Pluggable Data Source) module to access your inventory data of your suppliers

To do so, you, ideally need the URL to your suppliers XML-stream and a DTD or XML schema definition that describes the data provided by the XML stream. For example, the DTD would look something like the following:

<?xml version='1.0' encoding='UTF-8' ?>
<!ELEMENT INVENTORY (INVENTORY_ITEM+)>
<!ELEMENT INVENTORY_ITEM 
  ( ID ,
    TITLE ,
    SUBTITLE ,
    IN_STOCK ,
    BACK_ORDER ,
    BACKORDER_AVAILABLE ,
    ITEM_CANCELED_SINCE ,
    PRICE_PER_UNIT ,
    UNITS_PER_PACKAGING ,
    PACKAGE_DESCRIPTION
  )>
<!ELEMENT ID (#PCDATA)>
<!ELEMENT TITLE (#PCDATA)>
<!ELEMENT SUBTITLE (#PCDATA)>
<!ELEMENT IN_STOCK (#PCDATA)>
<!ELEMENT BACK_ORDER (#PCDATA)>
<!ELEMENT BACKORDER_AVAILABLE (#PCDATA)>
<!ELEMENT ITEM_CANCELED_SINCE (#PCDATA)>
<!ELEMENT PRICE_PER_UNIT (#PCDATA)>
<!ELEMENT UNITS_PER_PACKAGING (#PCDATA)>
<!ELEMENT PACKAGE_DESCRIPTION (#PCDATA)>

As the PDS is transparent to you, different PDSs (in this case, SQL and XML PDSs), work seamlessly together in the data-model and can be handled as if they are SQL-ones (that is, joined together).

Figure 14-6 shows a data-model in Oracle Report Builder, with both the SQL and XML data sources linked together.

Figure 14-6 Report Builder: A Data Model Showing the SQL and XML Data Sources Linked Together

Text description of the illustration rep5-37.jpg

You can also create a layout using data from both the data sources together. In this case, the columns, 'virtual stock' and 'backorder' come from the XML-stream and represent data for this particular item in your one supplier's inventory. The data is fetched everytime the report is run using a URL to a report on the supplier's site. This produces an XML stream of your supplier's inventory data.

Figure 14-7 shows the finished report of inventory data that combines SQL data, for example, retrieved from the merchants own inventory, and data from a remote XML stream (virtual data), for example, of inventory data retrieved from a supplier.

Figure 14-7 Example Report of Inventory Data Combining SQL Data and Data From a Remote XML Stream

Text description of the illustration rep5-38.jpg

Frequently Asked Questions: Reports and XML

Can We Output XML From Our Year End Reports Through a Database Interface?

We are working on AU/SG Year End Reporting which involve archiving and production of magtape files. Do you have information on EOY reporting? Have you created any sites/documentation that explain how XML is actually being used for EOY reporting? And what it's being used for? We are using DBIs in fast formulas to obtain a lot of the YE information. Can we use/access DBIs in XML?

Answer

You can output XML from Reports 6i by just changing a single parameter - DESFORMAT. Instead of it being set to HTML or PDF,.... just change it to XML. This will work with your existing report, so you should not have to do anything extra.

What you describe after generating the XML output (that is, applying a stylesheet) is trivial in reports - there is an undocumented PL/SQL built-in SRW.SET_XML_PROLOG to allow you to set the XML prolog line. Refer to bug 1265291 for information on how to do this. It should take about 5mins to test.

With Reports you could have nice looking printable output (PDF, postscript, and so on) or on the web in HTML or HTMLCSS - but at the same time, get XML out just by changing a single parameter, and then use in a B2B environment such as the one described.

Regarding XML report generation, you can do this in different ways:

• Using OracleAS Reports Services and an existing report structure. Generate XML as the output for the report (an Oracle Reports 6i feature). Then to apply a stylesheet for creating the totals and formatting the report following the schema given.

• Using Java and the Oracle XML SQL Utility (XSU). In this case a Java Stored Procedure is created for retrieving all the information needed in the format required. Information about the Oracle Developer Kit is at http://otn.oracle.com/tech/xml/

• Using the XSQL Servlet. You may find very useful information at http://larva.us.oracle.com/docs/tech/xml/oracle_xsu/doc_library/relnotes.html

• Using the current Magtape process. The magtape process already generates all the data necessary. The problem is that no XML code is produced. The solution in this case would involve the creation of a new file (in XML format) using the standard fast-formula strategy for the magtape process.

About your question on DBIs, you can access any table/view in the database using the XML developer kit or the XSQL Servlet, so you can generate XML code using the information from the database items (FF_DATABASE_ITEMS table)

Changing the Report Template

I have a report with a template. The same report is needed with another template. I copied the report and try to change the template. Is there another way of changing the template than the wizard, because the wizard changes the layout of the report and I do not want that.

Answer

You must apply the template first, because when you use the Report Wizard to apply a new template, it will then create a new layout overriding the existing layout. There is no other way.

However, you can use XML for modifying the Report at runtime without changing the layout. See this chapter under section, "Customizing Reports at Runtime with XML" , for some simple examples for modifying a report using XML at runtime.

REP-6106:Error in the XML report definition at line 1

I tried to apply XML to my RDF file to change the boilerplate text from English to Chinese. My XML is shown as below:

  <report name="am01.rdf" DTDVersion="1.0"> 
  <layout> 
  <section name="main"> 
  <field name="f_title" source="title" textColor="red" fontSize="16" 
fontStyle="bold"/> 
  </section> 
  </layout> 
  <customize> 
  <object name="B_4" type="REP_GRAPHIC_TEXT"> 
  <properties> 
  <property name="textSegment">µçÓ°Ãû</property> 
  </properties> 
  </object> 
  </customize> 
  </report> 

I saved this XML file as unicode format, and when I tried applying XML to my RDF through RWRUN60 from command line, report builder gives me the following message:

  REP-6106:Error in the XML report definition at line 1 in 'c:\am01.xml' 
  Start of root element expected instead of TEXT 'null' 

I am using Oracle Report Server/Developer 6i running with Apache web server.

Answer

See the following example for ideas on answering your question:

"Customizing Reports with XML, Example 4: Replacing a SELECT * Query"