PK
F_Eoa, mimetypeapplication/epub+zipPK F_E iTunesMetadata.plistn
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
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.
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
By default, both of your user parameters appear in the Runtime Parameter Form. In reality, you only want users to set CUTOFF_CNT. You do not want them to be able to set INCR_CNT (the amount by which your counter is increased for each record). To prevent users from seeing INCR_CNT on the Runtime Parameter Form, you will build your own Parameter Form.
To create a Parameter Form:
Choose Tools > Parameter Form Builder.
In the Parameter Form Builder dialog box, scroll down the list of parameters until you find INCR_CNT.
Click INCR_CNT to deselect it.
Change the label for CUTOFF_CNT to:
# of Top Customers:
Click OK.
Click the Run Paper Layout button in the toolbar.
In the Runtime Parameter Form, type a value for # of Top Customers, then click the Run Report button in the toolbar. You should now see as many records as you asked for in the Runtime Parameter Form and they should be in order from largest total purchases to smallest total purchases.
Tip: As an additional exercise, you could now change the Initial Value property of CUTOFF_CNT to see its effect on the Runtime Parameter Form. |
Save your report.
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".
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.
Congratulations! You have successfully created a wrapped field paper report. You now know how to:
manually create a data model.
create a master/detail (or group above) report using the Report Wizard.
modify the layout of your report to wrap the text in a field.
add space between 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".
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".
This chapter introduces the fundamental concepts of the 11g Release 1 (11.1.1) component of Oracle Reports. Each topic in this chapter is also included in the Basic Concepts section of the Oracle Reports online Help (see Section 4.1.1, "Using the Oracle Reports online Help").
Topics are grouped into the following sections:
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:
Congratulations! You have created a paper report and a JSP-based Web report that use the barcode JavaBean to generate barcode images.
You now know how to:
use the Java importer to add Java classes to a paper report.
use JavaServer Pages to call a JavaBean from within a report.
create a PL/SQL package.
use a Before Report trigger to tell Reports Builder what type of barcode image to use.
manually build a data model with a SQL query and formula columns.
edit the code in the Web Source view for a JSP-based Web report.
set up a JavaBean in JSP.
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 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.
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.
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 Oracle Reports components are:
Table 1-4 Oracle reports Component Descriptions
All components listed in Table 1-4, except rwclient
and rwserver
, can be run from the command line. See the Reference > Command Line section of the Oracle Reports online Help for detailed information about the components and command line keywords. Refer to Oracle Fusion Middleware Publishing Reports to the Web with Oracle Reports Services for information about managing and monitoring Reports Server, Reports Tools and Oracle Reports Bridge through Oracle Enterprise Manager or OPMN commands.
See also
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.
Congratulations! You have successfully created a report with a table of contents and an index. You now know how to:
create a table of contents (TOC) and index for a large paper report.
use the SRW.SET_FORMAT_ORDER built-in procedure to change the order in which the sections of a report are executed.
use the SRW.GET_PAGE_NUM built-in procedure to retrieve the page number for a specific record.
create a new table in your database to hold the necessary page numbers.
create a simple group above report using the Report Wizard.
manually create a simple data model to retrieve the information for your report.
create a format trigger to generate the necessary page numbers for an index.
use the Report Block Wizard to create a group report that displays the index in the trailer of a report.
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 distributed a report. You now know how to:
modify the layout of an existing report to burst on a group in your layout.
distribute a report using e-mail by modifying the distribution.xml
file.
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 created a time series calculations report for both paper and Web. You now know how to:
create a time series calculations report definition.
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".
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:
Note: To see a more complete view of the report, you can open the sample report we have provided, called |
When you print your report on to the form, it might look something like this:
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.
In this chapter, you will learn about reports that include a multilevel table of contents.
Note: Generating a table of contents is not supported for matrix reports. |
By following the steps in this chapter, you can generate the report output shown in Figure 36-1 and Figure 36-2.
Concepts
This chapter assumes that you know how to create a basic data model and layout for your report, and focuses on the building of the table of contents. If you are not sure how to create a data model and paper layout, refer to the Oracle Reports online Help or, for specific steps, refer to any other chapter in this manual.
When you create a large paper report, you can create a table of contents (TOC) to make it easier for users to navigate through the pages of information. In Chapter 35, you learned how to create a basic table of contents that categorized customer information by country, then listed the country and page ranges in the table of contents. You also learned how to index this information.
In this chapter, you will learn how to create a multilevel table of contents that will enable you to create a hierarchy. This hierarchy will further enable you to categorize your information, so that users can find a specific piece of information.
As in Chapter 35, you will use the SRW.SET_FORMAT_ORDER built-in procedure in an After Parameter Form trigger to generate the Main section of your report before the Header and Trailer sections. Doing so enables you to generate the page numbers for the report in the Main section, then display the page numbers in the table of contents in the Header section.
Note: Although you can use SRW.SET_FORMAT_ORDER to change the order in which the report is formatted, the report will still display the Header section first, then the Main section, and finally the Trailer section. You can use SRW.SET_FORMAT_ORDER to simply change the formatting order, but not the display order of the report sections. |
You will also create a format trigger based on a sub-category in your report that will use the SRW.GET_PAGE_NUM built-in package to retrieve the page number for each record and temporarily place it in a database table you create. This format trigger will also get the page numbers for each sub-category that you specify. Finally, you will use a SQL query to display the desired information in a multilevel table of contents that displays at the beginning of your report.
For more information on the SRW built-in package in Oracle Reports, refer to the Oracle Reports online Help.
Example Scenario
In this example, you have a large paper catalog that lists all the clothing products a company sells. This catalog simply lists all the items in a simple report. Since the product line has become much larger, the catalog report now exceeds 700 pages. You will create a multilevel table of contents that sorts the clothing by department (for example, Boys, Girls), then lists each item under each category.
As you build this example report, you will:
Create a table in the database to hold the TOC data (the first and second tier categories and page numbers) using SQL*Plus.
Create an After Parameter Form Trigger and a Format Trigger using the PL/SQL Editor to change the order in which the report sections are executed and fetch the page numbers for each category, sub-category, and record.
Create a Second Query in the Data Model to retrieve the information stored in the table you created.
Create a Report Block to Display the Table of Contents using the Report Block Wizard to create a group above report block in the Header section to display two levels of headings. You will also add a field to your layout to display the page number where each heading is found in report.
To see a sample report with a multilevel table of contents, open the examples folder named multileveltoc
, then open multilevel_toc.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 36-1.
Table 36-1 Example report files
File | Description |
---|---|
|
The source file that contains a basic paper layout and data model for your report. |
|
The final RDF version of the report with a multilevel table of contents. |
|
The various SQL statements you will use in this report. |
In your current report configuration, the SELECT
statements used by the REF CURSOR
queries reside within the report itself. In many cases, it is advantageous to have SELECT
statements reside in the packages that define the REF CURSOR
types. Then, you can simply reference the packages, rather than typing the same SELECT
statement directly into every report that uses it. If you need to change the SELECT
statement (for example, to modify or add clauses), you simply update it once in the package, rather than in every report that uses it.
The steps in this section will help you to move the SELECT
statements to the packages that define the REF CURSOR
types.
To move SELECT statements into a package:
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 cont_cv
in the Name field.
Select Package Body, and click OK to display the PL/SQL Editor for the new program unit.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
PACKAGE BODY cont_cv IS function query_container (p_department_id number) return container_refcur is tempcv_container cont_cv.container_ refcur; begin open tempcv_container for select employee_id, start_date, end_date, job_id, department_id from job_history where :department_id=department_id; return tempcv_container; end; END;
Note: You can open the provided file |
Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
Now that the function is defined, you must add it to the package spec so that it can be referenced. Other program units will know about the function in the package body only if it is described in the package spec.
In the Object Navigator, double-click the CONT_CV (Package Spec) object to display the PL/SQL Editor.
In the PL/SQL Editor, type the following line above the END;
statement:
function query_container (p_department_id number) return container_refcur;
Click Close.
Choose Program > Compile > All.
Click OK when done.
In the Object Navigator, under the Program Units node, double-click Q_CONTAINERREFCURDS to display the PL/SQL Editor.
In the PL/SQL Editor, edit the code to look as follows:
function Q_containerRefCurDS return cont_cv.container_refcur is temp_container cont_cv.container_refcur; begin temp_container:=cont_cv.query_container (:department_id); return temp_container; end;
When you are done, all of the query's logic will reside in the function named query_container
. From now on, when you change query_container
, you will change this and any other queries that reference it.
Note: You can open the provided file |
Click Compile.
If any compilation errors occur, check the code for syntax errors and recompile as needed.
Click Close.
Click the Paper Design button in the toolbar to view the report in the Paper Design view.
Save the report as ref_67_
your_initials
.rdf
.
Optional Exercise:
Repeat the above steps for the other two queries in the report.
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.
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.
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 the |
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. |
Another way to rank customers is by percentage of total sales. The idea is the same as ranking by count, but with an important difference. Since you need to fetch all of the data in order to compute a running percent of total summary, you don't want to use a group filter to weed out data. You need to use a format trigger on the repeating frame to compare the running total to your cutoff parameter.
To fetch the data for the percentage calculation:
In the Object Navigator, double-click the view icon next to the Data Model node.
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, 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 CUSTNAME CNAME2, SUM(AMOUNT) SUM_AMT2 FROM SALES GROUP BY CUSTNAME ORDER BY SUM(AMOUNT) DESC
Note: You can enter this query in any of the following ways:
|
Click OK. Your data model should now look like the following image.
Save your report.
To create a parameter for the percentage cutoff:
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_PCT.
Under Parameter, set the Datatype property to Number, set the Width property to 2, and set the Initial Value property to 75.
In the Data Model view, click the group object G_CNAME2, then click and drag the bottom handle down about 0.25 inches (0.5 centimeters) to make the group bigger.
Click the Summary Column tool in the tool palette.
Click in the empty space beneath SUM_AMT2 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 R_PCT.
Under Column, set the Datatype property to Number, and set the Width property to 10.
Under Summary, set the Function property to % of Total, set the Source property to SUM_AMT2, set the Reset At property to Report, and set the Compute At property to Report.
To create a second layout for the list of customers by percentage:
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 1 inch (2.5 centimeters) below the existing layout, click and drag a box about the same size as the existing layout to define the size of the second layout and display the Report Block Wizard.
On the Style page of the Report Block Wizard, select Tabular, then click Next.
On the Groups page, click G_CNAME2 in the Available Groups list and click Down to specify its Print Direction and move this group to the Displayed Groups list, then click Next.
On the Fields page, click the following fields and click the right arrow (>) to move them to the Displayed Fields list, then click Next:
CNAME2
SUM_AMT2
On the Labels page, change the labels and field widths as follows, then click Next:
Fields | Labels | Width |
---|---|---|
CNAME2 |
|
|
SUM_AMT2 |
|
|
On the Template page, select No template. (If you were to select a template, it would override the template of the previous layout), then click Finish to display your report layout in the Paper Layout view.
From the font lists in the toolbar, choose Arial (Western), point size 10.
In the Paper Layout view, click the Text tool in the tool palette.
Click directly above the label Customer Name in the new layout and type the following text in the new boilerplate text object:
Top &CUTOFF_PCT Percent of Sales:
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 the text is not already bold, click the Bold button in the toolbar to make it bold.
Shift-click the labels Customer Name and Total Purchases so that they are selected simultaneously with the object you just created.
Click the Underline button in the toolbar.
Click in an open area of the Paper Layout view to deselect everything.
Double-click field F_SUM_AMT2 to display the Property Inspector, and set the following properties:
Under Field, set the Format Mask property by typing:
-$NNN,NN0.00
To add the logic for the percentage cutoff:
In the Object Navigator, type R_G_CNAME2
in the Find field to locate that repeating frame.
Double-click the properties icon next to R_G_CNAME2 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 R_G_CNAME2FormatTrigger return boolean is begin if :r_pct <= :cutoff_pct 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 called |
To update the Parameter Form for the new percentage parameter:
Choose Tools > Parameter Form Builder.
In the Parameter Form Builder dialog box, scroll down the list of parameters until you find CUTOFF_PCT, and change its label to:
Top Percentage (%):
Click OK.
Click the Run Paper Layout button in the toolbar.
In the Runtime Parameter Form:
For # of Top Customers, type 5
.
For Top Percentage (%), type 80
.
Click the Run Report button in the toolbar.
In this chapter, you will learn about reports that rank data in different way. By following the steps in this chapter, you can generate the report output shown in Figure 34-1.
Concepts
You can create a report that ranks data by comparing it to a user-specified bind parameter. This enables you to rank data in different ways in the same report; for example, by count and by percentage. You can set the ranking criteria at runtime, or let them default to previously specified values.
Data Relationships
This report requires one query to fetch the data. To rank the data, you'll create the following objects:
A bind parameter that gives you the option of setting the cutoff point at runtime.
A bind parameter that increments by one each time a record is fetched from the database. The value in this parameter will be compared to the cutoff Bind parameter.
A customized group filter to include only records within the cutoff value. Using a customized filter rather than a packaged filter enables you to parameterize your cutoff values.
Layout
This report uses a tabular layout style, with some modifications.
Example Scenario
In this example, you will create a report that displays the names and total purchases of your top three customers and the top 75% of all customers. Furthermore, you will allow end users of your report to set the ranking criteria (that is, the top x customers and the top y% of all customers) at runtime.
As you build this example report, you will:
Create Ranking Logic for Top number of Customers by creating parameters and a group filter to control the ranking criteria.
Add a Layout Object for a Parameter to display the parameter value in the report output.
Create a Parameter Form that only displays the parameter you want end users to set.
To see a sample ranking report, open the examples folder named ranking
, then open the Oracle Reports example named rank.rdf
. For details on how to access 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. In this example, you will use the Data Model view to create your two queries, then use the tool palette to create a data link between the two queries to relate the data tables.
To create two queries:
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 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 ALL DEPARTMENTS.DEPARTMENT_ID, DEPARTMENTS.DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID, DEPARTMENTS.LOCATION_ID FROM DEPARTMENTS
Note: You can enter this query in any of the following ways:
|
Click OK.
The data model for your new query displays:
Create another query, this time using the following code:
SELECT ALL EMPLOYEES.LAST_NAME, EMPLOYEES.FIRST_NAME, EMPLOYEES.DEPARTMENT_ID FROM EMPLOYEES ORDER BY EMPLOYEES.LAST_NAME
Note: You can also copy and paste the code from the text file provided in the acrossbreak folder, called |
Click OK.
In the Data Model view, click the Data Link tool in the tool palette.
Click and drag your mouse from DEPARTMENT_ID column in Q_1, to DEPARTMENT_ID1 in Q_2 to create a data link between the two queries.
Your data model should now look like this:
Note: You can right-click the data link, then choose Property Inspector from the pop-up menu to ensure that the data link was created properly. |
The main toolbar is located at the top of the Oracle Reports Builder window, directly beneath the menu bar, as shown in Figure A-1:
The name and purpose of each button is as follows:
Icon | Description |
---|---|
New button. Displays the New Report dialog box. | |
Open button. Displays the Open dialog box. | |
Save button. Saves the report. If you have not saved the report before, the Save As dialog box displays. | |
Print button. Prints the paper report. | |
Mail button. Displays the Mail dialog box. | |
Connect button. Displays the Connect dialog box. | |
Cut button. Deletes the currently selected item and temporarily places it in the clipboard. Use Paste to paste the selected item. | |
Copy button. Temporarily places a copy of the selected item in the clipboard. Click the Paste button to paste the selected item. | |
Paste button. Pastes the item in the clipboard in current location of the cursor. | |
Undo button. Undoes the last action performed. | |
Redo button. Performs the last action again. | |
Run Web Layout button. Runs the current report to your Web browser. | |
Run Paper Layout button. Runs the current report to the Paper Design view in Reports Builder. | |
Help button. Displays the Oracle Reports online Help. |
In this chapter, you will learn about group left summary reports. By following the steps in this chapter, you can generate the report output shown in Figure 13-1.
Concepts
This report consists of master records (Name, at the upper left of the figure above), detail records (Product, Itemtot, and Orderdate, to the upper right), and summary records (Product, and Sum Total). The summary calculates totals for the details under each master record. Notice that the column Product appears twice. With Reports Builder, you can display columns any number of times.
A master/detail summary report is a master/detail report that also contains one or more summaries.
Data Relationships
This report will use two queries to select data from four tables. The master query will select the customer name, while the detail query will select the information associated with the products ordered by each customer.
Because the detail query will select data from several tables, you'll need to specify joins to link the information in the tables together.
You'll drag a column out of the detail group to further group the data. You'll also create a summary column to calculate the item totals.
Layout
The layout for this report is constructed in two parts. The top portion contains the master and detail information, and is created using the Report Wizard. The second portion is constructed by hand in the Paper Layout view and formats the summary.
You'll also resize two groups in the layout to ensure that the new, user-created bottom portion of the layout is integrated into the top portion, omit some columns from the layout that were queried only to join the tables, and include more space between instances of a repeating frame.
Example Scenario
In this example, you will create a report that displays and summarizes sales data by customer. This report would include the following for each customer:
a list of the products they purchased by order date and how much they spent on each product
a summary for each customer that shows how much they spent in total on each product over time
As you build this example report, you will:
Create a New Report Manually to create a new, empty report.
Create a Data Model with a Data Link between two queries.
Merge the Two Layouts to combine the separate layouts into one.
Format Fields to format monetary values.
To see a sample master/detail summary report, open the examples folder named masterdetailsummary
, then open the Oracle Reports example called grp_above_summ.rdf
. For details on how to access it, see "Accessing the Example Reports" in the Preface.
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.
Save your report as nested3_
your_initials
.rdf
To build the example in this chapter, you must have the example files provided, 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.
Note: The use of Interoperability between a 11g Release 1 (11.1.1) database or Oracle Forms Services client and a 10.1/10.2 database requires a minimum patchset level of 10.1.0.5 (for 10.1) or 10.2.0.2 (for 10.2). An attempt to reference a 10.1 or 10.2 PL/SQL unit or view under the following circumstances will fail with a
|
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 |
|
TOTAL_COMP |
|
On the Template page, click Finish to display your report layout in the Paper Layout view. It should look something like this:
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:
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:
Save your report.
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 type |
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: The |
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 created the two-query, group report. You now know how to:
create queries with a data link between them in the Data Model view.
layout the data with the Report Wizard.
format a field in the Paper Design view.
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 created a report to support any character set in your enterprise. You have used Oracle Reports Builder to generate a portable PDF file using font subsetting.
You now know how to:
create the query and paper layout for the multibyte report using the Report Wizard.
display multibyte characters using the UTF8 TrueType font.
create a portable PDF file of your report.
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.
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:
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.
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.
Congratulations! You have successfully created a matrix group above report. You now know how to:
create a matrix group above report with a single-query data model.
add labels and line for summaries.
add some white space between the master records.
create a Web layout without changing the paper 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".