Building and Customizing an Interactive Report in Oracle Application Express 5.0

Purpose

This tutorial shows you how to build, use, and customize an Interactive Report in Oracle Application Express.

Time to Complete

Approximately 60 minutes.

Overview

Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in an Oracle Database Cloud Service.

In this tutorial, you use Oracle Application Express Release 5.0 to create, use, and customize an Interactive Report. For more information on Interactive Reports, please refer to Using Interactive Reports on Oracle Help Center.

Please keep in mind the following while running this tutorial:

• Logging into your Oracle Application Express workspace: Your Oracle Application Express workspace may reside in an on-premises Oracle Database or in Oracle Database Cloud Services. The login credentials differ depending on where your workspace is located:
  • Logging into Oracle Application Express in a Oracle Database Cloud Service:  Reference the Oracle Help Center for your Oracle Database Cloud Service. To do this, go to the Oracle Help Center for Cloud, and select Platform and Infrastructure. From here, select your Database Cloud Service and the Get Started page will appear.
  • Logging in to Oracle Application Express on-premises: From your browser, go to the location of your on-premises installation of your Oracle Application Express workspace provided by your Workspace Administrator.
• Application ID: Screenshots in this tutorial show a blurred Application ID. Your Application ID can be any value assigned automatically while creating the application.
• Schema: If you are accessing an Oracle Application Express workspace in Database Schema Service,  you have one schema assigned to you with a schema name that you cannot change. If you are accessing the workspace in an on-premises Oracle database, you may have more than one schema assigned to your workspace by the Oracle Application Express Instance Administrator.

What Do You Need?

Before starting this tutorial, you should:

• Have access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
• Install Oracle Application Express Release 5.0 into your Oracle Database (for on-premises only).
• Provision at least one Oracle Application Express user. See Creating New User Accounts in Oracle Application Express Administration Guide.

In this topic, you create an application with an Interactive Report. To create an Interactive Report, log in to your Oracle Application Express workspace, either on-premises or in Oracle Database Cloud Service, and perform the following steps:

1. From the Oracle Application Express home page, click the Application Builder icon.
   

   

2. Click Create >.

   

3. Click the Desktop icon.

   

4. Enter Interactive Reports OBE for Name, and click Next >.

   

5. By default, the Home page gets created. To add another page, click Add Page.

   

6. Select Report and Form for Page Type. Select Home(1) for Parent Page, DEMO_PRODUCT_INFO for Table Name, Interactive for Report Type. Click Add Page.

   

7. Click Next >.

   

8. Accept the default, and click Next >.

   

9. You want to specify the type of authentication used to access this application. Select Application Express Accounts for Authentication Scheme. Also, you can specify a Date Format for the entire application. Click the List of Values icon for Date Format.

   

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

    

11. Click Next >.

    

12. Click Create Application.

    

13. Your application is created. Click Run Application.

    

14. The Log In screen appears. Enter your Oracle Application Express credentials, and click Log In.

    

15. The Home page is displayed. Select Demo Product Info in the navigation menu.

    

16. 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.Click Edit Page 2 in the developer toolbar.

    

You can add images to your Interactive Report. In this section, you add a product image for each product. Perform the following steps:

1. You want to change your query for this interactive report. In the Rendering tab, click DEMO_PRODUCT_INFO.

   

2. In the property editor, under Source, click the Code Editor: SQL Query icon.

   

3. The Code Editor - SQL Query opens. Replace the existing SQL query with the following SQL query, and click the Validate icon.
   select p.product_id,
       p.product_name,
       p.product_description,
       p.category,
       p.product_avail,
       p.list_price,
       (select sum(quantity) from demo_order_items
           where product_id = p.product_id) units,
       (select max(o.order_timestamp) od from demo_orders o, demo_order_items i
           where o.order_id = i.order_id and i.product_id = p.product_id) last_date_sold,
       p.PRODUCT_ID image
from demo_product_info p

   

4. A message is displayed as "Validation Successful". Click OK.

   

5. You also want to make some changes to the report column headings. In the Rendering tab, under DEMO_PRODUCT_INFO, expand Columns.

   

6. Under Columns, click PRODUCT_NAME.

   

7. In the property editor, under Heading, change Heading to Name.

   

8. Under Columns, click PRODUCT_DESCRIPTION.

   

9. In the property editor, under Heading, change Heading to Description.

   

10. Under Columns, click PRODUCT_AVAIL.

    

11. In the property editor, under Heading, change Heading to Available.

    

12. Under Columns, click LIST_PRICE.

    

13. In the property editor, under Heading, change Heading to Price.

    

14. Under Columns, click LAST_DATE_SOLD.

    

15. In the property editor, under Heading, change Heading to Last Sale.

    

16. Under Columns, click IMAGE.

    

17. In the property editor, under Identification, select Display Image for Type.

    

18. Under BLOB Attributes, click the List of Values:Table Name icon.

    

19. Click the DEMO_PRODUCT_INFO link.

    

20. Under BLOB Attributes, select PRODUCT_IMAGE for BLOB Column.

    

21. Under BLOB Attributes, select PRODUCT_ID for Primary Key Column 1.

    

22. Under BLOB Attributes, select MIMETYPE for Mime Type Column, FILENAME for Filename Column, and click Save and Run Page.

    

23. The report now displays image column along with other information. You can show or hide any columns on the report using the Actions menu. Click Actions, and click Select Columns.

    

24. Select Units and Last Sale. Click < (Remove).

    

25. Both the selected columns are moved to Do Not Display area. Click Apply.

    

26. Notice that the report no longer displays the Units and Last Sale columns.

    

Now that the Interactive Report has been created, you can manipulate the report in many ways. In this section, only some of them are discussed. You can review more on this topic in the tutorial, Using Interactive Report Regions with Oracle Application Express 5.0. Perform the following steps:

1. You can search for values in the report. Enter shoes in the search bar, and click Go.

   

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

   

3. To hide a column, you can select the column header link and then click the hide column icon. Alternatively, from the Actions pull down, you can select Select Columns and move the column to the Do Not Display area. In this case, click Available, and select the Hide Columns icon.

   

4. Notice that the Available column is no longer displayed on the report.
   Note: You can show the column again by clicking Select Columns from the Actions menu.

   

5. You can remove a filter by selecting the Remove Filter icon next the to filter or deselect a filter by unchecking the check box next to the filter.
   In this case, click the Remove Filter icon next to the filter.

   

6. To sort on a column, click the header you want to sort on and select the Sort Ascending or Sort Descending icon. Select the Name column and select the Sort Ascending icon.
   Alternatively, from the Actions menu, you can select Format > Sort, and select multiple sort levels.

   

7. The report is now sorted on Name.

   

8. You can create a control break on a column. Click the Category header, and select the Control Break icon.
   Alternatively, from the Actions menu, select Format, and then select Control Break.

   

9. The control break is created. Notice the Category column is eliminated in the list and now appears before each break point in the report.

   

10. Scroll down to the bottom of the report. Notice that there are 10 rows on this page. You can set the rows per page to be displayed.

    

11. Scroll to the top of the page. From the Actions menu, select Rows per Page, and select 5.

    

12. Scroll to the bottom of the page again and notice that only 5 rows are displayed. In order to go to the next page, you can select the Next icon. In this case, scroll to the top of the page again.
    
    

    

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

    

14. You want to highlight the row of a product if its price is higher than $80. Enter Top Tier Products for Name, make sure Enabled is set to Yes, Highlight Type is set to Row, and select [blue] for Background color. Under Highlight Condition, select Price for the Column, select > for the Operator and 80 for the Expression, and click Apply.

    

15. Notice that a highlight rule is created at the top of the report and the rows that meet the criteria are highlighted.

    

16. You can reorder or add additional columns to the report. From the Actions menu, select Select Columns.

    

17. Select Units in the Do Not Display region, and click > ( Move ) icon.

    

18. Units is moved to the Display in Report area. You want to move the Units column above the Image column. Select Units, and click the Up icon.

    

19. You want to remove the Product Id column from the report. Select the Product Id column from the list of Display in Report columns, and click the Remove icon.

    

20. Click Apply.

    

21. Notice that the Units column is displayed in the report and Product Id column is removed.

    

22. You can create a computed column. In this case, you want to create a column that shows the sales for each product. Click the Actions menu, select Format, and select Compute.

    

23. Enter Sales for Column Heading, and select $5,234.10 for Format Mask.

    

24. Under Columns, select Price.

    

25. Notice that the letter corresponding to the selected column appears in the computation area. Select * from the Keypad area.

    

26. Now, select Units under Columns.

    

27. Notice that the computation expression is created. Click Apply to create the Sales column.

    

28. Notice that the computed Sales column now appears in the report. If you want to reposition this computed column on the report, click Actions, and select Select Columns.

    

29. Select **Sales from the Display in Report area, and click the Up icon.

    

30. Click Apply.

    

31. Notice that the **Sales column is now displayed before the Image column. You can also aggregate a particular column. Click the Actions menu, select Format , and select Aggregate.

    

32. You want to total the Sales column. Select Sum for Function, **Sales for Column, and click Apply.

    

33. You now see total sales for each category.
    Note: The aggregated column inherits any formatting from the base column.

    

34. You can also create a group by report. From the Actions menu, select Format, and select Group By.

    

35. Specify the following and click Apply.

    Field	Value
    Add Group By Column	Name
    Functions	Percent of Total Sum
    Column	**Sales
    Label	Total Percent of Sales
    Sum	<Check the check box>

    

36. The Group By Report is displayed. It shows the Total Percent of Sales for each Product. Notice that it displays only 5 rows in the report. You want to change this setting to show all the records. From the Actions menu, select Rows Per Page, and select All.

    

37. All the rows are displayed. To return to the Report view, select the View Report icon.

    

38. You can save all the customizations you made to a saved report. Click the Actions menu and select Save Report.

    

39. 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.

    

40. You just created a private report called My Report. Notice that you now see a drop down list of the reports you have created. This customized view will be available each time you log in.

    

41. To go back to the other view, select Primary Report from the list of reports.

    

42. With the Primary Report selected, select Reset from the Actions menu.

    

43. Click Apply.

    

44. Note that the report has been reset back to the original version of the report. To go back to the report you formatted, select your private report, My Report from the list.

    

45. The formatted report is displayed. In the next section, you will examine some additional customization options. Select Edit Page 2 in the developer toolbar.

    

As a developer you can change the way the Interactive Report is rendered for all users. Perform the following steps:

1. In the Rendering tab, under DEMO_PRODUCT_INFO, click Attributes.

   

2. In the property editor, under Actions Menu, select the following values:

   Property	Value
   Highlight	No
   Save Public Report	Yes
   Subscription	Yes

   

3. Under Download, ensure that all the check boxes are selected for Download Formats, and click Save and Run Page.

   

4. Select Save Report from the Actions menu.

   

5. Enter Product Report by Category for Name and check the Public check box and click Apply.

   

6. Select the drop down list of Reports and notice that you now have a public report which can be accessed by anyone. The private report can only be accessed by the person who created it.

   

7. From the Actions menu, select Format. Notice that the Highlight option is no longer available because you deselected it from Actions Menu, in the property editor.

   

8. From the Actions menu, select Download.

   

9. Select the Email format.

   

10. To send the report via email, you can enter an email ID in the To field and click Send. In this case, you click Cancel.

    

11. Below is a view of the report that appears in the email message.

    

12. To set up an automatic email of the report to be sent on a scheduled basis, select Subscription from the Actions menu.

    

13. Enter your email ID for Email Address, Product Report by Category for Subject, Daily for Frequency. You only want the subscription to last 1 day. So, select the next day's date for Ending, and click Apply.

    

14. Your subscription is created. To edit a row, click the edit icon in front of any row. In this case, click the edit icon in front of Bag.

    

15. The form is displayed. This form allows you to change the values for this product. You may not want to allow people to change the product information.
    In this case, you can exclude the column that allows you to view this form from the report. Click Cancel.

    

16. Click Edit Page 2 in the developer toolbar.

    

17. In the Rendering tab, under DEMO_PRODUCT_INFO, click Attributes.

    

18. In the property editor, under Link, select Link to Single Row View for Link Column.

    

19. Click Save and Run Page.

    

20. Click the edit icon in front of Bag.

    

21. The single row view is displayed. You can display the next row by clicking the > icon or click Report View to return to the report.

    

In this tutorial, you have learned how to:

• Create an Application with an interactive report
• Add an image to your interactive report
• Manipulate and customize your interactive report

• Oracle Cloud Home Page

• Other tutorials on Oracle Database Cloud Service - Database Schema Service

• Curriculum Developer: Anjani Pothula

• Other Contributors:Drue Swadener