Posted on June 13, 2018
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.
- 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.”
- We have to simply change this query to lookup the additional data we need. So change it thus:select USERNAME,
(select ID || ‘ – ‘ || DESCRIPTION
(select DESCRIPTION FROM APP_EXPENSE_STATUSES
from APP_EXPENSES mainTo 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.
- 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.
© 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.