Wednesday, 11 September 2013

How to create a Dashboard for your Web-based Database Application - Part II

An excerpt from the book Create Rapid Web Applications Using Oracle Application Express - Second Edition

As mentioned earlier, the home page of your application carries six regions to present different information in a dashboard, related to sales. In this exercise you'll learn how those multiple regions are created. This post assumes that you have:

Downloaded and installed Oracle XE database on your PC
Upgraded Oracle Application Express (APEX)
Created Workspace for the application
Created the application with default pages

By default, Oracle APEX creates two pages (Home and Login) both for desktop and mobile applications. The desktop login page, that is used to access the application, usually doesn’t require any modification or enhancement. It comes with out-of-the-box functionalities and utilizes the authentication scheme, marked as current in Shared Components interface, to process the login request. The Home page, on the other hand, is created as a blank slate that you need to populate with content relevant to your application’s theme. For instance, the Home page of your Sales Web Application will have stuff related to sales as indicated in this post.

Let's create the first region to display current month's sales. As the name implies, this region will present sales figures in graphical format (using a dial chart), for the current month. The chart is dynamically rendered based on a SQL Statement each time the page is viewed. The value 870 being displayed on the chart is the sales value for the current month derived from the SQL SELECT statement in step 12 and is displayed by enabling Values attribute in step 14 below. Similarly, the attribute Major Ticks is enabled to display marks on the gauge chart. Major tick marks are the small marks used to represent a point on an axis scale, they indicate major intervals of an axis scale. For example, we set Major Interval attribute to 2500 in step 14 which created four equal intervals on the chart. This attribute works in conjunction with the max value (10000) set in the SQL SELECT statement.

1. Type http://localhost:8080/apex in your browser’s address bar and login to APEX using admin user’s credentials.

2. Click the Application Builder icon and then the Edit icon under Sales Web Application.

3. Click the Home link in the Name column (if you’re browsing the page in Report view). This will bring up the definition page (as shown below). Click the Component View button.

4. In the Regions section, delete the existing region named Breadcrumbs. APEX created this default entry at the time of application creation. Click the region name and then click the Delete button. Confirm deletion by clicking the Delete Region button.

5. Click the Create icon in the Regions section (as indicated in the above figure) to create a new region.

6. In the Region page, click on the Chart icon.

7. From Chart Rendering list, select HTML5 Chart.

8. Click the Gauges option.

9. Select Dial.

10. Enter This Month’s Sales in Title, select Standard Region - No Padding for Region Template (you’re free to test other available options for Region Template). Enter 20 in Sequence and click the Next button.

11. Select Look6 for Color Scheme, un-check the Show Labels box, and click Next. Use another scheme with a check on Show Labels to see the impact, after completing the exercise.

12. Enter the following SQL Query in the Enter SQL Query or PL/SQL function returning a SQL Query box. Click Next.

select sum(o.order_total) total, 10000 max, 0 low
from demo_orders o
where order_timestamp >= to_date(to_char(sysdate,'YYYYMM')||'01','YYYYMMDD')

13. Click the Create Region button. You'll be taken back to the Page Definition where you'll see a new region with the name This Month's Sales under the Regions section. Click this link to call the region for the following modifications:

Start New Row: Yes
Column: Automatic
Column Span: 4

14. Click the Chart Attributes tab on the same page to set the following chart attributes:

Chart Width: 300
Chart Height: 180
Values: Checked
Major Ticks: Checked
Major Interval: 2500

15. Save your changes by clicking the Apply Changes button.

This application is based on Theme 25 (new to APEX 4.2) that uses a grid layout (comprising 12 columns) to position page elements. The first attribute, in step 13, is set to Yes to put the region on a new row. Compare this value to the next region (Sales by Product), where it is set to No to place that region next to this one. The value Automatic in the Column attribute automatically finds a column position for the region. Since there exists no elements on the current row, column number 1 will be used as the starting place to position this region. As you saw in the figure, we have three regions on a single row. Equally divided in a 12 columns layout, each region spans 4 columns and this is the value we set for all the six regions on the Home page. This particular region will span from column number 1 to 4. Continue>>>

1 comment: