Posted on May 17, 2018
Tony Tauro, Performance Architects
In Chapter I of this series, we created our database schema and learned how to create an APEX application that runs when you call a URL but does nothing else of consequence.
So, let us now start adding content to our very basic APEX application. First, we’ll add a report that shows us all of the expenses – essentially the contents of the “app_expenses” table.
- To do this, login to APEX, go to the “App Builder,” and select your application (the expenses app from the last chapter) to open it. You should see the screen like below. To build our report, we start with clicking on the “Create Page” button as shown below:
- On the “Create a Page” screen select “Report,” and then click on “Next:”
- On the next screen, select “Interactive Report,” and then click on “Next.”Note: You can also select “Classic Report” or “Interactive Grid.” The “Interactive Grid” has an editable version called “Editable Interactive Grid,” which we will explore in the next chapter. It’s a report that is also editable pretty much like an Excel sheet. But I digress. For now, we need a report, so let’s go with that (the “Interactive Report” option).
- On the “Create Interactive Report” screen, enter a “Page Name” (say “Expenses Report”), leave the “Page Number” field defaulted to “2,” and then click on “Next.”
- On the next screen, specify if you want an entry navigation menu or not. The navigation menu is in the sidebar, and we would like to be able to call up this report by clicking on it in the sidebar. So, choose “Create a new navigation menu entry” on this screen, enter a value, say “Expenses Report” in the “New Navigation Menu Entry” field, and then click on “Next.”
- On the following screen, you specify the report source. This is where you tell APEX what table or view you want your data from. So, specify:
- Source Type: Table
- Table/View Owner: Name of schema where your table is
- Table/View Name: app_expenses
- Select all columns
- Then click on “Create.”Note: if APEX is installed in a different schema than the one where your table is, then you’ll need to grant access to the table to the APEX schema. In SQL Developer (or TOAD or the like), run “GRANT SELECT ON app_expenses TO <APEX-SCHEMA>” – using the actual name of the schema where APEX is installed.
- Now APEX will create your page and present you with the screen below:
- There is a lot in this screen, but for now all you want to do is to run your report. To do that click on the “Run” button in the top right corner as highlighted. This will refresh your app (if it’s already open) or open your app to the current page showing you your report:
This is a very simple report but it’s not without its share of features, the most remarkable of which is the relative ease with which we can create the content…and with little…actually no…code! But let’s not kid ourselves; the first time our user sees this report she will find five things wrong with it and at least one of those will need some coding. We’ll look at some of the coding in later chapters. For now, admire your achievement and check out the features, in particular, the search bar, and all of the options under the “Actions” menu. I won’t dwell on those but here’s a quick list of what you can do:
- You can download the report to CSV, PDF and HTML (duh!)
- You can move the columns around, hide them, group and aggregate on them. You can even add a computed column
- You can filter, add highlights, create a pivot table or add a chart
- You can even sort on multiple columns using a different order for each
Pretty cool that these are all available to the end-users and not just for developers!
© 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.