Customizing Reports with XML
10.3 Creating XML Data Models
Oracle9iAS Reports Services introduces a greater level of sophistication in the types of data models you can create using Reports XML tags. Use Reports XML for:
Creating Multiple Data Sources
Linking Between Data Sources
Creating Group Hierarchies within Each Data Source
Creating Cross-Product (Matrix) Groups
Creating Formulas, Summaries, and Placeholders at any Level
Creating Parameters
This section provides examples of these uses of Reports XML.
In addition to these data model types, Oracle9iAS Reports Services provides support for using PL/SQL in your Reports XML. This includes support for local program units, report-level triggers, and attached PL/SQL libraries.
10.3.1 Creating Multiple Data Sources
The<data>tag now supports the creation of multiple data sources as well as the new pluggable data sources. Each data source is enclosed within its own
<dataSource>tag. The data type definition for the dataSource element is:
<!ELEMENT dataSource ((select|plugin|plsql), comment?,
displayInfo?, formula*, group*)>
<!ATTLIST dataSource name CDATA #IMPLIED
defaultGroupName CDATA #IMPLIED maximumRowsToFetch CDATA #IMPLIED>
The following example creates two SQL data sources and names them Q_1 and Q_2.
It also creates all the necessary columns for the data sources and the default
group—giving the group the specified defaultGroupName or defaulting its own name if defaultGroupName is not specified.
<report name="anyname" DTDVersion="9.0.2.0.0">
<data>
<dataSource name="Q_1" defaultGroupName="G_DEPARTMENTS">
<select>
select * from departments
</select>
</dataSource>
<dataSource name="Q_2" defaultGroupName="G_EMPLOYEES">
<select>
select * from employees
</select>
</dataSource>
</data>
</report>
10.3.2 Linking Between Data Sources
In the presence of multiple data sources, it may be desirable to link the data sources together to create the appropriate data model. Reports data model link objects have also been exposed through Reports XML. They support both group- and
column-level links. You can specify any number of links to create the required data model.
The data type definition for the link element is:
<!ELEMENT link EMPTY>
The link element is placed within a data element and can link any two dataSource objects defined within the data element. For example:
<report name="anyname" DTDVersion="9.0.2.0.0">
<data>
<dataSource name="Q_1" defaultGroupName="G_DEPARTMENTS">
<select>
select * from departments
</select>
</dataSource>
<dataSource name="Q_2" defaultGroupName="G_EMPLOYEES">
<select>
select * from employees
</select>
Within the link element, the Reports defaulting mechanism recognizes
DEPARTMENT_ID1as an alias to theDEPARTMENT_IDcolumn in theEMPLOYEES table without your having to explicitly create such an alias.
10.3.3 Creating Group Hierarchies within Each Data Source
With Oracle9iAS Reports Services, the complete group hierarchy is available to you.
You can specify all the columns within each group and break the order of those columns. You can use formulas, summaries, and placeholders to further customize the objects within groups.
The data type definition for the group element is:
<!ELEMENT group
The following example demonstrates the use of a group element to create a break group under a data source.
<report name="anyname" DTDVersion="9.0.2.0.0">
<data>
<dataSource name="Q_1">
<select>
select * from employees
</select>
10.3.4 Creating Cross-Product (Matrix) Groups
Cross-product groups allow you to define a matrix of any number of groups in the data model. The dimension groups in a cross product may exist in the same data source or may be combined from different data sources to create a matrix. In support of this flexibility, the<crossProduct>tag is placed within the<data>
tag after all the data sources and groups have been created.
The data type definition for the crossProduct element is:
<!ELEMENT crossProduct
(xmlSettings|displayInfo|dimension|(formula|summary|placeholder)*|comment)*>
<ATTLIST crossProduct name CDDATA #IMPLIED mailText CDDATA #IMPLIED>
The following example demonstrates the creation of a single-query matrix.
<report name="anyname" DTDVersion="9.0.2.0.0">
<data>
<dataSource name="Q_1">
<select>
select * from employees
10.3.5 Creating Formulas, Summaries, and Placeholders at any Level
You can place formulas, summaries, and placeholders at any level within the data model. Additionally, you have complete control over all the attributes for each of these objects.
The following example demonstrates the creation of a report-level summary whose source is based on a group-level formula column.
<report name="anyname" DTDVersion="9.0.2.0.0">
<data>
<dataSource name="Q_1">
<select>
select * from employees
</select>
<summary name="CS_REPORT_LEVEL_SUMMARY" function="sum" width="20"
precision="10" reset="report" compute="report"/>
function CF_1Formula return Number is begin
return (:salary + nvl(:commission_pct,0));
end;
In Reports XML, the parameter element is placed between open and close data elements. The data type definition for the parameter element is:
<!ELEMENT parameter (comment?|listOfValues?)>
precision CDATA "0"
The following example demonstrates a dynamic list of values (LOV), an initial value, and a validation trigger.
<report name="anyname" DTDVersion="9.0.2.0.0">
<data>
<dataSource name="Q_1" defaultGroupName="G_DEPARTMENTS">
<select>
select * from departments
</select>
</dataSource>
<parameter name="P_LAST_NAME" datatype="character" precision="10"
initialValue="SMITH" validationTrigger="p_last_namevalidtrigger"
<![CDATA[function P_LAST_NAMEValidTrigger return boolean is last_name char(20);
begin
select count(*) into last_name from employees where upper(last_name)=upper(:p_last_name);
exception when OTHERS then return(FALSE);
end;
</report>