Start Oracle Reports by double-clicking the Reports Builder icon on your desktop. The welcome screen displays.

You have several choices for designing your report. For this tutorial, you will use the Report Wizard (the default).

Click OK.

Click Next on the Report Wizard welcome page.

The default option is Create both Web and Paper Layout. Click Next to accept this option.

On the next page of the Report Wizard, you specify the style of the report that you want to build and can include a title for your report.

Select Group Above as the style and enter Employee Report as the title.

Click Next.

For the data source, accept SQL Query as the data source type by clicking Next.

On the data source definition page, click Connect to connect to the database.

In the Connect dialog box, enter HR for the User Name, HR for the Password, and the name of your database for the Database field.

Click Connect.

You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file. For your convenience, a SQL query is provided for you. Click Import Query.

In the file import dialog box, click Report_Wizard.sql located in your working directory. Click Open.

The query fetches employee data from the database.

Now that the query has been defined, click Next to continue.

You specified that you wanted to create a Group Above report. The next page of the Report Wizard asks you to choose the field(s) by which you want to group the returned data. For this report, you will group employees by department.

Select DEPARTMENT_ID in the Available Fields list and click the [>] button. The field is now displayed on the right as the first level Group Field.

Click Next to continue.

You now need to decide on the fields you want to display in the report. DEPARTMENT_ID is already selected. From the Available Fields list, select EMPLOYEE_ID and click the [>] button. Repeat this process for FIRST_NAME, LAST_NAME, SALARY, and COMMISSION_PCT fields.

Ensure that the displayed fields are in the order shown below. You can drag and drop a field to modify the order.

Click Next.

This page of the Report Wizard enables you to define totals and calculations. For this report, you will display the average salary for each department.

Select the SALARY field in the Available Fields list and click the [Average >] button.

Note the field appears in the Totals list as a calculated field.

Click Next.

The wizard now allows you to modify the default labels and widths. For this tutorial, keep the defaults as they are. Click Next.

As the final step in the Report Wizard, you can include a template in the report definition. Templates allow you to create a standard look and feel, and also promote reusability. In this tutorial, you will use one of the predefined templates for your report.

Select Gray from the Predefined template list and click Next.

Click Finish. You now view the paper layout for your report in the Paper Design view of the Report Editor.

Close the Paper Design window.

Save your report as EmployeeReport_<your_initials>.jsp.

To save a report definition, select the report module in the Object Navigator window and click the Save icon in the toolbar , or select File > Save from the menu. The first time you save your report definition, the Save dialog box displays, giving you the opportunity to enter a meaningful name for your report.

Click Save.

Recall that you chose to create both a Web and paper layout for this report. To view the Web layout, click the Run Web Layout icon in the toolbar, or select Program > Run Web Layout from the menu.

The Web layout for your Employee Report displays in the default Web browser.

To preview the paper layout, click the Run Paper Layout icon on the toolbar, or select Program > Run Paper Layout from the menu.

The paper layout for your Employee Report displays in the Paper Design view.

Ensure that you have the Paper Design view of your report displayed.

Since your report displays salary information, it would be logical to apply a format mask to the data in that column.

Select the Salary column by clicking the data in the column.

With the Salary column selected, click the Currency icon in the stylebar to add a currency symbol to the data in the Salary column. 

Keeping the Salary column selected, click the Commas icon to add the thousand separator to the data in the column. Your report output should now look like this:

Apply the same formatting to the data displayed in the Average field. The Average field displays the average salary for each department.

Select the Average field by clicking the data in that field.

With the Average field selected, click on the Currency icon in the stylebar to add a currency symbol to the data in the Average field.

Keeping the Average field selected, click on the Commas icon to add the thousand separator to the data in the field. Your report output should now look like this:

The Employee Report includes the Commission_Pct column, but not all employees earn a commission. Navigate through the pages of the report using the Next Page icon until you reach Department 80. Employees in this department earn a commission. You want to ensure that this column displays two decimal digits to give the data a uniform look.

Select the Commission_Pct column by clicking on data in that column.

Keeping the Commission_Pct column selected, click the Add Decimal Place icon twice. Your report output should now look like this:

Next, you will customize the title for your report. The title, Employee Report, is a simple text string, referred to as ‘boilerplate text’. You can modify the text color, font, style, and size by using the stylebar, or alternately, the Format menu.

Select the title by clicking on the text string ‘Employee Report'.

Keeping the report title selected, use the Font drop-down list in the stylebar to choose a font face of your liking.

Modify the size of the font by using the Font Size drop-down list in the stylebar.

Note: Depending on the font size you select, you may need to increase the size of the boilerplate text object. To do this, select one of the object handles and drag it horizontally, or vertically, until the object is sized appropriately and the report title displays in its entirety.

You can also modify the text color. Keeping the report title selected, click on the Text Color icon in the vertical toolbar to display the color palette.

Select the color of your choice for the report title.

You have successfully customized the look and feel of the paper layout of your report to your liking.

Save your enhancements by clicking Save .

Select the report in the Object Navigator.

Select File > Generate to File > PDF from the menu. Save your report output as EmployeeReport_<your initials>.pdf, such as EmployeeReport_mjs.pdf.