Install Oracle Database 9.2 or above.

Download and Configure Oracle Application Express 3.1.

Download the OEHR Sample Objects Packaged Application here. Import and install the application and its supporting objects into your Oracle Application Express 3.1 instance.

Login to Oracle Application Express 3.1 as your Developer user. Click Application Builder.

Click Create.

Click Create Application.

Enter an Application Name and click Next.

Select Report for Page Type, make sure Page Source is set to Table and Implementation is set to Interactive. Click the up arrow to select a table.

Select the OEHR_EMPLOYEES table.

Click Add Page.

Click Next.

Accept the default to create One Level of Tabs and click Next.

Click Next.

You want to accept the default authentication scheme. You can now in Application Express specify a Date Format for the entire application. Click the up arrow for Date Format.

Select the 12-JAN-2004 link. Note: By setting the date format when you create the application, the same date format will be used for all report columns and items.

Click Next.

There are 2 new themes in Application Express 3.1: theme 19 and 20. In this tutorial, you want to select Theme 20 and click Next.

Click Create.

Run the Application.

Login with the username/password you use when you login to Application Express and click Login.

The Report is displayed. Notice that the date format is set according to the date you specified when you created the application. In the next section, you examine some of the Interactive Report features.

You can search for values. Enter John in the search bar and click Go.

Notice that only the rows where john is found are listed. A filter was also created above the report data.

To hide a column, you can select the column header link and then click the hide icon column.

Notice that the column no longer appears in the report. Note: You can show the column again by clicking Select Columns from the Actions menu (discussed later in this tutorial).

To sort on a column, click the header you want to sort on and select the Sort Ascending or Sort Descending icon. Select the Last Name column and click the Sort Ascending icon.

The report is now sorted on Last Name.

You can delete the filter you created earlier. Click the red X next to the filter.

You can create a control break on a column. Select the Actions menu pulldown and select Control Break.

You want to break on the Department ID. Select Department ID from the list of columns and click Apply.

The control break was created. Notice that the column was eliminated in the list and now appears before each breakpoint in the report.

You can also highlight a particular cell or row in the report based on some criteria. Click the Actions menu and select Highlight.

You want to highlight the row of an employee if their salary is higher than $10,000. Enter Top Tier Salary for the Name, make sure Enabled is set to Yes, and Highlight Type is set to Row. Select [red] for Background color and [blue] for Text color. Under Highlight Condition, select Salary for the Column, select > for the Operator and 10000 for the Expression and click Apply.

Notice that a highlight rule was created at the top of the report and the rows that met the criteria are highlighted.

You can create a computed column. In this case, you will create a column to show the monthly salary for each employee. Click the Actions menu and then select Compute.

Enter Monthly Salary for the column header and select the down arrow for Format Mask and select the $5,234.10 from the list.

From the list of Columns scroll down and select Salary. Notice that the letter corresponding to the column appears in the computation area. This is the column alias. Click / in the keypad and then enter 12 after the / in the computation area. Then click Apply.

Notice that the computed column now appears in the report.

You can also aggregate a particular column. Click the Actions menu and select Aggregate.

You want to total the Monthly Salary column. Select Sum for the Function and **Monthly Salary (the ** indicates that the column is a computed column) for the Column. Then click Apply.

You now see a total salary for each department. Note: the aggregated column inherits any formatting from the base column.

You can save all the customization you have made to this report. Click the Actions menu and select Save Report.

Enter My Report for the Name and click Apply. Note: Save as Named Report is available to all authenticated end users (when saving reports is enabled for the region).  The report settings are displayed as a tab above the report, with the name you provide, and is visible *only* for the APP_USER who saved it. Save as Default Report Settings is only available to developers.  These settings are applied to anyone who comes to the page for the first time in a session.

Notice that you now see a tab with all the display changes you made. This customized view will be available each time you login.

You can view a record in the report by clicking the icon before the row you want to view.

The record is displayed.

To navigate back to the report, click Report View.

To show the original report (without the customizations), click the Working Report tab.

Click the Action menu and click Reset.

Click Apply.

The report is displayed. In the next section, you review some of the changes you can make to the report itself.

Click the Edit Page 1 link in the Developer Toolbar.

Under Regions, select the Interactive Report link.

You can hide columns so that they do not appear in the report. For the EMPLOYEE_ID and PHONE_NUMBER, select Hidden for Display Text As and click Apply Changes.

Run the page to see that the 2 columns are now hidden from the report. Note that the columns will also no longer appear under Select Columns from the Actions menu. You want to make some additional changes, click Edit Page 1 in the Developer toolbar.

Under Regions, select the Interactive Report link again.

Select the Search Bar tab.

You can change the way that your Interactive report is rendered to the end user. You can change the search bar and Actions menu in this section. In this case, deselect the Highlight check box. Then click the Download tab.

Notice that CSV is the only download format selected. Select XLS and PDF. Then click the Region Definition tab.

You can change the query that is executed when the report is run. In this case, you want to change the column for DEPARTMENT_ID to the DEPARTMENT_NAME in the OEHR_DEPARTMENTS table. Click the Source tab.

Change the query to the following and click Apply Changes.

select 
a."EMPLOYEE_ID",
a."FIRST_NAME",
a."LAST_NAME",
a."EMAIL",
a."PHONE_NUMBER",
a."HIRE_DATE",
a."JOB_ID",
a."SALARY",
a."COMMISSION_PCT",
a."MANAGER_ID",
b."DEPARTMENT_NAME"
 from   "OEHR_EMPLOYEES" a, "OEHR_DEPARTMENTS" b
 where a."DEPARTMENT_ID"=b."DEPARTMENT_ID" 

Since you modified the query, a confirmation window appears that tells you that you need to select Reset from the Actions menu in order for you to see the changes in your report. Click Apply Changes.

Click Run Page 1 to see the changes you made.

The report is displayed. In order for the Department Name column to appear, you need to reset the report.

Select the Actions menu. Notice that the Highlight option is no longer in the list. Click Reset.

Click Apply.

The Department Name column is now shown.

To check that the other download options are displayed, click the Actions menu and select Download.

The three options you selected are displayed. Click PDF.

When the download dialogue appears, click OK (or Open depending on what browser you have) . The PDF is displayed. In the Creating PDF Reports with Oracle Application Express 3.1 tutotial, you learn ways to format this PDF file. Close this window.

Navigate to the Page 1 Page Definition. Under Regions, select the Interactive Report link again.

From the Interactive Report, you can create a group that will organize the single row view and group columns together in Select Columns from the Actions menu. Click the Groups tab.

Click Add Group.

Enter Sensitive Data for the Group Name and click Create.

To add the columns to assign to the Sensitive Data Group, click the Edit icon.

Select Salary and Commission Pct from the list and click >. Then click Apply Changes.

Click Run Page 1.

Notice that on the Single Row View page, the two columns are now grouped together. Click Report View to return to the report.

You can also change what happens when the Single Row View icon is clicked. You can link to a custom page or exclude the Single Row View from the report altogether. Navigate to the Page 1 Page Definition. Under Regions, select the Interactive Report link again.

Click the Link Column tab.

Select the Link Column drop down list.

Notice that you can link to a Custom Target (or another page or URL) or Exclude this column altogether. Select Exclude Link Column.

Click Apply Changes.

Click Run Page 1.

Notice that the Single Row View icon for each row in the report no longer appears.