PK
StBoa, mimetypeapplication/epub+zipPK StB iTunesMetadata.plistp
Oracle Reports enables you to access any data source. See Section 4.15.2, "Accessing non-Oracle data sources".
The topics in this section discuss information related to accessing other data sources in Oracle Reports Builder.
See also
The Pluggable Data Sources section of the Oracle Reports online Help, including the topics:
About pluggable data sources
Adding a pluggable data source
Connecting to a pluggable data source
Adding online Help to a pluggable data source
Pluggable data source interface definition
Troubleshooting PDS problems
Database roles provide a way for end users to run reports that query tables to which they do not have access privileges. For example, a report may query a table that includes sensitive information such as salary data, even though the final report does not display this data.
Database roles are checked in the runtime environment only. If a table requires special access privileges, end users without those privileges cannot run a report that retrieves data from that table. However, if a database role is defined for the report, end users with privileges for that role can run the report using Reports Runtime (rwrun
).
Note: To run a report for which a database role is defined, the end user must run the .rdf file, not the .rep file. When running multiple reports, Reports Runtime automatically switches to the role defined for the current report. |
If you try to open a report in Oracle Reports Builder for which a database role has been defined, you will be prompted for the role password. Typically, only the report designer and DBA have this information.
See also
Oracle Net Services is Oracle's remote data access software that enables both client-server and server-server communications across any network. It supports distributed processing and distributed database capability. Oracle Net Services runs over and interconnects many communication protocols. Oracle Net Services is backwardly compatible with Net8 and SQL*Net.
In releases prior to Oracle Reports 10g, user exits provided a way to pass control from Oracle Reports Builder to a program you have written, which performs some function, and then returns control to Oracle Reports Builder. You could write ORACLE Precompiler user exits, ORACLE Call Interface (OCI) user exits, or non-ORACLE user exits to perform tasks such as complex data manipulation, passing data to Oracle Reports Builder from operating system text files, manipulating LONG RAW data, supporting PL/SQL blocks, or controlling real time devices, such as a printer or a robot.
Beginning with Oracle Reports 10g, you can call Java methods using the ORA_JAVA built-in package and the Java Importer. This reduces the need to have user exits in a report and allows for a more open and portable deployment. You may also use the ORA_FFI built-in package, which provides a foreign function interface for invoking C functions in a dynamic library. With the availability of these new built-in packages, the use of user exits is deprecated in Oracle Reports, though makefiles will still be supplied to permit you to continue to work with existing user exits.
In releases prior to Oracle Reports 10g, the Oracle Call Interface (OCI) provided a set of standard procedures that you could call in your 3GL programs to call Oracle Reports components. These procedures (written in C) were shipped with the Oracle Reports Builder, Reports Runtime, and Reports Converter components. For example, to run a Oracle Reports Builder report from a Pro*FORTRAN program, you could add a RWCRRB procedure call to your program to run the report using the Reports Runtime component.
Beginning with Oracle Reports 10g, the OCI is obsolete. Instead, use the rwclient.exe command line interface or the JSP tag library.
To reference the page number fields:
In the Paper Layout view, click the Line Color tool in the tool palette.
In the color palette, click No Line.
Click the Text tool in the tool palette.
Position the cursor in the top margin where you would like the page number to appear, and click to create a boilerplate object.
In the new boilerplate object, type Page &F_PAGENO of &F_TOTAL_PAGENO
.
Figure 19-7 Page number source and reference fields in top margin of report
In the report output, the current page number will appear where you reference the field F_PAGENO, while the total number of pages for each master will appear where you reference the field F_TOTAL_PAGENO. If you wish, you can resize the page number boilerplate text object to lengthen it to ensure that no values for F_TOTAL_PAGENO get truncated.
Save and run your report. Your final report output should look something like this:
Figure 19-8 Final pages renumbered by repeating frame report output
In this case, it is easier to create the data model and layout separately. Hence, we will create an empty report first, then add the queries, and then create the layouts.
To create a blank report:
Launch Reports Builder (or, if already open, choose File > New > Report)
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
In this chapter, you will learn about reports that include data from an XML pluggable data source (PDS), which is provided with Oracle Reports. By following the steps in this chapter, you can generate the report output shown in Figure 45-1.
Figure 45-1 Report output using an XML PDS
Concepts
The information you must publish is often derived from data in various corporate data sources. These data sources may be SQL-based (relational databases) or non-SQL-based, such as XML. Often, you must combine data from one or more of these data sources to publish meaningful information. For example, you may need to combine data that exists in a relational database with data from a multidimensional database to compare trends and performance.
Oracle Reports enables you to leverage capabilities, such as aggregation, summarization, formatting, and scheduling, on data from any data source. You can leverage the pluggable data source (PDS) architecture to connect to your own data source, as well as to the data sources available with Oracle Reports (XML, JDBC, and Text).
Valid XML files have a document type definition (DTD) or XML schema and strictly adhere to it. XML files can come from any source. However, Oracle provides you with a number of utilities and methods to convert different types of data to XML data files and their DTD or schema.
The elements in your XML data file (data source) must follow the same sequence and format of elements specified in the DTD or XML schema file. For example, suppose your DTD or XML schema defines two elements: WAREHOUSE_ID
first, and PRODUCT_ID
second. In this scenario, WAREHOUSE_ID
must come before PRODUCT_ID
in your XML data file, too. The names of the elements do not need to match. For example, given the following XML data file:
<WAREHOUSE> <INVENTORY> <WAREHOUSE_ID>3</WAREHOUSE_ID> <PRODUCT_ID>2340</PRODUCT_ID> <QUANTITY_ON_HAND>69</QUANTITY_ON_HAND> <PRODUCT_NAME>Chemicals - SW</PRODUCT_NAME> </INVENTORY> <INVENTORY> <WAREHOUSE_ID>3</WAREHOUSE_ID> <PRODUCT_ID>2365</PRODUCT_ID> <QUANTITY_ON_HAND>73</QUANTITY_ON_HAND> <PRODUCT_NAME>Chemicals - TCS</PRODUCT_NAME> </INVENTORY> </WAREHOUSE>
A valid XML schema with suffix xsd
for the above XML is as follows:
</xsd:schema> <?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"> <xsd:element name="WAREHOUSE" > <xsd:complexType> <xsd:sequence> <xsd:element name="INVENTORY" minOccurs="0" maxOccurs="unbounded" <xsd:complexType> <xsd:sequence> <xsd:element name="WAREHOUSE_ID" type="xsd:integer"/> <xsd:element name="PRODUCT_ID" type="xsd:integer"/> <xsd:element name="QUANTITY_ON_HAND" type="xsd:integer"/> <xsd:element name="PRODUCT_NAME" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
The XML PDS implementation supports only two-dimensional listing of records. Oracle Reports expects the XML data file to be in simple table format with rows and columns. Oracle Reports iterates through the XML sequence at one level below the topmost element in the XML. If there are sequences at lower levels (nested elements), they are not handled. Thus, to generate a tabular report, you must "flatten" your XML file into simple row-column format, as shown in the following examples.
Example 45-1 shows an XML data file that includes G_DEPTNO
elements with nested G_EMPNO
elements. Oracle Reports parses all the departments, but not all the employees within each department. For each department, only one employee record would be shown.
Example 45-2 shows how you can modify this XML data file to "flatten" the data. In the example, you will see that the G_DEPTNO
elements include the G_EMPNO
data, without nesting. Now, Oracle Reports parses all the departments, including all the employees within each department.
Example 45-3 and Example 45-4 show the corresponding XML schema files for "nested" and "flattened" XML data, respectively.
Example 45-1 "Nested" XML Data File
<?xml version="1.0" encoding="WINDOWS-1252"?> <EMP> <G_DEPTNO> <DEPTNO>10</DEPTNO> <G_EMPNO> <EMPNO>7782</EMPNO> <ENAME>CLARK</ENAME> <JOB>MANAGER</JOB> <MGR>7839</MGR> <HIREDATE>09-JUN-81</HIREDATE> <SAL>2450</SAL> <COMM></COMM> </G_EMPNO> <G_EMPNO> <EMPNO>7839</EMPNO> <ENAME>MIKEb</ENAME> <JOB>BOSS</JOB> <MGR></MGR> <HIREDATE></HIREDATE> <SAL></SAL> <COMM></COMM> </G_EMPNO> </G_DEPTNO> <G_DEPTNO> <DEPTNO>20</DEPTNO> <G_EMPNO> <EMPNO>7369</EMPNO> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <MGR>7902</MGR> <HIREDATE>17-DEC-80</HIREDATE> <SAL>800</SAL> <COMM></COMM> </G_EMPNO> <G_EMPNO> <EMPNO>7876</EMPNO> <ENAME>ADAMS</ENAME> <JOB>CLERK</JOB> <MGR>7788</MGR> <HIREDATE>12-JAN-83</HIREDATE> <SAL>1100</SAL> <COMM></COMM> </G_EMPNO> </G_DEPTNO> </EMP>
Example 45-2 "Flattened" XML Data File
<?xml version="1.0" encoding="WINDOWS-1252"?> <EMP> <G_DEPTNO> <DEPTNO>10</DEPTNO> <EMPNO>7782</EMPNO> <ENAME>CLARK</ENAME> <JOB>MANAGER</JOB> <MGR>7839</MGR> <HIREDATE>09-JUN-81</HIREDATE> <SAL>2450</SAL> <COMM></COMM> </G_DEPTNO> <G_DEPTNO> <DEPTNO>10</DEPTNO> <EMPNO>7839</EMPNO> <ENAME>MIKEb</ENAME> <JOB>BOSS</JOB> <MGR></MGR> <HIREDATE></HIREDATE> <SAL></SAL> <COMM></COMM> </G_DEPTNO> <G_DEPTNO> <DEPTNO>20</DEPTNO> <EMPNO>7369</EMPNO> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <MGR>7902</MGR> <HIREDATE>17-DEC-80</HIREDATE> <SAL>800</SAL> <COMM></COMM> </G_DEPTNO> <G_DEPTNO> <DEPTNO>20</DEPTNO> <EMPNO>7876</EMPNO> <ENAME>ADAMS</ENAME> <JOB>CLERK</JOB> <MGR>7788</MGR> <HIREDATE>12-JAN-83</HIREDATE> <SAL>1100</SAL> <COMM></COMM> </G_DEPTNO> </EMP>
Example 45-3 Corresponding "Nested" XML Schema File
<?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"> <xsd:element name="EMP"> <xsd:complexType> <xsd:sequence> <xsd:element name="G_DEPTNO" minOccurs="0" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="DEPTNO" type="xsd:string"/> <xsd:element name="G_EMPNO" minOccurs="0" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="EMPNO" type="xsd:string"/> <xsd:element name="ENAME" type="xsd:string"/> <xsd:element name="JOB" type="xsd:string"/> <xsd:element name="MGR" type="xsd:string"/> <xsd:element name="HIREDATE" type="xsd:string"/> <xsd:element name="SAL" type="xsd:string"/> <xsd:element name="COMM" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
Example 45-4 Corresponding "Flattened" XML Schema File
<?xml version="1.0"?> <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema"> <xsd:element name="EMP"> <xsd:complexType> <xsd:sequence> <xsd:element name="G_DEPTNO" minOccurs="0" maxOccurs="unbounded"> <xsd:complexType> <xsd:sequence> <xsd:element name="DEPTNO" type="xsd:string"/> <xsd:element name="EMPNO" type="xsd:string"/> <xsd:element name="ENAME" type="xsd:string"/> <xsd:element name="JOB" type="xsd:string"/> <xsd:element name="MGR" type="xsd:string"/> <xsd:element name="HIREDATE" type="xsd:string"/> <xsd:element name="SAL" type="xsd:string"/> <xsd:element name="COMM" type="xsd:string"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>
For more information on pluggable data sources, refer to the Oracle Reports online Help. If your data source cannot use an existing PDS, the PDS API enables you to write your own PDS and incorporate it into Oracle Reports to access your own unique data sources. The API is documented in Oracle Reports Java API Reference, .
Example Scenario
In this example, you have an international business with warehouses in the United States and overseas. These warehouses are running a decentralized management system that stores the operational data locally at each site. The inventory of the warehouses are managed by the local managers. However, for planning purposes, a team at corporate headquarters needs to access the inventory data (in SQL), including the most recent data, of every warehouse. The warehouse data is only available as an XML stream. You will learn how to combine data from a local database (that is, the warehouse data) and data from an XML feed to create a Web report. You will use static XML files that are provided.
As you build this example report, you will:
Create a Report Manually with SQL and XML Queries:
Create an XML query to access your XML data source using the Data Wizard.
Create a data link between two queries, a SQL query and an XML query.
Apply alternating row colors to your report using format triggers and procedures.
Filter your XML data using a group filter to sort your XML data.
To see a sample report that uses an XML PDS, open the examples folder named xml_pds
, then open inventory_report.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 45-1.
Table 45-1 Example report files
File | Description |
---|---|
|
The final PDF version of the paper report. |
|
The final RDF version of the paper report. |
|
The various SQL statements you will use in this report. |
|
The XML data source for the query in your report. |
|
The XML data stream for your report. |
Congratulations! You have successfully built a report that aggregates data, for both paper and Web. You now know how to:
create a report definition that aggregates data.
modify your report for the Web.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
To build the example in this chapter, you must have access to the Order Entry sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
In this section, you will run your report to the Paper Design view so you can see how your report displays.
Click the Paper Design button in the toolbar to display the Paper Design view. Your report runs, then displays in the Paper Design view. It should look like the following:
Figure 15-7 Paper Design view for the wrapped field report
Save your report as wrappedbreak_
your_initials
.rdf
.
To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
In this chapter, you will learn about reports that display highlighted values based on one or more conditions. By following the steps in this chapter, you can generate the report output shown in Figure 22-1.
Figure 22-1 Conditional highlighting report output
Concepts
You can create a format trigger that will change the appearance of retrieved data depending on factors you define.
You can use the Conditional Formatting dialog box to create this format trigger, or you can manually create them using the PL/SQL Editor. The steps in this example will show you how to use the Conditional Formatting dialog box, then display the code in the PL/SQL Editor to see how the format trigger was automatically generated by Reports Builder.
With conditional highlighting, you can format specified portions of a report's output when certain criteria are met.
Data Relationships
This report uses one query to fetch all data.
Layout
This report uses a tabular layout style. To add conditional highlighting, you will use the Conditional Formatting dialog box to determine which names and salaries will be highlighted in the report output.
Example Scenario
In this example, you will create a report for managers that shows a complete list of employees in the company, but also highlights employee salaries that are greater than or equal to $10,000. You also need to indicate which employees' salaries are between $4,999 and $10,000. In this example, you will use conditional formatting to highlight these figures in bold and red text.
As you build this example report, you will:
Create a Basic Tabular Report using the Report Wizard to included a single query.
Add Conditional Formatting to the Report using the Conditional Formatting dialog box to highlight specific data in the report.
Examine the Conditional Format Trigger Code using the PL/SQL Editor, for the format trigger generated by Reports Builder.
To see a sample report that uses conditional highlighting, open the examples folder named condhigh
, then open the Oracle Reports example report named condhigh.rdf.
For details on how to open it, see "Accessing the Example Reports" in the Preface.
This chapter introduces the concepts for advanced users of Oracle Reports. Each topic in this chapter is also included in the Advanced Concepts section of the Oracle Reports online Help.
Topics are grouped into the following sections:
The steps in this section will show you how to use the Report Wizard to build a simple tabular report.
To create a tabular report:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout Only, then click Next.
On the Style page, type a Title for your report, select Tabular, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT ALL EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.SALARY FROM HR.EMPLOYEES
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 22.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Fields page, click the right arrow (>) to move the following fields to the Displayed Fields list, then click Next. Make sure you move them in the following order:
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
SALARY
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, select Predefined Template and click Beige, then click Finish to preview your report output in the Paper Design view. It should look something like this:
Figure 22-2 Paper Design view for the initial tabular report
Now, let us format the data to make it more meaningful.
To format the report:
In the Paper Design view, select the Salary column by clicking once on the column of data.
Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.
Click the Commas button.
Click the Add Decimal button twice. Two decimal places are added to the right of the decimal point.
The Salary column of your report should now look like this:
Change the alignment of your columns by doing the following:
Click the Salary column heading, then click the Align Center button in the toolbar.
Click the Salary column once, then click the Align Right button in the toolbar.
While the Salary column is selected, Shift-click to select the column heading too, then click and drag one of the right black squares to size the column and column heading.
Click the Employee Id column heading, Shift-click to select the Employee Id column too, then click the Align Center button in the toolbar.
The Paper Design view of your report should now look like this:
Figure 22-4 Paper Design view of the tabular report
Save your report as condhigh_
your_initials
.rdf
.
In this section, you will change the field to expand vertically if the contents of the break field are longer than the specified width. As you can see in Figure 15-6, the width of the fields are fixed, but the names are incomplete. You will also add space to display between each record in the report.
To modify the layout:
In the Object Navigator, double-click the repeating frame icon next to the R_G_NAME node under Paper Layout > Main Section > Body > M_G_Name_GRPFR, to display the Property Inspector.
Under Repeating Frame, change the Vert. Space Between Frames to 0.25
, then press Enter.
Close the Property Inspector.
In the Object Navigator, under the Paper Layout node, navigate to Main Section > Body > M_G_NAME_GRPFR > R_G_NAME.
Double-click the icon next to the F_CUST_LAST_NAME field to display the Property Inspector.
Under General Layout, make sure the Vertical Elasticity property is set to Expand, then close the Property Inspector to accept your changes.
Follow steps 5 and 6 for the F_CUST_FIRST_NAME field.
You have now modified the layout of your report to display all the text in the wrapped fields, and added space between the records.
To make your report easier to read, you can add formatting to the monetary values.
To format monetary values:
On the first page of the report, click one of the cell values. Notice that all of the values are immediately selected, indicating that you can change their properties simultaneously.
Shift-click one of the summary values at the bottom of a column of the matrix.
Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.
Click the Add Decimal Place button in the toolbar twice. Two decimal places are added to the right of the decimal point.
Resize the fields. Click and drag the rightmost handle of the cell value under the SALESMAN label about 0.5 inches (1.5 centimeters) to the left. After you complete this operation, the department summaries from the second page should move onto the first page.
Shift-click the SALESMAN label.
Click the Align Right button in the toolbar.
Click in an open area of the Paper Design view to deselect all of the objects.
Click one of the department summaries at the end of a row in the matrix. All of the department summaries are immediately selected.
Shift-click the report summary underneath the department summaries.
Use the left arrow key to move these summaries to the left until they are approximately flush with the SALESMAN column in the matrix.
Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.
Click the Add Decimal Place button in the toolbar twice. Two decimal places are added to the right of the decimal point.
Resize the fields. Click and drag the rightmost handle of one of the selected fields about 0.5 inches (1.5 centimeters) to the left.
Click the Align Right button in the toolbar.
Figure 25-9 Matrix report in Paper Design view with monetary values formatted
To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above), as well as access to the Order Entry sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
Now that your report has a data model and layout, you can add the logic to control the number of customers displayed. First, you will create two parameters that the user can enter values for at runtime. Second, you will create a group filter that uses the parameters to control which data is included.
To create parameters and add a group filter:
In the Object Navigator, under the Data Model node, click the User Parameters node.
Choose Edit > Create to create a new user parameter under the User Parameters node.
If the Property Inspector is not already displayed, right-click the new user parameter (P_1), then choose Property Inspector to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to CUTOFF_CNT.
Under Parameter, set the Datatype property to Number, set the Width property to 1, and set the Initial Value property to 3.
Repeat the steps above to create another user parameter, using the following property settings this time:
Under General Information, set the Name property to INCR_CNT.
Under Parameter, set the Datatype property to Number, set the Width property to 3, and set the Initial Value property to 0.
In the Object Navigator, under the Data Model node, expand the Groups node, then double-click the properties icon next to the G_CNAME group to display the Property Inspector, and set the following properties:
Under Group, set the Filter Type property to PL/SQL, then click the PL/SQL Filter property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function G_CNAMEGroupFilter return boolean is begin :incr_cnt:=:incr_cnt+1; if :incr_cnt <= :cutoff_cnt then return (TRUE); else return(FALSE); end if; end;
Note: You can enter this code by copying and pasting it from the provided text file calledrank_code.txt . This code is for the Group Filter. |
Click Compile.
Click Close.
This filter increments the counter by 1 each time a record in G_CNAME is fetched, then compares the counter's value to the specified cutoff. When the counter exceeds the cutoff, no more records are fetched.
Tip: Notice that, if the Paper Design view is still open while you add this logic, the report now returns no records in the Paper Design view. To fix this issue, you should display one of the other views (for example, the Data Model view) and then come back to the Paper Design view. You will be prompted by the Runtime Parameter Form to enter values for the two parameters, INCR_CNT and CUTOFF_CNT. |
Click the Run Paper Layout button in the toolbar to display the Runtime Parameter Form, which enables you to change the default values for CUTOFF_CNT and INCR_CNT.
Click the Run Report button to display the report output in the Paper Design view.
Save your report as rank
_your_initials
.rdf
.
Figure 34-3 Tabular report output restricted to top three customers
The steps in this section will show you how to add boilerplate text to your report layout that will display when no records are retrieved.
To add boilerplate text:
In the Paper Layout view, click the Confine Off and the Flex Off buttons in the toolbar.
Note: To adjust the way the boilerplate objects display in your resulting report, you must turn off Confine and Flex modes before you create the objects. |
Click the Text tool in the tool palette.
Draw a rectangle above the two fields F_ENAME and F_JOB to create a new boilerplate text object.
Click in the boilerplate text object, and type "No detail records retrieved.
"
Select the text, then choose Format > Font.
In the Font dialog box, choose Arial, then click OK.
While the Paper Layout view still displays, click the Object Navigator and position the two windows so that you can see them side-by-side.
In the Object Navigator, navigate to M_G_DEPTNO1_GRPFR, and select these objects using CTRL-click:
R_G_DEPTNO1
F_ENAME
F_JOB
Your Object Navigator should look like this:
Figure 21-6 Selected objects in the Object Navigator
In the Paper Layout view, click the Confine On button in the toolbar.
Choose Layout > Bring to Front.
Note: If this menu option is greyed out, click the title bar of the Paper Layout view, but do not click on the canvas itself. This menu option is only available when the Paper Layout view is active. |
By choosing this menu option, Reports Builder will display the records in front of the boilerplate text you just created. If there are no records, the boilerplate text will display.
The Object Navigator should now look like this:
Figure 21-7 Selected objects brought forward in the Object Navigator
Note: You will notice that the boilerplate text, B_1, is now located directly under the parent frame, M_G_DEPTNO1_GRPFR. This placement means that the records displayed by the objects in the repeating frame, R_G_DEPTNO1, will display in front of the boilerplate text. The boilerplate text, which says that no detail records were retrieved, only displays when no records are present. |
Now, we need to change the fill color of the parent frame, so that you cannot see the boilerplate text behind the detail records.
In the Object Navigator, click M_G_DEPTNO1_GRPFR so that it is the only object selected. In the Paper Layout view, you should see this same frame selected.
Click the Fill Color tool in the tool palette, and change the fill color to light blue, so that it matches the template.
Click the Paper Design button in the toolbar to run and display your report in the Paper Design view. Your report should look something like this:
Figure 21-8 Final Paper Output of the Suppressing Labels Report
Note: Notice how the record for Department 40 shows the boilerplate text you added, and the field names for the record are suppressed. |
Save your report as suppresslabels_
your_initials
.rdf
.
In this chapter, you will learn about reports that include fields with wrapped text. By following the steps in this chapter, you can generate the report output shown in Figure 15-1.
Concepts
Reports Builder enables you to modify the look of your report in multiple ways. In this example, you will build a break report where the line wraps on word boundaries if it is too long to fit on one line.
This report contains a field which is of a fixed horizontal width, but can expand vertically if the contents of the break field are longer than the specified width. The field's contents are not truncated; rather, the contents will wrap within the specified width of the field, and the field will expand vertically.
For more information on break reports, refer to the Oracle Reports online Help.
Data Relationships
This is a simple, one-query break report. In addition, you will add three summary columns.
Layout
This report is formatted as a master/detail report. You will modify the layout to add space between sets of information, then change the width of a field so that the break field will wrap.
Optional: You will change the format masks of three fields. You will also use Page Protect in this report. Page Protect causes all objects within a frame or repeating frame to remain together. By specifying Page Protect for the master repeating frame, the customer name and all of its related records will always appear on the same page.
Example Scenario
As you build this example report, you will:
Create a Query in the Data Model View manually, to include one query and two groups.
Create Three Summary Columns to calculate various totals and percentages for your report.
To see a sample wrapped field report, open the examples folder called wrappedbreak
, then open the Oracle Reports example report named wrappedbreak.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
If you scroll down in the Paper Design view, you will notice that the field labels from the second query, Q_Emp, are located directly below the fields from the first query, Q_Dept. In fact, they are almost touching each other. For this report, you will move all of the layout objects belonging to Q_Emp down the page to create some white space between the two layouts.
To create white space between the layouts:
Move and resize the Report Editor window such that you can easily view it and the Object Navigator simultaneously.
Click the Paper Layout button in the toolbar of the Report Editor window to display the Paper Layout view.
In the Object Navigator, expand the Paper Layout node, then the Main Section node, then the Body node.
Click the frame named M_G_LAST_NAME_GRPFR. Note that it is also now selected in the Paper Layout view.
Click the title bar of the Report Editor to make it the active window.
Press the down arrow key a few times to move the frame and all of its objects down the page about 0.25 inches (0.5 centimeters).
Notice that you have moved not only M_G_LAST_NAME_GRPFR but also all the layout objects within it. Here you take advantage of Confine mode, the default mode for the Paper Layout view. With Confine mode on, an object cannot be moved outside of or placed behind its parent. Such a situation renders the layout invalid and no output is produced.
Sometimes moving an object outside of its parent is a valid action. If you need to do so, you can turn off Confine mode by clicking the Confine Off button in the toolbar.
Figure 8-5 Paper Layout view with two layouts and white space added
Save the report as masterb_
your_initials
.rdf
.
Click the Paper Design button in the toolbar to preview your output again. Notice the additional space between the two layouts now.
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
To build the example in this chapter, you must have the example files we have provided (see "Example Scenario", above).
Congratulations! You have successfully built a report with spelled-out cash amounts on the check, as well as the payment and order details on the check stub. You now know how to:
use the Report Wizard to create a simple report definition.
use the tools in the Paper Layout view to import a blank check image and add fields to the report.
create a PL/SQL function that returns spelled-out numbers.
create a formula column based on a PL/SQL function.
use the tools in the Data Model view to link two queries.
use the tools in the Paper Design view to format your check printing report to print on a desired check.
create a check stub that displays a duplicate of the information on the check.
use the Report Block Wizard to create a simple tabular report that displays order details on a check stub.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
The steps in this section will show you how to create a layout that displays the objects in the desired locations on your preprinted form. You will start by adjusting the margin of your report, then creating the mailing label portion that will print at the top of each page that shows the address information for the customer. You will then create the body of the report, which contains the order information that display the date, order number, description, number, price per item, and total price.
Before you can start adding layout objects, you will need to adjust the margin of your layout.
To set up the layout:
Click the Paper Layout button in the toolbar to display the Paper Layout view of your report.
To make it easier for you to manipulate your layout objects, expand the size of the Paper Layout window.
Click the Edit Margin button in the toolbar to view the margin portion of the layout.
Using the resize handles, adjust the size of the margin (the solid black bar) to the dimensions of your form. In our example, our form is 8.5 inches by 11 inches (21.6 centimeters by 27.9 centimeters), so we will expand the margin to 8.5 inches (21.6 centimeters) across, and 11 inches (27.9 centimeters) down.
Tip: You can use the corner resize handles to adjust the size of your margin. |
Click the Edit Margin button in the toolbar again to return to the body of the layout.
Now, you are ready to create your report layout.
In this section, you will use the Report Block Wizard to create a mailing label for the address section of your preprinted form.
To create an address label:
In the Paper Layout view, click the Report Block tool in the tool palette.
Using the rulers along the edges of the Paper Layout view, drag a rectangle starting at 1 inch (2.5 centimeters) from the left and 0.25 inches (0.5 centimeters) from the top, to 6 inches (15 centimeters) from the left and 1.25 inches (3 centimeters) from the top. Release your mouse button to display the Report Block Wizard.
On the Style page of the Report Block Wizard, select Mailing Label, then click Next.
On the Groups page, click G_CUSTOMER and click Down in the Available Groups list to specify the Print Direction and move this group to the Displayed Groups list, then click Next.
In the Mailing Label Text field, create a mailing label by clicking the appropriate columns and buttons (for example, click EMPLOYEE_NAME in the Available Fields list, then click > and New Line, and so on), so that the Mailing Label Text field looks like this:
&<EMPLOYEE_NAME> &<CUSTOMER_NAME> &<C_STREET_ADDRESS> &<C_CITY>, &<C_STATE_PROVINCE> &<C_POSTAL_CODE>
Note: For more information on building a mailing label report, see Chapter 6, "Building a Mailing Label Report". |
Click Next.
On the Template page, select No template, then click Finish to display your report layout in the Paper Layout view. It should look something like this:
Figure 32-6 Paper Layout view of the initial layout
The region that you just created is a boilerplate object called B_TBP. While this object is selected, view the Object Navigator and notice the new boilerplate object:
Figure 32-7 Report Block boilerplate object in the Object Navigator
While the object is selected, choose Tools > Property Inspector to display the Property Inspector (or right-click the object and choose Property Inspector).
In the Property Inspector, under General Layout, set the Horizontal Elasticity property to Variable. This ensures that appropriate space is available for the mailing label text.
Save your report.
In this section, you will add a report block that displays the order items and details that correspond to the customer identified in each mailing label. These items and details are required for your preprinted form.
To add order item details to your report:
In the Object Navigator, click the repeating frame R_G_CUSTOMER that surrounds the boilerplate object B_TBP created in Section 32.3.2, "Create an address label for your form".
In the Paper Layout view, with R_G_CUSTOMER selected, use the corner resize handles to adjust the size of the repeating frame to the size of your preprinted form (in our example, 8.5 inches (21.6 centimeters) across and 11 inches (27.9 centimeters) down). Make sure the frame does not extend beyond the single page boundary.
Click the Report Block tool in the tool palette.
Using the rulers as guides, drag a region 0.25 inches (0.5 centimeters) from the left and 1.5 inches (4 centimeters) from the top, to 8.25 inches (21 centimeters) from the left and 2 inches (5 centimeters) from the top. Release your mouse button to display the Report Block Wizard.
On the Style page of the Report Block Wizard, select Group Left, then click Next.
On the Groups page, click the following groups and click Down in the Available Groups list to specify the Print Direction and move them to the Displayed Groups list, then click Next:
G_ORDER_DATE
G_ORDER_ITEMS
On the Fields page, click the following fields in the Available Fields list and click the right arrow (>) to move them to the Displayed Fields list, then click Next:
ORDER_DATE
PRODUCT_ID
PRODUCT_DESCRIPTION
QUANTITY
UNIT_PRICE
ITEM_TOTAL
On the Labels page, remove the label names since they are not required for the preprinted form, and adjust the widths as follows, then click Next:
Fields and Totals | Labels | Width |
---|---|---|
ORDER_DATE | <none> | 9 |
PRODUCT_ID | <none> | 8 |
PRODUCT_DESCRIPTION | <none> | 40 |
QUANTITY | <none> | 4 |
UNIT_PRICE | <none> | 8 |
ITEM_TOTAL | <none> | 10 |
Note: The Width values are based on the space available on the preprinted form. |
On the Template page, select No Template, then click Finish to display your report layout in the Paper Layout view. It should look something like this:
Figure 32-8 Paper Layout view of the preprinted form example with mailing label and order item details
Save your report.
Now that you have added the elements you want displayed on your report, you need to set up the layout so that only records required for the preprinted form display on each page, and that the correct amount of information displays in the available amount of space on the form. Also, the form requires that a summary of each page displays in the same location on the page.
To adjust the layout and add page summaries:
In the Object Navigator, click M_G_ORDER_ITEMS_GRPFR, as shown in the following image:
Figure 32-9 Selected frame in the Object Navigator
In the Paper Layout view, you should see the same object selected, as shown in the following image:
Figure 32-10 Selected repeating frame in the Paper Layout view
In the Object Navigator, double-click the properties icon next to M_G_ORDER_ITEMS_GRPFR to display the Property Inspector, and set the following properties:
Under General Layout, set the Vertical Elasticity property to Fixed to ensure that only this area is available for the order items to display.
In the Object Navigator, double-click the properties icon next to R_G_CUSTOMER to display the Property Inspector, and set the following properties:
Under Repeating Frame, set the Maximum Records per Page property to 1, to ensure that only one customer's information prints per page.
In the Object Navigator, double-click the view icon next to the Paper Layout node to display the Paper Layout view.
In the Paper Layout view, click the Field tool in the tool palette.
Draw a region 7 inches (18 centimeters) from the left and 10.25 inches (26 centimeters) from the top, to 8 inches (20 centimeters) from the left and 10.5 inches (27 centimeters) from the top.
Note: This field, as well as the second field you will create, should not be located within the repeating frame called M_G_ORDER_DATE_GRPFR. If you cannot fit these fields on the page, resize the repeating frame so that you can place the fields below the repeating frame, but still on the same page. |
Double-click the new field object (F_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to F_PAGE_TOTAL.
Under Field, set the Source property to CS_PAGE_TOTAL (the summary column you created in Section 32.2, "Manually Create the Data Model for Your Report").
Following the steps above, create another field directly below F_PAGE_TOTAL in the Paper Layout view, then set its Name property to F_ORDER_TOTAL and Source property to CS_ORDER_TOTAL.
The new layout objects should now look like this:
Figure 32-11 F_PAGE_TOTAL and F_ORDER_TOTAL fields in the Paper Layout view
Save your report.
Congratulations! You have successfully created a mailing label paper report. You now know how to:
define a mailing label report using the Report Wizard.
adjust the vertical spacing between labels.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
Now that you have created your paper report layout, you will learn how to take the same report and generate a JSP-based Web report that looks the same as your paper report.
To modify your JSP-based Web report:
In the Report Editor, click the Web Source button in the toolbar to display the Web Source view.
In the Web Source view, you need to change the dummy JSP code created by Oracle Reports Builder.
In the Web Source view, find the text:
<rw:dataArea id="yourDataArea"> <!-- Report Wizard inserts the default jsp here --> </rw:dataArea>
Tip: Choose Edit > Find and Replace, then typedataArea in the Find what text box. |
Ensure that you place the cursor in between the opening and closing <rw:dataArea>
tags, and choose Insert > Frame. This inserts the following code in your Web Source view:
<rw:include id="include" src="..."> </rw:include>
Note: Therw:include tag enables you to include the outer frame of your paper layout in the Web Source view as JSP code. Adding a rw:include tag to the rw:dataArea tag enables you to include and reuse the existing paper layout in a Web layout. |
In the Object Navigator, locate the top level layout object frame of your paper layout. Specify its name in the src
attribute of rw:include
tag. The line should look like this:
<rw:dataArea id="yourDataArea"> <!-- Report Wizard inserts the default jsp here --> <rw:include id="mypaperreport_id" src="M_G_EMPLOYEE_ID_GRPFR"> </rw:include> </rw:dataArea>
Click the Run Web Layout button in the toolbar to display your new JSP-based Web report in your browser. The report should look something like this:
The report displays, in Web format, the employee data you specified. Here, you can see employee details, such as their name, email, hire date, salary, department to which they belong and their location.
Save your report as myexcelreport_
your_initials
.jsp
.
Congratulations! You have successfully used a Text pluggable data source for a paper report. You now know how to:
set up your textpds.conf
file.
use the Report Wizard to create a paper report based on the text data source.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
When you create a report with multiple queries, it is typically easier to create all of the queries with the Data Wizard first and then create the layouts with the Report Wizard.
To create the queries:
In the Data Model view, choose Insert > Query to display the Data Wizard.
If the Welcome page displays, click Next.
On the Query page, type Q_Customer
for the Query name, then click Next.
On the Data Source page, select SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT CUSTID, NAME FROM CUSTOMER ORDER BY NAME
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 13.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Groups page, click Next.
Click Finish to display your first query in the Data Model view.
Repeat the steps above for a second query, but this time name your query Q_Item
and use the following SELECT
statement:
SELECT CUSTID, DESCRIP, ITEMTOT, ORDERDATE, ITEM.ORDID FROM ORD, PRODUCT, ITEM WHERE ITEM.ORDID = ORD.ORDID AND ITEM.PRODID = PRODUCT.PRODID ORDER BY CUSTID, DESCRIP, ORDERDATE
On the Groups page of the Data Wizard:
Click CUSTID1 and click the right arrow (>) to move this field to the Group Fields list.
Do the same for DESCRIP.
Figure 13-2 Groups page of the Data Wizard
Click Next.
On the Totals page, click ITEMTOT and click Sum.
Click Finish to display the data model for your report in the Data Model view. It should look something like this:
Figure 13-3 Two-query data model with summaries
To add the data link:
In the Data Model view, click the Data Link tool in the tool palette.
Click and drag from the CUSTID column in the G_CUSTID group to the CUSTID1 column in the G_CUSTID1 group. Notice that a line is drawn from the bottom of the G_CUSTID group to the Q_Item query. Labels for CUSTID and CUSTID1 are created at each end of the line to indicate they are the columns linking G_CUSTID to Q_Item.
Figure 13-4 Two-query data model with a data link
Double-click the new data link line to display the Property Inspector and examine the property settings:
G_CUSTID is identified as the parent, while Q_Item is listed as the child. In terms of the data, the customer's identifier and name make up the master record and should print once for the associated item order information retrieved by the Q_Item query.
Notice that WHERE already appears in the SQL Clause property. WHERE is the default clause used in master/detail relationships. You can replace WHERE with other SQL clauses such as HAVING and START WITH, but for this report the default is correct.
The other point to notice is that an equal sign (=) appears in the Condition property. An equality (that is, table1.columnname = table2.columnname) is the default condition for master/detail relationships defined through a data link. You can replace the equal sign with any other supported conditional operator (to see what is supported, click the field), but for this report the default is the proper condition.
Now that you have created your JSP-based Web report, which looks the same as your paper report, you will modify it further to display the report in Microsoft Excel in your Web browser.
First, copy myexcelreport_
your_initials
.jsp
to the deployment directory of your Application Server. For testing purposes, in this example, we will use the Oracle WebLogic Server instance shipped with the Oracle Developer Suite.
For more information on deploying a JSP-based Web report, refer to the section "Deploying Your Reports" in the chapter "Running Report Requests" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual
To modify the report to display in Microsoft Excel:
In the Report Editor, click the Web Source button in the toolbar to display the Web Source view.
In the Web Source view, you will change the content type of the HTTP response using the JSP directive contentType
, so that the browser opens Microsoft Excel to display your report output.
In the Web Source view, find the following text:
<%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %> <%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp" session="false" %> <%@ page contentType="text/html;charset=ISO-8859-1" %> <rw:report id="report"> <rw:objects id="objects"> </rw:objects> ...... ...... <!-- </rw:report> -->
and modify it to read as follows:
<%@ taglib uri="/WEB-INF/lib/reports_tld.jar" prefix="rw" %> <%@ page language="java" import="java.io.*" errorPage="/rwerror.jsp" session="false" %> <%@ page contentType="application/vnd.ms-excel;charset=ISO-8859-1" %> <rw:report id="report"> <rw:objects id="objects"> </rw:objects> ...... ...... <!-- </rw:report> -->
Save your report.
Copy the Web report (myexcelreport_
your_initials
.jsp)
into the following directory:
$DOMAIN_HOME\servers\MANAGED_SERVER_NAME\stage\reports\reports\web.war
Start your Oracle WebLogic Server instance to enable the deployment of your report.
On Windows, you can do either of the following:
From the Start menu, choose Programs > Oracle Classic Instance > Reports Services > Start WebLogic Server - WLS_REPORTS.
For example, the Oracle Classic Instance may be Oracle Classic Instance-asinst_1.
From the command line, run the following command:
cd $DOMAIN_HOME\bin\startManagedServer.bat WLS_REPORTS
On UNIX, start the shell script from the command line:
$DOMAIN_HOME\bin\startManagedServer.sh WLS_REPORTS
Tip: The Oracle WebLogic Server instance starts once the containers for Java EE have been initialized. |
Type the following URL to display your report in Microsoft Excel in your Web browser:
http://your_computer:port/reports/yourexcelreportname.jsp?userid=hr/hr@db
In our example, we use:
http://mycomputer-pc:8888/reports/my_excel_report.jsp?userid=hr/hr@orcl
Note: The connect string you type in the URL is for the database you used to create the data model in Section 29.2, "Create a Query and the Layout". For the purposes of this example, we have used plain text to pass the connect string. For information on using security, refer to the chapter "Securing Oracle Reports Services" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual. |
The report runs, and the browser displays your report in Microsoft Excel.
Note: If you want your report to display the same colors in Excel as in your report definition, you must use colors supported by the Excel color palette. Otherwise, Excel tries to match unsupported colors, but the match may not be what you expect. |
Your final report output should look something like this:
Figure 29-4 Microsoft Excel Output of Your Report
Notice in your output that the department values are properly positioned, but they repeat for every record in the department. What you really want is for the department values to appear once for each department. To accomplish this task, you will first create a global variable to be used in comparing the current department value to the previous one. You will then write a Format Trigger to determine which values to suppress based upon the comparison within each department's records.
To create a global variable:
In the Object Navigator, click the Program Units node.
Click the Create button in the toolbar. The New Program Unit dialog box displays.
Type global
in the Name field and select Package Spec.
Click OK.
In the PL/SQL Editor, type the following PL/SQL:
PACKAGE global IS prev_val varchar2(14); END;
Click Compile
Click Close.
To add the format trigger:
In the Object Navigator, type F_DEPARTMENT
in the Find field to select it.
Double-click the properties icon to the left of F_DEPARTMENT to display the Property Inspector, and set the following properties:
Under Advanced Layout, double-click the Format Trigger property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function F_DEPARTMENTFormatTrigger return boolean is begin If global.prev_val = :department then return(false); Else global.prev_val := :department; return(true); END IF; end;
Click Compile.
Click Close.
Click the title bar of the Report Editor to make it the active window. Return to the Paper Design view if you are not already there. Notice the change in your report output.
Figure 20-7 Final report output displayed in the Paper Design view
Save the report.
In this chapter, you will learn about reports that include headers and footers. By following the steps in this chapter, you can generate the report output shown in Figure 16-1.
Figure 16-1 Group left report output with header and footer
This report has a page header, Employee Summary Report
, printed in the upper margin area of every page of the report, and a footer, Total Salary for Department <number>: <sum_sal>
, printed at the end of the list of employee information for each department.
Concepts
A header and footer report contains boilerplate or fields in its header or footer. To create headers and footers in your report, add the boilerplate or field in the Paper Layout view of the Report Editor. If you create a field, make sure there is only one value for it. Otherwise, Reports Builder will not know which of the different values you want to print.
How often the boilerplate and fields appear and whether they are headers or footers depends on where you position them in the layout. Any boilerplate that is positioned within a repeating frame will print once every time the repeating frame prints. Boilerplate that is outside of all repeating frames will print once for the entire report.
Data Relationships
This report uses one query to select all necessary columns. You'll add a break by assigning a column to a second group.
Layout
This report uses a group left layout with modifications. You'll increase the size of a repeating frame and the frame enclosing it to ensure they are large enough to contain a footer, then add the footer. Then you'll modify the vertical spacing of a repeating frame to add space between each instance of it.
Example Scenario
In this example, you will create a report that displays and summarizes employee data by department. To make the report more readable, you decide to add a header to each page and to add footer for each master record.
As you build this example report, you will:
Move a Summary to move the departmental summary closer to its label.
Add a Page Heading that repeats on all pages.
Add White Space and Format Fields to format monetary values.
To see a sample report with a header and footer, open the examples folder named headerfooter
, then open the Oracle Reports example named headfoot.rdf
. For details on how to access it, see "Accessing the Example Reports" in the Preface.
The steps in this section show you how to build a simple data model and report layout in the Report Wizard, which you can then use to generate either a JSP-based Web report or a paper report. In the next section, you will modify the JSP, so that the appropriate information displays in your Web report.
To create a data model and layout:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout only, then click Next.
On the Style page, select Tabular, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT EMPLOYEES.EMPLOYEE_ID, EMPLOYEES.FIRST_NAME, EMPLOYEES.LAST_NAME, EMPLOYEES.HIRE_DATE, EMPLOYEES.SALARY, DEPARTMENTS.DEPARTMENT_NAME, LOCATIONS.CITY FROM EMPLOYEES, DEPARTMENTS, LOCATIONS WHERE EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID AND DEPARTMENTS.LOCATION_ID=LOCATIONS.LOCATION_ID ORDER BY CITY
Note: You can enter this query in either of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 29.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, select Predefined Template and click Wine, and then click Finish to display your report output in the Paper Design view. It should look something like this:
Save your report as mypaperreport_
your_initials
.rdf
.
This chapter provides procedures for using Oracle Reports to create objects and design your reports. Each topic in this chapter is also included in the How To... section of the Oracle Reports online Help (see Section 4.1.1, "Using the Oracle Reports online Help").
The procedures are grouped into the following sections:
Congratulations! You have successfully created an across group paper report. You now know how to:
manually create a data model with two queries.
create a master/detail report using the Report Block Wizard.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
To build this example report, you will need access to the data source and the graphics we have provided. You will also need to install an extra table into the database, which contains the pictures for this report.
To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you don't know if you have access to this sample schema, contact your database administrator.
You will also need to ask your database administrator to install the Pictures table by using the provided file called pictures.dmp
.
Before you build this report, make sure the following images are in the dynamicgraphics
directory where the sample report file is located:
NEW_YORK.TIF
BOSTON.TIF
DALLAS.TIF
CHICAGO.TIF
Although we have chosen to use TIF files in this example, you can use other graphic file formats, such as GIF, JPEG, and BMP.
Then, update your REPORTS_PATH
in the Windows registry to include the directory where these images are located. The REPORTS_PATH
is located under the Oracle Home entry in the Windows entry. When you edit the entry, add the full directory path of the location of your images.
On UNIX, you can set REPORTS_PATH
in $ORACLE_HOME/bin/reports.sh
.
For the purposes of this chapter, we have provided an RDF file you can use for bursting. In this section, you will set up the report for bursting by deleting the repeating frame and setting the Repeat On property of the Main section, so that the data bursts on each warehouse ID. This way, you can distribute the data for each warehouse as a report to the corresponding warehouse e-mail ID.
Note: For more information on the Repeat On property, refer the Oracle Reports online Help. |
To set up the report for bursting:
In Reports Builder, choose File > Open.
Navigate to the directory where your examples source files are located, and open the file inventory_report_dist.rdf
.
The report displays in the Object Navigator.
In the Object Navigator, under the report name, double-click the view icon next to the Paper Layout node to display the Paper Layout view.
In the Paper Layout view, select the outermost repeating frame (R_G_WAREHOUSE_ID), then delete it.
See Also: For more information on frames and repeating frames, refer the Oracle Reports online Help. |
In the Object Navigator, right-click the Main Section node, then choose Property Inspector to display the Property Inspector for the Main Section:
Under Section, set the Repeat On property to G_WAREHOUSE_ID.
Save the report as inventoryreport_dist_
your_initials
.rdf
.
You have set up your report to burst based on the warehouse ID.
The steps in this section show you how to use the Summary Column tool in the Data Model view to create three summary columns. These columns will calculate the percentage of each order total that the customer has purchased, the total purchases the customer has made, and the percentage of the total sales of all customers.
To create the summary columns:
In the Data Model view, click the Summary Column tool in the tool palette, then click in the G_CUST_FIRST_NAME group to create a summary column.
Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to PCT.
Under Summary, set the Function property to % of Total, set the Source property to ORDER_TOTAL, set the Reset At property to G_CUST_FIRST_NAME, and set the Compute At property to Report.
Create a second summary column in group G_NAME with the following properties:
Under General Information, set the Name property to CUST_TOTAL.
Under Summary, set the Function property to Sum, set the Source property to ORDER_TOTAL, and set the Reset At property to G_NAME.
Create a third summary column in group G_NAME with the following properties:
Under General Information, set the Name property to PCT_TOTAL_SALES.
Under Summary, set the Function property to Sum, set the Source property to PCT, and set the Reset At property to G_NAME.
Your data model should now look something like this:
Figure 15-5 Data Model with Three Summary Columns
Save your report as wrappedbreak_
your_initials
.rdf
.
To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
The chapters in this Part provide steps to build reports that include special formatting. The example reports show you how to wrap text data, add headers and footers, format headers with database values that change at runtime, renumber pages, include intermixed fields, suppress labels, use conditional text, and add text, color, and graphics to your report.
This Part contains the following chapters:
A wrapped field report wraps the data on word boundaries if it is too long to fit in one line. The report contains a field that is of a fixed horizontal width but can expand vertically if the contents of the break field are longer than the specified width. The contents of the fields are not truncated.
A header and footer report contains boilerplate or fields in the header or footer section. This report has a page header printed in the upper margin area of every page of the report and a footer printed at the end.
A header with database values report can include dynamic values in the page header of the report. For example, you can print both the first and last department numbers found on each page.
A report with graphics, text, and color enables you to modify the look of your report. You can enhance a report by adding an image to the margin, adding a title or a border, or applying different fonts and text styles.
A report that renumbers pages by repeating frame displays page numbers using the format "Page X of Y Pages". The first number (X) corresponds to the current page of each parent record. The second number (Y) corresponds to the total number of pages of each parent record.
An intermixed fields report includes the group field between its related fields. This is in contrast to a group report. Normally in the group report, the break field appears to the left of its related fields in the group left report or above in the group above report.
A report that suppresses labels is a master/detail report that fetches a master record with no associated details. Therefore, the report suppresses the detail information for a single record but allows the other master/detail records to display.
A conditional form letter report generates two different form letters from one report. Since the two letters share common features, it is more convenient to create a base form letter and then apply conditions to certain parts. The conditions will determine whether the part should display for the current record.
Conditional highlighting draws attention to specific data in a report by using visual formatting. You can use Oracle Reports Builder or the PL/SQL Editor to create a format trigger that changes the appearance of retrieved data depending on factors you define. For example, you can display amounts greater than 1000 in red.
A report with dynamic graphics contains drawings and images that are "dynamic." Any changes that you make to the graphics will be reflected in your report output at runtime.
In this chapter, you will learn about reports that include multibyte characters. By following the steps in this chapter, you can generate the PDF report output shown in Figure 38-1 and Figure 38-2.
Figure 38-1 Output of the multibyte character set UTF8 report in Japanese
Figure 38-2 Output of the same multibyte character set UTF8 report in Polish
Concepts
With multibyte character support built into Oracle Reports, you can publish reports in any language for your global enterprise. You can produce reports that display text in multibyte character sets, such as Simplified Chinese, Traditional Chinese, Japanese, Korean, and Unicode.
Oracle Reports supports multibyte characters in various output formats, including PDF. In this chapter, you will build a multibyte character set report using the UTF8 character set to generate PDF output.
You can generate a portable PDF file using Oracle Reports. The PDF features, such as font subsetting and embedding, enable you to generate PDF files that can be viewed on an end user's computer without having special fonts installed.
Note: You must have Acrobat Reader installed on your computer to view PDF output. |
When you use PDF font embedding, you include the entire font set in the PDF file.
PDF font embedding in Oracle Reports is for single-byte Type1 fonts and not for TrueType fonts. Therefore, if you want to include specific TrueType fonts in your report, you must first convert these fonts to Type1 fonts using any of the available third party tools.
Tip: Make sure that you have the necessary licenses for the fonts before embedding them in your output PDF file. |
Although PDF font subsetting and font embedding are mutually exclusive, PDF font subsetting is a variant of PDF font embedding. PDF font subsetting is the preferred method of creating multibyte reports because it works for both single and multibyte fonts.
With the font subsetting, the PDF output file contains information of only those characters that appear in the document. Therefore, this feature produces more efficient output in terms of file size. Both font embedding and subsetting eliminate the dependency of installing specific fonts on your computer.
Note: For more information, see the chapter "Using PDF in Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual. |
Example Scenario
In this example, you have an international business with offices in the United States, Japan, and Poland. In the organization, local managers have control of the inventory of the warehouses. Offices in different locations want to print the list of products in their preferred language. You will create a report in PDF format that contains multibyte characters for the Web and paper output.
As you build this example report, you will:
Create the Query and Paper Layout for a Multibyte Report using the Report Wizard.
Display Multibyte Characters in the Report using the UTF8 TrueType font.
To see a sample multibyte character set report, open the examples folder named multibyte
, then open the Oracle Reports example named multibyte_utf8.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
The topics in this section discuss the Object Navigator in Oracle Reports Builder.
The Object Navigator provides a hierarchical display of all objects in a report or template, including attached libraries and program units.
Using the Object Navigator, you can:
Create reports, parameters, PL/SQL program units, and attached libraries.
Select and work with reports, queries, PL/SQL program units and libraries, data model objects, layout objects, and parameter form objects.
Display Report Editor views.
Expand and collapse nodes.
Search for objects.
View objects by hierarchy or type.
Display properties.
Customize the Object Navigator.
Drag and drop PL/SQL program units.
Access
To display the Object Navigator:
Click Finish or Cancel in the Report Wizard.
Open an existing report.
Choose Tools > Object Navigator.
Press F5.
See also
Section 4.2.4, "Setting preferences for the Object Navigator"
The Object Navigator enables you to view objects by ownership hierarchy or by object type.
View > Change View > Ownership View displays objects in a parent-child hierarchy. For example, a query's columns would appear underneath the query and a field would appear underneath the repeating frame that encloses it. This view can be a useful debugging tool because it enables you to easily see the relationships between objects.
View > Change View > Object Type View displays objects by their type. For example, all queries would appear underneath the Queries heading and all database columns underneath the Database Columns heading. This view can be useful when you want to quickly find objects in the Object Navigator.
See also
Section 4.2.4, "Setting preferences for the Object Navigator"
To build the example in this chapter, you must have access to the Order Entry sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
Once your data model is complete, you need to create a layout for the data objects to display in the report output. This particular report consists of two separate layouts:
a group left layout for listing customer purchases individually
a tabular layout that shows a summary of the customer's purchases by product
Given that two layouts are required, you need to create the first layout through the Report Wizard and the second by inserting a report block. The reason for taking this approach is that the Report Wizard overwrites everything in the layout. Hence, you can only create the first layout through the Report Wizard. Additional layouts must be created by inserting a report block.
To create the first layout:
Choose Tools > Report Wizard.
On the Report Type page, select Create Paper Layout only.
On the Style page, select Group Left.
On the Groups page, ensure that all of the groups from your data model appear in the Displayed Groups list.
On the Fields page, click column names and the right arrow (>) or left arrow (<) to move columns so that the following columns appear in the Displayed Fields list:
NAME
DESCRIP
ITEMTOT
ORDERDATE
On the Labels page, change the labels and field widths as follows:
On the Template page, make sure Beige is selected under Predefined Template, then click Finish to display your report output in the Paper Design view. It should look like this:
Figure 13-6 Paper Design view for the group left layout with two group columns
To create the second layout:
Click the Paper Layout button in the toolbar to display the Paper Layout view.
In the Paper Layout view, click the Report Block tool in the tool palette.
Starting about 0.5 inches (1.5 centimeters) below the existing layout, click and drag a box about 2 inches (5 centimeters) high and 4 inches (19 centimeters) wide. Release your mouse button to display the Report Block Wizard.
In the Report Block Wizard, on the Style page, select Tabular, then click Next.
On the Groups page, click G_CUSTID1 and click Down to specify the Print Direction and move this group to the Displayed Groups list, then click Next. (G_CUSTID1 should be the only group in the Displayed Groups list when you are done.)
On the Fields page, click the following fields and click the right arrow (>) to move them to the Displayed Fields list, then click Next:
DESCRIP
SumITEMTOTPerCUSTID1.
DESCRIP and SumITEMTOTPerCUSTID1 should be the only fields in the Displayed Fields list when you are done.
On the Labels page, change the labels as follows, then click Next:
On the Template page, make sure Beige is selected under Predefined Template, then click Finish to display your report output in the Paper Design view. It should look like this:
Figure 13-7 Paper Layout view with two layouts
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
In this case, it is easier to create the data model and layout separately. Hence, we will create an empty report first, then add the queries, and then create the layouts.
To create a blank report:
Launch Reports Builder (or, if already open, choose File > New > Report)
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
You can build a matrix report with a single-query in the data model. A single-query data model typically performs better than a multiple-query data model.
When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.
To create a data model and layout:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout Only, then click Next.
On the Style page, type a Title for your report, select Matrix, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT TO_CHAR (HIREDATE, 'YY') YEAR, DEPTNO, JOB, SUM (SAL) FROM EMP GROUP BY TO_CHAR(HIREDATE, 'YY'), DEPTNO, JOB ORDER BY TO_CHAR(HIREDATE, 'YY'), DEPTNO, JOB
Note: You can enter this query in any of the following ways:
|
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 26.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Rows page:
Click YEAR in the Available Fields list and click the right arrow (>) to move this field to the Matrix Row Fields list. YEAR should appear under Level 1.
Figure 26-2 First matrix row in the Report Wizard
Click Level 1 in the Matrix Row Fields list. This step ensures that the next column added will be at Level 2 rather than Level 1.
Click DEPTNO in the Available Fields list and click the right arrow (>) to move this field to the Matrix Row Fields list. Notice that it is added under Level 2 rather than Level 1.
Figure 26-3 Second matrix row in Report Wizard
Click Next.
On the Columns page, click JOB and click the right arrow (>) to move this field to the Matrix Column Fields list. JOB should appear under Level 1, then click Next.
On the Cell page, click SUM_SAL and click the right arrow (>) to move this field to the Matrix Cell Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, change the labels and field widths as follows, then click Next:
Fields | Labels | Width |
---|---|---|
YEAR | (no change) | 4 |
DEPTNO | (no change) | (no change) |
JOB | (no change) | 10 |
SUM_SAL | <none> | 10 |
On the Template page, make sure Beige is selected under Predefined Template, then click Finish to display your report output in the Paper Design view. It should look like this:
Figure 26-4 Paper Design view for the nested matrix report
Note: You can set alignment and format monetary values directly in the Paper Design view. Simply click the item, then click the appropriate toolbar button (if you run the mouse over these buttons, hint text displays). If the Paper Design view and the Object Navigator are displayed side-by-side, notice that when you select an item in one, the selection is reflected in the other. |
Oracle Reports provides a convenient way for you to add a wide variety of graphs to both paper-based and JSP-based Web Reports.
Oracle Reports 11g Release 1 (11.1.1) supports the following features:
Scalable Vector Graphics (SVG) image support
Plotting Irregular time periods. For more information, see Plotting Irregular Time Periods.
New Chart Style in Graph Wizard. For more information, see New Chart Styles.
Number formatting support in Graph Wizard
With the new graph types and features, Reports Builder provides the following benefits:
More graph types for the varied usage
Better graph image output without loosing resolution
Plot time data without restrictions
Format the numbers in the graph independent of the data model
Plotting Irregular Time Periods
Oracle Reports 11g Release 1 (11.1.1) provides support for plotting irregular time periods on time axis. For more information see Figure 39-1, "Plotting Irregular Time Period".
Figure 39-1 Plotting Irregular Time Period
New Chart Styles
Oracle Reports 11g Release 1 (11.1.1) supports the following new chart styles:
Autumn
Black and White
Earth
Executive
Financial
Projection
Regatta
For more information, see Figure 39-2, "New Chart Styles".
Now that you have created a paper layout, suppose that you decide to have a Web layout for this report. You can quickly create a Web layout in the Report Wizard without changing your paper layout.
To create a Web Layout:
Choose Tools > Report Wizard.
Select Create Web Layout only.
Click Finish.
Click the Web Source view button in the toolbar and review the Web source for your Web layout.
Choose Program > Run Web Layout to preview your Web layout.
Notes: If Netscape 7.0 is your default browser, and the browser does not display, set the registry keyHKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser to the default browser location. Ensure that the BrowserName and the BrowserPath keys reflect the correct values. For example: BrowserName=Netscape 7 ; BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe . |
In this case, it is easier to create the data model and layout separately. Hence, we will create an empty report first, then add the queries, and then create the layout.
To create a blank report:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
Now that you have created the necessary formula columns and functions, you can create the layout for your report.
To create the report layout:
Click the Paper Layout button in the toolbar to display the Paper Layout view.
In the Paper Layout view, choose Insert > Report Block.
In the Report Block Wizard, on the Style page, select Tabular, then click Next.
On the Groups page, click G_FIRST_NAME in the Available Groups list and click Down to specify the Print Direction and move this group to the Displayed Groups list, then click Next.
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list. The Displayed Fields list should look like this:
Note: If the fields do not display in the correct order, simply click the field name and drag the field to the correct position in the list. |
Click Next.
On the Labels page, change the labels as follows, then click Next:
Fields | Labels |
---|---|
COMMISSION_PCT | Commission |
TOTAL_COMP | Total Compensation |
On the Template page, click Finish to display your report layout in the Paper Layout view. It should look something like this:
Figure 40-10 Paper Layout view of the PL/SQL example report
Click the Run Paper Layout button in the toolbar to run and display your report in the Paper Design view.
In the Paper Design view, click the Flex Off button in the toolbar.
Delete the Last Name label.
Change the text for the First Name label to Name.
Adjust the width of the new Name label to span over both the first name and last name columns.
Adjust the sizes of the first name and last name columns so that one character displays between the columns.
In the Object Navigator, double-click the properties icon next to the F_FIRST_NAME field to display the Property Inspector, and set the following properties:
Under General Layout, set the Vertical Elasticity property to Fixed, and set the Horizontal Elasticity property to Variable.
Repeat the above step for the F_LAST_NAME field.
Click the Paper Layout button in the toolbar to display the Paper Layout view.
In the Paper Layout view, click the Frame tool in the tool palette.
Draw a frame around the two fields: F_FIRST_NAME and F_LAST_NAME.
With the frame selected, choose Tools > Property Inspector to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to M_NAME.
In the Paper Layout view, make sure Flex Off is selected in the toolbar.
With the frame selected, choose Layout > Move Backward until the frame encloses both the F_FIRST_NAME and F_LAST_NAME fields.
Tip: You can watch the fields in the Object Navigator as you choose Layout > Move Backward. When you see the two fields are sub-nodes of M_NAME, stop. |
When you are done, the Object Navigator should look like this:
Figure 40-11 Object Navigator with M_NAME repeating frame
Click the Paper Design button in the toolbar to display the report in the Paper Design view.
In the Paper Design view, Shift-click the values under the following columns:
Salary
Commission
Bonus
Total Compensation
Click the Currency button in the toolbar to add "$" to the numbers.
Click the Add Decimal Place button twice to add two decimal points to the numbers.
Shift-click the Salary, Commission, Bonus, and Total Compensation column headings to select them too.
Click the Align Right button to right justify the columns.
Your report should now look something like this:
Figure 40-12 Paper Design view of modified report
Save your report.
The steps in this section will show you how to build a simple Parameter Form using plain HTML. You will then modify this HTML Parameter Form in Reports Builder so that you can call the Parameter Form from your JSP-based Web report.
If you do not want to create your own HTML file, you can open the sample HTML file we have provided in the Source directory, called paramform.html
, then view the source code.
To create a simple Parameter Form in HTML:
In a text editor or HTML editor, create an HTML page that contains a form. The form should contain a list of values, a field, and a button. The code for this form can look something like the following:
<form name="form1" method="post" action=""> <h2>Parameter Form</h2> <p>Choose a department from the list, then click the <b>Run Report</b> button to show salary details for each employee in that department.</p> <select name="p_department" size="1"> <option value="1">a</option> </select> <br> <input type="text" name="userid" value="hr/hr@db-connect"> <br> <input type="submit" name="Submit" value="Run Report"> </form>
Note: Although you can use the above code, you will need to change theuserid value to reflect the connection information for your data source. You can also copy and paste the HTML code from the provided file, called simplejsppf_code.txt in the simplejsppf/scripts directory, then modify it in a text or HTML editor. |
Save the HTML file as paramform_
your_initials
.html
. When you display this HTML file in a Web browser, it should look similar to the following image:
The steps in this section will show you how to create a new PL/SQL library, then create a function that will live in this library.
To create the library:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
In the Object Navigator, choose File > New > PL/SQL Library.
A new library displays in the Object Navigator below your report name, under the PL/SQL Libraries node.
If it is not already expanded, expand the node of the new library to show the two subnodes: Program Units and Attached Libraries.
Click the Program Units node, then choose Edit > Create.
In the New Program Unit dialog box, in the Name field, type BONUS_PAY
.
Select Function, then click OK to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
FUNCTION BONUS_PAY(JOB_ID IN CHAR, SAL IN NUMBER, COMM_PCT IN NUMBER) RETURN NUMBER IS BEGIN IF JOB_ID != 'SA_REP' THEN RETURN (SAL * 0.15); ELSE IF SAL * COMM_PCT >= 500 THEN RETURN ((SAL + SAL * COMM_PCT) * 0.15); ELSE RETURN ((SAL + SAL * COMM_PCT) * 0.10); END IF; END IF; END;
Note: You can enter this code by copying and pasting it from the provided text file calledplsql_code.txt . |
Click Compile.
If there are compilation errors, match your code to the code we have provided (either in the example RDF file or in this chapter), then compile it again.
Once there are no compilation errors, click Close.
Your new function displays in the Object Navigator.
Choose File > Save to save your new function.
In the Save Library dialog box, type bonus.pll
, make sure File System is selected, then click OK.
In the Object Navigator, under the MODULE1 report you have created, click the Attached Libraries node. Be sure to select this node, and not the one under the PL/SQL Libraries node.
Choose Edit > Create.
In the Attach Library dialog box, in the Library field, type bonus.pll
.
Note: If you savedbonus.pll to another directory, you can click Browse to find it on your file system. Just make sure you have selected File System before browsing. |
When the library name displays in the Library field, click Attach to attach the library.
Note: If you attach a library whose name also includes a path, Reports Builder will inform you that the path names are not portable, and will give you the option of deleting the path. If you choose to continue using a path specification, Reports Builder will only look in that specific location for the library. So, if you move the library, Reports Builder will not be able to find it. If you delete the path, Reports Builder will use a standard search path to locate the library if it is moved. |
The objects in your Object Navigator should now look something like this:
Save your report as plsqlreport_
your_initials
.rdf
.
To build the example in this chapter, you must have access to the Order Entry sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
Congratulations! You have successfully created a header and footer report. You now know how to:
create a data model and a layout.
move a summary field.
add a heading that repeats on all pages.
add white space and format values.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
Now that you have added space and created your format trigger, your report should display with space after every five records (or the number of records you specify in the Parameter Form).
To run your report:
Click the Run Paper Layout button in the toolbar. When the Parameter Form displays, click the Run button in the toolbar.
Your report displays in the Paper Design view, and should look something like this:
The steps in this section will show you how to create the query and the formula columns that will define the report and call the code in the bonus.pll external PL/SQL library you created.
We recommend that you create the objects in the order described, as some of the formula columns depend on the functions, and so on.
The steps in this section will show you how to create the query that will retrieve the data necessary for this report.
To create the query:
In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.
In the Data Model view, choose Insert > Query to display the Data Wizard.
If the Welcome page displays, click Next.
On the Query page, leave the default query name, then click Next.
On the Data Source page, select SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY,COMMISSION_PCT FROM EMPLOYEES ORDER BY LAST_NAME
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 40.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Groups page, click Next.
Click Finish to display your first query in the Data Model view. It should look something like this:
Figure 40-3 Data Model view of the PL/SQL report
Save your report.
The steps in this section will show you how to create a formula column that will calculate the salary bonus for each employee using the PL/SQL function.
To create the BONUS formula column:
In the Data Model view, click group G_FIRST_NAME, then click the bottom resize handle and drag it down to make room in the group for more columns. Here is an example of what it should look like now:
Figure 40-4 Data Model with expanded G_FIRST_NAME
Click the Formula Column tool in the tool palette, then click in the G_FIRST_NAME group to create a formula column.
Figure 40-5 Data Model with unnamed formula column
Double-click the new formula column object (CF_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to BONUS.
Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function BONUSFormula return Number is begin return BONUS_PAY(:JOB_ID, :SALARY, :COMMISSION_PCT); end;
Note: You can enter this code by copying and pasting it from the provided text file calledplsql_code.txt . This code is for the Bonus Formula Column. |
Click Compile.
Note: If there are compilation errors, compare your code closely against the code we have provided. |
When there are no compilation errors, click Close to display the data model for your report in the Data Model view. It should look something like this:
Figure 40-6 Data Model with BONUS formula column
Save your report.
The steps in this section will show you how to write a function that returns the total compensation for each sales representative (the values of columns SALARY plus COMM plus BONUS), as well as other employees (SALARY plus BONUS).
In the Object Navigator, click the Program Units node, then choose Edit > Create.
In the New Program Unit dialog box, in the Name field, type FINAL_CALC
.
Select Function, then click OK.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
FUNCTION FINAL_CALC RETURN NUMBER IS BEGIN IF :JOB_ID = 'SA_REP' THEN RETURN (:BONUS + :SALARY + :COMMISSION_PCT * :SALARY); ELSE RETURN (:BONUS + :SALARY); END IF; END;
Note: You can enter this code by copying and pasting it from the provided text file calledplsql_code.txt . This code is for Final Calc. |
Click Compile.
When the code is compiled without errors, click Close.
The new function, FINAL_CALC, now displays in the Object Navigator:
Figure 40-7 Object Navigator with FINAL_CALC function
Save your report.
The steps in this section will show you how to create another formula column that will calculate the total compensation. The value calculated by the report-level function FINAL_CALC will be assigned to the column TOTAL_COMP. If you are not sure how to create a formula column, refer to Section 40.3.2, "Create a formula column that calculates bonuses".
To create the TOTAL_COMP formula column:
In the Data Model view, follow the steps above to create a second formula column below the BONUS formula column.
Double-click the new formula column object (CF_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to TOTAL_COMP.
Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function TOTAL_COMPFormula return Number is begin return FINAL_CALC; end;
Note: You can enter this code by copying and pasting it from the provided text file calledplsql_code.txt . This code is for the Total Comp Formula Column. |
Click Compile.
When the code is compiled without errors, click Close to display the data model for your report in the Data Model view. It should look something like this:
Figure 40-8 Data Model with BONUS and TOTAL_COMP formula columns
Save your report.
Oracle Reports User's Guide to Building Reports
11g Release 1 (11.1.1)
B32122-02
November 2011
Oracle Fusion Middleware Oracle Reports User's Guide to Building Reports, 11g Release 1 (11.1.1)
B32122-02
Copyright © 2003, 2011, Oracle and/or its affiliates. All rights reserved.
Primary Author: Swati Thacker
Contributors: Ingrid Snedecor, Gururaj B S, Rohit Marwaha, Ratheesh Pai, Rajesh Ramachandran, Rajiv Malhotra, Prabakara Reddy, Balaravikumar Shanmugasundaram, Suma Shanthappa, Navneet Singh, Hariharan Srinivasan, Vidya Viswanathan, Philipp Weckerle, Vikram Nanda, Pankaj Yadav, Vinod Murthy, Usha M P
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Congratulations! You have successfully built a report that uses an external PL/SQL library to calculate employee bonuses, which you can now use in other reports by simply referring to it. You now know how to:
create and use an external PL/SQL library.
create a default layout in the Report Wizard.
add vertical space between a user-determined number of records.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
The steps in this section will show you how to manually create a query in the Data Model view that will return the items in the customer's order. This data retrieved will be used to display the order details in the check stub.
To manually create a query:
In the Data Model view, click the SQL Query tool in the tool palette, then click an open area in the Data Model view to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following SELECT
statement:
SELECT ALL ORDER_ITEMS.LINE_ITEM_ID, ORDER_ITEMS.ORDER_ID, ORDER_ITEMS.PRODUCT_ID, ORDER_ITEMS.UNIT_PRICE, ORDER_ITEMS.QUANTITY, PRODUCT_INFORMATION.PRODUCT_NAME FROM ORDER_ITEMS, PRODUCT_INFORMATION WHERE PRODUCT_INFORMATION.PRODUCT_ID=ORDER_ITEMS.PRODUCT_ID ORDER BY ORDER_ITEMS.LINE_ITEM_ID ASC
Note: You can enter this query in any of the following ways:
|
Click OK. Your query displays in the Data Model view, and should look something like this:
In the Data Model view, click the Data Link tool in the tool palette.
In the first query, Q_1, under G_ORDER_ID, click ORDER_ID, then drag the line to ORDER_ID1 in Q_2.
Your data model should now look like this:
Figure 31-9 Data Link between two queries
Save your report.
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
The topic in this section discuss the views of a report shown at runtime.
The Runtime Parameter Form is a dialog box that optionally displays at runtime in which you can override default parameter values (for example, values that modify SELECT
statements, route the report output to a specified device, and so on). You define the format of the Runtime Parameter Form in the Paper Parameter Form view. If you do not define a Runtime Parameter Form in the Paper Parameter Form view, Oracle Reports Builder displays a default Runtime Parameter Form for you at runtime.
Change the parameters as desired and then click the Run Paper Layout button in the toolbar to run the report.
Alternatively, you can click the Cancel Run button in the toolbar to cancel.
See also
Section 1.6.5, "About the Paper Parameter Form view"
The Previewer displays on your screen how the printed version of your report will look. In the Previewer, you can scroll though a single page of report output, page through the entire report, and split the screen to view different sections of the same report concurrently. You can also perform the following actions:
Table 1-3 Print previewer actions
To... | Click in the toolbar... |
---|---|
Print the report |
the Print button |
Specify page setup settings |
the Page Setup button |
Open a new Previewer |
the New Previewer button |
Close the Previewer |
the Close Previewer button |
Zoom in |
the Zoom In button |
Zoom out |
the Zoom Out button |
A physical page (or panel) is the size of a page that will be output by your printer. A logical page is the size of one page of your actual report (it can be any number of physical pages wide or long). The Previewer displays the logical pages of your report output, one at a time.
Access
To display the Previewer:
choose File > Print Preview.
Usage notes
To speed the execution of your report, avoid "fetching ahead" when sending report output to the Previewer or Paper Design view. The following items can result in fetching ahead when referenced before the data on which they rely:
total number of pages/panels
grand totals
break columns that are formulas
break columns that have Value if Null specified
Matrix (cross-product) groups also cause fetching ahead. To cross-tabulate the data, Oracle Reports must first fetch all of the data.
It should be noted that while these items slow down the Previewer or Paper Design view, they do not affect performance when writing to a file or some other destination.
Note: A column can cause fetching ahead even if it is not displayed. For example, a grand total may not appear in the report output, but since it is in the report, fetching ahead may still occur when Oracle Reports calculates it. |
See also
Section 4.7.16.3, "Displaying report output in the Previewer"
If you have many reports that use these same REF CURSOR
types and SELECT
statements, you can move the program units that you created into a PL/SQL library stored in a file, so that other reports can easily share the code. The steps in this section will help you to move the program units to a PL/SQL library.
To move the packages into a library:
In the Object Navigator, click the PL/SQL Libraries node, then click the Create button in the toolbar to add a new library.
Choose File > Save As.
Type DEPT_CONTAINER
as the Library.
Ensure that File System is selected.
Click OK.
Drag and drop the following program units from your report to the Program Units node under the newly created DEPT_CONTAINER library:
CONCL_CV (Package Spec)
CONT_CV (Package Spec)
CONT_CV (Package Body)
PORT_CV (Package Spec)
Save DEPT_CONTAINER
.
If the Paper Design view is open, close it.
In the Object Navigator, under the Program Units node for your report, delete CONCL_CV (Package Spec), CONT_CV (Package Spec), CONT_CV (Package Body), and PORT_CV (Package Spec).
Note: If the Paper Design view is open when you delete the packages from the report, you may get some errors. |
Click the Attached Libraries node for your report, then click the Create button in the toolbar to add a new attached library.
Click Browse to find the DEPT_CONTAINER library. It will have a .PLL
file extension. After you have found and selected DEPT_CONTAINER, click Open.
Click Attach.
Choose Program > Compile > All.
Click OK to close the Compile window.
Click the Run Paper Layout button in the toolbar to run the report and view it in the Paper Design view.
Save the report as ref_68_
your_initials
.rdf
.
For the header that you want to create (that is, departments found on this page), you need to create two summary columns that compute the values you need in order to populate the header.
In the Report Editor, click the Data Model button in the toolbar to display the Data Model view.
Click the Summary Column tool in the tool palette, then click in an open area of the Data Model view to create a summary column.
Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to FirstDeptRec.
Under Summary, set the Function property to First, set the Source property to DEPARTMENT_ID, set the Reset At property to Page.
Create a second summary column, and set its properties as follows:
Under General Information, set the Name property to LastDeptRec.
Under Summary, set the Function property to Last, set the Source property to DEPARTMENT_ID, set the Reset At property to Page.
Your data model should now look like this:
Figure 17-3 Data Model with Summary Columns for Header
The topics in this section discuss the various output formats and capabilities in Oracle Reports Builder.
Note: When you run a report to preview format, empty browser windows are launched to display the output. To avoid opening such empty browser windows, complete the following steps in Internet Explorer 7:
If you are using browsers other than Internet Explorer, read their help information to modify this setting. |
In prior releases, Oracle Reports formatted the sections of a report in sequential order: Header section, followed by Main section, followed by Trailer section. This release introduces the capability to change the order in which the three sections of a report are formatted.
Note: Regardless of the order in which the report sections are formatted, the output order is unchanged: Header section first, then Main section, then Trailer section. |
The format order can be set in either of the following ways:
SRW.SET_FORMAT_ORDER
built-in procedure (if defined, overrides the Format Order of Sections property setting)
This feature is useful for formatting any report section first to retrieve information that is known only at the time of formatting, such as page numbers, then using that information in the formatting of a previous section.
For example, to create a table of contents (TOC) for a report, you can format the Main section first and use report triggers to build a table containing the TOC entries. When the first element for the TOC is formatted, a trigger fires and creates a row in the TOC table containing the TOC entry and the page number. After the Main section has completed formatting, the format order setting can define that the Header section is formatted next. The Header section can contain a report block based on the TOC table. After formatting, you can output your report with a TOC (the Header section), followed by the report body (the Main section), followed by the Trailer section.
For the steps to create a TOC for a report, see the example reports in Chapter 35, "Building a Paper Report with a Simple Table of Contents and Index" and Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".
A note about page numbering:
The page numbering of a report follows the format order. For example, in a report with a Header section of 2 pages, a Main section of 8 pages, and a Trailer section of 3 pages, with Format Order set to Main-Trailer-Header, the page numbering will be as follows in the report output: 12, 13 (Header pages, which were formatted last), 1, 2, 3, 4, 5, 6, 7, 8, (Main pages, which were formatted first) 9, 10, 11(Trailer pages, which were formatted second).
If it is not necessary to examine report output in the Previewer (for example, you may have to generate large volumes of output from a fully-tested report or run several reports in succession), you can run your report in batch using rwrun
. This leaves you free to pursue other tasks while your reports are running.
You can run reports in batch mode from the command line, or use a command file to specify arguments. A command file can save you a great deal of typing if you wish to run several reports using the same set of arguments.
You can also use the Reports Server to batch process reports by specifying BACKGROUND=YES
on the command line (valid for rwclient
or rwservlet
) to run reports asynchronously (the client sends the call to the server, then continues with other processes without waiting for the report job to complete; if the client process is killed, the job is canceled).
See also
Section 4.7.2, "Running a report from the command line"
Section 4.7.3, "Running a report using a command file"
The Reference > Command Line section of the Oracle Reports online Help (for information about BATCH
and BACKGROUND
)
Report distribution enables you to design a report that can generate multiple output formats and be distributed to multiple destinations from a single run of the report. You can create distributions for an entire report, and also burst to distribute individual sections of the report. For example, in a single run of a report, you can generate HTML output, send a PostScript version to the printer, and also e-mail any or all sections of the report to a distribution list.
To distribute a report, you first define the distribution, then enable the distribution, as described in Section 4.7.12, "Distributing a report to multiple destinations".
For an example of using section-level distribution, see Chapter 37, "Bursting and Distributing a Report". This chapter covers defining distribution of a single section to multiple destinations, using the Repeat On property and distribution XML file.
Usage notes
You cannot mix character mode and bit-mapped output in one report. The MODE
system parameter can only be set to one value per the entire report (DEFAULT
, BITMAP
, or CHARACTER
).
In order to use the same report definition file to burst and distribute to data-driven formats such as XML
and DELIMITEDDATA
, as well as to layout-driven formats such as PDF
and ENHANCEDSPREADSHEET
, you must ensure the following requirements are met:
The distribution XML file must specify the include
element. For example:
<include src="mainSection"/>
The Repeat On property must be set appropriately for the section(s) specified in the distribution XML file.
The section(s) specified in the distribution XML file in the report paper layout must not be empty.
Examples
Example 1
You can use sectioning and distribution to publish your report output in HTML, and also send a PostScript version to the printer.
Example 2
You can send an executive summary of a report to senior management, and also e-mail detailed breakdowns to individual managers. In this example, a single report with two report sections needs to be created: a portrait-sized summary section and a landscape-sized detail section. Use the Repeat On property to associate the detail section with a data model group that lists the managers and then alter the destination to burst the report on each instance of the data model group to send the output to the appropriate managers.
See also
Section 2.1.2, "About report sectioning and sections"
Section 4.14.19, "Tracing report distribution"
Chapter "Creating Advanced Distributions" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Servicesmanual.
As an alternative to defining the distribution for a report or report section in the Distribution dialog box, you can also create a DST file and specify its name on the command line with the DESTINATION
keyword to distribute the report.
Note: DST files are supported for backward compatibility with prior releases; the preferred and recommended method of distributing reports is with the Distribution dialog box or using XML. |
If a DST file is specified on the command line, the distribution that it defines overrides the distribution defined using the Distribution dialog box.
Note: If you trace report distribution to identify distribution errors (see Section 4.14.19, "Tracing report distribution"), the trace file format is very similar to the DST file, so you can cut and paste to generate a DST file from the trace file. |
The format of each line of a DST file is as follows:
dist_ID: output_def
where
dist_ID
is an identifier for a distribution destination.
output_def
is a series of rwrun
or rwclient
command line keywords that specify the distribution definition. In addition, the following parameter is valid:
LEVEL
specifies the scope of the distribution.
Values for LEVEL
REPORT
means that the distribution applies to the entire report.
Header_Section
means that the distribution applies to the header section only.
Main_Section
means that the distribution applies to the main (body) section only.
Trailer_Section
means that the distribution applies to the trailer section only.
Default
REPORT
Example
The definition in this example sends report output to an HTML file, 3 copies of the main section to a printer, and the header section to a PDF file.
;dst file (specified with the DESTINATION keyword on the command line) DEST1: DESNAME=dst1.HTM DESTYPE=file DESFORMAT=HTML COPIES=1 LEVEL=Report DEST2: DESNAME=prt1 DESTYPE=printer DESFORMAT=BITMAP COPIES=3 LEVEL=Main_Section DEST3: DESNAME=SECT1.pdf DESTYPE=file DESFORMAT=pdf COPIES=1 LEVEL=Header_Section
Note: All parameters for each distribution destination in a DST file (DEST1 , DEST2 , and DEST3 in the example above) must be specified on a single line. Specifying parameters on a new line results in failed distribution with error message REP-3430 4 and/or REP-34305 . |
See also
Pluggable destinations can be used to distribute any content that an engine (not only the Oracle Reports engine) has created in the Reports Server's cache. Oracle Reports provides the following out-of-the-box destinations:
Web
printer
file
Oracle Portal
FTP
WebDAV
You can also define access to your own custom destination by using the Oracle Reports Java APIs to implement a new destination component in the Reports Server. You can choose for your jobs to use an out-of-the-box destination or your customized destination to determine the destination for the output in the cache.
For information and steps to implement and register a destination class, then use the destination with Oracle Reports, see the Oracle FTP Destination tutorial available on the Oracle Reports Plugin Exchange on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html
), click Plugin Exchange.
Note: Currently there is no support for FTP and WebDAV destinations from the Oracle Reports Builder environment. However, these destinations are supported from the Reports Runtime and the Reports Server environments. |
See also
Chapter "Configuring Destinations for Oracle Reports Services" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual.
Event-driven publishing enables you to set up a report to execute when a certain action has been performed. For example, when an employee submits an expense report, new data is being inserted into the database. When this insert event (for example, a database trigger or an Advanced Queuing (AQ) message) occurs, a report is sent to the employee's manager through their portal page or e-mail notifying them to approve/reject this expense report.
Event-driven publishing functionality includes making report objects available inside Oracle Workflow.For detailed information, refer to the chapter "Using Event-Driven Publishing" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
Using the Before Report, Between Pages, or format triggers, you can switch to different printer trays as your report formats. This enables you to easily print pages of the same report on different sheets of paper (for example, letterhead, forms, colored).
You can determine the names of the printer trays defined for your printer in the Page Setup dialog box, then use SRW.SET_PRINTER_TRAY
to set the printer tray as desired.
See also
Oracle Reports uses XML (Extensible Markup Language) in the following ways:
XML tags are used to define Web-based reports (see the topic "Oracle Reports XML tags" in the Reference section of the Oracle Reports online Help).
XML is used to define tag-delimited, structured information.
XML is a supported pluggable data source (PDS). For more information, see Chapter 45, "Building a Report with an XML Pluggable Data Source".
XML is a form of encoding text formats that can be read by many different applications. The XML tags can be used to output information or as a basis for building a pluggable data source to exchange electronic data with a third-party application (EDI).
You may change the XML properties that control XML output for your report at three levels: report, group, and column. Note that in any Oracle Reports Builder-generated XML file, your output mimics the data model, structured by groups and columns. For information on how to view your changes in XML output, see Section 4.7.7, "Generating XML output".
For detailed information about using XML for report distribution and customizing reports through XML, see the chapters "Creating Advanced Distributions" and "Customizing Reports with XML" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
See also
Section 4.7.7, "Generating XML output"
Section 4.7.1, "Running and dispatching a report from the user interface"
The XML PDS section of the Oracle Reports online Help
Oracle Reports Builder can generate report output to Hypertext Markup Language (HTML) and Hypertext Markup Language with a Cascading Style Sheet (HTMLCSS) files, containing the formatted data and all objects. When you generate your report output to an HTML or HTMLCSS file, you can distribute the output to any HTML destination, including e-mail, printer, Oracle Portal, and Web browser.
Usage notes
You apply style sheets and user-defined styles to your report or report objects for HTMLCSS output, using the following properties:
Style Sheets property
CSS Class Name property
CSS ID property
You can include HTML formatting in all bitmap output supported by Oracle Reports (including PDF, RTF, HTML, HTMLCSS, and PostScript), as described in Section 2.8.9, "About HTML formatting".
HTML and HTMLCSS output pass HTML 4.01 syntax checkers.
Note: If you modify your report's Before Report Value property or After Report Value property, you must make sure that you specify valid HTML. If the HTML is not valid, the generated HTML report output may not pass HTML 4.01 syntax checkers. |
You can preview your HTML or HTMLCSS report output in your Web browser by choosing File > Preview Format > Paginated HTML (or Paginated HTMLCSS).
Bookmarks cause multiple HTML files to be created. One master file is created with two frames: one for bookmarks and one for the report output. One HTML file is created for each of these frames. The master document filename is the name specified in the DESNAME parameter. The bookmark filename is desname
b.htm
. The report output filename is desname
d.htm
.
Linked images, image fields, and graphs in a report cause GIF files to be created and referenced from the HTML document. Note that even if the linked boilerplate or image field refers to an external GIF file, a new GIF file is generated.
If an image is stored in the database, one GIF file may be generated for each occurrence of the image in the report. If an image is stored in a file (for example, imported images, linked boilerplate, or image fields that reference files), only one GIF file will be generated per image regardless of how many times it is repeated in the report.
Any GIF image files generated for HTML output have a number sequence (for example, desname
0.gif
... desname
17.gif
).
A comment block in the master document contains the names of all of the files that are associated with the master document.
HTML has seven sizes for text. The font sizes in the report are mapped according to the following table. Note that the user can override the size specified in the HTML file from their browser.
Table 2-1 Report to HTML font sizes
Report font size | HTML font size |
---|---|
less than 8 |
1 |
8 through 9 |
2 |
10 through 12 |
3 |
13 through 15 |
4 |
16 through 20 |
5 |
21 through 30 |
6 |
more than 30 |
7 |
Note: If the font used in the report is non-proportional, TeleType mode is turned on for the generated HTML file. |
HTML does not have the concept of a page. A separator line is placed between each page of the report. Depending upon the browser, you may or may not need to scroll to see the entire report page. Furthermore, if you print the HTML document from your browser, the printer will not necessarily print the separator lines at the bottom of each page. If you do not want the separator line or you want to use a different separator line, you can use SRW.SET_AFTER_PAGE_HTML to change it.
For HTMLCSS, graphics and text can be overlapped.
Once you have generated your report to an HTML or HTMLCSS file, the data model and looping tags are removed and replaced with the data. You can open the HTML or HTMLCSS file in Oracle Reports Builder, but it will be a static text file and not a report.
For information about paginating HTML or HTMLCSS output, see Section 2.8.8.1, "About HTML page streaming".
Restrictions
Objects cannot overlap one another. For example, you could not have text on top of an image.
If objects overlap slightly (two characters or less), then the underneath object is truncated to prevent overlap.
If objects overlap significantly (or one is completely on top of the other), then the underneath object is removed altogether. In this case, any linking information of the removed object is transferred using the same rules as if it were a frame (see the rules about frames below).
Text always takes precedence over horizontal lines, regardless of which is on top. This prevents the line underneath a column label from eliminating the label text.
When multiple output files are generated (for example, when bookmarks are used), any file except the master file will be overwritten without confirmation. For example, GIF files and bookmark files would be overwritten without prompting.
Report frames are not visually represented in the HTML output. Any fill or border attributes of frames do not appear in HTML output.
If the frame in a report is the target of a link or a bookmark, that attribute is transferred to the visible object nearest to the upper left corner of the frame in HTML output. If the frame object is a hyperlink, that attribute will be transferred to all the child (interior) objects that are not hyperlinks. If the hyperlink attribute cannot be transferred to the child objects, the frame's hyperlink is lost.
The only drawn object supported in HTML is a solid, black, horizontal line. The line width specified in the report may be honored depending upon the browser. All other drawn objects (for example, rectangles or circles) in the report layout will not show up in the HTML output. Space for these drawn objects is reserved, but there is no visible representation in the HTML output. HTMLCSS output supports some additional drawn objects (for example, rectangles).
Background (fill) and border (line) colors/patterns for text are not available in HTML. Bold, italic, underline, and foreground (text) color are supported if the browser supports them.
The PDF action attribute is ignored for HTML output.
Any browser customizations that have been made will affect how the generated HTML is displayed.
See also
Section 1.2.2, "About Web Reports"
Section 2.2.3, "About Web links for HTML output"
Section 4.7.5, "Generating HTML or HTMLCSS output"
Section 4.9.2.3, "Creating a boilerplate text object for HTML tags"
Section 4.9.2.7, "Linking an HTML text object to a file"
Section 4.8.4, "Selecting HTML tags from the database"
HTML page streaming enables you to display individual pages of your HTML/HTMLCSS report output in your Web browser, without having to download the entire report. From the first page of the report, you can navigate to any page in the rest of the report. When you click a bookmark or hyperlink with a destination:
within the report, the frame that contains the current page will update with the destination page.
outside the report, the entire base frame (including the bookmark frame, the page, and the navigation frame) will reload.
Navigation controls
You can specify the navigation controls script for a report in either of the following ways:
with PL/SQL (SRW.SET_PAGE_NAVIGATION_HTML
in a Before Report trigger)
in the Report Property Inspector with the Page Navigation Control Type and Page Navigation Control Value properties
Output file names
With HTML page streaming, each page is formatted as a separate HTML document. If your HTML file is named myreport.htm
and there are no bookmarks, the new files are named as follows:
myreport.htm
(for the base frame)
myreport
number
.htm
(for the bookmark file, present only if bookmarks are used in the HTML files)
myreport
number
_1.htm
through myreport
number_n
.htm
( for the pages)
myreport
number
j.htm
(for the navigation JavaScript)
Scope of HTML output
To specify HTML to be displayed on only the first (header) or last (footer) pages of your report, set the Before Report or After Report properties or use the SRW.SET_BEFORE_REPORT_HTML
or SRW.SET_AFTER_REPORT_HTML
PL/SQL procedures. To specify global HTML to apply to the entire report, such as background colors or images, set the Before Page properties or SRW.SET_BEFORE_PAGE_HTML
PL/SQL procedure. The Oracle Reports Builder-generated HTML logo appears only on the last page of your report.
Enabling page-streamed output
To enable page streaming when you format your report as HTML or HTMLCSS output, you must specify PAGESTREAM=YES
on the command line. This option cannot be set using the Oracle Reports Builder user interface.
See also
Section 4.7.16.5, "Displaying individual pages of HTML report output"
Section 4.6.10.2.11, "Adding navigation controls for HTML page-streamed output using PL/SQL"
Oracle Reports allows a variety of fonts, styles, and colors for text objects in your reports. Through the use of the SRW
built-in procedures (see the Reference section of the Oracle Reports online Help), you can allow end users to change text attributes at runtime to a whole object, but not to a section of the text (for example, different formatting for different parts of a text object).
Alternatively, you can use a defined set of HTML formatting tags to format text style (bold, italics, underline, and strikethrough) and text attributes (font name, font color, and font size), and generate formatted text objects in all bitmap output formats supported by Oracle Reports when the objects' Contains HTML Tags property is set to Yes.
The text objects can have static values as in boilerplate text objects, or dynamic values as in field objects or text file link objects. A boilerplate text object can also have dynamic values if it references a field object or a report-level column. For boilerplate text, the formats specified at design-time are merged with rich text formats obtained at runtime. This feature also supports multibyte text containing HTML tags.
In the Paper Design view or Paper Layout view, you can choose the objects to which HTML formatting should be applied by setting their Contains HTML Tags property to Yes. If you run the report from the command line with CONTAINSHTMLTAGS=NO
, Oracle Reports will not interpret the HTML tags for the report, regardless of the object's Contains HTML Tags property setting. For HTML and HTMLCSS ouput, the browser will interpret the HTML formatting tags; for other output formats, the HTML tags themselves will appear as is in the report output.
The following HTML tags are interpreted by Oracle Reports if they appear in a boilerplate text, text file link, or field object. Any HTML tags or attributes not listed appear as is in non-HTML/HTMLCSS report output.
Table 2-2 Supported HTML tags
Tag | Result |
---|---|
|
Text enclosed in either of these tags will be bold. For example:If the text is formatted as |
|
Text enclosed in either of these tags will be italics. For example:If the text is formatted as the output will appear as |
|
Text enclosed in this tag will be underlined. For example: If the text is formatted as the output will appear as |
|
Text enclosed in either of these tags will be struck through. For example: If the text is formatted as the output will appear with a line struck through it. |
|
Generic style container, used to specify font family, size, and color attributes, as described in Table 2-3. For example:
the output will appear in Times New Roman font, size 10 point, in red color. |
The following are the supported properties for the span
tag's style
attribute:
Table 2-3 Supported properties for style attribute of span tag
Property | Result |
---|---|
|
This property specifies the font name. Oracle Reports provides support for specifying only one font name. If you specify a comma-separated list containing more than one font name, Oracle Reports uses the system default font (usually Courier, or its equivalent) to render this tag, without generating a warning. For example:
|
|
This property describes the size of the font.CSS level 2 specifies the following options for this property:
Oracle Reports provides support for only where:
For example:
|
|
This property describes the color of the enclosing element's text content. Oracle Reports supports the following options for this property:
where color names and rgb equivalents are as follows: aqua: For example:
Note: For
is equivalent to:
|
Usage notes
The supported output formats are: PDF, RTF, HTML, HTMLCSS, and PostScript.
HTML formatting supports interleaving and nesting of tags. For example, if you open an italics tag, it is applied to the point of its corresponding closing tag. If a tag is opened several times, it must be closed the same number of times for its effect to cease.
Format attributes are not inherited across instances of a text object of the same name or type (for example, if they are enclosed in a repeating frame). If, however, a text object contains multiple lines, formatting instructions are inherited across the lines of this object only.
This feature supports HTML tags that are hard-coded or referenced in a boilerplate text object. The tags can also be retrieved from a table column with a field object. A file link object that points to a text file which in turn contains HTML tags is also supported.
Oracle Reports tries to find the closest match according to the following criteria for fonts with the same character set:
font face > font size > font style > font weight > font width
If Oracle Reports cannot match the font face, it will try to match the font size; if it cannot match the font size, it will try to match the font style; and so on. For the font lookup alogrithm, refer to the chapter "Managing Fonts in Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services . In font lookup, it is possible that the font used does not have all the required attributes (such as style, size, and so on) embedded in data. In this case, the unavailable font attributes do not appear in the report output.
Limitations
A tag must be specified in its entirety on a single line. If the tag continues to the next line, it is not supported.
For example:
My<b
>format</b>
Only one font name can be specified in the span
tag's style
attribute font-family
property.
For example:
font-family:Arial
If you specify a comma-separated list containing more than one font name, Oracle Reports uses the system default font (usually Courier, or its equivalent) to render this tag, without generating a warning.
Entities such as "
are not supported.
For example:
<span style="font-family: "Courier New";">my format!</span>
You must use literal double quote (") instead.
Examples
Example 1: Inline font change
In a boilerplate text object, you can specify the text boilerplate as follows:
<b>My</b>text
With the Contains HTML Tags property set to Yes, the formatted output will look like this:
My text
Example 2: Single line text with multiple formats
Assume a span of text (in a boilerplate object or database column) that formats on one line, such as:
<b>My <span style="font-family:Courier">HTML</span>
</b><u><span style="font-family:Times New Roman">Formats</span></u>
With the Contains HTML Tags property set to Yes, the formatted output will look like this:
My HTML
Formats
Example 3: Multiline text with multiple formats
Assume a span of text that formats on multiple lines, such as:
Line 1: <i>My<span style="font-family:Courier">
Line 2: HTML</span></i><span style="font-family:Times New Roman><u>
Line 3: Formatting</u></span>.
With the Contains HTML Tags property set to Yes, the formatted output will look like this:
Line 1: My
Line 2: HTML
Line 3: Formatting.
Note in the preceding example that the formats are maintained in successive lines.
See also
Section 4.7.5, "Generating HTML or HTMLCSS output"
Section 4.9.2.3, "Creating a boilerplate text object for HTML tags"
Oracle Reports Builder can generate report output to PDF files, containing the formatted data and all objects. When you generate your report output to a PDF file, you can distribute the output to any PDF destination, including e-mail, printer, Oracle Portal, and Web browser.
Document taxonomy (classification) for PDF output is provided by the report properties Title, Author, Subject, and Keywords, which assist in cataloging and searching a report document.
With font subsetting, the PDF file includes the font information needed to render the PDF, regardless of the availability of that font on the machine used to view the report. PDF font subsetting works for single byte, multibyte, and Unicode fonts, and is the preferred method of creating multibyte reports.
Oracle Reports 10g Release 2 (10.1.2) provided the following enhancements to PDF output:
Direct subsetting of True Type fonts (as a Type0 font), no longer converting to Type3 fonts. As a result, PDF output is clearer, smoother, searchable, and accessible.
Improved True Type Collection (TTC) support, allowing you to use the zero-based index to indicate the specific TTC font file to pick up. In prior releases, Oracle Reports picked up the first font file in the True Type Collection (TTC).
Oracle Reports 11g Release 1 (11.1.1) adds more enhancements to PDF output:
Ability to run the reports designed on Windows on Unix platforms by simply copying the font file to the fonts
folder.
With the new font model, no misalignment will be seen in the PDF subsetted report output.
For detailed information about PDF enhancements and capabilities in Oracle Reports, refer to the chapter "Using PDF in Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
Usage notes
You can preview your PDF report output in your Web browser by choosing File > Preview Format > PDF.
If you are building a multibyte report for multibyte languages, such as Chinese or Japanese, and you need to alias the font in PDF output, you need the CID fonts named within the Acrobat 4.0 packs. Otherwise, you do not need the CID fonts in the Acrobat 4.0 packs.
Oracle Reports does not support Windows UDC for PDF output. For the user-defined characters to be printed or rendered, all the glyphs must be within a single TTF or TTC file.
Graphics and text can be overlapped.
The foreground color of the object will be used as the fill color (regardless of a specified pattern).
You can modify the PDF file, if you have:
the fonts used in your report installed on your machine.
a PDF writer.
Restrictions
For PDF output, the bit-mapped drivers (for example, PostScript) for the currently selected printer are used to produce the output.
See also
Section 1.2.2, "About Web Reports"
Oracle Reports Builder can generate report output to Rich Text Format (RTF) files, containing the formatted data and all objects. RTF can be read by many different word processing software packages, such as Microsoft Word. You can use the software's editing and graphics features to modify and enhance your report output. When you generate your report output to an RTF file, you can distribute the output to any RTF destination, including e-mail, printer, Oracle Portal, and Web browser.
Usage notes
Graphics and text can be overlapped.
Text can only be rotated by 90-degree variations.
As with PDF output, the foreground color of the object will be used as the fill color (regardless of a specified pattern).
Note: When you view the report in Microsoft Word in Office 95, you must choose View > Page Layout to see all the graphics and objects in your report. |
See also
Section 4.7.8, "Generating RTF output"
Section 4.7.1, "Running and dispatching a report from the user interface"
Oracle Reports can generate report output that includes a delimiter to delimited files (for example, files that contain comma-separated or tab-separated data), which are easily imported into spreadsheets or for use with word processors.
Oracle Reports provides two options for generating delimited output:
Delimited: uses the paper layout to generate the output.
DelimitedData (for use when you have problems running large volume reports with Delimited): does not take into account the paper layout, and directly runs off the data model (that is, all fields that are available in the data model will appear in the output, not only those that are displayed in paper layout).
You can specify a delimiter (a character or string of characters) to separate the data (boilerplate or field objects) in your report output in either of the following ways:
On the command line using the DELIMITER
keyword.
In the Delimited Output dialog box or DelimitedData Output dialog box (displayed with File > Generate to File > Delimited or File > Generate to File > DelimitedData) in Oracle Reports Builder.
Note: If you do not specify a delimiter, the default delimiter is a tab. |
You can further distinguish the cells by using a cell wrapper. A cell wrapper can consist of any series of characters, such as a comma or parentheses.
For example, if the data in your report output include the same character as the delimiter (for example, a comma), you can use the parentheses cell wrapper to distinguish each cell:
(1,000,000),(3,6000),(543),(2,003,500)…
Usage notes
DelimitedData output:
When you generate a report to DelimitedData output, only data (as defined by the report data model) displays in the output. Any formatting changes defined in the layout are not reflected in the DelimitedData output.
You can set the following column properties to alter column names and exclude columns from the DelimitedData output file:
The XML Tag property can be used to enter a column alias.
The Exclude from XML Output property can be used to exclude the column from the DelimitedData output.
To generate report output that preserves the report layout information, see Section 2.8.13, "About Enhanced Spreadsheet output".
You can distribute and burst a report in DelimitedData output format, specified either in a distribution XML file or in the Distribution dialog box.
Delimited output:
When you generate a report to Delimited output, the data displays according to the positions of the objects in the Paper Layout view.
If you place A above or to the left of B (where A and B are any boilerplate or field objects) Oracle Reports Builder displays each instance of A before each instance of B in every line of output.
If you create a frame that contains a boilerplate object (A) and encloses a repeating frame that contains a field object (B), each instance of A displays with each instance of B.
Figure 2-9 Delimited output of frame enclosing repeating frame
In the previous layout, if you add another frame that contains a boilerplate object (C) and encloses another repeating frame that contains a field object (D), A displays for every instance of B, and then C displays for every instance of D.
Figure 2-10 Delimited output of two frames enclosing repeating frames
If you create a matrix in your report, be sure to align your objects carefully according to the grid in the Layout Model view. If the objects are not aligned, Oracle Reports Builder may interpret the extra space as an extra row or column and disrupt your report output.
If you create a boilerplate object outside of a matrix, each instance of the boilerplate repeats with every row (not column) of the matrix. Note that boilerplates contained in the matrix will not be repeated with field objects outside of the matrix.
Figure 2-11 Delimited output of boilerplate outside matrix
Restrictions
If the text file contains a field labeled ID
(in uppercase) as the first field, you will be unable to open the file in Microsoft Excel. The following delimited output causes an error in Excel:
ID, name, title, dept
If you want to generate delimited output that contains an ID
field, try changing the database column name to lowercase (that is, id
), or re-arranging the order of the fields.
Report distribution and bursting do not support Delimited output format. You cannot specify DELIMITED
as an output format in a distribution XML file or in the Distribution dialog box.
Note: You can distribute and burst a report in DelimitedData output format, specified either in a distribution XML file or in the Distribution dialog box.
See also
Section 4.7.10, "Generating delimited output"
Section 4.7.1, "Running and dispatching a report from the user interface"
Oracle Reports 11g Release 1 (11.1.1) introduces a new spreadsheet output format, ENHANCEDSPREADSHEET
, which enables you to burst and distribute reports to the spreadsheet format, as well as generate large data sets (up to 75,000 rows) to spreadsheets.
ENHANCEDSPREADSHEET
output formats generate output from paper layout report to HTML files that can be directly opened with Microsoft Excel 2000. You can:
Generate report output to spreadsheet format from existing paper layout reports saved in any format (.rdf
, .xml
, .jsp
), using rwrun
or Reports Server clients (rwclient
, rwservlet
). See the Example below.
Preserve the rich layout formatting such as colors, fonts, conditional formatting, graphs, and images.
Note: DESFORMAT=ENHANCEDSPREADSHEET behavior is the same as the functionality of the rw:include JSP tag. Enhanced Spreadsheet output is not paginated; no page setup information is written in the output, and it is formatted as a single worksheet. Enhanced Spreadsheet output is driven by the layout; the reports ouput displays objects that are in the body area of each section (Header, Main, and Trailer) of the paper layout, and does not format any content in the margin, header, or footer areas. |
Prerequisites
Microsoft Excel 2000 or higher (on the client machine for viewing the output). Note that this software is not needed on the machine where Oracle Reports Services is running.
Usage notes
Enhanced Spreadsheet output requires more memory than DelimitedData output. This is an important consideration for long-running and large reports. To change the name of the output worksheet, set the report's Title property.
Only bitmapped reports can be generated to spreadsheet output; you cannot generate a character-mode report to Enhanced Spreadsheet output.
The following paper layout objects are supported in Enhanced Spreadsheet output:
text (boilerplate and field)
images (any format), including images that are imported, linked, or stored in the database
imported drawings, as well as CGM and OWF drawings
graphic lines
The following paper layout objects are not supported in Spreadsheet output: graphic arc, polygon, rectangle, rounded rectangle, stretchable line, and OLE external object (for reports developed prior to Oracle9i Reports (9.0.2) ). Space for these drawn objects is reserved, but there is no visible representation in the output. This limitation does not apply to horizontal lines.
The following font attributes are supported in Spreadsheet output: size, face, color, style (italic, oblique, underline, overstrike, outline, shadow), weight (bold, normal, extra bold, ultra bold).
The following Web report properties are supported in Spreadsheet output:
Hyperlink property
Hyperlink Destination property
Additional Attributes (HTML) property (for Parameter Form fields)
Table Attributes (HTML) property (for frames and repeating frames)
Alternative Text property (for images)
Contains HTML Tags property (for boilerplate text or field objects that include HTML tags)
The following report taxonomy properties are supported in spreadsheet output:
Title property
Author property
Subject property
Keywords property
Graphs embedded in Spreadsheet output are static image files, and are not interactive. Thus, the Graph Hyperlink property is not supported in Spreadsheet output.
For reports that support globalization, the metadata for direction
and charset
must be provided in a before report escape, because this information is not written to the generated output, by default. For example, you can define a before report escape as follows:
<html> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=&encoding"> <body dir=&Direction bgcolor="#ffffff">
The frames and repeating frames defined in the paper layout are translated to tables in the output. You can specify table attributes such as cellspacing
, cellpadding
, and width
using the Table Attributes (HTML) property for frames and repeating frames.
For reliable formatting of spreadsheet output, the whole layout area should be enclosed in a frame. This prevents the possibility of parallel objects displaying in different vertical positions, one below the other.
The order of the report sections (Header, Main, and Trailer) is preserved in spreadsheet output; that is, the Header section output appears first, followed by the Main section, followed by the Trailer section.
If boilerplate objects or fields are not contiguous to each other; that is, if there is a gap between their boundaries, this might result in empty cells in the spreadsheet output.
If your report defines conditions that may change cell positions when the report is formatted, these changes are not reflected in spreadsheet output format. For example, if a report defines a condition that causes column 2 to display at the position of column 1 when column 1 is empty, Spreadsheet output will always display both columns, even when the condition is met. Oracle Reports generates the HTML table that forms the spreadsheet immediately after the report is compiled, not during formatting. During formatting of spreadsheet output, the table cells are filled with values.
The Oracle Reports color palette is used while designing the report. When you generate your report output to spreadsheet output, Microsoft Excel will show the closest matching color from its color palette.
If you rotate a boilerplate object in the paper layout, the object will appear horizontal in the spreadsheet output.
The following cell properties are not supported in spreadsheet output: background pattern, border color, and border pattern.
Images included in the paper layout of the report will appear in the spreadsheet output only if the Reports Server is running in non-secure mode. In the case of a secure Reports Server, images will not appear in the spreadsheet output. This is because generating images in the output involves multiple calls to the Reports Server (one call per image). Once the user is authenticated, Oracle Reports passes the user's identity between the browser and the secure Reports Server using cookies. However, Excel does not support cookies. As a result, the call to the secure Reports Server seems like a call from a non-authenticated user. Thus, the Reports Server refuses to pass on the images. As a workaround, you can generate the spreadsheet output from a secure Reports Server to a URL using WebDAV.
Summary and formula columns will be shown as values, not as dynamic Excel formulas. Also, the page-level summary columns are reduced to report-level summary columns, since there is no page concept in Excel.
If you set the Vertical Elasticity property of a frame to Fixed, the output in Excel will show only as many records as could appear on the first page of the paper output. Since Excel does not have a page concept, it is not able to "overflow" the remaining rows to the next "page".
Since spreadsheet output is not paginated, only the before report escape and after report escape are supported (see Section 2.2.10, "About before and after escapes").
In an after report escape, adding an HTML command outside the body does not reflect when the report is generated to spreadsheet output, even though the HTML command is written in output properly. For example:
</body>
<table><tr><td><B> the text to be bolded </B></td><tr></table> </html>
Adding the HTML command before the closing body tag reflects correctly when the report is generated to spreadsheet output. For example:
<table><tr><td><B> the text to be bolded </B></td><tr></table>
</body>
</html>
For a matrix report, if the horizontal panels per page is set to a value higher than 1, then running the report in the ENHANCEDSPREADSHEET output format would result in the following error:
REP-1223: THE PAGE SIZE IS INVALID
You can set the horizontal panels per page to a value higher than 1 for a matrix report for all DESFORMAT
s except ENHANCEDSPREADSHEET
.
Spreadsheet format is supported in rwservlet
commands such as SHOWJOBS
. It is also supported in Enterprise Manager's job queue, and in Oracle Portal's access object definition.
You can distribute a report in Enhanced spreadsheet output format, specified in a distribution XML file.
Restrictions
XML-based bursting is supported in ENHANCEDSPREADSHEET
output format only.
Example
To generate the paper layout of your report to Microsoft Excel output through a URL (using rwservlet
), you can type a URL that includes the following in your browser:
http://hostname:port/reports/rwservlet?report=report_name +server=server_name+userid=db_connect_string+destype=file +desformat=Enhancedspreadsheet+desname=C:\temp\myexcel_output.htm
Note: Microsoft Office 2000/2002/2003 supports Hypertext Markup Language (HTML) as a native file format. Enhanced Spreadsheet output relies on producing HTML output, which can be understood by Microsoft Excel as a file with valid Excel format. |
Other capabilities for generating Spreadsheet Output
You can also generate spreadsheet output as implemented in releases prior to Oracle Reports 11g Release 1 (11.1.1), in the following ways:
Generate a report to delimited output, as described in Section 4.7.10, "Generating delimited output":
Advantage: Delimited output can be generated from existing paper layout reports saved in any format (.rdf
, .xml
, .jsp
), either directly from Oracle Reports Builder, or by running the report from the command line.
Disadvantage: Only data (as defined by the report Data Model), no layout information, displays in the output.
Deploy a report with the Reports Server under Oracle WebLogic Server to display it in Microsoft Excel inside your Web browser, using either of the following methods:
When you do not have an existing paper layout: open an Excel HTML template file in Oracle Reports Builder, add the data, save the report as a JSP file, then deploy the report. This method enables you to use JSP coding to include dynamic Excel formulas (not just static values of Reports summary and formula columns), dynamic graphs (not just static Reports graph images).
When you do have an existing paper layout: starting with a paper layout report, edit the Web Source view to add a rw:include
tag to include the group frame of your paper layout in the Web source view as JSP code, save the report as a JSP file, then deploy the report. For the steps to implement this method, see Chapter 29, "Building a Report for Enhanced Spreadsheet Output".
Advantage: You can use rich formatting such as colors and fonts so that you generate report layout, not just data, in Microsoft Excel output inside a Web browser.
Disadvantages: You must add custom JSP coding to get the output in Excel. The report can only be run through the JSP engine and not through rwservlet
, thus this method can only display the Microsoft Excel output inside a Web browser.
See also
Section 4.7.11, "Generating Enhanced spreadsheet output"
For choosing the appropriate delimited output solution for given requirements, see "Displaying Report Output in Microsoft Excel" in the appendix "Troubleshooting Oracle Reports Services" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
Oracle Reports Builder can generate report output to text files, containing the formatted data and all objects. When you generate your report output to text, and the running mode is character (MODE=CHARACTER, or MODE system parameter Initial Value property set to Character), the result is pure text output, which can be read by many different applications. If the running mode is bitmap (MODE=BITMAP, or MODE system parameter Initial Value property set to Bitmap), the result is PostScript output, which can be read and rendered only by PostScript-compatible applications (such as a PostScript printer).
See also
To create a character-mode report, you first create a bit-mapped report, then convert that report to an ASCII (character-mode) report. The process will create a new character-mode version of your bit-mapped report; the original bit-mapped report remains unchanged.
After conversion, many of your fields and text objects may need to be resized. Also, graphical objects such as images and drawings will not be included in your character-mode report. The following lists summarize what is supported in each output format:
Table 2-4 Supported items in bit-mapped and character-mode reports
Bit-mapped | Character-mode |
---|---|
Images |
Boxes |
Colors |
Horizontal lines |
Drawings |
Vertical lines |
Ellipses/Circles |
ASCII text |
Italicized text |
Boldface text |
Diagonal lines |
Underlines |
Bit-mapped patterns | |
Multimedia objects |
See also
Section 4.5.9, "Creating an ASCII (character-mode) report"
Section 4.2.5, "Setting properties for an ASCII (character-mode) report"
Section 4.7.1, "Running and dispatching a report from the user interface"
Oracle Reports 11g Release 1 (11.1.1) supports the following and graph types:
A funnel graph is a visual representation of data related to steps in a process. As the value for a given step of the funnel approaches the quota for that slice, the slice fills. A funnel renders a three-dimensional chart that represents the target and actual values, and levels by color. For more information, see Figure 39-3, "Funnel Graph".
A curved line or fit-to-curve is a graph in which data is represented as a curved line, as a series of data points, or as data points that are connected by a line. Curved line graphs require data for at least two points for each member in a group. For more information, see Figure 39-4, "Curved LIne Graph".
A pie graph represents data as sections of one or more circles, making the circles look like sliced pies. With the enhanced pie, you can include any combination of data value, percent, and text label.
The steps in this section will show you how to adjust the margins of your check printing report and align the fields with an image of a check. You can scan any check and use its image to lay out the objects of your check report. In this section, we use the image we have provided to you, called blankcheck.jpg
. This image is located in the spellcash
example folder.
Before you can insert the check image, you must first rearrange the layout objects.
To rearrange the layout objects:
Click the Paper Layout button in the toolbar to display the Paper Layout view.
The layout currently looks like this:
Figure 31-10 Paper Layout view of your report
Click the Edit Margin button in the toolbar.
In the Paper Layout view, click the margin frame (the heavy black line) to select it.
At the top of the frame, click the center black resizing square and drag it up, so that the margin is 0.25 inches (0.5 centimeters).
Tip: When you click the center square, notice that a dotted guideline displays while your mouse button is depressed. You can use these guidelines to help place objects exactly where you want them in the Paper Layout view. |
When you are done, it should look something like this:
Figure 31-11 Paper Layout view with resized margin
Save your report.
In this section, you will import an image of a blank check. You can use any check you like. For this example, we have provided an image called blankcheck.jpg
in the spellcash example directory.
To import the blank check image:
While the frame is still selected, choose Insert > Image.
In the Import Image dialog box, make sure that File is selected.
In the text box next to File, type or browse for the location of the image, blankcheck.jpg
, then click OK to display the blank check image in the Paper Layout view.
Click the Edit Margin button in the toolbar to return to edit mode. Notice that the image no longer displays.
The steps in this section will show you how to use the various tools in the Paper Layout view to modify the look and feel of your check report. Here, you will learn how to add and modify layout objects and fields according to how you want them to display on the resulting checks. You will create copies of certain fields that you will then use in Section 31.6, "Create a Check Stub With Payment Information and Order Details" to create the check stub.
To set up the check printing fields:
In the Paper Layout view, click the Order No. boilerplate text.
Click the Select Parent Frame button in the toolbar.
The repeating frame called R_G_ORDER_ID should now be selected in the Paper Layout view. You can also look in the Object Navigator, under Paper Layout, to make sure R_G_ORDER_ID is selected.
While R_G_ORDER_ID is selected, click and drag the bottom center resize handle to 8.75 inches (22 centimeters), or the bottom of the page.
Tip: When you click and drag a resize handle in the Paper Layout view, guidelines display along the ruler to help you place your objects. In this case, drag the bottom center resize handle down the page until the horizontal guideline reaches 8.75 inches (22 centimeters). |
Click the Flex Off button in the toolbar to set Flex mode off.
In the Paper Layout view, click F_ORDER_TOTAL, then press CTRL+C on your keyboard.
Click in the area below your layout objects and press CTRL+V.
You should see a new field called F_ORDER_TOTAL1. If this field displays on top of the other layout objects, click and drag it down below the other layout objects so that your Paper Layout view now looks something like this:
Figure 31-12 Partial Paper Layout view with F_ORDER_TOTAL1
Copy the F_ORDER_DATE field and paste it below the other fields, so that your layout now looks like this:
Figure 31-13 Partial Paper Layout view with F_ORDER_DATE1
Note: When you copy and paste a field, Reports Builder maintains the size of the field. So, when you copy and paste F_ORDER_DATE, you may not see the full name of the field. While the field is selected, you can click and drag the right border of the field to the right so that you can see the full name of the field, F_ORDER_DATE1. |
Copy F_CUSTOMER_NAME and paste the field below the other layout objects, so that your layout looks like this:
Figure 31-14 Partial Paper Layout view with F_CUSTOMER_NAME1
Make two copies of F_CHECK_NO by pressing CTRL+C on your keyboard once, then CTRL+V twice.
Note: Since this field is small, it may be difficult to find. You can use the Object Navigator to find and select the field. When you click a field in the Object Navigator, under Paper Layout, the corresponding field is also selected in the Paper Layout view. |
Position these two new fields below the other layout objects, so that your layout looks like this:
Figure 31-15 Partial Paper Layout view with F_CHECK_NO1 and F_CHECK_NO2
Note: We have expanded the size of the two new fields (F_CHECK_NO1 and F_CHECK_NO2) to make them easier to see, but you do not need to resize these fields. |
Click the Field tool in the tool palette.
Draw a field below the other layout objects about 5 inches (12.5 centimeters) long, so that your layout now looks like this:
Figure 31-16 Partial Paper Layout view with F_1 field
While the new field, F_1, is still selected, click the Fill Color tool in the tool palette, and choose No Fill.
Click the Line Color tool in the tool palette, and choose No Line.
Double-click the field F_1 display the Property Inspector.
Set the Name property to F_SPELLED_AMOUNT.
Under Field, set the Source property to SPELLED_AMOUNT (the formula column you created in Section 31.3.2, "Create a formula column in your data model").
Save your report.
The steps in this section will show you how to use the Paper Design view to rearrange the fields you just created. You will use the blank check image we have provided as a guide.
Click the Paper Design button in the toolbar to display your report in the Paper Design view.
Note: When you click the Paper Design button while you are in another view (that is, Paper Layout view or Data Model view), Reports Builder runs your query and your layout. If you receive any error messages when you click the Paper Design button, go back to your original view to verify your changes. You can always compare your report against the sample report we have provided, calledspellcash.rdf . |
You will see your layout objects, as well as the blank check image:
Figure 31-17 Partial Paper Design view of the check report
Note: You will see that the preview does not quite look like a check report. The steps in this section will show you how to move your fields so that your report looks like a proper check. |
In the Paper Design view, position the F_ORDER_TOTAL field in the "Amount" box in the blank check image.
Note: If you closed Reports Builder for any reason and are returning to building this report, make sure you click the Flex Off button in the toolbar so that you can move the fields around in the Paper Design view. |
Click the Align Right button in the toolbar, so that this portion of the check looks like the following:
Position the F_CHECK_NO field in the "Check No." box on the blank check, and center-align the text.
Position the F_ORDER_DATE field in the "Date" box on the blank check, and center-align the text.
Position the F_CHECK_NO2 field in the upper right-hand corner of the check image, and right-align the text.
Position the F_SPELLED_AMOUNT field next to the "Pay Exactly:" text on the blank check image.
Position the F_CUSTOMER_NAME field next to the "Pay To The Order Of:" text on the blank check image.
The check should now look like this:
Figure 31-19 Check image with fields positioned
Save your report.
The steps in this section will show you how to change the font, alignment, and formatting of the data on your check.
To modify the look and feel of the check:
In the Paper Design view, choose Edit > Select All (or press CTRL+A).
Choose Format > Font.
In the Font dialog box, choose Arial font, Regular style, Size 10, then click OK.
In the Paper Design view, click anywhere to deselect all the objects.
Tip: If you have trouble deselecting the objects, go to the Object Navigator and click any item. The objects that were selected in the Object Navigator should display as deselected. |
In the Paper Design view, click F_CHECK_NO2 (the check number in the upper right-hand corner of the check) and change the font to Arial, Bold, 12 point.
Click the F_ORDER_TOTAL field (the number in the Amount box on the check).
Click the Currency button in the toolbar, then click the Commas button.
While F_ORDER_TOTAL is still selected, click the Add Decimal Place button in the toolbar twice, so that the amount now displays like this: $46,257.00
.
Choose Tools > Property Inspector to display the Property Inspector for F_ORDER_TOTAL.
Under Field, next to the Format Mask property, add to the beginning of the existing format mask "****
", then close the Property Inspector.
The amount should now display like this: ****$46,257.00
.
Select the F_CUSTOMER_NAME and F_SPELLED_AMOUNT fields by Shift-clicking them, then change the font to Times New Roman, 12 point.
Your report should now look something like this:
Note: You will notice that the other boilerplate text is still overlaying the image. We will rearrange this text later in the chapter. |
Save your report.
Congratulations! You have successfully created a conditional form letter report. You now know how to:
create a data model and a layout.
add text.
anchor objects together and create format triggers to control whether the text displays.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
The topics in this section build on the basic concepts discussed in Section 1.2, "Reports".
You can add a title to a report in either of the following ways:
Manually, by creating a boilerplate text object in the margin of the Paper Layout view.
Typing in the Title field on the Style page of the Report Wizard.
When you use the Report Wizard to add a title and do not select a template for your report output, the title is inserted into the margin of the report with default attributes defined by Oracle Reports Builder. You can modify the attributes in the Paper Layout view.
When you use the Report Wizard to add a title, and do select a predefined template or a user-defined template file for your report output, Reports Builder looks for a boilerplate text object named B_OR$REPORT_TITLE
defined for the selected template:
If B_OR$REPORT_TITLE
exists, the title is displayed using the attributes of this object, with the text you typed in the Report Wizard.
If B_OR$REPORT_TITLE
is not found, the title is displayed using the attributes defined by the Default properties (under the Title node in the Property Inspector) of the selected template.
Note: If you do not specify a title in the Report Wizard, theB_OR$REPORT_TITLE object is not copied to your report. |
For layouts created using the Report Block Wizard, the title is inserted into the new layout as a group title rather than into the margin of the report. In this case, the attributes are set per the Default properties (under the Title node in the Property Inspector) of the selected template, and B_OR$REPORT_TITLE
is ignored. If you do not select a template, the title uses the default attributes defined by Oracle Reports Builder.
See also
Section 4.5.6, "Adding a title to a report"
Report sectioning enables you to define multiple layouts in the same report, each with a different target audience, output format, page layout, page size, or orientation. You can define up to three report sections, each with a body area and a margin area: the names of the sections are Header, Main, and Trailer. By default, a report is defined in the Main section. In the other sections, you can define different layouts, rather than creating multiple separate reports. If you wish, you can use the margin and body of the Header and Trailer sections to create a Header and Trailer page for your reports.
In the Object Navigator, the report sections are exposed in the Object Navigator under the Paper Layout node as Header Section, Main Section, and Trailer Section.
You can specify the order in which the three sections of a report (Header, Main, and Trailer) are formatted using SRW.SET_FORMAT_ORDER
or the Format Order of Sections property. This capability allows Oracle Reports to format any section first to create information that is only known at the time of formatting, such as page numbers, then use that information in the formatting of another section. As an example, this property can be used to create a table of contents. For an example of using SRW.SET_FORMAT_ORDER
for this purpose, see Chapter 35, "Building a Paper Report with a Simple Table of Contents and Index" and Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".
For an example of using section-level distribution, see Chapter 37, "Bursting and Distributing a Report". This chapter covers defining distribution of a single section to multiple destinations, using the Repeat On property and a sample distribution XML file. For information about advanced section-level distribution and creating your own distribution XML file, see the chapter "Creating Advanced Distributions" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services .
Examples
Example 1
You can use sectioning and distribution to publish your report output in HTML, and also send a PostScript version to the printer.
Example 2
You can send an executive summary of the report to senior management, and also e-mail detailed breakdowns to individual managers. In this example, a single report with two report sections needs to be created: a portrait-sized summary section and a landscape-sized detail section. Use the Repeat On property to associate the detail section with a data model group that lists the managers and then alter the destination on each instance of the data model group to send the output to the appropriate managers.
See also
Section 2.8.3, "About report distribution"
A report can be defined using inches, centimeters, or points. The unit of measurement is independent of the device on which you build the report. As a result, you can design reports that will run on any platform on which Reports Builder runs. You can change a report's unit of measurement in these ways:
Setting the Unit of Measurement property.
Converting the report using rwconverter, specifying a different unit of measurement with the DUNIT keyword.
Opening the report in a different environment. For example, if you open a character-mode report, Reports Builder will change the report's unit of measurement to the bit-mapped environment's default. If you then save the report, it will be saved with the new unit of measurement.
A report page can have any length and any width. Because printer pages may be smaller or larger than your paper report's "page," the concept of physical and logical pages is used. A physical page is the size of a page that is output by your printer. A logical page is the size of one page of your report; one logical page may be made up of multiple physical pages.
For each section (header, main, trailer) of a report:
you specify the dimensions of the physical page (including the margin) using the Width property and Height property.
you specify the dimensions of the logical page (report page) in physical pages (printer pages) using the Horizontal Panels per Page property (width) and the Vertical Panels per Page property (height). For example, a Horizontal Panels per Page size of 1 means that each logical page is one physical page wide, and a Vertical Panels per Page size of 2 means that each logical page is two physical pages in height.
In this example, one logical page is made up of six physical pages. The logical page is three physical pages wide and two physical pages high. Consequently, Horizontal Panels per Page size is 3 and Vertical Panels per Page size is 2. If you wanted the logical page to be two physical pages wide and three physical pages high, you would specify a Horizontal Panels per Page size of 2 and a Vertical Panels per Page size of 3.
For detailed information about using and adding fonts in Oracle Reports, including font configuration files, font aliasing, troubleshooting font issues, and font types, refer to the chapter "Managing Fonts in Oracle Reports" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual.
Using the Conditional Formatting and Format Exception dialog boxes, you can specify output formatting attributes (font and color) for a selected layout object based on conditions that exist. The conditions that you define are called format exceptions.
You can display the Conditional Formatting dialog box from the Paper Layout view or Paper Design view in any of the following ways:
Double-click the object to display the Property Inspector. Under the General Layout node, click the Conditional Formatting value field (labeled...).
Display the pop-up menu (right-click in Windows) for the object.
Click the object, then choose Format > Conditional Formatting.
The Format Exception dialog box displays when you click New or Edit in the Conditional Formatting dialog box, and enables you to quickly and easily specify output formatting attributes for a selected layout object based on defined conditions. After you specify conditions and formatting for the current layout object in the Format Exception dialog box, the entire definition is exported to a PL/SQL format trigger. If a format trigger already exists for the layout object, the definition in the Format Exception dialog box overwrites the existing trigger code when you confirm the Oracle Reports Builder prompt.
You can edit the format trigger manually through the PL/SQL Editor; however, if you subsequently modify the definition using the Format Exception dialog box, Oracle Reports Builder displays a prompt to overwrite the existing format trigger.
See also
Section 4.9.1.5, "Applying conditional formatting to a layout object"
A nested matrix report is a matrix report in which at least one parent/child relationship appears within the matrix grid.
A nested matrix report has more than two dimensions; therefore, it has multiple dimensions going across or down the page. For example, see . Notice that for each year there is a nested list of related departments. Also notice that the list of jobs (the across values) appears only once. Because the job values appear only once, a summary of each category of jobs can be made to line up with the values it summarizes.
Note: In a nested matrix report, where you create a matrix query that has a child matrix query, the fields on which you join should be separated into a separate group in your master matrix. Otherwise, on some platforms, only the first group prints. |
For a detailed example, see Chapter 26, "Building a Nested Matrix Report".
See also
A matrix with group report is a group above report with a separate matrix for each value of the master group. For example, for each year (master) in the report below there is a unique matrix that contains only that year's departments and jobs. This means that a summary of each job category may not line up with the values it summarizes because the position of each job category in the matrix may vary for each year.
A multiquery matrix with group report is similar to a nested matrix report in that it has more than two dimensions. For example, in the following report, notice that for each year there is a nested list of related departments.
Figure 2-2 Sample matrix with group and nested matrix report
The advantage of using multiple queries is that you get a real break, or master/detail relationship, for the nesting groups (for example, notice that in the multiquery example above, Year 80 shows only record 20; with a single query, Year 80 would show all records whether or not they contain data for Year 80). If you want to suppress detail records that do not contain data for a particular master record, you must use multiple queries.
For a complete example, see the example report in Chapter 27, "Building a Matrix with Group Above Report".
See also
This part of the manual explains how you can build various kinds of reports to suit your business requirements.
In this example, you will build a report that calculates and displays the four-month average of purchases for each customer. You will use the Report Wizard to create a simple time series calculations report for both paper and the Web. For the JSP-based Web report, you will modify the Web source to change labels and add format masks.
Figure 3-26 Final output of the time series calculations report example
For more information on building this example, refer to Chapter 28, "Building a Time Series Calculations Report".
In this example, you will deploy a report with the Reports Server under Oracle WebLogic Server to display it in Microsoft Excel inside your Web browser, when you have an existing paper layout. You will generate a report to Microsoft Excel output after integrating the paper layout into a Web layout by modifying the Web source of your report.
Figure 3-27 Final output of the Microsoft Excel report
For more information on building this example, refer to Chapter 29, "Building a Report for Enhanced Spreadsheet Output".
In this example, you will build a report that collects and displays names of all employees whose salaries fall within the range of 0 to 999, then collects and displays all employees whose salaries fall within the range of 1000 to 1999, and so on. You will be able to modify this report to display any aggregate range you need.
Figure 3-28 Final output of the aggregate data report example
For more information on building this example, refer to Chapter 30, "Building a Report with Aggregate Data".
In this example, you will build a check printing report with a stub and spelled-out cash amounts. The steps described in this example will help you create a PL/SQL function that returns spelled-out numerical values. You will also learn how to import an image of a preprinted form (in this case, a blank check image) and use the tools in the Paper Layout and Paper Design views to print your report on such a form. Although we use a check as the example in this report, you can use the steps to use any preprinted form with Oracle Reports.
Figure 3-29 Final output of the check printing report with spelled-out cash amounts
For more information on building this example, refer to Chapter 31, "Building a Check Printing Report with Spelled-Out Cash Amounts".
In this chapter, you will learn formatting techniques for printing reports on preprinted forms when you do not have access to a computer readable version of the forms. Such reports must be designed so that the data prints in exact positions on the form.
Figure 3-30 Final output of the preprinted forms example
For more information on building this example, refer to Chapter 32, "Building a Report Using a Preprinted Form".
In this example, you will build a report that displays several distinguishing characteristics of a typical invoice, such as customer name and address, sales order number, billing information, and billing totals.
Figure 3-31 Final output of the invoice report example
For more information on building this example, refer to Chapter 33, "Building an Invoice Report".
In this example, you will build a report that ranks data in two different ways: by count and by percentage. The upper portion displays the names and the total purchases of the top three customers; the lower portion displays the names and total purchases of those customers who constitute 75% of all sales. You can set the ranking criteria at runtime, or let them default to previously specified values.
Figure 3-32 Final output of the ranking report example
For more information on building this example, refer to Chapter 34, "Building a Ranking Report".
This example is designed to teach you how to add navigational items to a large paper report. You will learn how to create a group above report, then add a simple table of contents to the beginning of your report, which will enable users to find an item by its category. You will also learn how to create an index so users can directly find a specific piece of information.
Note: Generating a table of contents or index is not supported for matrix reports. |
For more details on building this example, refer to Chapter 35, "Building a Paper Report with a Simple Table of Contents and Index".
This example is designed to teach you how to add a multilevel table of contents to a large paper report. You will add a table of contents based on a category and sub-category to an existing paper report.
Note: Generating a table of contents is not supported for matrix reports. |
For more details on building this example, refer to Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".
In this example, you will modify a simple report we have provided to burst each section to a separate report. You will then modify a sample distribution XML file to send an e-mail to each destination with an attachment based on the separate reports. You will also send multiple e-mails to the same e-mail address with a single attachment (the entire report).
For more information on building this example, refer to Chapter 37, "Bursting and Distributing a Report".
Note: No sample output is available for this report. |
In this example, you will learn how to create a report in PDF format that contains multibyte characters for the Web and paper output. You will build a multibyte character set report using UTF8. You can produce reports that display text in multibyte character sets, such as Simplified Chinese, Traditional Chinese, Japanese, Korean, and Unicode.
Figure 3-36 Multibyte character set UTF8 report in Japanese
For more details on building this example, refer to Chapter 38, "Building a Report with Multibyte Characters for PDF Output".
Now that your queries are complete and linked, the steps in this section will help you to create columns to summarize the data.
To add summary columns:
In the Data Model view, click the Summary Column tool in the tool palette.
Click inside the G_EMPLOYEE_ID group to create a summary column.
Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to CS_classcount.
Under Summary, set the Function property to Count, set the Source property to employee_id, and set the Reset At property to G_department_id.
You have now created a summary that counts up the number of employees. You will not use the summary in this report's layout, but you will use it as the source for other, more interesting summaries later.
Repeat the steps above to create summaries with the following characteristics:
Table 41-2 Summary Characteristics
Create in Group | Name | Function | Source | Reset At |
---|---|---|---|---|
G_conlabel |
CS_conlabel_classcount |
Sum |
CS_classcount |
G_conlabel |
G_department_id |
CS_port_count |
Sum |
CS_conlabel_classcount |
G_DEPARTMENT_ID |
You may not understand these summaries now. Their purpose will become clearer when you create the report layout and preview the live data.
Your data model should look similar to the following:
Figure 41-7 Data model with summary columns
Note: You can also compare your results to the file we have provided, calledref_emp65.rdf . |
Save the report as ref_65_
your_initials
.rdf
.
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
In this chapter, you will learn about across group reports. By following the steps in this chapter, you can generate the report output shown in Figure 12-1.
Concepts
Reports Builder enables you to modify the look of your report in multiple ways. In this example, you will build an across group report that prints the values of a database column across the page instead of down.
In across reports with breaks, the master (or break) group prints "top to bottom"; that is, as it would in other master/detail reports. However, the values in the detail group print across the page, from left to right. When there are more values than will fit on a line, Oracle Reports wraps the line and prints the remaining values across the page on the next line. Note, however, in the case of ENHANCEDSPREADSHEET
output, the across group keeps repeating (until it reaches the 75,000 rows limit). This is natural Excel behavior because Excel does not have a fixed "page width", and Oracle Reports adapts ENHANCEDSPREADSHEET
output to this Excel behavior.
For more information on break reports, refer to the Oracle Reports online Help.
Data Relationships
The break in this report is created through a data link between a master group and a detail query.
Layout
To create the layout used in this report, you will select the master/detail style, then modify the Print Direction setting for one of the groups to ensure it prints across the page instead of down the page.
Example Scenario
As you build this example report, you will:
Create Two Queries that select all of the columns displayed, then link them to establish a master/detail relationship
Create the Default Layout using the Report Block Wizard.
To see a sample across report with control breaks, open the examples folder called acrossbreak
, then open the Oracle Reports example report named acrossbreak.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
Once your data model is complete, you need to create a layout for the data objects to display in the report output. The Report Wizard enables you to create layouts for your data model.
Tip: When you have multiple queries in your data model, make sure that you check the names of the groups associated with each query prior to entering the Report Wizard. The Report Wizard requires you to choose data for the layout by group name. |
To create the layout:
In the Data Model view, right-click on the canvas, then choose Report Wizard.
In the Report Wizard, on the Report Type page, select Create Paper Layout only.
On the Style page, select Group Above.
On the Groups page, ensure that both groups from your data model appear in the Displayed Groups list.
On the Fields page:
Click the double right arrows (>>) to move all of the fields to the Displayed Fields list.
Click REPID in the Displayed Fields list and click the left arrow (<) to move it back to the Available Fields list. Since REPID and EMPNO represent the same value, you only need to display one of them. EMPNO is part of the master group, which is the level where we want to see its values in the report. REPID is part of the detail group. Hence, you remove REPID from the Displayed Fields list to prevent it from appearing in the output.
Figure 11-4 Fields page of the Report Wizard
On the Labels page, change the labels and field widths as follows:
On the Template page, make sure Beige is selected under Predefined Template, then click Finish to display your report output in the Paper Design view. It should look like this:
Figure 11-5 Paper Design view for the two-query group report
Congratulations! You have successfully created a summary report. You now know how to:
create a data model with summaries and lay out the data with the Report Wizard.
format fields in the Paper Design view.
examine the summaries and their properties.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
To build the example in this chapter, you must have access to the Summit Sporting Goods (SUMMIT) schema. To download the SQL scripts that install the schema, see "Accessing the Data Sources" in the Preface.
The topics in this section discuss the use of templates in Oracle Reports Builder.
Templates define common characteristics and objects that you want to apply to multiple paper-based reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report.
When you use the Report Wizard to create a paper-based report, you use the Templates page of the wizard to apply a template (.tdf file) to the report. The Templates page lists the default templates, as well as any templates that you have created.
Note: If the list of templates does not appear, make sure that the REPORTS_PATH environment variable includes the location of the templates (for example,ORACLE_HOME \reports\templates ). |
When you choose a template, objects in the margin area of a template are imported into the same locations in the current report section, overwriting any existing objects. The characteristics (formatting, fonts, colors, and so on) of objects in the body area of the template are applied to objects in the body area of the current report section. Any template properties, parameters, report triggers, program units, and attached libraries you have defined are also applied. You can apply different templates to each section of the report. However, if you are applying one of the default templates, you cannot combine two report blocks that use different default templates in a single report. All of your report blocks in any one report must use the same default template.
If you later apply another template to a report, the existing template objects will be deleted in the current report section.
See also
In the Layout Body area of a template, you can define Default and Override attributes under the following Object Navigator nodes:
Frames, which contains the following:
a Section Frame node, which defines attributes for the parent frame surrounding the currently selected section.
a Headings Frame node, which defines attributes for the parent frame surrounding the column headings.
a Fields Frame node, which defines attributes for the parent frame surrounding the fields.
a Summaries Frame node, which defines attributes for the parent frame surrounding the summaries (totals).
Field Labels/Headings, which contains the following:
a Character node, which defines attributes for the labels or column headings of character fields.
a Number node, which defines attributes for the labels or column headings of number fields.
a Date node, which defines attributes for the labels or column headings of date fields.
Fields, which contains the following:
a Character node, which defines attributes for character fields.
a Number node, which defines attributes for number fields.
a Date node, which defines attributes for date fields.
Summary Labels, which contains the following:
a Character node, which defines attributes for the labels of summaries on character fields.
a Number node, which defines attributes for the labels of summaries on numeric fields.
a Date node, which defines attributes for the labels of summaries on date fields.
Summaries, which contains the following:
a Character node, which defines attributes for summaries on character fields.
a Number node, which defines attributes for summaries on number fields.
a Date node, which defines attributes for summaries on date fields.
Default attributes
The Default node in the Object Navigator defines the default visual attributes (formatting, fonts, colors, and so on) for all report styles. If you want to define attributes for individual report styles, you do so under the Override node. When you apply a template to a report, all Default attributes are applied to the report, except for attributes that are localized under the Override node.
Override attributes
Under the Override node in the Object Navigator, you can define attributes for individual report styles. Each report style contains one or more sections that map to groups in the report:
Single-section report styles: Tabular, Form, Mailing Label, Form Letter
Multiple section report styles: Group Left, Group Above, Matrix, Matrix with Group
For the report styles that support multiple groups, you can create additional sections as needed. Sections are mapped to groups as follows:
Same number of groups as sections: one-to-one mapping (the first section is mapped to the first group, the second section to the second group, and so on).
More groups than sections: one-to-one mapping until the next-to-last section. Then, all subsequent groups are mapped to the next-to-last section, and the last group is mapped to the last section. If only one section exists, all groups are mapped to it.
More sections than groups: one-to-one mapping until the next-to-last group. Then, the last group is mapped to the last section.
See also
When you apply a template to a report, all of the following objects, properties, and attributes from the template are applied to the current report section:
parameters and their validation triggers
physical page size
logical page size
character/bitmap mode
margin position
panel print order
In addition, all of the layout objects in the margin of the template are copied into the same location in the current report section.
Usage notes
The template will be applied to the current section of the report (the section displayed in the Paper Layout view) by default. If no section is displayed, the template will be applied to the Main Section of the report by default. If you select the node for the entire report in the Object Navigator, the template will be applied to the Main Section of the report by default. To apply the template to a specific section of your report, select the node for that section in the Object Navigator.
You can apply different templates to each section of the report. However, if you are applying one of the default templates, you cannot combine two report blocks that use different default templates in a single report. All of your report blocks in any one report must use the same default template.
See also
In templates, Sections, Frames, Fields, Labels, Headings, and Summaries properties all may inherit their values.
Default properties
The Default properties inherit the values preset by Oracle Reports Builder. When a property is set to its default Oracle Reports Builder value, the icon next to it in the Property Inspector is a small circle. Default properties become localized when you change their values, or select them and click the Localize Property button in the toolbar. When a property is localized, the icon next to it changes to a square. To return the properties to their inherited values, select the properties and click the Inherit Property button in the toolbar.
Override properties
The properties of Override Sections inherit their values from the Default properties. When a property inherits from a Default property, the icon next to it in the Property Inspector is an arrow. Properties in the Override Sections become localized when you change their values, or select them and click the Localize Property button in the toolbar. When a property is localized, the icon next to it changes to an arrow with a red cross through it. To return the values of properties in the Override Sections to their inherited values, select the properties and click the Inherit Property button in the toolbar.
The Template Editor is a work area in which you can define objects and formatting properties for your templates. It is similar to the Paper Layout view of the Report Editor. You can create, delete, and modify objects (for example, page numbers, text, and graphics) in the margin area. You cannot create and delete objects in the body area, but you can modify the properties of body objects in the Property Inspector (Tools > Property Inspector).
The Report Style list enables you to view the layout for a selected report style. To define default settings for all report styles, you can choose Default from the Report Style list. To make changes for an individual report style, you can select that report style from the Report Style list to specify settings that override the default.
Access
You can access the Template Editor in the following ways:
When creating a new template:
Choose File > New > Template.
In the Object Navigator, click the Templates node, then click the Create button in the toolbar.
When displaying an existing template:
From the Window menu, choose a window displaying Template Editor Paper Layout view.
In the Object Navigator, double-click the view icon next to the Paper Layout node for a template.
To make your matrix report more readable, you should format the monetary values.
To format monetary values:
On the first page of the report, click one of the cell values. Notice that all of the values are immediately selected, indicating that you can change their properties simultaneously.
Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.
Click the Add Decimal Place button in the toolbar twice. Two decimal places are added to the right of the decimal point.
Resize the fields. Click and drag the rightmost handle of the cell value under the SALESMAN label about 0.5 inches (1.5 centimeters) to the left. After you complete this operation, the department summaries from the second page should move onto the first page.
Shift-click the SALESMAN label.
Click the Align Right button in the toolbar.
Click in an open area of the Paper Design view to deselect all of the objects.
Figure 26-17 Nested matrix report output with formatted monetary values
Save your report as nested3_
your_initials
.rdf
In this chapter, you will learn about matrix reports. By following the steps in this chapter, you can generate the report output shown in Figure 25-1.
Concepts
A matrix report looks like a grid. As shown by the example report above, it contains one row of labels, one column of labels, and information in a grid format that is related to both the row and column labels.
This sample matrix also contains three additions to the basic matrix: summaries have been added, zeroes replace non-existent values in the cells, and the cells themselves are surrounded by grid lines. Of the summaries, one sums the salaries by department, one sums them by job, and one sums them for the whole report.
Certain requirements exist for building matrix reports:
You must have at least four groups in your data model.
At least two of the groups must be within the cross product group. These groups furnish the "labels" of the matrix report.
At least one group must be a "cell" group; that is, it must provide the information related to the labels. The values from this group fill the cells created by the matrix.
These requirements can be seen in the preceding example. It contains four groups--one group supplies the vertical labels (department numbers) and one group supplies the horizontal labels (job identifiers). These two groups are the children of the third group, called the cross product group, which creates the grid. The fourth group provides the values that fill in the grid.
Matrix reports are different from tabular reports because the number of columns is not known in advance; that is, the number of columns in your report is not determined by the number of columns you specify in your SELECT
statement plus the columns you create yourself. The number of columns in your report depends on the number of values contained in the columns providing the horizontal and vertical labels. Thus, the report would automatically be extended if a new job function, called RECEPTIONIST, was added to the underlying data tables.
The queries used to select data for these sample matrix reports are not intended as definitive examples of matrix queries. If you are concerned with performance issues, for example, there are alternate methods of querying data that can improve the performance of a matrix report.
You can create matrix reports with any number of queries. Section 25.2, "Create a Single-Query Matrix" explains how to create the matrix report using one query. Section 25.3, "Create a Multiple-Query Matrix" explains how to create the same report using three queries. These two methods yield the same results. They are presented as options; feel free to try both methods and settle on a favorite.
This report uses the matrix layout style. You'll modify some default settings to ensure that the column and row labels display correctly. You'll also modify some field widths to ensure that the fields fit across the page.
For additional conceptual information, see Section 1.3.7, "About matrix reports",
Example Scenario
In this example, you will create report that cross tabulates salaries by job function and department. The result would be a matrix with job functions listed across the top, departments down the side, and sums of salaries in the cells. Thus, you could quickly determine the sum of all of the salaries for clerks in department 20 and compare that value to the one for all clerks in some other department.
As you build this example report, you will:
Add Summaries to the Single-Query Matrix for rows and columns.
Add a Grid to add grid lines around cells.
To see a sample matrix report, open the examples folder named matrix
, then open the Oracle Reports example named matrix1qb.rdf
for a single-query matrix report, or matrix3qb.rdf
for a multi-query matrix report. For details on how to access these reports, see "Accessing the Example Reports" in the Preface.
In this chapter, you will learn about the new graph types in Oracle Reports 11g Release 1 (11.1.1) and new features supported by Oracle Reports Builder.
In the Paper Design view, notice the Salary field. The values are neither aligned nor displayed as monetary amounts. You can quickly rectify this in the Paper Design view.
To assign a format mask to monetary values:
In the Paper Design view, select the first number value underneath the Salary label in the second layout. Notice that all of the values are immediately selected, indicating that you can change their properties simultaneously.
Tip: If you are familiar with format mask syntax, you could now right-click the field values, choose Property Inspector, and choose or manually type a value for the Format Mask property. |
Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.
Click the Add Decimal Place button twice. Two decimal places are added to the right of the decimal point.
Click the Align Right button. All of the values are immediately right aligned.
Save your report.
Figure 8-6 Paper Design view with monetary values formatted
The steps in this section will show you how to preview your report in the Paper Design view, and make a few last-minute tweaks using the tools in the Paper Design view. It is sometimes easier to use the Paper Design view to finish your report, since you can see actual data displayed.
To format your report in the Paper Design view:
Click the Paper Design button in the toolbar to display the Paper Design view.
In the Paper Design view, click the field F_PRODUCT_DESCRIPTION.
Tip: If you cannot find this field, you can use the Object Navigator to find the field name and click it. When you select an item in the Object Navigator, the corresponding object is selected in the Paper Design view. |
Increase the size of this field as much as possible, given the size of the area on your form.
Shift-click to select the four numbered fields, then click the Align Right button in the toolbar.
At the bottom of the page, Shift-click the two fields (F_PAGE_TOTAL and F_ORDER_TOTAL), then click the Line Color tool in the tool palette and choose No Line.
Shift-click the three currency number fields on the right.
Click the Currency button in the toolbar to add a dollar sign to the amounts.
Click the Commas button to add a comma to values over 999.
Click the Add Decimal Place button twice to add two decimal places to the values.
In the Object Navigator, Shift-click the following three objects:
B_TBP
F_ORDER_TOTAL
F_PAGE_TOTAL
Choose Tools > Property Inspector to display the Property Inspector, and set the following properties:
Under Advanced Layout, set the Print Object On property to All Pages, and set the Base Printing On property to Enclosing Object.
In the Paper Design view, adjust the sizes of the fields according to the measurements on your preprinted form.
Your report should now look like this:
Figure 32-12 Final preview of your preprinted form report
Note: To see a more complete view of the report, you can open the sample report we have provided, calledpreprint.rdf . To view the report, open the report and click the Run Paper Layout button in the toolbar. |
When you print your report on to the form, it might look something like this:
Figure 32-13 Final report printed on a sample preprinted form
Note: The Paper Design view of your report will not look like the image above. We printed our report on a sample preprinted form. The above image is a snapshot of that form. |
Save your report.
Using Oracle Reports and the standards defined in the Web Accessibility Guidelines, you can create a report that produces output that is compatible with assistive technologies such as screen readers. Oracle Reports provides properties and tags to support this effort.
On the Oracle accessibility site (http://www.oracle.com/accessibility
), you can learn more about accessibility and find the Creating Accessible Enterprise Reports using Oracle Reports white paper.
To learn more about creating accessible Web reports, refer to the example on the Oracle Technology Network (OTN):
On the Oracle Reports 10g page (http://www.oracle.com/technology/products/reports/index.html
), click Getting Started to display the Getting Started with Oracle Reports home page.
In the list of topic sections on the left, click Index.
In the Topic list, choose Building and Developing Reports.
In the Collateral Type list, choose Examples.
Click Search.
In the list of topics that displays, click Building an Accessible JSP-based Web Report.
The chapters in this Part provide steps to build simple reports.
This part contains the following chapters:
A tabular report is the most basic type of report. The output of this report is organized in a multicolumn, multirow format (with rows and columns corresponding to those in the database table).
A mailing label report is displayed in a format suitable for use as address labels on envelopes. You write custom text and embed database values, or text from files, to create the labels. You can print the labels in one or many columns and at any position.
A form letter report is displayed in a format suitable for use in printed forms or letters. Similar to the mailing label report, you can write custom text and embed to it, database values or text from files.
A master-master report (also called a parent-parent report) consists of at least two sets of data that are not directly related. This report contains two or more queries with no links (that is, no parent-child relationships).
A summary report contains a field that summarizes at least one column of the report. This summary can be: total sales, average of a list of commissions, maximum amount found in a series of purchase orders, and so on.
The topics in this section discuss the Property Inspector in Oracle Reports Builder.
The Property Inspector is a window that enables you to access the properties of the currently selected object(s) in the Object Navigator, Report Editor, and Template Editor.
Every Oracle Reports Builder object (query, group, frame, parameter, and so on.) has associated properties that can be viewed using the Property Inspector. To get help on any property, click the property in the Property Inspector and press F1.
You can select multiple objects at the same time by using Shift+Click or Ctrl+Click in the navigators or the editors. When two or more objects are selected, a list of object names is displayed at the top of the Property Inspector.
The Intersection/Union button on the Property Inspector toolbar determines which properties are displayed in the property list when more than one object is selected. Toggling between Intersection and Union changes the list of properties displayed in the Property Inspector, but does not affect the setting of any property.
Table 1-2 Property Inspector Intersection/Union
Button | Description |
---|---|
Intersection |
The default. Only properties common to all selected objects are displayed. |
Union |
All properties of every object selected are displayed. |
Properties that are common to two or more objects in a multiple selection are listed only once, and the property setting is displayed as follows:
If the property has the same setting for all of the selected objects that have it in common, the common setting is displayed.
If the property is set differently for the objects that have it in common, the string ***** is displayed.
See also
Section 4.2.2, "Setting report properties"
Section 4.2.5, "Setting properties for an ASCII (character-mode) report"
Section 4.2.7, "Setting properties of multiple objects"
Section 4.2.8, "Comparing the properties of one object to another"
In this chapter, you will learn about reports that generate different versions of a form letter based on changing conditions. By following the steps in this chapter, you can generate the report output shown in Figure 23-1 and Figure 23-2.
Figure 23-1 Conditional form letter report output, base version
Figure 23-2 Conditional form letter report output, alternate version
Concepts
The two form letters above were generated from the same report. As you can see, the two letters share a number of features. Hence, it is more convenient to create a base form letter and then apply conditions to certain parts to determine whether they should be displayed for the current record, in this case, employees.
Conditional printing is useful when you want to display a section of a report only if certain conditions are met at run time. Conversely, you may wish to use conditional printing to suppress certain information for those who don't need it.
Data Relationships
This report uses one query to select all data.
Layout
This report uses the Form Letter layout style. You'll also create the various pieces of boilerplate that will comprise the letter. To govern the printing of these boilerplate objects, you'll use vertically collapsing anchors and PL/SQL format triggers to conditionally produce different form letters for employees who meet the specified criteria.
Example Scenario
In this example, you will create a form letter to invite all of your employees to the company picnic. For your top sales representatives, though, you also want to include a special invitation to a dinner party in the form letter. For all other employees, you want to include a thank you without the dinner invitation.
As you build this example report, you will:
Add Logic for Text, which involves anchoring objects together and creating format triggers to control whether the text displays.
To see a sample conditional form letter report, open the examples folder named condform
, then open the Oracle Reports example named condform.rdf
. For details on how to access it, see "Accessing the Example Reports" in the Preface.
The topics in this section build on the basic concepts discussed in Section 1.2.2, "About Web Reports".
See also
Section 1.2.2, "About Web Reports"
JavaServer Pages (JSPs) technology is an extension to the Java servlet technology from Sun Microsystems that provides a simple programming vehicle for displaying dynamic content on a Web page. A JSP is an HTML page with embedded Java source code that is executed in the Web server or application server. The HTML provides the page layout that is returned to the Web browser, and Java provides the business logic.
JSPs keep static page presentation and dynamic content generation separate. Because JSPs cleanly separate dynamic application logic from static HTML content, Web page designers who have limited or no Java programming expertise can modify the appearance of the JSP page without affecting the generation of its content, simply using HTML or XML tags to design and format the dynamically-generated Web page. JSP-specific tags or Java-based scriptlets can be utilized to call other components that generate the dynamic content on the page.
JSPs have the .jsp
extension. This extension notifies the Web server that the page should be processed by a JSP container. The JSP container interprets the JSP tags and scriptlets, compiles the JSP into a Java servlet and executes it, which generates the content required, and sends the results back to the browser as an HTML or XML page.
A JSP can be accessed and run from a browser-based client, typically over the Internet or a corporate intranet. Unlike traditional client-server applications, JSP applications:
run on a wider variety of client machines and browsers.
run on thinner clients, thereby consuming fewer client-machine resources.
scale to a larger number of simultaneous users.
require less effort to install and maintain.
When a JSP is called for the first time, it is compiled into a Java servlet class and stored in the Web server's memory. Because it is stored in memory, subsequent calls to that page are very fast, thereby avoiding the performance limitations seen with traditional Common Gateway Interface (CGI) programs, which spawn a new process for each HTTP request.
For additional background information about JSP technology, see the Sun Microsystems Java and Java EE Web site at http://java.sun.com
.
Oracle Reports supports JavaServer Pages (JSPs) as the underlying technology to enable you to enhance Web pages with information retrieved using Oracle Reports Builder.
In Oracle Reports, you use JSPs to embed data retrieved using the data model into an existing Web page to create a JSP-based Web report. You can create new JSP reports, or save existing reports as JSP reports. New reports are by default saved as JSP reports. The benefit of saving reports as JSPs is that JSPs are text files that are easy to edit as opposed to, for example, the binary .rdf
format. When a report is saved as a JSP file, the data model is embedded using XML tags. The entire report can now be defined using XML tags and saved as an XML file.
Using the Oracle Reports custom JSP tags, you can easily add report blocks and graphs to existing JSP files. These tags can be used as templates to enable you to build and insert your own data-driven Java component into a JSP-based Web report. Not only can you edit the HTML or XML code that encapsulates the report block, but you can also edit the report block in the JSP itself, by modifying, adding or deleting their bodies and attributes.
The Report Editor's Web Source view displays the source code for your Web report, including HTML, XML, and JSP tags.
By default, a new JSP created in Oracle Reports Builder contains the following:
<%@ page contentType="text/html;charset=ISO-8859-1" %>
If you are creating your JSP outside Oracle Reports Builder, you should ensure that it contains similar encoding information.
In prior releases, Oracle Reports introduced Web links that you can add to paper-based reports, which become active when you display your paper report in a Web browser or PDF viewer. For JSP reports, hyperlinks have to be created manually, and if the hyperlinks need to substitute data values, the data values must be provided through the rw:field
JSP tag. For example:
<a href="http://hostname:port/path/rwservlet?report=department.jsp&p_deptno=<rw:field
id="F_Deptno" src="Deptno"/>"> <rw:field id="F_Deptno" src="Deptno">10</rw:field> </a>
If your JSP-based Web report's character encoding (for example, EUC-JP) differs from the character set portion of the NLS_LANG environment variable (for example, JA16SJIS), you will get the following errors:
When running the JSP file:
REP-6106
or REP-6104
with javax.servlet.jsp.JspException (multibyte)
REP-0495 Unable to tokenize the query (singlebyte)
When opening the JSP file using Oracle Reports Builder:
REP-0069 Internal Error or REP-6106
To work around this issue, you must ensure that your JSP-based Web report's character encoding matches the IANA encoding corresponding to Oracle Reports Builder's character set portion of the NLS_LANG
environment variable.
For example:
JSP-based Web report encoding:
<%@ page contentType="text/html;charset=EUC-JP" %>
<META http-equiv="Content-Type" content="text/html;charset=EUC-JP">
This JSP file needs to be encoded in the character set (EUC-JP
).
Oracle Reports Builder encoding:
NLS_LANG=JAPANESE_JAPAN.JA16EUC
In this example, the JSP-based Web report's encoding (EUC-JP
) matches Oracle Reports Builder's character set portion of NLS_LANG
; that is, JA16EUC
.
In Oracle Reports, Web report templates are configured for Western European character encoding by default. However, for other languages, you can specify the character encoding for every JSP file by using both the charset
attribute of the <Meta>
tag and the <%@page%>
page directive.
To dynamically associate the appropriate character encoding with the JSP file, you can make the following modifications:
Edit the rw*.html
files and the blank_template.jsp
file, as follows:
Modify the page directive to read:
<%@ page contentType="text/html;charset=
yourIANAencoding
" %>
where:
yourIANAencoding
is the IANA encoding name that corresponds to the NLS_CHARACTERSET
portion of the NLS_LANG
variable.
Modify the <Meta>
tag inside the <Head>
tag to read:
<meta http-equiv="Content-Type" content="text/html;charset=
yourIANAencoding
" />
Edit the template.xsl
(ORACLE_HOME
/reports/templates/
) file, as follows:
Modify the <xsl:output>
tag to read:
<xsl:output
method="jsp"
indent="yes"
encoding="yourIANAencoding
"
/>
where:
yourIANAencoding
is the IANA encoding name that corresponds to the NLS_CHARACTERSET
portion of the NLS_LANG
variable.
Add the page directive to the file:
<%@ page contentType="text/html;charset=yourIANAencoding" %>
Add or modify the <META>
tag inside the tag:
<meta http-equiv="Content-Type" content="text/html;charset=yourIANAencoding" />
where:
yourIANAencoding
is the IANA encoding name that corresponds to the NLS_CHARACTERSET
portion of the NLS_LANG
variable.
See also
Topics "Oracle Reports JSP tags" and "Oracle Reports XML tags" in the Reference section of the Oracle Reports online Help
You can preview a JSP-based Web report by clicking the Run Web Layout button in the toolbar, or by choosing Program > Run Web Layout, to run the Web Source. Reports Builder displays Web reports in your default browser.
Note: If Netscape 7.0 is your default browser, and the browser does not display, set the registry keyHKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser to the default browser location. Ensure that the BrowserName and the BrowserPath keys reflect the correct values. For example: BrowserName=Netscape 7 ; BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe . |
It is not necessary that you have Reports Server configured to use this functionality. Oracle Reports Builder uses the standalone Oracle WebLogic Server for running a report to Web layout.
Document Root
By default, the Oracle Reports Builder document root directory is
. This can be configured in the Oracle Reports Builder configuration file (
$DOMAIN_HOME/servers/<MANAGED_SERVER_NAME>/stage/reports/reports/web.war/rwbuilder.conf
) as follows:
<webLayout
docroot="DOMAIN_HOME/servers/<MANAGED_SERVER_NAME>/stage/reports/reports/web.war/"/>
The end user can also override this configurable docroot
from the command line using the WEBSERVER_DOCROOT
command line keyword. If your JSP depends on external files, such as images, style sheets, and so on, make sure you copy them into the docroot
directory. The docroot
should point to any Oracle WebLogic Server component, $DOMAIN_HOME/servers/<MANAGED_SERVER_NAME>/stage/reports/reports/web.war/
directory where Reports Application has been deployed.
Web Server Port
By default, the OHS port is considered to be the Web server port. This can be configured in the Oracle Oracle Reports Builder configuration file (rwbuilder.conf
) as follows:
<webLayout port="ohs_port"/>
The end user can override this configurable port parameter from the command line using the WEBSERVER_PORT
command line keyword.
Usage notes
The large numbers in generated filenames are simply unique IDs, and have no particular meaning (for example, emp012345678.jsp
).
The temporary files created in the docroot directory get cleaned up when you exit Oracle Reports Builder.
See also
Section 4.7.16.4, "Displaying report output in your Web browser"
This topic discusses the Web links that you can add to paper-based reports that will become active when you generate your report to an HTML file and display it in a Web browser.
In most cases, you can define Web links in an object's Property Inspector. You can specify column and field names in the link value to create dynamic links at runtime. If you require more complex implementation of Web links, such as conditional settings, you must specify the link using PL/SQL and the Oracle Reports Builder built-in packaged procedure SRW.SET_ATTR.
Reports output in HTML format can include the following types of Web links:
A link from an object to another object within the same report, or to another HTML or PDF document (see Section 2.2.5, "About hyperlinks").
An identifier for the destination of a Web link (see Section 2.2.7, "About hyperlink destinations"). The destination can be any printable object (field, boilerplate, frame, and so on) in your report layout.
A string in a frame of the master HTML document that links to an associated object (see Section 2.2.8, "About bookmarks"). You can associate a bookmark with any printable object (field, boilerplate, frame, and so on) in your report layout. In the formatted report, you can click a bookmark to display the associated object at the top of the window.
URLs that specify image resources. The URLs must be available to your Web server so that the images can be located when the HTML output is displayed by the server.
A graph hyperlink to link areas of a graph to specified destinations.
Additionally, your report can include the following headers and footers that use escapes to add HTML tags to your paper-based report:
a document header (a before report escape) for placing a logo or some standard links at the beginning of an HTML document (see Section 2.2.10, "About before and after escapes").
a document footer (an after report escape) for placing a logo or some standard links at the end of an HTML document.
a page header (a before page escape) for placing a logo or some standard links at the beginning of one page or all pages in an HTML document.
a page footer (an after page escape) for placing a logo or some standard links at the end of one page or all pages in an HTML document.
a Parameter Form header (a before form escape) for placing a logo or some standard links in the header of the HTML Parameter Form.
a Parameter Form footer (an after form escape) for placing a logo or some standard links in the footer of the HTML Parameter Form.
This topic discusses the Web links that you can add to paper-based reports that will become active when you run your report to a PDF file and display it in a PDF viewer.
Reports output in PDF format can include the following types of Web links:
A link from an object to another object within the same report, or to another HTML or PDF document (see Section 2.2.5, "About hyperlinks").
An identifier for the destination of a Web link (see Section 2.2.7, "About hyperlink destinations"). The destination can be any printable object (field, boilerplate, frame, and so on) in your report layout.
A string in the bookmark area of the PDF viewer that links to an associated object (see Section 2.2.8, "About bookmarks"). You can associate a bookmark with any printable object (field, boilerplate, frame, and so on) in your report layout. In the formatted report, you can click a bookmark to display the associated object at the top of the window.
A link that executes a command when clicked (see Section 2.2.9, "About application command line links"). You can associate a command with any printable object (field, boilerplate, frame, and so on) in your report layout. In the formatted report, you can click the object to execute the associated command.
A graph hyperlink to link areas of a graph to specified destinations.
In most cases, you can define Web links in an object's Property Inspector. You can specify column and field names in the link value to create dynamic links at runtime. If you require more complex implementation of Web links, such as conditional settings, you must specify the link using PL/SQL.
A hyperlink is an attribute of an object that specifies a hypertext link to either of the following destinations:
an object identified with a hyperlink destination within the same report
another HTML or PDF document on the same machine or on a remote Web server
You can set the Additional Hyperlink Attributes property to specify additional HTML to be applied to the hyperlink.
See also
Section 4.6.10.1.8, "Creating a hyperlink using the Property Inspector"
A graph hyperlink provides an active link from an area of a graph to a specified destination in a Web report. When end users display the report on the Web (JSP-based, or paper-based PDF or HTML), they can click one or more areas of the graph to drill down to additional linked information.
See also
A hyperlink destination is an attribute of an object that identifies the destination of a hypertext link.
See also
Section 4.6.10.1.7, "Creating a hyperlink destination using the Property Inspector"
Section 4.6.10.2.8, "Creating a hyperlink destination using PL/SQL"
A bookmark is an attribute of an object that specifies a string that is a link to the object.
See also
Section 4.6.10.1.10, "Creating a bookmark using the Property Inspector"
Section 4.6.10.2.10, "Creating a bookmark using PL/SQL"
Section 4.6.10.1.11, "Creating a bookmark on break columns using the Property Inspector"
(PDF output only) An application command line link is an attribute of an object that specifies a command line to be executed when the object is clicked.
Restrictions
An object that is associated with a application command line link cannot also be the source of a Web link (a hyperlink).
See also
Section 4.6.10.1.9, "Creating an application command line link using the Property Inspector"
Section 4.6.10.2.9, "Creating an application command line link using PL/SQL"
A before report escape specifies any text, graphics, or HTML commands that you want to appear at the beginning of your document.
An after report escape specifies any text, graphics, or HTML commands that you want to appear at the end of your document.
A before page escape specifies any text, graphics, or HTML commands that you want to appear at the beginning of one page or all pages of your document.
An after page escape specifies any text, graphics, or HTML commands that you want to appear at the end of one page or all pages of your document.
A before form escape specifies any text, graphics, or HTML commands that you want to appear at the top of the HTML Parameter Form.
An after form escape specifies any text, graphics, or HTML commands that you want to appear at the bottom of the HTML Parameter Form.
Limitations
In an after report escape (see Section 2.2.10, "About before and after escapes"), adding an HTML command outside the body does not reflect when the report is generated to spreadsheet output, even though the HTML command is written in output properly. For example:
</body><table><tr><td><B> the text to be bolded </B></td><tr></table> </html>
Adding the HTML command before the closing body tag reflect correctly when the report is generated to spreadsheet output. For example:
<table><tr><td><B> the text to be bolded </B></td><tr></table></body></html>
For more information about spreadsheet output, see Section 2.8.13, "About Enhanced Spreadsheet output".
See also
Section 4.6.10.1.1, "Creating an HTML document header using the Property Inspector"
Section 4.6.10.2.1, "Creating an HTML document header using PL/SQL"
Section 4.6.10.1.2, "Creating an HTML document footer using the Property Inspector"
Section 4.6.10.2.2, "Creating an HTML document footer using PL/SQL"
Section 4.6.10.1.3, "Creating an HTML page header using the Property Inspector"
Section 4.6.10.2.3, "Creating an HTML page header using PL/SQL"
Section 4.6.10.1.4, "Creating an HTML page footer using the Property Inspector"
Section 4.6.10.2.4, "Creating an HTML page footer using PL/SQL"
Section 4.6.10.1.5, "Creating an HTML Parameter Form header using the Property Inspector"
Section 4.6.10.2.5, "Creating an HTML Parameter Form header using PL/SQL"
Section 4.6.10.1.6, "Creating an HTML Parameter Form footer using the Property Inspector"
Section 4.6.10.2.6, "Creating an HTML Parameter Form footer using PL/SQL"
Style sheets (or Cascading Style Sheets) refer to HTML extensions that provide powerful formatting flexibility. With style sheet support, your HTML documents can include any of the following:
any font size or style
overlapping objects
horizontal and vertical lines and rectangles of any color or width
precise object positioning on a page
pagination
printing from a Web browser
inline image maps
This means that the sophisticated formatting in a report is preserved when you format the report as an HTMLCSS document. Without style sheet extensions, your HTML documents display only basic text formats and imported images. With style sheets, images of highly formatted text can be replaced with text objects of equivalent style, color, and font. Text objects can be positioned to overlay image objects. All text is fully searchable, and fewer images have to be downloaded.
To view an HTML document that takes advantage of style sheets, you must display it in a browser that supports style sheets.
Using external style sheets for HTMLCSS output
Every corporate Web site today uses style sheets to enforce the corporate look-and-feel across Web pages. External style sheets are Cascading Style Sheet (CSS) files that are referenced by these Web pages. End users typically want the same style used in pages on their Web site applied to their Web reports.
In prior releases, applying style sheets and user-defined styles to reports involved manually editing the HTMLCSS output.
You can specify user-defined styles and style sheets for HTMLCSS output using Oracle Reports Builder. Styles can be applied to report, field, text, frame, and repeating frame objects using the new properties Style Sheets, CSS Class Name, and CSS ID properties (see the Oracle Reports online Help for descriptions of these properties). The generated HTMLCSS output includes links to the style sheets and the user-defined styles are applied to the objects.
Restrictions
The following elements are not supported by HTML style sheet extensions:
ellipses, arcs, polygons/polylines, and diagonal lines
rounded rectangles (formatted as rectangles)
arrows on lines
dashes on lines or borders of objects
See also
Section 4.7.16.4, "Displaying report output in your Web browser"
When you create a report with multiple queries, it is typically easier to create all of the queries with the Data Wizard first and then create the layouts with the Report Wizard.
To create the queries:
In the Data Model view, choose Insert > Query to display the Data Wizard.
If the Welcome page displays, click Next.
On the Query page, type Q_Salesrep
for the Query name and click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT ENAME, EMPNO FROM EMP WHERE JOB = 'SALESMAN'
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 11.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Groups page, click Next.
Click Finish to display the data model for your report in the Data Model view.
Repeat the steps above for a second query, but this time name your query
Q_Product
and use the following SELECT
statement:
SELECT REPID, PRODNAME, AMOUNT, CUSTNAME FROM SALES ORDER BY REPID, CUSTNAME
Figure 11-2 Two-query data model without a link
To add the data link
In the Data Model view, click the Data Link tool in the tool palette.
Click and drag from the EMPNO column in the G_ENAME group to the REPID column in the G_REPID group. Notice that a line is drawn from the bottom of the G_ENAME group to the Q_Product query. Labels for EMPNO and REPID are created at each end of the line to indicate they are the columns linking G_ENAME to Q_Product.
Figure 11-3 Two-query data model with a data link
Double-click the new data link line to display the Property Inspector and examine the property settings:
G_ENAME is identified as the parent, while Q_Product is listed as the child. In terms of the data, the sales rep's name and employee number make up the master record and should print once for the associated product information retrieved by the Q_Product query.
Notice that WHERE already appears in the SQL Clause property. WHERE is the default clause used in master/detail relationships. You can replace WHERE with other SQL clauses such as HAVING and START WITH, but for this report the default is correct.
The other point to notice is that an equal sign (=) appears in the Condition property. An equality (that is, table1.columnname = table2.columnname) is the default condition for master/detail relationships defined through a data link. You can replace the equal sign with any other supported conditional operator (to see what is supported, click the field), but for this report the default is the proper condition.
Linking the group G_ENAME and the query Q_Product through the EMPNO and REPID columns is analogous to writing both queries as the single-query shown below:
SELECT ENAME, EMPNO, REPID, PRODNAME, AMOUNT, CUSTNAME FROM EMP, SALES WHERE JOB = 'SALESMAN' AND EMPNO = REPID (+) ORDER BY REPID, CUSTNAME
Figure 10-8 Group left report output with two break columns
The report above looks similar to the group left report you built in Section 10.2, "Group Report with One Break Column". However, notice that DEPARTMENT_ID values sometimes print more frequently than they did in the previous report. The DEPARTMENT_ID value repeats for each unique value of JOB_ID within the department. This behavior occurs because DEPARTMENT_ID is now grouped with JOB_ID and must print with JOB_ID. While DEPARTMENT_ID values may repeat several times, not until the position of PU_CLERK in department 30 does a job repeat, and not until that point can the break group actually break.
Concepts
You can specify that your report break on certain combinations of information by varying the columns you include in the break group.
You can modify your previous report by moving JOB_ID into the break group, so that your report has two break columns instead of one.
After changing the data model, redefault the layout to incorporate your changes, then specify the format mask again.
To see a sample group left report with two break columns, open the examples folder named break
, then open the Oracle Reports example named grp2col_lft1.rdf
. For details on how to access it, see "Accessing the Example Reports" in the Preface.
The first task in changing your previous report is to modify the data model by placing an additional column in the break group.
To add a column to the break group:
Open the report you created in the previous section.
In the Object Navigator, double-click the view icon next to the Data Model node to display the Data Model view.
In the Data Model view, click the break group, G_DEPARTMENT_ID, then click and drag the handle on the bottom center of the G_DEPARTMENT_ID group box down about 0.25 inches (0.5 centimeters) to resize it.
Click and drag the JOB_ID column in the G_EMPLOYEE_ID group and move it into the G_DEPARTMENT_ID group, underneath the DEPARTMENT_ID column.
Figure 10-9 Data model with two break columns
In order for your data model change to be reflected in your output, you need to redefault the layout for your report using the Report Wizard.
To redefault the layout with the Report Wizard:
Click the title bar of the Report Editor to make it the active window. The Report Editor must be the active window for you to access the Report Wizard.
Choose Tools > Report Wizard.
Select Create Paper Layout only.
Click Finish. Notice the changes to the output in the Paper Design view. Also note how the formatting of the Salary field and the additional spacing between records is retained. When possible, Reports Builder will retain your manual modifications between uses of the Report Wizard.
Save your report.
Now that you have added the repeating frame footer, it is time to add a page header. You perform this task from the Paper Layout view.
In the Paper Layout view, click the Edit Margin button in the toolbar.
From the font lists in the toolbar, choose Arial Black, point size 16.
Click the Text tool in the tool palette.
Click somewhere to the right of the logo image and type the following text:
Employee Summary Report
Move to an open area of the Paper Layout view and click the mouse button to exit text mode. Notice that the text object you just created is still selected, you can now adjust its positioning with the arrow keys. If you click in an open area a second time, the object is deselected.
Note: Similarly, you can add a page footer, below the page margin. |
Click the Run Paper Layout button in the toolbar to display your report in the Paper Design view. It should look like the following:
Figure 16-4 Group left report output with page heading
Save the report as headfoot_
your_initials
.rdf
.
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
The steps in this section will show you how to make a few modifications in the Paper Layout view and run your report.
To modify and run your report to paper:
In the Paper Layout view, change the font of the field (F_1) to Arial, Bold.
Change the Fill color to any color (for example, Beige), and the Line Color to No Line Color.
Click the Run Paper Layout button in the toolbar.
Note: To generate the table of contents (TOC), you must click the Run Paper Layout button. If you click the Paper Design view button, the change of format order will not take effect, thus the TOC will not be generated. |
The table of contents displays on the first page of the report, and looks something like the following:
Figure 36-5 Table of contents page of the report
In this example, under the BOYS product department, the SHIRTS sub-category starts at page 22 and the next sub-category, SHOES, starts at page 38. If you move to any page between page 22 and 38 in the report, you will see the SHIRTS sub-category.
Note: The page numbers may differ depending upon the data used in your report. |
Figure 36-6 Results for the BOYS product department and SHIRTS sub-category
You can navigate to various pages in your report to view the data for each department and sub-category.
Note: At this point, you can compare your report against the example file we have provided,multilevel_toc.rdf . First, compile the PL/SQL by choosing Program > Compile > All, then run the report to paper. |
Some preprinted forms may require you to print page numbers. The steps in this section will show you how to add page numbers to your output that will display when the number of order items extends beyond a single page.
To add page numbers to your report:
Click the Paper Layout button in the toolbar to display the Paper Layout view.
Click the Field tool in the tool palette.
Draw a small region on your layout 6 inches (15 centimeters) from the left and 0.25 inches (0.5 centimeters) from the top, to 7 inches (18 centimeters) from the left and 0.5 inches (1.5 centimeters) from the top.
Double-click the new field object, F_1, to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to F_PAGE_NUMBER.
Under General Layout, set the Horizontal Elasticity property to Variable.
Under Field, set the Source property to Page Number, and set the Visible property to No. Click the Page Numbering property field to display the Page Numbering dialog box.
In the Page Numbering dialog box:
Clear the Header Section and Trailer Section check boxes, and make sure that the Main Section check box is selected.
In the Reset At list, click R_G_Customer.
Click OK.
In the Paper Layout view, following the steps above to create another field from (7.25, 0.25) to (8.25, 0.5) inches (or (18.5, 0.5) to (21, 1.5) centimeters) .
Double-click the new field object to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to F_TOTAL_PAGES.
Under General Layout, set the Horizontal Elasticity property to Variable.
Under Field, set the Source property to Total Pages, and set the Visible property to No. Click the Page Numbering property field to display the Page Numbering dialog box.
In the Page Numbering dialog box:
Clear the Header Section and Trailer Section check boxes, and make sure that the Main Section check box is selected.
In the Reset At list, click R_G_Customer.
Click OK.
Now that you have created the page number fields, you need to make them visible.
In the Paper Layout view, click the Text tool in the tool palette.
Draw a region on your layout 6 inches (15 centimeters) from the left and 1 inch (2.5 centimeters) from the top, to 8 inches (20 centimeters) from the left and 1.25 inches (3 centimeters) from the top.
Click inside the rectangle and type &<F_PAGE_NUMBER> of &<F_TOTAL_PAGES>
.
Note: The "&<F_PAGE_NUMBER>" text will replace that text with the current value of the summary field you just created, which determines the page number of the current page. The "&<F_TOTAL_PAGES>" text will replace that text with the current total number of pages of the report, based on the value of the summary field of the same name. |
With the text object selected, choose Tools > Property Inspector to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to B_PAGE_NUMBERS.
Under Advanced Layout, set the Print Object On property to All Pages, and set the Base Printing On property to Enclosing Object.
In the Paper Design view, click the Run Paper Layout button in the toolbar to run your report and display it in the Paper Design view.
Your report should now look like the following image. Notice how Reports Builder calculates that this particular report is one page, thus displays "1 of 1" above the order details.
Figure 32-14 Paper Design view of the preprinted form report with page numbers
Save your report.
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
To build the example in this chapter, you must have access to the Summit Sporting Goods (SUMMIT) schema. To download the SQL scripts that install the schema, see "Accessing the Data Sources" in the Preface.
In this chapter, you will learn about form letter reports, one of the standard report styles. By following the steps in this chapter, you can generate the report output shown in Figure 7-1.
Concepts
Form letter reports contain database values embedded in boilerplate text (boilerplate text can be defined as any text that appears each time the report is run). It can be text generated by Oracle Reports, text you create, or text you import from a file.
A form letter report is useful for printing multiple copies of the same letter addressed to different people.
A form letter report is similar to a mailing label report, in that it is a simple report with a form letter layout style.
Data Relationships
There are no special restrictions on data relationships for a form letter report.
Layout
A default form letter layout style is provided by Oracle Reports. It consists of the field names for the columns you have selected, prefixed by ampersands (&
). The ampersands indicate that they are hidden fields, and are contained within boilerplate and repeating frame objects. You then add the text of the letter to the layout. Oracle Reports prints one record (that is, one letter) per page.
Hidden fields, which are the default for a form letter layout, appear in the layout but not the output until referenced. You can do this in the Paper Layout view by typing its name, prefixed by an ampersand (&
). Oracle Reports treats the reference as a normal field.
In general, if you are embedding a field within boilerplate text (as in a form letter), it is best to hide the field and reference it where desired. The field values will then flow with the text. A field can appear in a report both where placed by default in the report layout and where referenced. Fields can also be referenced more than once in the same piece of boilerplate text.
Example Scenario
As you build this example report, you will:
Use the Report Wizard to Create a Form Letter Report with a paper layout that includes one query to select all of the columns displayed in this report. Oracle Reports will create all other necessary objects, for example, groups and columns, by default
To see a sample form letter report, open the examples folder called formletter
, then open the Oracle Reports example report called formletter.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
To build the example in this chapter, you must have access to the Human Resources sample schema (HR) provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
Congratulations! You have finished the REF CURSOR
query sample report. You now know how to:
create package specs that define REF CURSOR
s.
create REF CURSOR
queries.
create data links between REF CURSOR
queries.
create summaries to describe data.
create a report layout.
move SELECT
statements into packages.
move packages into a PL/SQL library.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
In this chapter, you will learn about reports that include graphics that change depending on the data. By following the steps in this chapter, you can generate the report output shown in Figure 24-1.
Figure 24-1 Dynamic graphics report output
Concepts
Oracle Reports enables you to link to and display drawings and images that are "dynamic." That is, any changes made to the graphics will be reflected in your report output at runtime.
Data Relationships
One way to display dynamic graphics in a report involves creating a database column that stores the names of the graphic files you want to display. You do this in the Data Model view.
In the Data Model view, Oracle Reports provides a property called Read from File that enables you to pull the latest versions of the graphics into your report. When you set the Read from File property for a column listing file names, Oracle Reports displays the graphic contained within the named file, rather than the file name itself. Doing so enables Oracle Reports to pull into the report the latest version of the graphic at runtime.
Another way to include dynamic graphics in your report is to use the File Link tool in the Paper Layout view. This tool enables you to create a boilerplate object used to contain an external file, such as graphics and text. This example does not show you how to use this method, but you can read more about this tool in the Oracle Reports online Help.
Layout
This report uses a simple Group Above layout.
Example Scenario
In this example, you will create a simple report using the Group Above layout to display data about an employee, showing a different image depending on the location of the employee's department.
As you build this example report, you will:
Create the Data Model with Two Linked Queries manually in the Data Model view.
Create the Layout of the Report, first using the Reports Wizard, then making modifications in the Paper Layout view.
To see a sample report containing dynamic graphics, open the examples folder called dynamicgraphics
, then open the Oracle Reports example report named dynamicgraphics.rdf.
For details on how to open it, see "Accessing the Example Reports" in the Preface.
When you create a report, you can either use the Report Wizard to assist you or create the report yourself. To build this report, you will need to create two queries: a SQL query and an XML query.
To create a SQL query:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
Your new report displays in the Object Navigator as something like MODULE 2. You will also see the Data Model view of your new report.
In the Data Model view, click the SQL Query tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following SELECT
statement:
SELECT W.WAREHOUSE_ID, W.WAREHOUSE_NAME, L.CITY, L.STATE_PROVINCE, C.COUNTRY_NAME FROM WAREHOUSES W, HR.LOCATIONS L, HR.COUNTRIES C WHERE (W.LOCATION_ID = L.LOCATION_ID(+)) AND (L.COUNTRY_ID = C.COUNTRY_ID(+)) ORDER BY C.COUNTRY_NAME, W.WAREHOUSE_NAME
Note: You can enter this query in any of the following ways:
|
Click OK.
Note: If the Connect dialog box displays, enter the user ID, password, and name of the database that contains the sample schema. |
The data model displays in the Data Model view, and should look something like this:
Figure 45-2 Data Model for the XML PDS example SQL query
Save your report as inventoryreport_xml_
your_initials
.rdf
.
You have created a SQL query to retrieve the data for your report.
In this section, you will create a query to access the XML data source. You can view the resulting report we have provided to make sure your query is correct. Please note that you must update the paths to the Data Definition files with the location of the example files we provided.
To create an XML query:
In the Data Model view, choose Insert > Query to display the Data Wizard.
If the Data Wizard Welcome page displays, click Next.
On the Query page, click Next.
On the Data Source page, click XML Query, then click Next.
On the Data page, click Query Definition to display the Define XML Query dialog box.
In the Define XML Query dialog box, under Data Definition, click Browse to locate the XSD file we have provided, warehouse_inventory.xsd
and open it.
Under Data Source, click Browse to locate the XML file we have provided that contains your data, warehouse_inventory.xml
and open it.
If you want to compare your data definition to the one we provided, make sure that you replace the data definition locations with the locations of your files.
Click OK.
In the Data Wizard, still on the Data page, click Next.
On the Groups page, click Next.
Click Finish to display your data model in the Data Model view. It should look something like this:
Figure 45-3 Data model for the XML PDS example with XML and SQL queries
Save your report.
You have created an XML query to access the XML data source we have provided.
You will now need to link the SQL query and the XML query so that you can access your corporate data as well as the data for each of the local warehouses.
To create a data link:
In the Data Model view, click the Data Link tool in the tool palette.
Click the WAREHOUSE_ID column in your first query (Q_1).
Drag your cursor until it is over the WAREHOUSE_ID1 column in the second query (Q_2).
Your data model should now look something like this:
Figure 45-4 Data Model with a data link between a SQL query and an XML query
You will notice that the WAREHOUSE_ID column is now highlighted at the bottom of Q_1, with a line pointing to the WAREHOUSE_ID1 column.
Save your report.
You have created a data link between the WAREHOUSE_ID columns in the two queries.
Before you can run any report, you must define a layout. The easiest way to do this is to use the Report Wizard.
To create a paper layout:
In the Data Model view, right-click on the canvas, then choose Report Wizard.
In the Report Wizard, on the Report Type page, select Create Paper Layout only, then click Next.
On the Style page, select Group Above.
On the Groups page, make sure the G_WAREHOUSE_ID and G_WAREHOUSE_ID1 groups are listed in the Group Fields list with a Down Print Direction.
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list.
On the Labels page, adjust the labels as desired.
On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:
Figure 45-5 Paper Design view for the XML PDS report
Save your report.
You have created the layout for your paper report.
Note: You can also run the provided reportinventory_report.rdf . Before you can run the report, double-click the XML query in the Data Model view, and point the XML data source to the appropriate XSD and XML files. |
Now that you have created the report, you can make it more user-friendly by using a summary column to apply alternating row colors.
To create a summary column to count the rows:
In the Data Model view, click the Summary Column tool in the tool palette.
If you are still in the Paper Design view, you can click the Data Model button in the toolbar to display the Data Model view.
Click in the XML query group (G_WAREHOUSE_ID1) to create a summary column.
Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to LineNo.
Under Column, make sure the Column Type property is set to Summary, and that the Datatype property is set to Number.
Under Summary, set the Function property to Count, and set the Source property to PRODUCT_NAME.
To create a procedure that changes the row colors:
In the Object Navigator, click the Program Units node for your report.
Click the Create button in the toolbar to display the New Program Unit dialog box.
In the New Program Unit dialog box, type linecolors
as in the Name field.
Select Procedure, and click OK to display the PL/SQL Editor for the new program unit.
In the PL/SQL Editor, enter the following PL/SQL code to change the text color of the alternating rows to blue:
PROCEDURE LineColors IS BEGIN if (:LineNo mod 2=0) then srw.set_text_color('blue'); else srw.set_text_color('black'); end if; END;
Note: You can copy and paste this code from the procedure provided in thexmlpds_code.txt file. Just copy the text under Line Colors Procedure . |
Click Compile to compile the procedure.
If any errors display, make sure the code is correct, and that you created the summary column in the steps above.
Click Close.
Note: Optionally, you can also change the fill colors of the alternating rows by following the steps in the above section, and using the following PL/SQL code. In this example code, we have changed the fill color of alternating rows to red and blue:PROCEDURE LineColors IS BEGIN if (:LineNo mod 2=0) then srw.set_foreground_fill_color('blue'); srw.set_fill_pattern('solid'); else srw.set_foreground_fill_color('red'); srw.set_fill_pattern('solid'); end if; END; |
To create a format trigger for each field that calls the procedure:
In the Object Navigator, under your report name, expand the Paper Layout node and navigate to Main Section > Body > M_G_WAREHOUSE_ID_GRPFR > R_G_WAREHOUSE_ID > M_G_WAREHOUSE_ID1_GRPFR > R_G_WAREHOUSE_ID1.
Under R_G_WAREHOUSE_ID1, double-click F_PRODUCT_ID to display the Property Inspector.
Note: If you cannot find a particular field, use the Find field at the top of the Object Navigator. |
In the Property Inspector, under Advanced Layout, double-click the Format Trigger property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function F_PRODUCT_IDformatTrigger return Boolean is
begin
LineColors;
return (TRUE);
end;
Note: Make sure you do not touch the boldface text. Simply type in the code below this text to create the format trigger. You can copy and paste this code from the procedure provided in thexmlpds_code.txt file. Just copy the text under Format Trigger Code. |
Add a format trigger for the following fields, using the same code as in the previous step. Be sure not to delete the first line of the code, where the format trigger name is defined:
F_PRODUCT_NAME
F_QUANTITY_ON_HAND
F_WAREHOUSE_ID1
Save your report.
Click the Run Paper Layout button in the toolbar to run your report to paper. Your report should look something like this:
Figure 45-6 XML PDS Report with Alternating Row Colors
You have now applied alternating row colors to your report.
If you have a lot of data in your XML file, you might want to consider sorting and filtering it. You can do so by creating a group filter and a hierarchy. The steps in this section will show you how to create a filter that will only show the inventory items for a user-defined quantity amount. The filter will be based on a parameter that the user can enter at runtime. You will then create a hierarchy in your data model to group the data in your report.
To create a user parameter and a group filter:
In the Object Navigator, under the User Parameters node, create a new user parameter called P_MAXQTY
, with a Datatype of Number, Width of 20, and Initial Value of 50 (see Section 4.11.2, "Creating a user parameter").
In the Data Model view, double-click the G_WAREHOUSE_ID1 group in the XML query to display its Property Inspector.
In the Property Inspector, under Group:
set the Filter Type property to PL/SQL.
click the PL/SQL filter property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function G_WAREHOUSE_ID1GroupFilter return boolean is begin if :quantity_on_hand < :P_maxqty then return (TRUE); else return (false); end if; end;
Note: You can also copy and paste this code from the provided file calledxmlpds_code.txt . Copy the code under the heading "Group Filter Code." |
Click Compile, and fix any errors.
Note: If you are not familiar with compiling PL/SQL, refer to a PL/SQL reference manual. |
When the code compiles successfully, click Close.
Save your report.
Click the Run Paper Layout button in the toolbar to run your report to paper. Notice how a Parameter Form now displays where you can adjust the quantity of items displayed in your report.
You can also run the provided report inventoryreport.rdf
to view the results in Reports Builder.
Save your report.
To create a hierarchy for the XML query:
In the Data Model view, click the PRODUCT_ID column in the XML query, then drag it between the query name and the G_WAREHOUSE_ID1 group.
Your data model should look like this:
Figure 45-7 Data Model with Group Hierarchy
Note: Notice in the above image that a green circle displays above G_WAREHOUSE_ID1. This circle indicates that a group filter has been created for the group. |
Save your report. You have now created a group hierarchy that sorts the data in your report.
AFM
Acronym for Adobe Font Metrics. AFM and PPD files are supplied by Adobe and by printer vendors. These files contain information about the printer. Along with other parameters, these files are read for the information about the available fonts for the printer, which Oracle Reports will use. For all the fonts listed in the PPD file, Oracle Reports searches for the corresponding AFM file according to the font name and loads all of the fonts for which there is an available AFM.
CGI
Acronym for Common Gateway Interface. A standard for transferring information between a Web server and a CGI program. CGI specifies how to pass arguments to the program as part of the HTTP request, and defines a set of environment variables that are made available to the program. The program then generates output to pass back to the browser. CGI provides server-side processing to allow Web servers to interact dynamically with users.
column
A vertical space in a database table that represents a particular domain of data. A column has a column name (for example, ENAME) and a specific datatype (for example, CHAR). For example, in a table of employee information, all of the employees' names would constitute one column. A record group column represents a database column.
A data model object created automatically for each column expression in a query's SELECT list, or created manually to perform summaries, formulas, or act as a placeholder.
The representation of an attribute of an entity.
data model
A relational model that defines what data should be fetched from the data source(s), what values should be computed, and how data should be ordered in a report. Reports Builder objects that define the data model are queries, groups, columns, parameters, and links.
Data Model view
One of the views of the Report Editor that displays a structural representation of the data in a report. The objects do not appear in the report output, but the structure determines the layout style, and the data objects provide the values that appear in the layout objects.
database
A source for data returned by a query, including database objects such as tables, views, synonyms, snapshots, and queries stored as views. Oracle Reports Services enables you to access any data source.
A set of dictionary tables and user tables that are treated as a unit.
data source
A source for data returned by a query, including database objects such as tables, views, synonyms, snapshots, and queries stored as views. Oracle Reports Services enables you to access any data source.
The pluggable data source (PDS) architecture replaces Oracle Open Client Adapter (OCA), and the Open Database Connectivity (ODBC) drivers are obsolete in Oracle Reports. However, Java Database Connectivity (JDBC) is one of the pluggable data sources available that can utilize the JDBC-ODBC bridge, allowing access to other data sources.
detail query
When defining a master/detail report, the detail query retrieves all related records for each record retrieved by the master, or parent, query.
dialog box
A partial screen or window that prompts you to enter information necessary to complete an operation.
disabled
An interface element state that means a menu item, button, and so on, cannot be used in the current context (that is, it does not respond to keyboard or mouse input).
enabled
An interface element state that means that a menu item, button, and so on, can be used in the current context (that is, it responds to keyboard or cursor/mouse input).
field
An interface element in which you enter, edit, or delete data.
A layout object that defines how the data for a specific query column appears.
format mask
A setting that defines the appearance of the value of a field. For example, a format mask is used to specify the display of currency amounts and dates.
format trigger
A PL/SQL function that enables you to dynamically change the formatting attributes of an object.
formula column
A user-created column that gets its data from a PL/SQL function or expression, a SQL statement, or a combination of these.
frame
A layout object used to enclose other layout objects and control the formatting, frequency, and positioning of several objects simultaneously.
group
In Reports Builder, a data model object that is created automatically to contain all the columns selected by a query, or created by the user to modify the hierarchy of the data appearing in a report; it is used primarily for creating breaks in a report, as well as for resetting computations.
An object that is composed of several other objects.
HTML
Acronym for HyperText Markup Language. A tag-based ASCII language used to specify the content and links to other documents on Web servers on the Internet. End users with Web browsers view HTML documents and follow links to display other documents.
HTTP
Acronym for HyperText Transfer Protocol. The protocol used to carry Web traffic between a Web browser computer and the Web server being accessed.
hyperlink
A reference (link) from some point in one document to (some point in) another document or another place in the same document. A Web browser usually displays a hyperlink in some distinguishing way (in a different color, font or style). When users activate hyperlinks (by clicking on them with a mouse) the browser displays the target of the link.
IANA
Acronym for Internet Assigned Numbers Authority. An organization working under the auspices of the Internet Architecture Board (IAB) that is responsible for assigning new Internet-wide IP addresses. IANA-defined character sets refers to those character sets that can be defined for the charset
tag and may be used in the Internet.
image
A bitmapped object that can be stored and loaded into an application. The client cannot modify an imported image.
intranet
An internal TCP/IP network, access to which is restricted (through a firewall) to individuals inside the company or organization. An intranet provides similar services within an organization to those provided by the Internet, but is not necessarily connected to the Internet. A common example of an intranet is when a company sets up one or more Web servers on an internal network for distribution of information or applications within the company.
Java
A computer language that supports programming for the Internet in the form of platform-independent "servlets" or "applets".
Java EE
Acronym for Java Platform, Enterprise Edition. An environment for developing and deploying enterprise applications in Java consisting of a set of services, application programming interfaces, and protocols that provide for developing multitiered, Web-based applications.
JAR
Acronym for Java ARchive. A file used for aggregating many files (Java class files, images, and so on) into one file.
JSP
Acronym for JavaServer Page. JSP technology is an extension to the Java Servlet technology from Sun Microsystems that provides a simple programming vehicle for displaying dynamic content on a Web page. JSP is a server-side technology. A JSP is an HTML page with embedded Java source code that is executed in the Web server or application server. The HTML provides the page layout that is returned to the Web browser, and the Java provides the business logic.
margin
An optional report region that appears at the top and bottom of each logical page in a report section (Header, Main, or Trailer). The margin may include any layout object, but typically contains boilerplate and fields (for page numbers, page totals, grand totals, and current date and time).
object
An item that can be placed on the layout. The following are examples of objects: rectangle, line, ellipse, arc, polygon, polyline, rounded rectangle, freehand, chart, text, symbol, and text field.
In an Oracle database, an instance of an object type. An object can be a row in an object table, or the portion of a row contained in a column object in a relational table.
Object Navigator
A hierarchical browsing and editing interface that enables you to locate and manipulate application objects quickly and easily. Features include:
A hierarchy represented by indentation and expandable nodes (top-level nodes show module types, database objects, and built-in packages), enabling tasks such as creating, editing, renaming, and deleting objects.
A find field and icons, enabling forward and backward searches for any level of node or for an individual item in a node
Icons in the horizontal toolbar replicating common File menu functions
Oracle Fusion Middleware (OracleAS)
A strategic platform for network application deployment. By moving application logic to application servers and deploying network clients, organizations can realize substantial savings through reduced complexity, better manageability, and simplified development and deployment. OracleAS provides the only business-critical platform that offers easy database Web publishing and complete legacy integration while transitioning from traditional client/server to network application architectures.
Oracle Developer Suite
A pre-11g suite of components that combined leading Oracle application development and business intelligence tools into a single, integrated product. Built on Internet standards such as Java and XML, the suite provided a complete and highly productive development environment for building applications for Oracle Application Server and the Oracle database. In Oracle Fusion Middleware, you can install the Developer Tools to install the 11g Release 1 (11.1.1) versions of these components (Oracle Oracle Reports Builder, Oracle Forms Builder, and so on).
ORACLE_HOME
An alternate name for the top directory in the Oracle directory hierarchy on some directory-based operating systems. An environment variable that indicates the root directory of Oracle products.
You can refer to the directory specified by ORACLE_HOME
in syntax:
On UNIX: $ORACLE_HOME
On Windows: %ORACLE_HOME%
Oracle Portal
A browser-based development tool for building scalable, secure, extensible HTML applications and Web sites. Oracle Reports Services uses Oracle Portal to control end user access to reports published on the Web by storing information about report requests, the secured server, and any Oracle Reports Services printer used to print report output.
Oracle Reports Bridge
Oracle Reports Bridge (rwbridge
) provides functionality for discovering a Reports Server across Farms.
Oracle Reports Client (rwclient
)
An Oracle Reports component that provides a command-line interface to send a report to a remote Reports Server (rwserver
).
Oracle Reports Builder (rwbuilder
)
An Oracle Reports component that provides the report building component of Oracle Reports. Report developers use the Oracle Reports Builder design-time user interface to create and maintain report definitions. For more information, refer to the Oracle Reports Online Help (select Help > Contents in Oracle Reports Builder), and the Oracle Reports Building Reports manual.
Oracle Reports Runtime (rwrun
)
An Oracle Reports component that runs a report using the Oracle Reports Services in-process server.
Oracle Reports Services
The runtime environment that executes, distributes, and publishes your reports for enterprise wide reporting. Using Oracle Reports Services to deploy your reports results in gains of flexibility, time savings, and processing capacity.
Oracle Reports Servlet (rwservlet
)
A component of Oracle Reports Services that translates and delivers information between either a Web Server or a Java EE Container (for example, Oracle WebLogic Server) and the Reports Server, enabling you to run a report dynamically from your Web browser.
Oracle Reports Queue Manager (rwrqm
)
(Windows only) Maintains timestamp and status information about reports jobs managed by the Reports Server (rwserver
).
Paper Design view
One of the views of the Report Editor that displays output for paper reports and enables you to make many commonly required, simple modifications to the layout, such as spacing, formatting fields, color, and editing text, without having to open the Paper Layout view.
Paper Layout view
One of the views of the Report Editor that displays the layout objects in a paper report and enables you to make many modifications to any layout object. All layout objects have properties that you can modify using the Property Inspector. The hierarchy of the layout objects is determined by the Data Model.
Paper Parameter Form view
Displays the layout of the Parameter Form that, at runtime, allows user input of parameter values in the Runtime Parameter Form.
Acronym for Portable Document Format. A file format (native for Adobe Acrobat) for representing documents in a manner that is independent of the original application software, hardware, and operating system used to create the documents. A PDF file can describe documents containing any combination of text, graphics, and images in a device-independent and resolution independent format.
PL/SQL
Oracle's proprietary extension to the SQL language. Adds procedural and other constructs to SQL that make it suitable for writing applications.
PPD
Acronymn for PostScript Printer Definition. PPD and AFM files are supplied by Adobe and by printer vendors. These files contain information about the printer. Along with other parameters, these files are read for the information about the available fonts for the printer, which Oracle Reports will use. For all the fonts listed in the PPD file, Oracle Reports searches for the corresponding AFM file according to the font name and loads all of the fonts for which there is an available AFM.
Property Inspector
A window that enables you to view, locate, and set the properties of the currently selected object(s) in the Object Navigator, Report Editor, and Template Editor. Every Reports Builder object (query, group, frame, parameter, and so on) has associated properties that can be viewed using the Property Inspector. The Property Inspector features:
expandable and collapsible nodes
in-place property editing
search features
multiselection
complex property dialogs
the ability to invoke multiple instances of the Property Inspector
To get help on any property, click the property in the Property Inspector and press F1.
query
A SQL SELECT statement that specifies the data you wish to retrieve from one or more tables or views of a database.
RDF file
A file that contains a single report definition in binary format. .RDF files are used to both run and edit reports.
REP file
A file that contains a single report definition in binary format. .REP files are used solely to run reports; you cannot edit a .REP file.
Reports CGI (rwcgi
)
Note: Reports CGI (rwcgi ) is deprecated (maintained only for backward compatibility); instead, use Reports JSPs, rwservlet (Reports Servlet), or Reports Web Services. |
An Oracle Reports component, also known as the Common Gateway Interface (CGI) or Reports Web Cartridge, that translates and delivers information between either a Web Server or a Java EE Container (for example, Oracle WebLogic Server) and the Reports Server, to run a report dynamically from your Web browser.
Report Editor
The Oracle Reports Builder window that provides different views to help you handle the data objects and layout objects for Web and paper reports. The views are:
Reports Engine
A component of Oracle Reports Services that fetches data from the data source, formats the report, send output to cache, and notifies the Reports Server that the job is ready.
Reports Server (rwserver
)
Reports Server (rwserver
) is an Oracle Reports component that provides reporting services to execute, distribute, and publish your reports for enterprise-wide reporting. A component of Oracle Reports Services that processes client requests, including user authentication, scheduling, caching, and report distribution. Use Oracle Reports clients such as Oracle Reports Servlet (rwservlet
), Reports JSP, and Oracle Reports Client (rwclient
) to send a report to Reports Server.
row
One set of field values in a table; for example, the fields representing one employee in the example table EMP.
Runtime Parameter Form
A screen or window appearing optionally at runtime in which a user can modify print options and parameters prior to report execution.
schema
A collection of related database objects, usually grouped by database user ID. Schema objects include tables, views, sequences, stored program units, synonyms, indexes, clusters, and database links.
SELECT statement
A SQL statement that specifies which rows and columns to fetch from one or more tables or views.
servlet
A Java application that runs in a Web server or application server and provides server-side processing, typically to access a database or perform e-commerce processing. Because they are written in Java, servlets are portable between servers and operating systems.
The Reports Servlet (rwservlet
) and JSP are components of Oracle Reports Services that process custom (JSP) report tags and deliver information between the Oracle HTTP Server and the Reports Server.
SQL
A standard interface for storing and retrieving information in a relational database. SQL is an acronym for Structured Query Language.
SQL script
A file containing SQL statements that you can run to perform database administration quickly and easily. Several SQL scripts are shipped with Oracle products.
style sheet
HTML extensions that provide powerful formatting flexibility in HTML documents. To view an HTML document that takes advantage of style sheets, display it in a browser that supports style sheets.
table
A named collection of related information, stored in a relational database or server, in a two-dimensional grid that is made up of rows and columns.
tabular
A default layout displaying labels at the top of the page and rows of data underneath the labels.
template
A skeleton definition containing common style and standards, and may include graphics. A template provides a standard format to enable quick and easy development of professional standard look-and-feel reports.
Template Editor
A work area in which you can define objects and formatting properties for your templates. It is similar to the Paper Layout view of the Report Editor. You can create, delete, and modify objects (for example, page numbers, text, and graphics) in the margin area. You cannot create and delete objects in the body area, but you can modify the properties of body objects in the Property Inspector.
tool palette
A collection of tools represented by iconic buttons in the user interface that allow a report developer to perform tasks, such as drawing a rectangle in the Paper Layout view or creating a query in the Data Model view.
toolbar
A collection of iconic buttons that perform product commands. Usually aligned horizontally along the top, or vertically down the side of a window.
URL
Acronym for Uniform Resource Locator. A compact string representation of the location for a resource that is available through the Internet. It is also the text string format clients use to encode requests to OracleAS.
view
In Reports Builder, a work area in which you perform a specific set of tasks, such as defining a report data model, layout, or Parameter Form.
A virtual table whose rows do not actually exist in the database, but which is based on a table that is physically stored in the database.
Web browser
A program that end users utilize to read HTML documents and programs stored on a computer (serviced by a Web server).
Web server
A server process (HTTP daemon) running at a Web site which sends out Web pages in response to HTTP requests from remote Web browsers.
Web Source view
One of the views of the Report Editor that displays the HTML or JSP source for a report. You can use this view to add dynamic content to a Web page using the Report Block Wizard and the Graph Wizard. Experienced Java developers can edit the Web source directly in this view.
A step-by-step interface for commonly performed tasks. The wizards in Reports Builder are:
Report Wizard: guides you through the steps to create a basic paper or Web report. Each page of the wizard asks you for information to help you create your initial report.
Data Wizard: helps you helps you quickly define or modify a query for a multiquery data models.
Graph Wizard: Adds variety of charts and graphs, including true 3-dimensional graphs. Implemented in Reports Builder with the Oracle BI graph bean.
Report Block Wizard: enables you to add data to a static HTML page.
XML
Acronym for Extensible Markup Language. A metalanguage using SGML to define and structure data. Reports Builder supports XML output to enable Web publishing as well as electronic data exchange with third-party applications. You can also use XML to build report definitions that can be merged with other report definitions at runtime or run separately.
The steps in this section show you how to use the Report Block Wizard to create the layout and choose how your data will display in your report. Here, you will choose the style of report you want to create, and choose to display the data across the report (hence creating the across group report).
To create the default layout:
In the Object Navigator, double-click the Paper Layout node to display the Paper Layout view.
In the Paper Layout view, choose Insert > Report Block to display the Report Block Wizard.
In the Report Block Wizard, on the Style page, select Group Above, then click Next.
On the Groups page:
Click G_LAST_NAME in the Available Groups list, then click Across to specify the Print Direction and move this field to the Displayed Groups list.
Click G_DEPARTMENT_ID, then click Down.
Click Next.
On the Fields page, click the right arrow (>) to move the following fields from the Available Fields list to the Displayed Fields list, then click Next:
DEPARTMENT_ID
DEPARTMENT_NAME
LAST_NAME
FIRST_NAME
On the Labels page, click Next.
On the Template page, make sure Beige is selected under Predefined Template, then click Finish to display your report output in the Paper Design view. It should look like this:
Figure 12-4 Paper Layout view for the across group report
To build the example in this chapter, you must have access to the Order Entry sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
In this section, you will use the Vertical Spacing property to add space between each record in your mailing label report. You can adjust this spacing according to the size of the mailing labels where the records will be printed.
In the Object Navigator, under your report name, expand the Paper Layout node.
Under Paper Layout, expand the Main Section node.
Under Body, find the repeating frame called R_G_FIRST_NAME.
Double-click the repeating frame icon next to the R_G_FIRST_NAME node to display the Property Inspector.
Under Repeating Frame, next to Vert. Spacing Between Frames, type 0.25
.
Press Enter to add your changes.
In the toolbar, click Run Paper Layout to display your report in the Paper Design view.
Your report should look something like this:
Figure 6-3 Final mailing label report with vertical spacing
In this chapter, you will learn about reports that include aggregate data, based on value ranges. By following the steps in this chapter, you can generate the report output shown in Figure 30-1.
Concepts
In a report that aggregates, or collects, data within ranges, values from the database are retrieved and formatted based on an aggregate range that you define. You can even use parameters to specify the range over which the data should be collected.
Data Relationships
This report uses two "functions" in its SELECT
statement to specify the aggregate range. The functions are (FLOOR(SAL/1000))*1000)
, which calculates the lowest salary, and (CEIL*((SAL+1)/1000) * 1000)
, which calculates the highest salary. The columns that receive the values of these functions are placed into a break group to produce the control break format of this example report.
Layout
This report uses the Group Left layout style.
Example Scenario
In this example, you will build both a paper and JSP-based Web report that collects and displays names of all employees whose salaries fall within the range of 0 to 999, then collects and displays all employees whose salaries fall within the range of 1000 to 1999, and so on. You will be able to modify this report to display any aggregate range you need.
As you build this example report, you will:
Create a Query and the Layout using the Report Wizard to create a data model and layout for both the paper and Web reports.
Modify the Web Source of Your JSP Report to generate the JSP-based Web report.
To see a sample report that aggregates data within ranges, open the examples folder named aggregatingdata
, then open the Oracle Reports example named aggregatingdata.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
In this chapter, you will learn about reports that resets page numbers based on data shown. By following the steps in this chapter, you can generate the report output shown in Figure 19-1.
Figure 19-1 Pages renumbered by repeating frame report output
This example numbers pages using the format "Page X of Y". The first number (X) corresponds to the current page for each parent record (that is, each sales representative). This page number is reset to "1" for each sales representative, thus tracking the statistics of each representative separately.
The second number (Y) is the total pages required to display the customer information for each sales representative. This field is also reset with each new parent record.
Important Note: The steps in this chapter explain how to produce the "Page X of Y Pages" for the paper layout and have no effect on the Web source, because the Web source does not have pagination.
Concepts
The Page Numbering dialog box enables you to specify that the page number either reset at a particular repeating frame or increment over the entire report.
However, if you decide to use a page total that repeats on several pages, as with the second number in the example report, you need to be aware of certain rules that apply when formatting a field that contains a count of other objects. To be able to display such a value, called a page–dependent reference, on the first page—or any page prior to the final page—of the report, Reports Builder must format the field that will contain the value before it knows the value. As a result, any field displaying a value of this type must be fixed, so that Reports Builder knows how much space to reserve for it when formatting the report. Even if you set the Horizontal Elasticity property or Vertical Elasticity property to Expand, Contract, or Variable for such a field, Reports Builder considers it fixed. When you create such a field, you must ensure that it is large enough to contain the value.
An alternative is to hide the field and reference it from a boilerplate object. When using this method, the field can be variable, but the boilerplate remains fixed, so be sure to create a boilerplate object of sufficient size to hold the field.
Some values that are treated in this fashion by Reports Builder are &TOTAL LOGICAL PAGES, &TOTAL PANELS, and &TOTAL PHYSICAL PAGES, and any summaries or formulas that reference them. Summaries with either a Compute At property or Reset At property setting of Page and formulas that reference them are also considered page–dependent references. For a complete list of such values and a comprehensive discussion of Reports Builder's treatment of them, see the topics "Vertical Elasticity property" or "Horizontal Elasticity property" in the Oracle Reports online Help.
Data Relationships
As illustrated in Figure 19-1, this report contains both a master/detail relationship and a break. This results in two levels of data differentiation. The top level (the break) will determine when page numbers reset.
Layout
This report uses the master/detail layout style, with modifications. In addition, you will create two hidden fields for the page numbers, then reference them in a boilerplate object. This will ensure that the field displaying the total number of pages is the proper size. Then you will check the Maximum Records Per Page property setting for the break group's repeating frame to ensure that your pages are numbered correctly.
Example Scenario
In this example, you will create a page number in the margin of a report in the format "Page X of Y Pages". The page number X is incremented for each page produced by customer sales for one sales representative at a time. Y represents the total number of pages for the sales representative of that sales grouping and is reset for the next sales representative.
As you build this example report, you will:
Add a Second Query to group the output for each sales representative by customer.
Redefault the Layout to use the additional query data.
Create New Fields for the page numbers of the report.
Reference Fields to create the page numbers.
To see a sample report that renumbers pages by repeating frame, open the examples folder named pagenum
, then open the Oracle Reports example called pagenum.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
The steps in this section will show you how to build a simple data model and report layout in the Report Wizard, which you can then use to generate either a JSP-based Web report or a paper report. In the next section, you will modify the JSP so that the appropriate information displays in your Web report.
When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.
To create a data model and layout:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create both Web and Paper Layout, then click Next.
On the Style page, type a Title for your report, select Group Left, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT (FLOOR(SAL/1000))*1000 BOTTOM, CEIL((SAL+1)/1000) * 1000 TOP, ENAME, DEPTNO FROM EMP ORDER BY 1,2, SAL
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 30.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Groups page, click the following fields in the Available Fields list and click the right arrow (>) to move them to the Group Fields list, then click Next:
BOTTOM
TOP
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:
Figure 30-2 Paper Design view for the aggregating data report
In the Paper Design view, click the text Bottom and change the text to Salary Range
.
Delete the text Top.
Note: You can also modify the other column headers to make the text more meaningful. |
Click the Paper Layout button in the toolbar to display the Paper Layout view. The layout currently looks like this:
Figure 30-3 Paper Layout view of the Aggregating Data report
In the Paper Layout view, click the Flex Off button in the toolbar to set Flex mode off.
Click the right edge of the f_bottom frame and drag it to the left about 0.5 inches (1.5 centimeters) to make room between the f_bottom and f_top fields.
Tip: To find objects in the Paper Layout view, you can use the Object Navigator. When you click an item name in the Object Navigator, the corresponding object is selected in the Paper Layout view. |
Click the Text tool in the tool palette.
Drag a boilerplate text object between f_bottom and f_top, then type "-
" in the text box. The layout should now look like this:
Figure 30-4 Paper Layout view with new boilerplate text
Now, click the Run Paper Layout button in the toolbar to display your report. It should look something like this.
Note: Notice how the report displays the employee names per salary range, hence aggregating the data. |
Save your report as aggregatereport_
your_initials
.rdf
.
Take a look at the data model of your report. You can use this data model to generate either a paper report or a JSP-based Web report. To view the data model, click the Data Model button in the toolbar. Your data model should look something like this:
Figure 30-6 Data Model for the aggregating data report
In this section, you will import the barcode JavaBean, then create a paper-based report that shows the invoice for a particular customer. This invoice will display the address of the customer, his order, and a barcode that represents the tracking number for the order. The company can scan this barcode to find out the status of the order.
To create a paper report using the barcode JavaBean, you must first import two Java classes into Reports Builder. When you import these Java classes, Reports Builder automatically creates the packages you need to build the report.
Note: You do not need to perform this task if you are creating a Web report, as you will write JSP code that calls the JavaBean. |
To import the Java classes:
Launch Reports Builder.
Note: You must launch Reports Builder now so that the newREPORTS_CLASSPATH is used. |
Close the Welcome dialog box by clicking Cancel.
Choose Program > Import Java Classes to display the Import Java Classes dialog box.
Under Select Java Classes, navigate to:
oracle.apps.barcode.util.BarCodeConstants
Note: If you do not see this class listed, try exiting Reports Builder and make sure theREPORTS_CLASSPATH reads correctly. Then, launch Reports Builder again. |
Select the class, then click Import.
Once the packages have been created, import the second JavaBean: oracle.apps.barcode.BarCodeMaker
.
Click Close.
In the Object Navigator, under the report named MODULE 1, click the Program Units node. You will notice that Reports Builder created two package specifications and two package bodies named BARCODECONSTANTS and BARCODEMAKER.
In this report, you want to create a package where the information will be stored.
To create a package for storing your information:
In the Object Navigator, under your new report, click the Program Units node.
Click the Create button in the toolbar to display the New Program Unit dialog box.
In the New Program Unit dialog box, type globals
.
Select Package Spec, then click OK to display the PL/SQL Editor:
In the PL/SQL Editor, type the following code:
PACKAGE globals IS bcobj ora_java.jobject; barcode_to_use varchar2(256); tempdir varchar2(100); directory_sep varchar2(2); END;
Note: You can also enter this code by copying and pasting it from the provided text file calledbarcode_code.txt . |
Click Compile to make sure there are no errors in your code.
Note: If your code does not compile, make sure you have typed in exactly the code we have provided. |
Once the code is compiled, click Close.
In the Object Navigator, click your report name (for example, MODULE 1).
Save the report as shippingmanifest_
your_initials
.rdf
.
You have created a package that will contain the global information for your report.
You can use a Before Report trigger to perform specific tasks before the report is run. Here, you will define the type of barcode you want to use in your report, as well as the temporary directory where your barcode images will be stored.
To create a Before Report trigger:
In the Object Navigator, under SHIPPINGMANIFEST_your_initials
, expand the Report Triggers node, then double-click the icon next to BEFORE REPORT to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function BeforeReport return boolean is begin globals.barcode_to_use := BarCodeConstants.BAR_CODE_128; globals.bcobj := barcodemaker.new(); return (TRUE); end;
To modify the type of barcode you want to use, you can change the value BarCodeConstants.BAR_CODE_128
to any other valid value. To determine which values are valid, check the contents of the package by opening the BarCodeConstants package spec in the Object Navigator, under the Program Units node.
Note: You can enter this code by copying and pasting it from the provided text file calledbarcode_code.txt . |
Click Compile to make sure there are no errors.
Note: If you have errors, make sure you have imported the necessary Java classes and compare your code closely with the code above. If you change the code, be sure to compile it again. |
When the code is compiled, click Close. Notice how the node icon next to the BEFORE REPORT trigger has changed.
Save your report.
You have created a trigger that will set up the barcode type for you when you run the report.
In this section, you will manually create the query that the report will use to retrieve data from the sample schema.
To create the query:
In the Object Navigator, under SHIPPINGMANIFEST_your_initials
, double-click the view icon next to the Data Model node to display the Data Model view for your report.
In the Data Model view, click the SQL Query tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following SELECT
statement:
SELECT ALL CUSTOMERS_A1.CUST_FIRST_NAME, CUSTOMERS_A1.CUSTOMER_ID, CUSTOMERS_A1.CUST_LAST_NAME, CUSTOMERS_A1.CUST_ADDRESS.STREET_ADDRESS, CUSTOMERS_A1.CUST_ADDRESS.POSTAL_CODE, CUSTOMERS_A1.CUST_ADDRESS.CITY, CUSTOMERS_A1.CUST_ADDRESS.STATE_PROVINCE, CUSTOMERS_A1.CUST_ADDRESS.COUNTRY_ID, ORDERS.ORDER_ID, ORDERS.ORDER_DATE, ORDERS.ORDER_TOTAL, ORDER_ITEMS.LINE_ITEM_ID, PRODUCTS.PRODUCT_NAME, ORDER_ITEMS.UNIT_PRICE, ORDER_ITEMS.QUANTITY, COUNTRIES.COUNTRY_NAME FROM CUSTOMERS CUSTOMERS_A1, ORDER_ITEMS, ORDERS, PRODUCTS, HR.COUNTRIES WHERE ((ORDER_ITEMS.ORDER_ID = ORDERS.ORDER_ID) AND (ORDERS.CUSTOMER_ID = CUSTOMERS_A1.CUSTOMER_ID) AND (ORDER_ITEMS.PRODUCT_ID = PRODUCTS.PRODUCT_ID) AND (CUSTOMERS_A1.CUST_ADDRESS.COUNTRY_ID = HR.COUNTRIES.COUNTRY_ID)) AND ORDERS.ORDER_ID = :P_ORDER_ID ORDER BY order_ID, line_item_ID
Note: You can enter this query in any of the following ways:
|
Click OK.
If you are not connected to a database that contains the sample schema we have provided, you must log in now. If you are not sure what your connection string is, contact your database administrator. Note that this example uses the Order Entry sample schema.
When a message displays indicating that the bind parameter p_order_id
was created, click OK.
In the data model you just created, select all of the following columns using Shift-click, then drag them below the current query to create a detail group:
LINE_ITEM_ID
PRODUCT_NAME
UNIT_PRICE
QUANTITY
The resulting data model should look like this:
In this section, you will create a formula column that will communicate with the JavaBean to create the barcode, then return the file name of the generated image.
To create a formula column:
In the Data Model view, click the Formula Column tool in the tool palette.
Click in the master group (G_CUST_FIRST_NAME) to create a new formula column.
Double-click the new formula column object (CF_1) to display the Property Inspector, and set the following properties:
Under General Information, set the Name property to ImageFilename.
Under Column:
Set the Datatype property to Character
Set the Width property to 500
Set the Read from File property to Yes
Set the File Format property to Image
Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function ImageFilenameFormula return VarChar2 is myFileName varchar2(500); result varchar2(500); barcodeData VarChar2(50) := :customer_ID || :order_ID; begin myFileName := srw.create_temporary_filename; barcodemaker.setBarWidthInch(globals.bcobj, 0.005); barcodemaker.setBaseCodeData(globals.bcobj,barcodeData); barcodemaker.setBarCodeType(globals.bcobj,globals.barcode_to_use); barcodemaker.setFullPath(globals.bcobj, myFileName); barcodemaker.renderBarCode(globals.bcobj); return(myFileName); end;
Note: You can enter this code by copying and pasting it from the provided text file calledbarcode_code.txt . |
Click Compile to make sure there are no errors.
Note: If you have errors, make sure you have imported the necessary Java classes and compare your code closely with the code above. If you change the code, be sure to compile it again. |
When the code is compiled, click Close.
To create the second formula column:
In the Data Model view, create a formula column in the detail group G_LINE_ITEM_ID.
Open the Property Inspector for the formula column, and set the following properties:
Under General Information, set the Name property to LineTotal.
Under Column, make sure the Datatype property is set to Number.
Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function LineTotalFormula return Number is begin return (:quantity * :unit_price); end;
Note: You can enter this code by copying and pasting it from the provided text file calledbarcode_code.txt . |
Click Compile to make sure there are no errors.
When the code is compiled, click Close.
Save the report.
You have created the data model for your barcode report, which contains a formula column that retrieves the barcode information and displays the barcode image on your report, and another formula column that displays the order total.
Your data model and the PL/SQL for the formula column should look similar to this:
Figure 43-3 Data Model with two new formula columns
Before you can run your report, you must create a layout.
To create a paper layout:
Under your report's node in the Object Navigator, right-click Paper Layout, then choose Report Wizard.
In the Report Wizard, on the Report Type page, select Create Paper Layout Only, then click Next.
On the Style page, select Group Above, then click Next.
On the Data Source page, click Next.
On the Data page, click Next.
On the Groups page, make sure the following fields are listed in the Group Fields list (if not, use the arrows to move the field to the appropriate list):
ORDER_ID
ORDER_DATE
CUSTOMER_ID
CUST_FIRST_NAME
CUST_LAST_NAME
STREET_ADDRESSS
POSTAL_CODE
COUNTRY_NAME
CITY
STATE_PROVINCE
COUNTRY_ID
ORDER_TOTAL
CF_1
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Finish.
In the Paper Layout view, click the Run Paper Layout button in the toolbar to run your report.
In the Runtime Parameter Form, next to P_ORDER_ID, type 2354
.
Once your report displays in the Paper Design view, you can rearrange your layout objects in the Paper Layout view to make your report look something like Figure 43-4. To create this format:
In the Paper Layout view, remove the surrounding parent frame M_G_CUST_FIRST_NAME_GRPFR
, then click the Confine Off button and the Flex Off button in the toolbar.
Using the shippingmanifest.rdf
example report as a guide, add three rectangles and three text objects for:
Shipping Details: Set text properties to Arial, 16, bold. Create a frame to add the following fields: cust_first_name, cust_last_name, street_address, city, state_province, postal, country_name.
Tracking Details: Set text properties to Arial, 16, bold. Move the ImageFilename object onto this rectangle.
Order Details: Set text properties to Arial, 16, bold. Move the labels and fields for Order ID and Order Date onto this rectangle. Move the field F_Order_Total onto this rectangle and set text properties to Courier New, 24, bold.
Set the text properties for all other field values to Courier New, 10. Additionally, set F_Line_Total to bold.
Format numeric field values to display currency, decimals, and right align them.
Set Fill Color for each rectangle, totals field, and table header as desired.
Figure 43-4 Paper Design view of the barcode paper report
Note: If you are not sure whether you produced the desired results, you can always open the example report calledShippingManifest.pdf in Acrobat Reader. Or, you can run ShippingManifest.rdf to paper and the report will display in the Paper Design view. |
Save the report. You have now finished building a barcode report for paper.
To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.
To create a data model and group above layout:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout Only, then click Next.
On the Style page, type a Title for your report, select Group Above, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT DEPT.DNAME, EMP.ENAME, EMP.SAL FROM DEPT, EMP WHERE (EMP.DEPTNO = DEPT.DEPTNO)
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 20.1, "Prerequisites for This example" describes the sample schema requirements for this example. |
On the Groups page, click DNAME in the Available Fields list and click the right arrow (>) to move this field to the Group Fields list.
Click Next.
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, change the labels as follows, then click Next:
Fields | Labels |
---|---|
DNAME | Dept. |
ENAME | Name |
SAL | Salary |
On the Template page, click Finish to display your report output in the Paper Design view. It should look something like this:
Figure 20-3 Paper Design view for intermixing fields report
Save the report as intermix_
your_initials
.rdf
.
In this chapter, you will learn about reports that show data based on calculations over a period of time. By following the steps in this chapter, you can generate the report output shown in Figure 28-1.
Figure 28-1 Time series calculations report output
Concepts
Reports with time series calculations calculate values over a specified period of time. The techniques described for this report can be used to produce other formats of time series calculations, as well.
Data Relationships
This time series calculations report uses a query that will compute four-month moving averages of customer purchases. The SELECT
statement will sum the current purchase (TOTAL
) with purchases made by that customer in the previous four months, then average that sum through use of a self-join. For example, if the data queried is 30-JUL-00, Oracle Reports will average all purchases the customer made between 30 MAR-00 and 30-JUL-00.
Example Scenario
In this example, you will use the Report Wizard to create a simple time series calculations report for both paper and the Web that calculates and displays the four-month average of purchases for each customer. For the JSP-based Web report, you will modify the Web source to change labels and add format masks.
As you build this example report, you will:
Create a Query and the Layout using the Report Wizard to create a data model and layout for both the paper and Web reports.
Modify the Web Source of Your JSP Report to generate the JSP-based Web report.
To see a sample time series calculations report, open the examples folder named timeseries
, then open the Oracle Reports example called timeseries.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface.
This section provides procedures for the following tasks that you may perform as you work with the Object Navigator:
See also
Section 1.5.1, "About the Object Navigator"
To display a Report Editor view from the Object Navigator, do any of the following:
Double-click the icon next to the node of the view you want to display. For example, to display the Paper Layout view, double-click the view icon next to the Paper Layout node.
Click the node, then click the right mouse button and choose Report Editor.
Click the node, then choose Tools > Report Editor.
Note: In the Object Navigator, there is no Paper Design node; the Paper Design view is displayed only after running a report. |
See also
Section 1.6.2, "About the Data Model view"
Section 1.6.3, "About the Paper Layout view"
To expand or collapse a node one level, do either of the following:
In the Object Navigator, click the plus or minus sign next to the node.
Click the node, then click the Expand or Collapse tool in the tool palette.
To fully expand or collapse a node, do either of the following:
In the Object Navigator, click the plus or minus sign next to the node repeatedly until fully expanded.
Click the node, then click the Expand All or Collapse All tool in the tool palette.
Click the node, then choose View > Expand All or View > Collapse All.
To search for a specific node in the Object Navigator:
Type the full of partial name of the node in the Find field.
To search for the next match, click the Find Next button in the toolbar.
To search for the previous match, click the Find Previous button in the toolbar.
To view objects in the Object Navigator by object hierarchy to see the parent-child relationships:
To view objects in the Object Navigator by their type such as all queries under a single heading:
See also
See Section 4.2.4, "Setting preferences for the Object Navigator".
A matrix report displays a juxtaposition of data – in other words, the values held in common by two different categories of data. These categories are indicated by the row and column labels.
The matrix displays this juxtaposition of values using a grid-like format. If the two categories have nothing in common, the grid at that point is empty. The matrix appears to be full of "holes". You can fill the holes using boilerplate text.
Note: Do not confuse empty spaces in the grid with null values. A null value is an actual value fetched from the database. The spaces in a matrix report are empty because nothing has been fetched to fill them. |
To replace blanks with zeroes:
Open the matrix report to which you previously added the summaries.
In the Object Navigator, double-click the view icon next to the Paper Layout node to display the Paper Layout view.
Tip: The steps that follow require some precision in the placement of objects. Hence, you may want to magnify the view to make the process easier. Click the Magnify tool and then click somewhere in the Paper Layout view. Repeat as many times as necessary. |
In the Paper Layout view, click the Confine On and Flex On buttons in the toolbar to turn both modes on.
From the font lists in the toolbar, choose Arial (Western), point size 10.
Click the Align Right button in the toolbar.
Click the Text tool in the tool palette.
Click the top of the F_SUM_SAL field about 0.75 inches (2 centimeters) from its right edge. Your objective is to create an object right on top of F_SUM_SAL.
Type the following:
$0.00
Click in an open area of the layout to deselect all objects.
In the Object Navigator, type B_1
in the Find field. You will be taken to the object you just created. If you are viewing the Object Navigator in Ownership View (View > Change View > Ownership View), you should see B_1 underneath R_G_SUM_SAL and on the same level as F_SUM_SAL.
Tip: If B_1 does not appear underneath R_G_SUM_SAL, return to the Paper Layout view, delete B_1 and try again. |
Figure 25-10 Object Navigator with new object selected
Click R_G_SUM_SAL and then ctrl-click F_SUM_SAL so that they are both selected and B_1 is deselected.
Figure 25-11 Object Navigator with repeating frame and field selected
Click the title bar of the Report Editor to make it the active window.
Click the Confine Off button in the toolbar to turn Confine mode off.
Choose Layout > Move Forward.
Tip: After this operation, B_1 should appear just above R_G_SUM_SAL, as a peer rather than a child of R_G_SUM_SAL. If B_1 is still appearing as a child under R_G_SUM_SAL, repeat steps 11 through 14 until it is no longer appearing as a child of R_G_SUM_SAL. |
In the Paper Layout view, click the Confine On button in the toolbar to turn Confine mode back on again.
Click the Paper Design button in the toolbar to display the Paper Design view.
Tip: If the $0.00 is not quite aligning with the other monetary values around it, select it and use the arrow keys to move it to the desired location. |
Figure 25-12 Matrix report output with zeroes replacing blanks
You have successfully created and executed the report. Next, you have to change the font to a UTF8 multicharacter set TrueType font to see the characters of the language chosen in the report output.
To change the font to a multicharacter TrueType font:
In the Paper Design view, Shift-click the values below the Translated Name and Translated Description labels to select all the values in these two columns. You can now change their properties simultaneously.
From the font box in the toolbar, choose Arial Unicode MS.
The report is executed and the Paper Design view of the report output is displayed in Japanese characters. It should look something like this:
Figure 38-5 Paper design view of the report with Japanese characters
To run the same report in another language:
To run the same report again with a different runtime parameter and see the output in another language, perform the following steps:
Click the Run Paper Layout button in the toolbar, or choose Program > Run Paper Layout.
In the Runtime Parameter Form, type the value PL
, and click the Run Report button to run the report.
The Paper Design view of the report output is displayed with Polish characters. It should look something like this:
Figure 38-6 Paper design view of the report with Polish characters
The steps in this section will show you how to create a basic table of contents for a large, multipage report. The table of contents will look like the following:
The steps in this section will show you how to create a table in the database that will hold the page numbers for the records you want to list in the table of contents. If you are not sure whether you have the privileges to create a table in the database, contact the database administrator.
To create a table in the database:
Start SQL*Plus.
On Windows, from the Start menu, choose Programs > Oracle Application Server - oracle_home_name> Application Development > SQL Plus.
On UNIX, type sqlplus
.
Connect to the Sales History schema of the database (for example, use the sh/sh@
database_name connect string).
At the SQL prompt, type the following line:
create table toc_example (topic varchar2(100), page number);
Press Enter.
You should see a notification that the table has been created.
Exit SQL*Plus.
The steps in this section will show you how to build a simple group above report in Reports Builder. This group above report will display the country name, then the customers and customer e-mail addresses under each country name. The table of contents you create will then be based on the country name in this report.
To create a simple group above report:
In Reports Builder, choose File > New > Report, then choose to create the report manually.
In the Data Model view that displays, right-click on the canvas, then choose Report Wizard from the pop-up menu.
On the Report Type page, select Create Paper Layout only, then click Next.
On the Style page, select Group Above, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT ALL COUNTRIES.COUNTRY_NAME, CUSTOMERS.CUST_LAST_NAME, CUSTOMERS.CUST_FIRST_NAME, CUSTOMERS.CUST_email FROM CUSTOMERS, COUNTRIES WHERE (CUSTOMERS.COUNTRY_ID = COUNTRIES.COUNTRY_ID)
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 35.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Groups page, click COUNTRY_NAME, then click > to move the field to the Group Fields list.
Click Next.
On the Fields page, click >> to move all fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, click Finish.
In the toolbar, click the Data Model view button. The data model should look like this:
Figure 35-4 Data model for the group above report
In the toolbar, click the Paper Layout view button. The Paper Layout view should look like this:
Figure 35-5 Paper Layout view of the group above report
Save your report as toc_
your_initials
.rdf
. You have now created the data model and the basic layout for your report.
The steps in this section will show you how to create two triggers. The first trigger will use the SRW.SET_FORMAT_ORDER built-in procedure to format the Main section of the report first, then the Header and Trailer sections. The second trigger will fetch the number of the page on which each country name displays, and place that information into the table you created in Section 35.2.1, "Create a table in the database to hold the TOC data".
An After Parameter Form trigger is a function that executes after the parameter form is executed. In this section, you will set the trigger to change the order of the report execution, so that the Main section runs first.
To create an After Parameter Form trigger:
In the Object Navigator, under your report name (TOC_
your_initials
), expand the Report Triggers node.
Right-click AFTER PARAMETER FORM, then choose PL/SQL Editor from the pop-up menu.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
function AfterPForm return boolean is begin srw.set_format_order(srw.main_section, srw.header_section, srw.trailer_ section); return (TRUE); end;
Note: You can also copy and paste the code from the provided file,toc_index_code.txt . |
Click Compile.
Once the code has compiled, click Close.
In this section, you will create a format trigger based on the field F_COUNTRY_NAME
. This field displays the country name. This format trigger will fetch the page number for each country name, so that the table of contents will enable the user to navigate to various parts of the report based on the name of the country.
To create a format trigger:
In the Object Navigator, under the Paper Layout node for your report, navigate to Main Section > Body > M_G_1_GRPFR > R_G_1, then click F_COUNTRY_NAME.
Tip: If you cannot find this field, use the Find field at the top of the Object Navigator. |
Choose Tools > Property Inspector to display the Property Inspector for this field.
Under Advanced Layout, double-click the Format Trigger property field to display the PL/SQL Editor.
In the PL/SQL Editor, type the following code:
function F_COUNTRY_NAMEFormatTrigger return boolean is pageNum number; begin -- get current page number srw.get_page_num(pageNum); -- insert row into table insert into toc_example values (:country_name, pageNum); return (TRUE); end;
Note: You can also copy and paste this code from the provided file,toc_index_code.txt . |
Click Compile.
Once the code is compiled, click Close, then close the Property Inspector.
Save your report.
The steps in this section will show you how to create a second query in the data model with a formula column that calculates the page range for the data under each country name. This query will fetch the information from the database table you created in Section 35.2.1, "Create a table in the database to hold the TOC data". You will later create a tabular layout to display this information in your table of contents.
To create a second query with a formula column:
In the Data Model view, click the SQL Query tool in the tool palette then click in an open area of the Data Model view, somewhere to the right of query Q_1, to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following SELECT
statement:
SELECT TOPIC, MIN(PAGE) FROMPAGE, MAX(PAGE) TOPAGE FROM TOC_EXAMPLE GROUP BY TOPIC
Note: You can enter this query in any of the following ways:
|
Click OK. Your data model should now look like this:
In the Data Model view, click the Formula Column tool in the tool palette, then click in the group (G_TOPIC) for the second query.
While the formula column is selected, choose Tools > Property Inspector to display its Property Inspector.
Under General Information, set the Name property to TOC_pages
, and the Datatype property to Character.
Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following code:
function TOC_pagesFormula return Char is begin if :fromPage = :toPage then return (:fromPage); else return (:fromPage ||'-'|| :toPage); end if; end;
Note: You can also copy and paste this code from the provided file, calledtoc_index_code.txt . |
Click Compile.
Once the code is compiled, click Close.
Save the report.
The steps in this section will show you how to create a tabular report block in the Header section of your report. This report block will display the headings in the table of contents (in this example, the country name), and the page range where the information can be found in the report.
To create a tabular report block in the Header section:
In the Paper Layout view, click the Header Section button in the toolbar.
Click the Report Block tool in the tool palette, then draw an area about about 5 inches (12.5 centimeters) wide and 1.5 inches (4 centimeters) high in the Paper Layout view. When you release the mouse button, the Report Block Wizard displays.
On the Style page, select Tabular, then click Next.
On the Groups page, click G_TOPIC and click Down to move it to the Displayed Groups list, then click Next.
On the Fields page, click each of the following fields in the Available Fields list, then click the right arrow (>) to move them to the Displayed Fields list, then click Next:
TOPIC
TOC_pages
On the Labels page, click Next.
On the Template page, click Finish.
Your report block displays in the Paper Layout view:
Figure 35-7 Paper Layout view of the Header Section
Save your report.
To run your report to paper:
Click the Run Paper Layout button in the toolbar.
Note: To generate the table of contents (TOC), you must click the Run Paper Layout button. If you click the Paper Design view button, the change of format order will not take effect, thus the TOC will not be generated. |
The table of contents displays on the first page of the report, and looks something like the following:
Figure 35-8 Table of contents page of the report
You can navigate to various pages in your report to view the data for each country. For example, when you navigate to page 1, you will see the e-mail addresses for the customers in Argentina:
Figure 35-9 Results for customers in Argentina
Note: At this point, you can compare your report against the example file we have provided,toc.rdf . First, compile the PL/SQL by choosing Program > Compile > All, then run the report to paper. |
For information on creating a multilevel table of contents, see Chapter 36, "Building a Paper Report with a Multilevel Table of Contents".
To build the example in this chapter, you must have access to the Summit Sporting Goods (SUMMIT) schema. To download the SQL scripts that install the schema, see "Accessing the Data Sources" in the Preface. Only the EMP, CUSTOMER, and SALES tables are required.
The HTML view of the report that displays the page number technique must be deployed through a report server using the report in JSP or RDF format and HTML as the destination format. For example:
http://computer_host:port/reports/rwservlet?report=pgnum.rdf
&userid=scott/tiger@my_database&destype=cache&desformat=html&server=rep_server
Now that you have created your paper report layout, you will now learn how to take the same report and generate a JSP-based Web report that looks the same as your paper report.
To modify your JSP-based Web report:
Save your report, aggregatereport_
your_initials
.rdf
as a JSP under the same name (aggregatereport_
your_initials
.jsp
).
Click the Web Source button in the toolbar to display the Web Source view.
In the Web Source, you need to change the column titles to match those of the paper report.
In the Web Source view, find the text:
<th <rw:id id="HBbottom" asArray="no"/> class="OraColumnHeader"> Bottom </th>
Tip: Choose Edit > Find and Replace, then type "bottom " in the Find what text box. |
Change the column header to "Salary
" so that the line now looks like this:
<th <rw:id id="HBbottom" asArray="no"/> class="OraColumnHeader"> Salary </th>
The next line of code indicates the header for the "Top
" column. Delete the header text so that the line looks like this:
<th <rw:id id="HBtop" asArray="no"/> class="OraColumnHeader"> </th>
Change the other two columns for Ename and Deptno as desired. For example, we changed them to "Name
" and "Dept
", like so:
<th <rw:id id="HBename" asArray="no"/> class="OraColumnHeader"> Name </th> <th <rw:id id="HBdeptno" asArray="no"/> class="OraColumnHeader"> Dept </th>
Save your report as a JSP.
Click the Run Web Layout button in the toolbar to display your new JSP-based Web Report in your browser.
Note: If Netscape 7.0 is your default browser, and the browser does not display, set the registry keyHKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser to the default browser location. Ensure that the BrowserName and the BrowserPath keys reflect the correct values. For example: BrowserName=Netscape 7 ; BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe . |
The report should look something like this:
Figure 30-7 Aggregating Data JSP-based Web Report
The report displays, in Web format, the aggregate data you specified. Here, you can see which employees fall into the specified salary ranges.
Note: For information on creating a Parameter Form for a JSP-based Web report, refer to the documented example on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html ), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Examples, and click Search. In the list of examples that displays, click Building a JSP-based Web Report with a Parameter Form. |
Congratulations! You have successfully created a report with a multilevel table of contents. You now know how to:
create a multilevel table of contents for a large paper report.
Use the SRW.SET_FORMAT_ORDER built-in procedure to change the order in which the report sections are formatted.
create a format trigger that fetches the page numbers for categories and subcategories that you specify.
use the Report Block Wizard to create a simple group above report layout to display your table of contents.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
Congratulations! You have successfully built a report that highlights specified data in the report output. You now know how to:
use the Report Wizard to create a simple tabular report.
format the appearance of your report using tools in the Paper Design view.
use the Conditional Formatting dialog box and the Format Exception dialog box to create format triggers that highlight certain data in your report output.
examine the code automatically generated by Reports Builder.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
In this chapter, you will learn about reports that suppress the display of field labels. By following the steps in this chapter, you can generate the report output shown in Figure 21-1. Notice how the field labels for Department 40 do not display because no detail records were found
Figure 21-1 Suppressed labels report output
Concepts
A default master/detail report must print a detail label or field, even if there are neither fetched detail records nor values for user-created columns. This example demonstrates how to suppress the detail information for a single record, but allow the other master/detail records to display.
Data Relationships
This report uses a master/detail data model. You will also create a formula column in the detail group whose sole function is to return a value; this ensures the detail group contains at least one column that will always return a value each time a detail record is returned (as opposed to, for example, a null value).
You will create a summary to count the number of times this formula column returns a value. In the layout, you will create a format trigger that references the summary to determine if the detail labels should be displayed. Doing so provides a reliable method for determining the existence of detail records.
Layout
This report uses a Group Above layout style with modifications.
Example Scenario
In this example, you will build a report that does not display field labels when there are no detail records. Instead, text displays that notifies the user that no detail records were retrieved.
As you build this example report, you will:
Create the Data Model with Two Linked Queries manually in the Data Model view.
Create a Formula Column and a Summary Column in the Data Model view.
Create the report layout using the Report Wizard.
Add a Format Trigger to Suppress Labels when no detail records are retrieved.
To see a sample report that suppresses labels, open the examples folder named suppresslabels
, then open the Oracle Reports example named suppresslabels.rdf.
For details on how to open it, see "Accessing the Example Reports" in the Preface.
Congratulations! You have successfully created a paper report and modified the text color and fill colors, as well as added graphics. You now know how to:
use the Report Wizard to create a simple report definition.
modify the layout of the objects in your report.
add a border to the report.
add bullets to each record.
add a horizontal separator between each record.
add a title to your report using fill colors and layout features.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
This manual will help you learn about the extensive capabilities of Reports Builder (the report-building component of Oracle Reports), how to build different types of reports, and how to customize your reports to meet a wide variety of requirements.
This manual is intended for anyone who uses Oracle Reports to build reports. The needs of both novice and advanced users are addressed. Following the step-by-step instructions, you can build the example reports from start to finish. Each report that you build will demonstrate how to use many of the powerful features in Reports Builder.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/us/corporate/accessibility/index.html
.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/support/contact.html
or visit http://www.oracle.com/accessibility/support.html
if you are hearing impaired.
For more information about Oracle Reports, refer to the following resources:
Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services
Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
The following text conventions are used in this document:
Convention | Meaning |
---|---|
boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
monospce italic | Monospace italic type indicates variables or user-supplied names. |
[ ] | Brackets enclose optional clauses from which you can choose one or none. |
The supporting files for the reports you will build are available on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html
), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Topic list, choose Building and Developing Reports; in the Collateral Type list, choose Examples, then click Search. In the list of topics that displays, find Building Reports sample files and click the Download icon to download a zip file, called buildingreports_examples.zip
. This zip file contains subfolders that correspond to the names of each chapter. Each of the subfolders contains all the supporting files (example reports, images, SQL query code, and so on) necessary for building the individual reports. Extract this zip file into ORACLE_HOME
\examples
(for example, D:\orawin\examples
).
Additionally, the remaining examples in Getting Started with Oracle Reports provide a Web-based selection of examples in this manual, as well as examples not covered in this manual.
The data sources used in this manual are:
The SCOTT, Human Resources, Sales History, and Order Entry schemas are available with the Oracle Database. You can contact your database administrator for more information on where to find these schemas. You can download the SQL scripts to install the Summit Sporting Goods (SUMMIT) schema from the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html
), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Topic list, choose Building and Developing Reports; in the Collateral Type list, choose Tools/Utilities, then click Search. In the list of tools and utilities that displays, click the Download icon to download the SQL scripts to install the SUMMIT sample schema.
Congratulations! You have successfully created a single-query group report. You now know how to:
create a data model and layout with the Report Wizard.
format fields in the Paper Design view.
add white space between records with the Property Inspector.
add another column to the break group and redefault the layout.
add another break group and redefault the layout.
For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Oracle Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 4.1.1, "Using the Oracle Reports online Help".
The chapters in this Part provide steps to build reports that are intended for specific business purposes. The examples provided include computing values over specified periods of time, collecting data over a specified range, and grading data. Following the steps, you will learn how to print reports on preprinted forms (for checks or invoices), split a report into sections and distribute each section to a different destination, and include a simple table or contents and index, or a multilevel table of contents.
A time series calculations report computes values over a specified period of time. You can use the techniques used for this report to produce other formats of time series calculations. For example, you can build a report that calculates and displays the quarterly average of purchases for each customer.
A paper report generated to spreadsheet output can be displayed in your browser. You can integrate the paper layout into a Web layout by modifying the Web source of your report, and then generate the output. You can then deploy this report with the Reports Server under Oracle WebLogic Server and display it in a spreadsheet utility (such as Microsoft Excel) inside your browser.
A report with aggregate data collects data within a range, retrieves values from the database, and formats them based on an aggregate range defined. You can use parameters to specify the range over which the data should be collected.
A check printing report contains a stub and spelled-out cash amounts. You can create a PL/SQL function that returns spelled-out numerical values, imports an image of a preprinted form (such as a blank check image), and prints your report on the form.
A report using a preprinted form enables you to use formatting techniques to print reports on preprinted forms when you do not have access to a computer readable version of the forms. Such reports must be designed so that the data prints in the exact positions on the form.
An invoice report displays several distinguishing characteristics of a typical invoice, such as customer name and address, sales order number, billing information, and billing totals. You can import an image and print your report on a preprinted form.
A ranking report grades data in two different ways: by count and by percentage. You can set the ranking criteria at runtime or let the criteria default to previously specified values.
Navigational tools, such as a simple table of contents and an index, can be added to a paper report. First, the page numbering must be determined for the entire report, then Oracle Reports Builder can generate the table of contents and index based on the established page numbering.
A multilevel table of contents can be added to a paper report to facilitate navigation. This table of contents is created similarly to that in Chapter 35, "Building a Paper Report with a Simple Table of Contents and Index", however with a hierarchy that categorizes the information in the table of contents.
A report built for bursting and distributing a report enables you to simultaneously deliver a single report to multiple destinations. You can create a single report, burst each section to a separate report, and then send each section in any format (such as, PDF or HTML) to multiple destinations.
A multibyte character report can display text in virtually any language. You can produce reports that display text in multibyte character sets, such as Simplified Chinese, Traditional Chinese, Japanese, Korean, and Unicode.
The topics in this section discuss the features and functionality in Oracle Reports Builder.
Oracle Reports Builder is the report-building component of Oracle Reports, a powerful enterprise reporting tool that enables you to rapidly develop and deploy sophisticated Web and paper reports against any data source (including an Oracle database, JDBC, XML, and text files). Leveraging Java EE technologies such as JSP and XML, you can publish your reports in a variety of formats (including HTML, XML, PDF, spreadsheet, delimited text, PostScript, and RTF) to any destination (including e-mail, Web browser, Oracle Portal, and file system) in a scalable, efficient manner. Recognizing the differences between Web publishing and paper publishing, Oracle Reports Builder provides the power to develop high quality output for the Web and e-business requirements, as well as high-fidelity printed reports Oracle Reports Builder includes:
user-friendly wizards that guide you through the report design process
pluggable data sources (PDSs), such as JDBC and XML, that provide access to data from any source for your reports
a query builder with a graphical representation of the SQL statement to obtain report data
default report templates and layout styles that can be customized if needed
a live editor that enables you to modify paper report layouts in WYSIWYG mode
the ability to add dynamic report output to an HTML page by embedding custom JSP tags within an HTML document
an integrated graph builder to graphically represent report data
the ability to generate code to customize how reports will run
tools that dynamically generate Web pages based on your data
standard report output formats such as HTML, HTMLCSS, XML, PDF, RTF, Spreadsheet, PCL, PostScript, and ASCII
client-side parameter validation using JavaScript
the ability to execute dynamic SQL statements within PL/SQL procedures
support for Oracle database objects
event-based reporting (report execution based on database events)
seamless integration with Oracle Portal for administering report security and publishing report output to portlets
Oracle Reports 11g Release 1 (11.1.1) provides many new features and enhancements. The primary themes of this release are:
Tighter integration with Oracle Fusion Middleware Platform and Services to provide simplified administration of complex topologies and deployments.
Enhancements in top supportability areas with architectural changes.
New features in key enterprise reporting areas.
The chapter titled "Introduction" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual, available on the Oracle Technology Network details the new functionality in Oracle Reports 11g Release 1 (11.1.1), along with the equivalent 10g Release 2 (10.1.2) functionality, when applicable.
For additional information, see the Oracle Reports 11g page on the Oracle Technology Network.
In this section, you will learn how to modify an HTML Parameter Form in Reports Builder to populate the list of values (LOV) you created with values from a data source. You will use JSP tags for Oracle Reports to enable the Parameter Form to access elements from a data model.
The steps in this section will show you how to create a simple data model for the Parameter Form.
To create a data model:
In Reports Builder, open the HTML file you created, paramform_
your_initials
.html
.
In the Object Navigator, double-click the icon next to the Data Model node to display the Data Model view.
In the Data Model view that displays, click the SQL Query tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.
In the SQL Query Statement field, enter the following SELECT
statement:
select department_name, department_id from departments order by department_name
Note: You you can enter this query in any of the following ways:
|
Click Connect, then type the connection information for the Human Resources sample schema (HR).
Click OK. Your data model should look like this:
Figure 42-4 Data Model view of the Parameter Form
Save the report as paramform_
your_initials
.jsp
.
In this section, you will learn how to modify the Web source to pull data into the existing list of values (LOV) in your Parameter Form. This data will rely on the data model you created in the previous section. We will also examine the code to explain how each element operates.
To modify the LOV in the Parameter Form in Reports Builder:
Click the Web Source button in the toolbar to display the Web Source view.
In the Web Source view, look for the following code:
<select name="p_department" size="1"> <option value="1">a</option> </select>
Note: In the above code, the LOV returns a static value. The display name is "a" and the value is "1". |
Since the LOV is currently static, we need to change this HTML element to dynamically retrieve data based on our data model.
In the Web Source view, modify the above code so that it looks like the following:
<select name=" p_department"> <rw:foreach id="fn" src="G_department_name"> <option value="<rw:field id="f_deptId" src="department_id"/>"><rw:field id="deptname1" src="department_name"/></option> </rw:foreach> </select>
Note: You can either type the code manually or copy and paste it from the provided file calledsimplejsppf_code.txt . |
Save your report.
Examine the JSP elements in the code:
By using JSP tags for Oracle Reports in the above code, we retrieve data into the Parameter Form's LOV by basing the parameters on fields in the data model. Let us examine each element:
<option>
: The display name of the LOV is replaced by the field department_name
from the data model. When the user displays the Parameter Form, the department name will display in the list.
<rw:field>
: This element accesses each element of the g_department_name
group.
<rw:foreach>
: This element iterates through the results based on the g_department_name
group in the data model.
src
: This parameter is used in both <rw:foreach>
and <rw:field>
elements. For the <rw:foreach>
element, src
must match the group name of the data model (G_department_name
). For the <rw: field>
element, it must match the field that is referred (department_name
).
id
: This parameter can be any value, but it must be unique. This parameter also applies to both <rw:foreach>
and <rw:field>
elements.
Note: For more infomation on these JSP elements and their parameters, refer to the Oracle Reports online Help. |
By making these modifications to the code, we have replaced the return value attribute of the LOV with the field department_id
, based on the data model we created in the previous section. If we now choose a department name from the list of values, its related department ID is returned. Note that the return value is not displayed.
Now that we have modified and examined our Web source, let us view the Parameter Form in a Web browser.
Click the Run Web Layout button in the toolbar.
Note: If Netscape 7.0 is your default browser, and the browser does not display, set the registry keyHKEY_CURRENT_USERS\Software\Oracle\Toolkit\Tkbrowser to the default browser location. Ensure that the BrowserName and the BrowserPath keys reflect the correct values. For example: BrowserName=Netscape 7 ; BrowserPath=C:\Program Files\Netscape\Netscape\Netscp.exe . |
The Parameter Form displays in your Web browser, and should look like the following:
Note: In the modified Parameter Form, notice how the list of values for the Department has changed from "a" to "Accounting." |
Click the Department list and notice how the list is now populated with department names. Although you can click Run Report, nothing will happen because we have not yet defined an action for it.
To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.
In this chapter, you will learn about reports that include a barcode. By following the steps in this chapter, you can generate the report output shown in Figure 43-1.
Figure 43-1 Barcode JavaBean Web report output
Concepts
By using a barcode JavaBean, you can build reports that display a barcode to make tasks like tracking shipping orders and employee identification numbers easier.
You can create a paper or Web report that uses JavaBeans. For a paper report, you must use the Java Importer to create a PL/SQL wrapper so that you can call the JavaBean from your PL/SQL business logic. For a JSP-based Web report, you must call the JavaBean from your report. In Reports Builder, the JavaBean automatically generates the barcode for you.
Note: For information about the Java Importer, and theORA_JAVA built-in package, see Oracle Reports online Help topics under Reference > Java Importer. |
You can learn more about the barcode JavaBean on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html
), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Any Format. In the expanded list of sub-topics, click PL/SQL-Java Bridge.
Example Scenario
In this example, you will build two reports, one for paper and one for the Web. The paper report shows an invoice for a single customer who has ordered multiple items from a company. The barcode indicates the tracking information for the order.
As you build these example reports, you will:
Create a Barcode Report for Paper:
Import the Java classes into Reports Builder using the Java Importer to add the barcode JavaBean.
Create a package to store your information using the Program Unit editor.
Create a Before Report trigger to set up your barcode JavaBean for the report.
Create a query in the Data Model view to retrieve the data from the schema..
Create a Barcode Report for the Web:
Create three formula columns in your data model to call the barcode data for the report.
Initialize the barcode JavaBean and set its properties by editing the JSP code in the Web Source view.
Run your report to the Web to view the output in a browser.
To see a sample paper report with a barcode, open the examples folder named barcodebeanpaper
, then open result\shipping_manifest.pdf
. To see a sample Web-based JSP report with a barcode, open the examples folder named barcodebeanweb
, then open result\shipping_manifest_web.jsp
. For details on how to open these reports, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 43-1.
Table 43-1 Example report files
File | Description |
---|---|
|
The final PDF version of the paper report, containing the barcode. |
|
The final RDF version of the paper report, containing the barcode. |
|
The barcode JavaBean. |
|
All the code used in this chapter, so you can copy and paste the code from this file instead of typing it manually. |
|
The source file for the sample paper report. Running this RDF in Reports Builder will display the final result of your paper report in the Paper Design view. |
|
The final JSP version of the Web report, containing the barcode. |
|
The final RDF version of the Web report, containing the barcode. |
|
The images that Oracle Reports generated when the JSP was run. |
|
The SQL for the query you need to enter. |
|
All the code used in this chapter, so you can copy and paste the code from this file instsead of typing it manually. |
|
The HTML page that you will use as a basis for the Web report. |
|
The source file for the sample Web report. Running this RDF in Reports Builder to the Web will display the final result of your Web report in your browser. |
|
The images and other files that your JSP-based Web report will require to display properly on the Web. |
|
The image you will use in your JSP-based Web report. |
The Oracle Reports distribution XML file enables you to specify the details of your distribution. For example, if you are distributing through e-mail, you can specify such details as the addressee, the reply to address, and the subject.
In this section, you will learn how to modify a distribution XML file. We have indicated locations where you need to enter your own information to make the distribution work.
When you want to distribute a report, you need to either:
Make sure your source report (for example, inventory_report_dist.rdf
) and your distribution XML file (for example, distribution.xml
) are in the same directory.
OR
When you run the report from the Reports Server, set the destination to the path of the XML file. (This is explained in Section 37.4, "Run the Report".)
Note: For more information on distribution, see the chapter "Creating Advanced Distributions" in the Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services manual. |
To edit the distribution XML file:
In a text editor, such as Notepad, open the sample distribution.xml
file we have provided.
Find the placeholder text: <YourFilePath>
, and replace it with the location of where your resulting PDFs will be stored.
Example: Replacing the placeholder text with:
d:\temp\
changes the path to:
d:\temp\Rep_%amp;<city>.pdf
Using this complete path places the resulting PDF files in d:\temp
.
Find the placeholder text: <OriginEmailAddress>
, and replace it with the sender's e-mail address.
Perform step 3 for all instances of the placeholder text: <OriginEmailAddress>
.
Find the placeholder text: <DestinationEmailAddress>
, and replace it with the recipient's e-mail address.
Perform step 5 for all instances of the placeholder text: <DestinationEmailAddress>
.
Note: For this example, we show you how to send e-mails to static e-mail addresses. However, if you want to send the report to various e-mail destinations, you must create a recipient field in your data model. Then, in theex2 section of distribution.xml , replace the placeholder text <DestinationEmailAddress> with &<recipient> . You can then delete the first section of the distribution.xml file (marked ex1 ). |
Save the XML file to the same directory where you have saved inventoryreport_dist_
your_initials
.rdf
.
Note: It is not required that you save the XML file to the same directory where your RDF is located, as you can specify the location of the XML file at runtime. Eitherdistribution.xml and RDF must be in Reports Path, or provide the complete path in the command line URL. |
You have finished customizing the distribution XML file to send a single e-mail to corporate headquarters with all of the individual warehouse reports, and multiple e-mails with a single attachment each to the individual warehouses.
This part of the manual explains how you can build reports using PL/SQL and Java.
In this example, you will learn how to use an external PL/SQL library and PL/SQL within a report to modify formatting and calculate the total compensation for each employee. In the sample output below, notice the spacing between records (for example, between the record for Alexander Khoo and the record for Alexis Bull). This space is not due to a break; it is the result of using a PL/SQL procedure in a format trigger.
Figure 3-37 Final output of the PL/SQL report example
For more information on building this example, refer to Chapter 40, "Building a Report that Includes PL/SQL".
In this example, you will learn how to use Reports Builder's features for using REF CURSOR
s. To build this paper report, you will use the Data Model view to create a multiquery data model, and then use the Report Wizard to create the report layout. You will make fairly extensive manual refinements in the Data Model view.
Figure 3-38 Final output for the REF CURSORs example
For more information on building this example, refer to Chapter 41, "Building a Paper Report with REF CURSORs".
This example is designed to teach you how to build a simple JSP Parameter Form for a Web report. You will build a simple form in HTML, then create a JSP Parameter Form based on a data model in Reports Builder. You will then learn how to link the Parameter Form with an existing JSP-based Web report, then deploy and test both the JSP Parameter Form and the Web report through your browser.
Figure 3-40 JSP-based Web report based on a user parameter
For more details on building this example, refer to Chapter 42, "Building a Simple Parameter Form for a JSP-based Web Report".
In this example, you will build a report using the barcode Java bean in Oracle Reports. You will build two reports: one for paper and one for the Web. The paper report shows an invoice for a single customer who has ordered multiple items from a company. The barcode indicates the tracking information for the order.
Figure 3-41 Final output of the barcode report with paper layout
Figure 3-42 Final output of the barcode report with Web layout
For more details on building this example, refer to Chapter 43, "Building a Report with a Barcode".
In this example, you will use the properties in Oracle Reports to produce a JSP-based Web report that is accessible using assistive technology. You will learn how to modify an existing paper-based report to be accessible.
For more information on building this example, refer to Chapter 44, "Building an Accessible JSP-based Web Report".
Note: No sample output is available for this report. |
When you create a report, you can either use the Report Wizard to assist you or create the report yourself. To build the simple report in this example, you can use the Report Wizard. Using the wizard enables you to define the layout for the report, as well as set the data definition.
To create a simple report:
Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create Paper Layout Only, then click Next.
On the Style page, type a Title for your report, select Tabular, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT
statement in the Data Source definition field:
SELECT ALL DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID FROM DEPARTMENTS ORDER BY DEPARTMENTS.DEPARTMENT_ID
Tip: This query selects all the department IDs, the department names, the manager IDs, and the location IDs, then sorts the data by the department IDs. |
Note: You can enter this query in any of the following ways:
|
Click Next.
Note: If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 5.1, "Prerequisites for This Example" describes the sample schema requirements for this example. |
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, click Next.
On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:
Figure 5-2 Paper Design view for the tabular report
Note: In the Paper Design view, you can see how the tabular report displays the data like a table, in order of department ID number. |
Save the report as tabularreport_
your_initials
.rdf
.
In this chapter, you will learn about Enhanced Spreadsheet reports. Oracle Reports generates spreadsheet output using the ENHANCEDSPREADSHEET
format. The ENHANCEDSPREADSHEET
output format is new in Oracle Reports 11g Release 1 (11.1.1) and provides the following benefits:
Large data sets (up to 75,000 rows) can be generated to the ENHANCEDSPREADSHEET
output format.
ENHANCEDSPREADSHEET
output format allows for bursting and distributing reports to spreadsheet output format.
In ENHANCEDSPREADSHEET
output, the complete report data is shown as one unbroken chunk of information in Microsoft Excel, not broken up by page breaks, as happens in other output formats, such as PDF and RTF that are restricted by horizontal and vertical page sizes.
For more information about Enhanced spreadsheet output, refer to Section 2.8.13, "About Enhanced Spreadsheet output".
By completing the steps in this chapter, you can generate the Enhanced Spreadsheet report output, as shown in Figure 29-1.
Figure 29-1 Enhanced Spreadsheet report output
Concepts
This chapter provides steps for building a JSP-based Web report and deploying it with Reports Server under Oracle WebLogic Server to display it in Microsoft Excel inside your Web browser. You can use an existing paper layout report to create a new Web layout.
If you want to generate Microsoft Excel output from your Web report, but you do not have an existing paper layout, refer to the slideshow demonstration on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html
), click Getting Started to display the Getting Started with Oracle Reports home page. In the list of topic sections on the left, click Index. In the Collateral Type list, choose Demonstrations, and click Search. In the list of demos that displays, click Output to Excel with Oracle Reports.
Note: The steps in both this chapter and the Getting Started demonstration allow you to use rich formatting, such as colors and fonts so that you generate report layout, not just data, in Microsoft Excel output inside a Web browser. However, you must add custom JSP code to get the output in Microsoft Excel. The Web report can only be run as JSP in Oracle WebLogic Server and not throughrwservlet , thus this method can only display the Microsoft Excel output inside a Web browser. |
The ENHANCEDSPREADSHEET
output format enables you to burst and distribute reports to spreadsheet format, as well as generate large data sets (up to 75,000 rows) to spreadsheets.
The ENHANCEDSPREADSHEET
output formats generate output from paper layout reports to HTML files that can be directly opened with Microsoft Excel 2000. You can:
Generate report output to Enhanced spreadsheet format from existing paper layout reports saved in any format (.rdf
, .xml
, .jsp
), using rwrun
or Reports Server clients (rwclient
, rwservlet
). See the following example scenario.
Preserve the rich layout formatting, such as colors, fonts, conditional formatting, graphs, and images.
For the steps to implement this functionality to directly generate spreadsheet output, see Section 2.8.13, "About Enhanced Spreadsheet output" and Section 4.7.11, "Generating Enhanced spreadsheet output".
Example Scenario
In this example, you will generate a report to Microsoft Excel output. The output is generated after integrating the paper layout into a Web layout by modifying the Web source of your report.
As you build this example report, you will:
Create a Query and the Layout using the Report Wizard to create a simple query for a paper layout report.
Modify the Web source of Your JSP Report to generate the JSP-based Web report.
To see a sample report, open the examples folder named papertoexcel
, then open the Oracle Reports example named mypaperreport.rdf
. For details on how to open it, see "Accessing the Example Reports" in the Preface. The example files used in this chapter are listed and described in Table 29-1.
Table 29-1 Example Report Files
File | Description |
---|---|
|
The sample paper report. Running this RDF in Reports Builder will display the result of your paper report in the Paper Design view. |
|
This JSP-based Web report contains the modifications to the paper report you will make in Reports Builder. |
|
The SQL for the query you need to enter. |