TAGS

Recent Posts

Archives

Oracle APEX for Newbies: Chapter 5 – Report Modifications in a Demo Application
Posted on June 13, 2018
Author: Tony Tauro, Performance Architects

This blog post is the fifth in a series. It builds on the work done in previous chapters including the first chapter, where we created the schema. Links to previous blogs are:

In Chapter 2, we created a simple report based on a database table. Sandy, our hypothetical end-user, has since seen this report and “requested” a few changes.

Sandy does not understand the numbers in the “Expense Type” and “Status” fields, and those need to be replaced by descriptions. Now she seems to recognize the “Cost Center” numbers and wants to continue to see those, but feels that it would also be helpful to have a description.

These are actually pretty straightforward changes.

  1. Open the page in the “Page Designer.” Go to the “Component View,” then click on “Expenses Report” in the “Content Body.: Now observe the “SQL Query” in the “Property Editor.”
  2. We have to simply change this query to lookup the additional data we need. So change it thus:select USERNAME,
    EXPENSE_DATE,
    (select DESCRIPTION
    from APP_EXPENSE_TYPES
    where type=MAIN.EXPENSE_TYPE
    and rownum<=1)
    EXPENSE_TYPE,
    (select ID || ‘ – ‘ || DESCRIPTION
    from APP_COST_CENTERS
    where ID=MAIN.COST_CENTER
    )
    COST_CENTER,
    AMOUNT,
    (select DESCRIPTION FROM APP_EXPENSE_STATUSES
    where STATUS=MAIN.STATUS)
    STATUS
    from APP_EXPENSES main
    To understand what we are doing here, just compare it to the APEX-generated code from the previous screenshot. We are replacing the “EXPENSE_TYPE” field with a lookup from the “APP_EXPENSE_TYPES” table and using the “DESCRIPTION” field. The “rownum<=1” clause is just a backup and should be irrelevant once proper constraints are added to the table. We use the “EXPENSE_TYPE” alias because that is what the user expects to see, but a different alias would automatically update the column name in the report as well. We do the same thing with “STATUS.” For “COST_CENTER” we simply append the “ID” and the “DESCRIPTION” because Sandy expects to see both.

  3. Click “Save” and “Run.”

Note: Sometimes new columns get hidden by default columns. If so open the “Actions” menu, click on “Columns” and add the missing columns back.

Sandy is delighted.

Sandy is also a squirrel from Texas with a good knowledge of karate and science.

Share
This post was posted in Business and tagged APEX , Business Intelligence , Oracle , Oracle APEX , Oracle Application Express .
© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Leave a Reply

Your email address will not be published. Required fields are marked *