TECHNOLOGY: Browser-Based

As Published In September/October 2010

User-Built Applications

Oracle Application Express 4.0 Websheets enable end users to integrate, share, and maintain content.

The Websheets feature in Oracle Application Express 4.0 gives business users a quick and simple way to integrate, interact with, and share data and other content on the Web. By using SQL tags, declarative database reports, and data grids, users can control the content and structure of Websheet applications without relying on developers.

In this column, you’ll build a Websheet application that integrates existing data from the sample database application in Oracle Application Express with data you enter locally in a data grid. You can run through these steps on the hosted instance of Oracle Application Express 4.0 at apex.oracle.com. (You must request a free workspace to use this hosted instance.)

If the sample database application is not already installed, go to Application Builder -> Create, select Sample Applications, and click Install next to the Sample Database application image.

Creating a Websheet

1. From the Oracle Application Express home page, select Administration.

 

 

2. Click the Websheet Database Objects task.

 

 

3. If the Create Websheet Database Objects option appears, click that option, click Continue, and then click Create. (If instead you see options to remove or validate Websheet database objects, do nothing on this page.)

 

 

4. Select Application Builder -> Create.

 

 

5. Select Websheet, and click Next. Enter Magazine for Name, and enter Customers for Title for the Home Page Section.

 

 

6. Click Next, and then click Create.

 

 

7. Click the Edit Properties image.

 

 

8. In the SQL section, select Yes for Allow SQL Tags and Reports and then click Apply Changes.

 

 

9. Click the Run icon.

 

 

10. Enter your Oracle Application Express developer credentials, and click Login.

Your browser now displays the home page for a new Websheet application entitled Magazine. Next you’ll add some data to it.

Modifying Websheet Pages

1. From your Websheet application home page, click Edit in the top right corner of the Customers region.

 

 

2. Enter

    

    

   [[SQL: select * from demo_customers]] 

    

   in the Content box, and click Apply Changes. The home page now displays a customer report.

    

3. To improve the look of the report, click Edit in the top right corner of the Customers region. Replace the query in the Content box with

    

    

   [[SQL: select 
   cust_first_name||' '||
   cust_last_name Name, 
   cust_city City, 
   cust_state State, 
   phone_number1 Phone, 
   cust_email Email 
   from demo_customers 
   order by cust_last_name]]
   

    

   and click Apply Changes.

Now you’ll add a report to the Websheet. The report’s datasource is the sample database application’s Products table. Follow these steps to create the report:

1. From the menu, click Data -> Create.

 

 

2. Select Report, and click Next.

 

 

3. Select Table for Report Source. Click the up arrow to the right of Table or View Name, and select DEMO_PRODUCT_INFO, enter Product Information for Report Name, enter PROD for Report Alias, and click Next.

 

 

4. Click Create. An interactive report listing products appears.

 

 

5. To limit which columns are displayed, click Actions -> Select Columns. Select Product Id, Product Image, Mimetype, Filename, and Image Last Update. Click < to move these columns into the Do Not Display region, and click Apply.

 

 

6. To reorder the records, click Actions -> Format -> Sort, select Category for Column 1, select Product Name for Column 2, and click Apply.

 

 

7. Save the report as the default report. Click Actions -> Save Report, select As Default Report Settings for Save, and click Apply.

Return to the Customers page by selecting Page -> Home Page from the menu. Now you’ll add two new sections to the home page. One will contain the Product Information report you just created, and the other will contain automatically generated navigation links for the application. Follow these steps:

1. In the Page list on the right-hand side, click New Section, click Data, select Report for Data Selection Source, select Product Information for Report, click Next, click Next, and click Create.

 

 

2. Click New Section, select Navigation, select Section Navigation, click Next, enter 1 for Sequence, and click Create. The Navigation section, which appears at the top of the page, looks like the one in Figure 1.
3.  

 

Figure 1: Navigation section in a Websheet application

Try clicking the navigation links to test their functionality.

Adding a Data Grid

Follow these steps to create a data grid that contains information about sales events:

• From the Magazine application’s menu, click Data -> Create.

• Select Data Grid, click Next, select From Scratch, and click Next. Enter Events for Name. Enter the following column names and corresponding types: Event Date (Type: Date), Name (Type: String), Location (Type: String), Cost (Type: Number), and Sales (Type: Number).

• Click Create.

• Click Add Row.

• Use the date picker to select Event Date, enter values for the remaining fields, and click Save and Add Another.

• Repeat these steps to add three more records with different costs and sales, and then click Save.

An updatable interactive report showing the events appears. Make the following changes to it:

1. Edit a date by clicking a specific date and selecting a new date from the date selector that pops up. The new value will be saved to the data grid.

 

 

2. Edit the other fields by clicking each field and performing an inline edit. Click any other field to save the update to the data grid.

 

 

3. Edit an individual row by clicking the Edit icon for that record.

 

 

4. To add an attachment, click Add File, click the Browse button and locate the file, and then click Apply. To add a sticky note, click Add Note, enter some text, and click Apply. To add a URL, click Add Link, enter a name for Link Name, enter a target for URL Target, and click Apply.

 

 

5. Click Apply Changes to return to the report view.

 

 

6. To display the annotations you created in Step 3, click Actions -> Select Columns; select Files, Notes, Links, and Tags; then click > to move those columns into the Display in Report region; and click Apply.

Follow these steps to add a computational column to the data grid that compares sales with costs:

1. Click Actions -> Format -> Compute, enter Sales v Cost for Column Heading, select $5,432.10 for Format Mask, enter E - D for Computation, and click Apply.

 

 

2. Save the data grid as the default report. Click Actions -> Save Report, select As Default Report Settings for Save, and click Apply.

1. Click New Section.

 

 

2. Click Chart.

 

 

3. Click Horizontal Bar.

 

 

4. Select Data Grid for Data Selection Source, select Events for Data Grid, and click Next. Click Next again.

 

 

5. Select Event Date for Chart Label, select **Sales V Cost for Chart Value, select Label - Ascending for Sort, and click Create.

 

 

6. Click the new Events link in the home page Navigation section to view the chart you’ve created.

Sharing Websheet Applications

Conclusion