Search Bar

Install Oracle Database 9.2 or above.

Download and Configure Oracle Application Express 3.1 and have access to the Oracle Application Express 3.1 Sample Application.

The Select Columns icon identifies which column to search (or all).

Run the Sample Application in Oracle Application Express 3.1. You can get the Application Express URL from your Systems Administrator. Note: Login as the demo user and the password is the all lowercase name of the workspace in which this application is located. (Hint: the workspace name is displayed in the lower left corner of the application home page).

Enter jan in the text area and click Go. Note: when the Sample Application is installed, the dates are updated so that the orders are current. As a result, you may have slightly different data in your report. Review the data and enter the first 3 letters of the month that is currently assigned for Albert Lambert. For example, if the month for Albert Lambert is currently June, enter jun in the text area and click Go.

The search is not case sensitive, so it finds 'January 2008' in the Order Month. Notice that the filter shows below and the word is no longer in the text area which allows you to create multiple filters.

Create another filter. Enter Logan in the text area and click Go.

Notice that the list only shows rows that meet both filter criteria.

You can remove a filter by clicking the red X next to the filter you want to remove. Click the red X next to the first filter.

Remove the other filter by clicking the red X.

You can also create a filter for just one column using the select columns icon. Click the Search Columns icon and select Customer Name.

Enter jan in the text area and click Go.

This time you receive no hits because the filter was only applied to the Customer Name column. Click the red X next to the jan filter to remove it.

You can change the number of rows that are displayed in an Interactive Report. Click the Products tab in the Sample Application.

Another Interactive Report is displayed. Notice that only 5 rows are shown. Select 10 from the Rows drop-down list.

Click Go.

You now see more rows in the report.

Switch back to the Home tab to the previous Interactive Report.

Select the view icon to the left of any row in your report.

Note: A single record view icon may not be included in your report. If it is, it might be a different icon (determined by the developer) but will always be the first column shown.

A single-record view of that record is shown. Note this may take you to a custom page depending on the application. You can scroll through the records by using the arrow buttons. Click the > button to scroll to the next record.

The next record is displayed. The Exclude Null Values check box does not have any affect on this page because all the columns are populated. If one of the columns was null, it would be hidden if the check box was selected.

This page is a view only form. This form can have more columns than the interactive report shows. The Displayed columns checkbox reduces this form to just the columns that were displayed on the interactive report. Click the Displayed Columns check box.

To return to the Interactive Report, click the Report View button.

Selecting Columns

Click the Products tab again to return to the Products Interactive Report Page.

Click the Actions Menu icon and select Select Columns.

To hide columns from the report display, select a column and click the < to move the columns to the Do Not Display area.

To reorder the columns, select a column and click the Up or Down arrow.

Click Apply to see the changes to the Report.

The changes you made have been applied.

Click the Actions Menu icon and select Filter.

You want to create a filter where the Category = Video. From the Column drop-down list, select Category.

Select Video from the list of expressions. Notice that this list of values is determined automatically based on all the values in the Category column. The list of expressions can show up to 500 unique values.

Click Apply to create the filter.

The filter has been applied. You can have more than one filter for a report. If you decide that you want to disable a particular filter, you can click the check box to disable the filter.

You can add another filter. Click the Actions Menu icon and select Filter.

Select Price from the list of columns.

Select > for the Operator.

Enter 100 for the Expression and click Apply.

Notice that there are now 2 filters applied. The rows displayed are fewer now because they are only the rows that meet the filter criteria.

You can remove each filter by clicking the red X next to the filter you want to remove. Click the red X for both the Price > 100 and Category=Video filters.

The filters has been removed.

Click the Actions Menu icon and select Sort.

You want to sort the report in ascending order by price. From the Column drop-down list select Price.

Note: The null sorting field indicates where null values should appear in the report for the column being sorted. If set to default, nulls will default to the value set in the Direction field for this sort entry. You can also force nulls to always show first or last.

Click Apply to sort the report.

Notice that the report is now sorted by Price. The sort indicator is located next to the columns that are sorted. The arrow indicates whether it is in ascending or descending order.

To change the sort to descending order, you can click on the sort ascending icon in the header for Price and it will change to sort descending. This is discussed and shown in a later topic.

Click the Actions Menu icon and select Control Break.

You want to create a control break on Category. From the Column drop-down list select Category.

Click Apply to create the control break

Notice that the control break was created and the Category column was extracted from the report. In this case, the report now shows a list of Products by Category.

Click the Actions Menu icon and select Highlight.

Enter Only One Customer for the Name and select Cell for the Highlight Type. Notice that you can also highlight for an entire row if the row meets the criteria that was set for the filter.

Select [yellow] for the Background Color and select the color icon for the Text Color.

Select any color from the pallette.

You want to highlight the Customers cell when the value is set to 1. From the Column drop-down list select Customers. Then enter 1 for the Expression and click Apply.

The filter has been applied and the cells that meet the criteria are now highlighted.

Click the Actions Menu icon and select Compute.

You want to create a computation column that has the calculation Price * 1.05. Enter Price With Tax for the Column Heading and select the down arrow for the format mask and select $5,234.10.

Place your cursor in the computation field and click Price from the list of Columns.

An F will appear in the Computations area. F appears because column aliases are used for the columns and the alias is displayed in the table on the left. Click * from the Keypad.

An * will now appear after the F in the Computations area. Enter 1.05 after the * so that the computation now reads F*1.05. Then click Apply.

The new computed column now appears in the report.

Click the Actions Menu icon and select Aggregate.

You want to add an aggregate that is a sum of Price. Select Sum from the list of Functions.

Select Price from the list of Columns. Note: Only base columns can be used in aggregates, not computed columns.

Click Apply.

The aggregate now appears in the report. Notice that the sum is shown for Price per control break.

Click the Actions Menu icon and select Chart.

You want to create a pie chart that shows how many products there are per category. Select the Pie chart type

Select Category for the Label.

Select Count for the Function. Note: You do not select a column for Value in this case because you are doing a count. For other functions, you would select a Value column

For pie charts, you can select a sort order, you can sort by label descending or you can sort on categories within the pie. For the function, you will not select a Sort Value. Click Apply.

The pie chart is displayed. You can edit the chart or switch back to the report. Click View Report.

In order to show a flashback query, you need to make a change to the database first. Click Create Product.

Enter iPOD for the Product Name, 4GB Memory for the Description, select Audio from the Category list, make sure Y is select for Available and 250 is entered for the Price. then click Create.

The iPOD product is now displayed in the report.

Click the Actions Menu icon and select Flashback.

You want to show the data as of 5 minutes ago. Enter 5 in the field and click Apply.

The flashback was applied and you do not see iPOD in the list because it was created less than 5 minutes ago.

Click the Actions Menu icon and select Save Report .

Enter a name for the report and click Apply.

A tab with the name of your report was created and the details (filters, control breaks, sorts, etc.) have been saved. You can also view the working report. Click Working Report.

The working report is now displayed. You can make any changes to this report and it will not be reflected in the saved report.

Click the Actions Menu icon and select Reset.

Click Apply to confirm that you want the reset to be performed.

All the filters, control breaks, etc. have been lost and the report has been reset. Notice that you still have a tab for your Saved Report. Click your saved report tab.

The Saved Report changes are still entact.

Click the Actions Menu icon and select Download.

Select a format to download, in this case CSV.

A CSV file was created with all the data.

Sorting Columns

Selec the Working Report tab.

Select the Category Header and select the sort ascending icon .

Notice that the sort ascending icon is now next to the Category header indicating that this is the column that the report is currently sorted on. Note that the sort descending icon works the same way as the sort ascending icon .

Select the Sales Header and select the Hide Column icon .

Notice that the Sales column no longer appears in the report. Note: To get the column back, use the Select Columns option from the Actions menu.

Select the Category Header and select the Control Break icon .

The report now has a control break on the Category column.

Select the Name Header and select the Column Information icon .

The Column Information is displayed.

Select the Name Header and enter the word player in the text area. Notice that the list of values changes as you type. Select the value you want to create a filter for.

Notice that the filter is created.