| Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports 11g Release 1 (11.1.1) Part Number B32122-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports 11g Release 1 (11.1.1) Part Number B32122-02 |
|
|
PDF · Mobi · ePub |
This section provides procedures for the following tasks that you may perform as you work with the data model of your report:
To create a query, you can use any of the following tools:
Report Wizard (single-query reports only)
Data Wizard
SQL Query tool (to create a query that selects data from an Oracle relational database)
XML Query tool (to access an XML data file, if you have the DTD file)
JDBC Query tool (to access any JDBC-enabled data source)
Text Query tool (to create a query that selects data from a text pluggable data source)
REF CURSOR Query tool (to use REF CURSORs)
See also
Section 1.7.1, "About queries"
Section 1.7.3, "About database columns"
To create a query using the Report Wizard:
On the Data page of the Report Wizard, enter the query statements for the type of data source previously selected. For example, if you selected the SQL Query data source, you can enter your SQL query in any of the following ways:
Type the SELECT statement in the Data Source definition field.
Click Query Builder for a graphical method of creating a query without a knowledge of SQL.
Click Import Query to use a query that has been written by someone else or to use the text editor of your choice.
To create a query using the Data Wizard:
In the Data Model view, choose Insert > Query to display the Data Wizard.
Follow the wizard to create the first query for the data model.
Repeat Steps 1 and 2 for each query you want to create.
Modify the resulting data model in the Data Model view.
To re-enter the Data Wizard, do either of the following:
Right-click, and choose Data Wizard.
Click the query, then choose Edit > Settings.
Note:
The Data Wizard does not support creating links between queries. To define parent/child query relationships, you can create a data link manually.To create a SQL query:
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, define a SELECT statement for the query in any of the following ways:
Click OK.
Refine the query using either of the following methods:
Right-click the query object, then choose Property Inspector to set desired properties.
Right-click the query object, then choose Data Wizard to specify which fields to display, group fields, and any totals.
Repeat Steps 1 through 5 for each query you want to create.
Note:
If you define multiple queries in the Data Model view of your report, the Data page does not appear when you invoke the Report Wizard to default the layout.To create an XML query:
In the Data Model view, single-click the XML Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the Define XML Query dialog box, define the XML query based on the fields defined in the data definition file (DTD) selected. The query is run against the data in the XML data file. If an XSL file is specified, it translates the XML data file before running the query.
Click OK.
To create a JDBC query:
In the Data Model view, single-click the JDBC Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the JDBC Query dialog box, define the JDBC query or procedure and the connection parameters for the data source. Click Help for assistance.
Click OK.
To create a Text query:
In the Data Model view, single-click the Text Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the Define Text Query dialog box, specify the data definition and data source for the text query. Click Help for assistance.
Click OK.
To create a REF CURSOR query:
Create a package that defines a REF CURSOR type in one of the following ways:
Create a local program unit (see Section 4.13.3.1, "Creating a local program unit")
Create an external PL/SQL library (see Section 4.13.5.1, "Creating an external PL/SQL library")
Create a stored program unit (see Section 4.13.3.2, "Creating a stored program unit")
If the package created in Step 1 is an external PL/SQL library, you must attach it to the report before referencing it.
In the Data Model view, single-click the REF CURSOR Query tool in the tool palette.
Click in the main area (canvas region) of the window.
In the PL/SQL Editor, type the PL/SQL for a function that opens a cursor and returns a cursor variable of the REF CURSOR type you defined in the package. For example:
empCur rcPackage.empCurType; BEGIN OPEN empCur FOR SELECT * FROM emp; RETURN empCur; END; /* Note, rcPackage is a local program unit defined as: */ PACKAGE rcPackage IS TYPE empCurType IS REF CURSOR RETURN emp%ROWTYPE; END;
Note:
Oracle Reports supports both static and dynamic
REF CURSORs. For more information, see Section 2.6.10, "About REF CURSOR queries".Refine the query as desired:
Click the REF CURSOR query object, then choose Tools > Property Inspector to modify properties.
Click the query object, then choose Tools > Data Wizard to specify which fields to display, group fields, and any totals.
Repeat Steps 1 through 6 for each REF CURSOR query you want to create.
See also
To modify a SQL query statement or the columns displayed in your report:
In the Data Model view, click the query object, then right-click and choose Data Wizard.
To change the SELECT statement, click the Data tab.
To change the break groups, click the Groups tab.
To add or change summary columns for totals, click the Totals tab.
To change which selected database columns are displayed in your report, choose Tools > Report Wizard, then click the Fields tab.
To modify the properties of a query:
In the Data Model view, click the query object, then right-click and choose Property Inspector.
In the Property Inspector, modify the properties as desired.
You can use Query Builder to define almost any query that you would build using a SQL SELECT statement. Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Query Builder workspace.
See the How To section of the Oracle Reports online Help for topics about using Query Builder.
See also
Note:
This procedure applies to paper-based reports.To select HTML tags from the database:
Create a query, with a SELECT statement that selects a column containing HTML tags or the names of files that contain HTML tags.
In the Data Model view, double-click the HTML tags column to display the Property Inspector.
If the column contains the names of files that contain the HTML tags, rather than the HTML tags themselves:
Under the Column node, set the Read from File property to Yes.
Set the File Format property to Text.
In the Paper Layout view, create a field object that references the column.
Double-click the field object to display the Property Inspector.
In the Property Inspector, under the Web Settings node, set the Contains HTML Tags property to Yes.
Usage notes
When the text object's Contains HTML Tags property is set to Yes, HTML tags and attributes are formatted in your report output as follows:
If the report is generated to HTML or HTMLCSS output, all HTML tags and attributes are interpreted and formatted in the output.
If the report is generated to other bitmap output formats (for example, PDF, RTF, PostScript), a fixed set of HTML tags and attributes are interpreted and formatted in the output, as described in Section 2.8.9, "About HTML formatting". Any HTML not in the supported set of tags and attributes are not interpreted, and appear as is in the report output.
Note:
These steps, and the steps in Section 4.9.8.1.2, "Linking an image object to a file" (for paper-based reports only), allow you to include images in a wide variety of formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM. For paper-based reports, you can also use Insert > Image to import an image from a file into the report layout for images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD (see Section 4.9.8.1.1, "Importing an image").To select an image from the database:
Create a query that selects a column containing images or the filenames of image objects.
In the Data Model view, double-click the image column to display the Property Inspector.
If the column contains the filenames of image objects, rather than the images themselves:
Under the Column node, set the Read from File property to Yes.
In both cases (a column containing images or a column containing the filenames of image objects): under the Column node, set the File Format property to Image.
If you are developing a JSP-based Web report, you will also need to add the containsHtml attribute to the <rw:field> JSP tag for the column, as follows:
Display the Web Source view for the report.
Search for the rw:field tag for the column (<rw:field ... src="column_name" ...>)
Add the containsHtml=yes attribute to specify that the field's value contains an image.
For example:
<rw:field id="F_MYIMAGE" src="MYIMAGE" nullValue=" " containsHtml="yes">
To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout to run the Web Source. If you have made changes to the Web Source view (for JSP-based Web reports), this enables you to immediately see the effect of your changes on the output.
See also
Notes:
This procedure is for HTML output only.These steps, and the steps in Section 4.9.8.1.3, "Linking an image object to a URL" (for paper-based reports only), allow you to include images in a wide variety of formats supported by Oracle Reports, including JPEG (all types, such as Progressive JPEG and Exif JPEG), PNG, BMP, TIFF, GIF, and CGM. For paper-based reports, you can also use Insert > Image to import an image from a file into the report layout for images in the following formats: TIFF, JFIF, BMP, TGA, PCX, PICT, GIF, CALS, RAS, OIF, PCD (see Section 4.9.8.1.1, "Importing an image").
To select a URL from the database that point to an image to include in an HTML report:
Create a query, with a SELECT statement that selects a column containing URLs.
In the Data Model view, double-click the column containing the URLs to display the Property Inspector.
Under the Column node:
Set the Read from File property to Yes.
Set the File Format property to Image URL.
If you are developing a JSP-based Web report, you will also need to add the containsHtml attribute to the <rw:field> JSP tag for the column, as follows:
Display the Web Source view for the report.
Search for the rw:field tag for the column (<rw:field ... src="column_name" ...>)
Add the containsHtml=yes attribute to specify that the field's value contains an image.
For example:
<rw:field id="F_MYIMAGE" src="MYIMAGE" nullValue=" " containsHtml="yes">
To preview your report output with Web layout in a Web browser, choose Program > Run Web Layout to run the Web Source. If you have made changes to the Web Source view (for JSP-based Web reports), this enables you to immediately see the effect of your changes on the output.
See also
You can define break groups in the Report Wizard or create them manually.
To create a break group manually:
In the Data Model view, drag the group that contains the column at which you want to break your report down from the query object about 2 inches (5 centimeters).
Select the column that you want to use to divide your report (for example, if you want to group a list of employee data by department number, select the department number column) and drag it out of and above the group to create a new group. This is the break column.
Double-click the new group object to display its Property Inspector, where you can set properties for the break group.
See also
You can define matrix groups in the Data Wizard or create them manually.To create a matrix (cross-product) group using the Data Wizard:
In the Data Model view, right-click and choose Data Wizard.
On the Query page, select the Matrix query check box.
Follow the wizard to select the columns for the matrix group(s), rows, columns, and cells.
To create a matrix (cross-product) group manually:
In the Data Model view, drag the title bar of the group object down.
Drag the "row" column and "column" column out of the group object into the space to create two new groups.
Single-click the Cross Product tool in the tool palette.
Drag a box around the groups you want to include in the matrix (cross-product) group.
If the cell group is a separate query from the matrix (cross-product) groups, create the appropriate links between the columns in the matrix groups and the cell group.
See also
Section 2.3.7, "About matrix objects"
Section 1.3.7, "About matrix reports"
Section 2.1.7, "About nested matrix reports"
Section 2.1.8, "About matrix with group reports"
Section 4.9.1.3, "Creating a matrix object"
Section 4.5.3, "Creating a nested matrix report"
To create a data link:
In the Data Model view, single-click the Data Link tool in the tool palette. A link is always drawn from the parent group to the child query.
Create the link:
Create a Group to Group Link: To create a link between one query's group and another query's group, which is useful when you want the child query to know about the parent's data, click the parent group (avoiding the columns in the group) and drag a link to the child group.
Create a Column to Column Link: To create a link between columns, click a column of the parent query and drag a link to a column of the child query.
Double-click the new link object to display the Property Inspector, and set the desired properties.
See also
To create or edit a formula column:
In the Data Model view, single-click the Formula Column in the tool palette, then:
To create a column within a group, click in the group at the position you want the column placed in the hierarchy.
To create a report-level column, click in an open area of the canvas region.
Double-click the formula column object to display the Property Inspector.
Under the Placeholder/Formula node, double click the PL/SQL Formula property field.
In the PL/SQL Editor, define the PL/SQL for the formula for example SAL * 0.07.
Example: Referencing a PL/SQL function in formulas
Suppose that you have a report with the following groups and columns:
Groups Columns Summary
---------------------------------------
RGN REGION
RGNSUMSAL SUM(DEPTSUMSAL)
COSTOFLIVING
DEPT DNAME
DEPTNO
DEPTSUMSAL SUM(EMP.SAL)
JOB JOB
HEADCOUNT COUNT(EMP.EMPNO)
EMP ENAME
EMPNO
SAL
COMM
Given these groups and columns, you might create multiple formulas that apply the cost of living factor (COSTOFLIVING) to salaries. To avoid duplication of effort, you could create the following PL/SQL function and reference it from the formulas:
function CompSal(salary number) return number is begin return (salary*CostofLiving); end;
Following are some examples of how you might reference the PL/SQL function in formulas:
CompSal(:RGNSUMSAL)
or
CompSal(:SAL) + COMM
See also
To create a summary column (for totals or subtotals) using the Data Wizard:
In the Data Model view, click the query that contains the column you want to total.
Choose Tools > Data Wizard.
On the Totals page, follow the wizard to add the desired summary to your report.
Note:
For group reports, the Report Wizard and Data Wizard create n summary fields in the data model for each summary column you define: one at each group level above the column being summarized, and one at the report level. For example, if a report is grouped by division, and further grouped by department, then a summary column defined for a salary total would create fields for the sum of salaries for each division and each department group (group-level summaries), and the sum of all salaries (report-level summary).To create a summary column (for totals or subtotals) using the tool palette:
In the Data Model view, single-click the Summary Column tool in the tool palette, then:
To create a column within a group, click in the group at the position you want the column placed in the hierarchy.
To create a report-level column, click in an open area of the canvas region.
Double-click the new summary column object to display the Property Inspector.
Under the Summary node:
Set the Function property by choosing the type of summary you want from the list.
Set the Source property to the column you want to summarize.
Set the Reset At property to the group at which the summary column value resets.
Usage notes
For JSP-based Web reports with a Group Above or Matrix style, if any summary column values are not correctly left-aligned when you click the Run Web Layout button in the toolbar (or choose Program > Run Web Layout), you can implement the following workaround to align the values:
In the Web Source view, locate the section where the summary column is defined, and delete the following line to remove the extra space:
<th class="summary_column_name"> </th>
See also
Section 2.3.1, "About summary columns"
To create or edit a placeholder column:
In the Data Model view, single-click the Placeholder Column tool in the tool palette.
To create a column within a group, click in the group at the position you want the column placed in the hierarchy.
To create a report-level column, click in an open area of the canvas region.
Double-click the placeholder column object.
In the Property Inspector, set the desired properties for the placeholder column.
Set the value of a placeholder column in:
the Before Report trigger, if the placeholder is a report-level column
a report-level formula column, if the placeholder is a report-level column
a formula in the placeholder's group or a group below it (the value is set once for each record of the group)
See also
|
 Copyright © 2003, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|