Building a Websheet in Application Express 4.0

Enter the following URL to log in to Oracle Application Express.

http://localhost:8080/apex

To log in to Oracle Application Express, enter the following details, and click Login.

Workspace: <your workspace name>
Username: <your username>
Password: <your password>

Select the drop down next to Application Builder and select Websheet Applications.

Click Create.

Select Websheet and click Next.

Enter an Application Name and click Next.

Click Create.

You want to make a change to the properties of your websheet. Click Edit Properties.

Click the SQL subtab.

Change Allow SQL Tags and Reports to Yes and click Apply Changes.

You are ready to run the websheet. Click the Run icon.

Login as the same user you logged in to APEX as. In this case, enter obe for both Username and Password and click Login.

You can add a file. Select the Add link next to the File icon in the Annotations section.

Click the Browse... button to add your file.

Select the dog.gif file from the directory where you files are located and click Open. Enter an Alias and Description and click Add.

The file was added to your application. Now you can add to your Welcome page. Click the Edit link in the Welcome section.

You need to specify the link using Markup Syntax. If you don't remember the syntax, you can click the Markup Syntax link in the Help area.

There is markup text for each component in your websheet. Click the Application Content tab.

The markup text for each page is listed. Click the Files / Images subtab.

The file you just added is displayed. To see the syntax, select the arrow in front of the About at the bottom of the window. Since you want the file to be displayed inline on the page rather than displayed via download, you need to use the syntax [[IMAGE: <filename>]]

Click Close to return to the Page to add the syntax.

Change the Title and enter some text in front of the following markup syntax and click the arrow in the upper right corner of the Content box.

[[IMAGE: dog.gif]]

The Format Editor appears. Select the text you entered and change the size and color and make it bold. Then click Apply Changes.

Notice that your text and image are displayed. In the next topic of this tutorial, you create a new page.

In the Page area, select the New Page link.

Enter Employees for the Name, Employee List for First Section Name and the SQL below in the Section Contents area and click Create Page.

[[SQL:select * from emp]]

The SQL is executed and the page is displayed. You can customize the SQL further. Select the Edit link.

Replace the SQL with the following and click Apply Changes.

[[SQL: select e.empno "Employee Number", e.ename "Name", e.job "Job", m.ename "Manager", to_char(e.hiredate, 'DD Month YYYY') "Hire Date", to_char(e.sal, '$9,999.00') "Salary", to_char(e.comm, '$9,999.00') "Commission", d.dname "Department" from emp e, emp m, dept d where e.mgr = m.empno and e.deptno = d.deptno]] 

The page is displayed based on the SQL you entered. In the next section you will create a new data grid.

Select Data > Create.

Select Data Grid and click Next.

Select From Scratch and click Next.

You can define your own columns on this page or you can copy and paste from a spreadsheet. Click Previous to return to the previous wizard window.

Select Copy and Paste and click Next.

Open the tasks_2010.xls spreadsheet and select the entire spreadsheet of data and select ctrl+c to copy the data to the clipboard.

Switch back to Application Express and Enter Tasks for both Name and Alias, select the First Row Contains Column Heading check box and click ctrl+v in the Paste Spreadsheet Data Below area and click Upload.

The Data Grid was created successfully. In the next topic, you manipulate data in the grid.

You can hid a column in your data grid. Select the ASSIGNED_TO column header and select the Hide Column icon.

The ASSIGNED_TO column is now hidden. You may also want to remove the Data Grid created message. Click the 'X' in that message box. In the next topic you change data inline.

You can create a filter on a particular value in a column. Select the STATUS column header. Select closed (with the lowercase 'c') from the list of STATUS values.

A filter is created. Click in the closed value and you will be placed in edit mode.

Change the value so that the first letter is upper case so the status is Closed and press enter.

Notice that the Status value is now changed. You can remove the filter by clicking the Remove Filter icon.

Select the STATUS column header. Notice that Closed only appears once in the list now because the closed status was changed to match the other values.

You want to change all the On-Hold status values to Pending. Select Manage > Rows > Replace.

Select STATUS from the list of Columns, enter On-Hold in the Find What area and Pending in the Replace With area and click Apply.

Select the STATUS column heading again. Notice that all the On-Hold values were changed to Pending and the On-Hold value no longer appears in the list.

You want to change this column so that end users can not change the value, they can only select from a list of values. Select Manage > Columns > Column Properties.

Select STATUS for Column Name, select Select List for Display As, select - New List of Values Based on Current Values - for List of Values and click Apply.

Select one of the values in the Status column and notice now that it is a select list instead of a text field.

You can also add a column to your data grid. Select Manage > Columns > Add.

Specify the following and click Apply.

Notice that the new Priority column is displayed. Select one of the values. Notice that the select list is displayed.

Select Manage > History.

A list of changes you made is displays. Select the View Single Row icon in front of one of the rows.

The detail is displayed. In the next section, you add some annotations to your data grid. Select the Tasks breadcrumb.

Click the Edit icon for one of the rows in your data grid.

You want to add a file to this project. Select the Add File link in the Actions area.

Click Browse....

Select the hardware.xls file from the directory where your files are located and click Open. Then click Apply.

Your file was added successfully. Now you want to add a Note. Select the Add Note link in the Actions area.

Enter Task needs review and click Apply.

Your Note was added successfully. Now you want to add a link. Select the Add Link link in the Actions area.

Enter APEX OTN Page in the Link Name field and enter http://otn.oracle.com/apex in the URL Target area and click Apply.

Your link was created successfully. Now you want to add a tag. Select the Add Tags link in the Actions area.

Enter DAVID, MARCIE and click Apply.

Your tag was created successfully. Now you can return to the Report View and add the annotations you just added to the report. Click Report View.

You need to add the annotation columns to the report. Click Actions and select Select Columns.

Select Files, Notes, Links and Tags from the list of Do Not Display columns and click the Move icon to move the columns to the Display in report area.

Click Apply.

The columns are displayed. If you place your mouse over the files icon, you see the name of the file. You can click the file icon to download the file.

Place your mouse on the Notes icon and you see the note displayed.

To see the link, right-click the link icon and select Open Link in a New Tab.

The URL for the link is displayed.

Switch back to your Websheet. Select the Tags column header to see that you can select a tag (which shows the people separated) from the list which will show you all the projects that have that tag as an annotation.

Click Actions and select Save Report.

Select As Default Reports Settings from the Save drop down list.

An alternative report is a report that all users will see. It is equivalent to a public report. Select the Alternative Default Report Type, enter Tasks with Annotations for the name and click Apply.

You want to return to the Primary Report. Select Primary Reports from the list of reports.

You want to reset this report to its default settings. Note that it will look the same as the My Report Alternative Report until it is reset. Click Actions and select Reset.

Click Apply.

Notice that your report is reset to the default settings. To return to the other report, select Tasks with Annotations from the list of reports.

The report is displayed with the modifications made. In the next section, you create a Worksheet SQL Data Report.

Select Data > Create.

Select Report and click Next.

Enter DEPT for Table or View Name, Departments for Report Name and DEPT for Report Alias and click Next.

Click Create.

The Report is displayed. You want to change the header names and remove the single row view link column. Click Manage > Edit Attributes.

Change the following and select the Link Column subtab.

Select Exclude Link Column from Link Column drop down list.

Click Apply Changes.

The Report is displayed with your changes. In the next section, you add the data components (data grid and report) to your Employees page you created earlier.

Select Page > Page Directory.

Select the Employees page.

In the Page area, select the New Section link.

Select the Chart section type and click Next.

Select the Horizontal Bar chart type and click Next.

Make sure that Data Grid is selected for Chart Source. Select the Tasks Data Grid and click Next.

Accept the default and click Next.

Specify the following and click Next.

Click Create.

Scroll down to see the chart displayed. In the next topic of this tutorial, you create a Data section. In the Page area, select the New Section link.

Select the Data section type and click Next.

Select the Report for Data Section Source, select Departments for the Report and click Next.

Accept the default and click Next.

Click Create.

Scroll down to the bottom of the page to see the Report section. In the next topic in this tutorial, you create a Navigation section on the page. Scroll up to the top of the page.

In the Page area, select the New Section link.

Select the Navigation section type and click Next.

Select Section Navigation as the type and click Next.

Enter 1 for Sequence so that this section is placed at the top of the page. Enter Navigation for the Title and click Create.

Your Navigation section was created successfully. Select the Departments link to navigate to that section in the page.

The Departments section is displayed. In the next topic of this tutorial, you create a Text section on your Home page. Scroll up to the top of the page.

Select the Home breadcrumb to go to the Home Page.

Select the Navigation page section type and click Next.

Select Page Navigation as the navigation type and click Next.

You need to select a Starting Page that this Navigation Section will be based on. Select the List of Values icon to select a page.

Select the Home link.

Change the Sequence number to 1 so that it appears at the top of the page and click Create.

The Page Navigation section was created successfully. In the next topic, you create another text section on the Home page.

In the Page area, select the New Section link.

Select Text as the section type and click Next.

Enter Links for Title and enter the following in the Content area.

OTN Home Page: [[URL:http://otn.oracle.com/apex]]

Note that the line you entered contains HTMl Markup language. You also want to add a link to your Data Grid. In the Help area, select the Data Grids link.

Select the Data Grid Link from the list for Tasks, which should be [[data grid: tasks | Tasks]] and press ctrl+c to copy it to the clipboard.

Switch back to Application Express and press enter after the first line of content to go to the next line and enter the label Maintain Data: and then press ctrl+v to paste what you copied from the clipboard. You want to add another link to your Report. In the Help area, select the Reports link.

Select the Report Link from the list for Departments, which should be [[report: dept | Departments]] and press ctrl+c to copy it to the clipboard.

Switch back to Application Express and press enter after the second line of content to go to the next line and enter the label View Report: and then press ctrl+v to paste what you copied from the clipboard. Then click Create to add the page section.

The section was added to your page. In the next topic in this tutorial, you move this section to a different page. Select the Edit link for the Links section.

In the Tasks area, select the Move section to another page link.

Make sure Employees is selected for New Page, enter 5 for New Section Sequence so this section will appear after the Navigation section you created earlier and click Move Section.

Your section was moved. In the Child Pages area, select the Employees page link.

Notice that the Links section now appears.

Select Page > History.

You see a list of changes you made to the page sections. Select the View Single Row icon in front of one of the sections.

The detail of the change is displayed. In the next topic, you create an Access Control List to control access to the websheet.

Click Administration.

Click Application Properties.

Click the Authentication tab.

Notice that the current authentication is Application Express Account. To utilize this method, you need to create some new Application Express users so you need to add them in the Application Express Development environment. Click the Return to Application Express Development Environment icon.

Select Administration > Manage Users and Groups.

You will create three developer users. Click Create User >.

Enter ws_admin for Username, ws_admin@mail.com for Email Address and obe for both Password and Confirm Password and click Create and Create Another.

Enter ws_contributor for Username, ws_contributor@mail.com for Email Address and obe for both Password and Confirm Password and click Create and Create Another.

Enter ws_reader for Username, ws_reader@mail.com for Email Address and obe for both Password and Confirm Password and click Create User.

To view only the websheet users you just created, enter ws in the search area and click Go.

The users are displayed. You want to run the websheet again as the ws_reader user. Select Application Builder > Websheet Applications.

Click your websheet application icon.

Click Run.

Enter ws_reader for Username and obe for Password and click Login.

The Home page is displayed. Notice that because the user ws_reader is a developer, the access to the websheet application does not allow administration access (there is no Administration tab). It does provide Edit capability however so you need to create an Access Control List for the Websheet to specify the correct access. You need to login as the obe user to the Application Express Development Environment and edit the properties for the websheet as this user. Click the Return to Application Express Development Environment icon.

Enter obe for Workspace, Username and Password and click Login.

Click your websheet application.

Click Edit Properties.

Click the Authorization tab.

You want to create your Access Control List which specifies which users have which roles in the websheet. Click Edit Access Control List.

Click Create Entry >.

Enter ws_admin for Username, select Administrator for Privilege and click Create and Create Another.

Enter ws_contributor for Username, select Contributor for Privilege and click Create and Create Another.

Enter ws_reader for Username, select Reader for Privilege and click Create and Create Another.

Only an administrator can update the worksheet properties and you are logged in as the OBE user so you want to create one more entry. Enter <user you are currently logged in as> (in this case, the obe user) for Username, select Administrator for Privilege and click Create.

The users you created are displayed. Click the Application <n> Properties breadcrumb.

To view what access each type of user will have access to, click Access Control Roles.

A table is displayed which shows what type of access a user will have based on whether an access control list is defined against a particular task category. For example, a user with reader privilege will not be able to modify a page or section but a user with contributor privilege will be able to modify a page or section. When done, click Close.

You want to use the access control list you just defined. Select Custom for Access Control List Type and click Apply Changes.

Click Run.

Enter ws_reader for Username, obe for Password and click Login.

Notice that you still do not have administration access, but now, you also do not have edit access. You want to change users. Click Logout.

Enter ws_contributor for Username, obe for Password and click Login.

Notice that you still do not have Administration access but you do now have edit access. Lastly, you will login as the administrator user. Click Logout.

Enter ws_admin for Username, obe for Password and click Login.

You now have Administration access as well as view and edit access.