Step 3: Create the Report

In Oracle Application Express, a report is the formatted result of a SQL query. For this example, you use the tables that are installed with the sample application. In particular, the tables DEMO_PRODUCT_INFO and DEMO_ORDER_ITEMS are joined to show all products with detailed information.

Creating a report is as easy as stepping through a wizard. There are two types of report wizards in Oracle Application Express: the Easy Report and the SQL Report. The Easy Report wizard does not require you to provide any SQL to create a report; the wizard guides you through the steps for choosing tables and columns. For this example, however, you use the SQL Report wizard, which requires you to enter a SQL query.

To create a report by using the SQL Report wizard:

1. In the developer toolbar at the bottom of the page, click on New.
2. Choose Component, and click on Next.
3. Choose Report, and click on Next.
4. Choose SQL Report, and click on Next.
5. Optionally change the page name, and click on Next.
6. Where prompted, enter the following query:

   select p.category,
           p.product_name,
           p.product_description,
           p.product_avail,
           i.unit_price
      from demo_product_info p,
           demo_order_items i
      where p.product_id = i.product_id
   

7. Click on Next.
8. In the Region name field, enter Products.
9. Click on Next.
10. Click on Finish.
11. Click on Run Page.

Step 4: Add a Region to Hold an Input Form

In Oracle Application Express, content is placed on a page by use of regions. In step 3, you placed a Report region on the page, and now you add a Form region that will hold any form elements needed to control the report's appearance.

Oracle Application Express renders regions on a page from top to bottom in a sequence-number order that you specify when you create a region. To ensure that the Form region appears on the page before the Report region, you adjust its sequence accordingly.

Step 6: Add Button to Run Report

Next, you add a button to the Form region. Clicking on this button causes Oracle Application Express to render page 1 again.

To add a button to the page:

1. In the developer toolbar, click on New.
2. Choose Page Control, and click on Next.
3. Choose Button, and click on Next.
4. From the Region list, select Report Options.
5. From the Task radio group, choose Create a button in a region position, and click on Next.
6. In the Button Name field, enter RUN_REPORT.
7. In the Label field, enter Run Report, and click on Next.
8. In the Display Properties step, leave all attributes unchanged, and click on Next.
9. In the Branch to Page field, enter 1.
10. Click on Create Button.
11. Click on Run.

You have added a Run Report button to your form, so you can now rerun the report at will. Note that during the last step of the Create Button wizard, you created a branch, an instruction to the Oracle Application Express engine to go to a page and run it. In this case, the branch executes when you click on the Run Report button. Rather than going to a different page, the branch takes you back to page 1, allowing you to run the report over and over.

Step 7: Modify Report to Incorporate User Input

Now that you have created check boxes and a Run Report button, you need to adjust the report attributes so that end users can control which columns appear in the report. When an end user submits a page with checked check boxes, all checked values are submitted as a colon-separated list and automatically stored in session state. So if the Category and Description check boxes are checked, the item called P_SHOW_COLUMNS will have the following value:

CAT:DESCR

This value can be referenced with bind variable syntax—in this case:

:P1_SHOW_COLUMNS

To enable end users to control which columns appear in the report, you reference this bind variable in a display condition applied to several columns.

To set up the display condition with the bind variable:

1. In the developer toolbar, click on Edit Page 1.
2. In Page Rendering, under Regions, click on Products.
3. Switch to the Report Attributes view, by clicking on the Report Attributes tab.
4. Next to the PRODUCT_DESCRIPTION column, click on the edit icon.
5. Scroll down to Conditional Display, or click on the Conditions quick link at the top of the page.
6. From the Condition Type list, choose PL/SQL Expression.
7. In the Expression 1 field, enter

    instr (:P1_SHOW_COLUMNS, 'DESCR' ) >0
   

8. Click on Apply Changes.
9. Repeat the actions in Nos. 4 through 8 above for the CATEGORY and AVAILABILITY columns, making sure to reference the appropriate check box value (CAT and AVAIL, respectively).
10. In the Tasks region on the right side, click on Run this page.

You now see the report page again.

Step 8: Clean Up Report

Next, make a few cosmetic changes to the report to add the final touches. To clean up the report:

1. In the developer toolbar, click on Edit Page 1.
2. In Page Rendering, under Regions, click on Products.
3. Switch to the Report Attributes view, by clicking on the Report Attributes tab.
4. Under Report Column Attributes, click on the Custom radio button.
5. Adjust the column headings as needed. For example, change Product Avail to Availability.
6. Adjust the alignment of the UNIT_PRICE column to the right, by selecting right from the Column Align list.
7. Click on Apply Changes.
8. In the green vertical bar on the left side, click on Run Page.

Step 9: Creating an Access Control Rule

You're now ready to start applying access control, allowing only certain users to view certain columns in the report. For this example, you implement access control by using an authorization scheme.

Authorization schemes are centrally defined access control rules that can be applied to different elements within an application. For example, you can use an authorization scheme to control access to individual pages, tabs, fields, or buttons. You can implement authorization schemes by using simple declarative instructions such as "the value of item X has the value Y" or by using more-complex techniques that involve SQL queries selecting from multiple database tables. Often, authorization schemes perform some sort of check based on the username of the currently logged-in user. In this exercise, you implement access control on the UNIT_PRICE column in the report, by showing this column to all users except GUEST users.

The username of the currently logged-in user is determined by the authentication scheme, a method that verifies a user's identity and then informs the Oracle Application Express engine that it has succeeded and passes along the relevant username. That username may come from a login page inside an Oracle Application Express application or from an external login page, as is the case when you use Oracle Application Server single sign-on, for example.

Oracle Application Express applications allow the authentication scheme to be changed at any time. This gives developers the flexibility to change to authentication based on Lightweight Directory Access Protocol (LDAP) or Oracle Application Server single sign-on, for example, without changing any application logic. For this exercise, you use an authentication method that doesn't challenge users for a password but simply sets the currently logged-in user to whatever username that person entered in the username field on the login page. Although this is not a particularly secure authentication scheme, it is convenient for testing various user scenarios.

To create an authorization scheme:

Note that this implementation of an authorization scheme is purposely simplified. Rather than simply comparing the logged-in user with a single static value GUEST, authorization schemes usually query database tables to enforce access control.

Step 10: Applying Authorization

Having created the authorization scheme, you are now ready to apply it to the appropriate element in the application. Remember that only privileged users are allowed to view price data about products and that GUEST users are not allowed to see this information.

To apply the authorization scheme:

Next Steps READ about Oracle Application Express LEARN how to use Oracle Application Express

1. In the navigation bar at the top right of the page, click on Build.
2. In the page list, click on Page 1.
3. In Page Rendering, under Regions, click on Products.
4. Click on the Report Attributes tab.
5. Next to the UNIT_PRICE column, click on the edit icon.
6. Scroll down, and under Authorization, from the Authorization Scheme list select the newly created scheme Guest user not allowed.
7. Click on Apply Changes.
8. In the Tasks region on the right side of the page, click on Run this page.

Step 11: Testing Access Control

To test access control, do the following:

1. At the top right of the report page, click on Logout.
2. In the Username field, enter guest.
3. Click on Login.

Note that the price column does not appear in the report, because you are now logged in as a GUEST user.

Conclusion