Oracle® Fusion Middleware Publishing Reports to the Web with Oracle Reports Services 11g Release 1 (11.1.1) Part Number B32121-01 |
|
|
View PDF |
Oracle Reports Services introduces a greater level of sophistication in the types of data models you can create using Oracle Reports XML tags. Use XML for:
This section provides examples of these uses of XML.
In addition to these data model types, Oracle Reports Services provides support for using PL/SQL in your XML. This includes support for local program units, report-level triggers, and attached PL/SQL libraries.
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>
In the presence of multiple data sources, it may be desirable to link the data sources together to create the appropriate data model. Oracle Reports data model link objects have also been exposed through Oracle 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> <!ATTLIST link name CDATA #IMPLIED parentGroup CDATA #IMPLIED parentColumn CDATA #IMPLIED childQuery CDATA #IMPLIED childColumn CDATA #IMPLIED condition (eq|lt|neq|gt|gteq|like|notLike) "eq" sqlClause (startWith|having|where) "where">
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> </dataSource> <link name="L_1" parentGroup="G_DEPARTMENTS" parentColumn="DEPARTMENT_ID" childQuery="Q_2" childColumn="DEPARTMENT_ID1" condition="eq" sqlClause="where"/> </data> </report>
Within the link
element, Oracle Reports defaulting mechanism recognizes DEPARTMENT_ID1
as an alias to the DEPARTMENT_ID
column in the EMPLOYEES
table without your having to explicitly create such an alias.
With Oracle 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 (field|exception|rowDelimiter|xmlSettings|displayInfo|dataItem|formula| summary|placeholder|filter|comment)*> <!ATTLIST group name CDATA #IMPLIED fillColor CDATA #IMPLIED lineColor CDATA #IMPLIED formatTrigger CDATA #IMPLIED>
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> <group name="G_DEPARTMENTS"> <dataItem name="DEPARTMENT_ID"/> </group> <group name="G_EMPLOYEES"> <dataItem="EMPLOYEE_ID"/> <dataItem="FIRST_NAME"/> <dataItem="LAST_NAME"/> <dataItem="JOB_ID"/> <dataItem="MANAGER_ID"/> <dataItem="HIRE_DATE"/> <dataItem="SALARY"/> <dataItem="COMMISSION_PCT"/> </group> </dataSource> </data> </report>
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 </select> <group name="G_DEPARTMENTS"> <dataItem name="DEPARTMENT_ID"/> </group> <group name="G_JOB_ID> <dataItem name="JOB_ID"/> </group> <group name="G_MANAGER_ID"> <dataItem name="MANAGER_ID" </group> <group name="G_EMPLOYEE_ID"> <dataItem name="EMPLOYEE_ID"/> <dataItem name="FIRST_NAME"/> <dataItem name="LAST_NAME"/> <dataItem name="HIRE_DATE"/> <dataItem name="SALARY"/> <dataItem name="COMMISSION_PCT"/> </group> </dataSource> <crossProduct name="G_Matrix"> <dimension> <group name="G_DEPARTMENTS"> </dimension> <dimension> <group name="G_JOB_ID"> </dimension> <dimension> <group name="G_MANAGER_ID"> </dimension> </crossProduct> </data> </report>
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> <group name="G_EMPLOYEES"> <dataItem="EMPLOYEE_ID"/> <dataItem name="EMPLOYEE_ID"/> <dataItem name="FIRST_NAME"/> <dataItem name="LAST_NAME"/> <dataItem name="HIRE_DATE"/> <dataItem name="SALARY"/> <dataItem name="COMMISSION_PCT"/> <dataItem name="DEPARTMENT_ID"/> <formula name="CF_REMUNERATION" source="cf_1formula" datatype="number" width="20" precision="10"/> </group> </dataSource> <summary name="CS_REPORT_LEVEL_SUMMARY" function="sum" width="20" precision="10" reset="report" compute="report"/> </data> <programUnits> <function name="cf_1formula" returnType="number"> <textSource> <![CDATA[ function CF_1Formula return Number is begin return (:salary + nvl(:commission_pct,0)); end; ]]> </textSource> </function> </programUnits> </report>
In Oracle Reports XML, the parameter
element is placed between open and close data
tags. The data type definition for the parameter
element is:
<!ELEMENT parameter (comment?|listOfValues?)> <!ATTLIST parameter name CDATA #REQUIRED datatype (number|character|date) "number" width CDATA "20" scale CDATA "0" precision CDATA "0" initialValue CDATA #IMPLIED inputMask CDATA #IMPLIED validationTrigger CDATA #IMPLIED label CDATA #IMPLIED defaultWidth CDATA #IMPLIED defaultHeight CDATA #IMPLIED>
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" defaultWidth="0" defaultHeight="0"> <listOfValues restrictToList="yes"> <selectStatement hideFirstColumn="yes"> <![CDATA[select last_name, 'last_name||'-'||employee_id' from employees]]> </selectStatement> </listOfValues> </parameter> </data> <programUnits> <function name="p_last_namevalidtrigger" returnType="character"> <textSource> <![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; return(TRUE); end; ]]> </textSource> </function> </programUnits> </report>