Creating Interactive APEX Reports Over OLAP 11g Cubes

What is APEX?

APEX is a Web-based application development and deployment tool integrated with Oracle Database 11g. Using only a Web browser, you can quickly create a reliable, secure, and scalable Web application that can be instantly deployed to thousands of users.

APEX combines the ease of use and productivity of a personal database with the availability and scalability of an enterprise database. APEX wizards and built-in features simplify the building of Web applications. You can quickly assemble database-driven applications with little or no programming, in a short span of time.

Import and Install a Predefined Workspace

In this topic, you log into APEX as the administrator. You then import and install a predefined workspace that contains an APEX developer. You will also create an APEX schema that provides access to the sample OLAPTRAIN schema.

Follow these steps to create the interactive report:

1.	Start your web browser and navigate to the Application Express login page for administrators using the following URL format: http://<hostame>:<port#>/apex/apex_admin Note: The port number is defined by the APEX administrator when APEX is installed. For example: http://localhost:8080/apex/apex_admin
2.	Log in using the username and password that was defined for the administrator when APEX was installed
3.	In the Home page, select Manage Workspaces.
4.	In the Manage Workspaces section, select Import Workspace.
5.	In the Import Workspace step: a. Click Browse. b. In the Choose file dialog, navigate to the location where you downloaded the predefined workpace, select SALESTRACK_WORKSPACE.sql, and click Open. c. Click Next. Result: the workspace is imported, as shown below:
6.	Next, click Install.
7.	In the Install Workspace step, enter SALESTRACK as the Schema Name, and oracle as the Schema Password. Then, click Next.
8.	In the Schema Assignment Status step, select the check box option at the top (as shown below), and click Next.
9.	In the Install Workspace confirmation window, click Install Workspace. Result: the workspace import and installation is complete:
10.	Click the Logout link to log out of the APEX Administration tool.

Back to Topic List

Create an Application

In this topic, you log into APEX as a developer and access the predefined workspace. WIthin the workspace, you define an application for your interactive report.

Follow these steps:

1.	Start your web browser and navigate to the Application Express login page using the following URL format: http://<hostame>:<port#>/apex Note: The port number is defined by the APEX administrator when APEX is installed. For example: http://localhost:8080/apex
2.	Log in using the following details: - Workspace: SALESTRACK - Username: olaptrain - Password: oracle
3.	If you are prompted for a new password (if not, move to step 4): a. Enter oracle as both the current and the new password, and then click Apply. b. Click Return. c. Once again, enter oracle as the password, and click Login. Result: You should now be at the APEX developer Home page, as shown here:
4.	Select Application Builder > Create Application, as shown here:
5.	In the Method step, select the first application option, as shown below, and click Next.
6.	In the Name step, enter OLAP 11g as the name and select OLAPTRAIN as the schema. Then, click Next.
7.	In the Pages step, perform the following: a. In the Add Page section, select Blank as the Page Type, enter Sales Analysis as the Page Name, and click Add Page. . b. In the Create Application section, click Create.
8.	In the Confirm step, click Create. Result: The new application appears in the Application Builder window.

Back to Topic List

Create an Interactive Sales Analysis Report

Next, you perform two primary tasks to develop the interactive report:

	Create three Hidden Items that are used later as part of a parameterized query. The parameters you define will be used for setting up dimension “drilling” in a later topic. For example, a hidden item named P1_PRODUCT will be used as a parameter for drilling down on the Product dimension in the sales query as follows: p.parent = nvl(:P1_PRODUCT, 'ALL_PRODUCTS') Note: This condition selects the children of the product represented by the parameter (and selects “ALL_PRODUCTS” if P1_PRODUCT is null). You will create similar hidden items for the Time and Geography dimensions.
	Create an interactive report region that: - Incorporates a predefined OLAP cube SQL query that drives the report - Shows sales performance by region, product, and time.

Perform the following steps:

1.	In the Application Builder window, which is shown at the end of the previous topic, click the Sales Analysis icon. Result: You are now in the Page Editor, as shown here.
2.	Create a hidden item P1_PRODUCT: a. In the Items pane, click the Create button. b. On the Item Type step, select Hidden and click Next. c. Then, select Hidden for the Hidden Item Type and click Next. d. In the Display Position and Name step, enter P1_PRODUCT for the Item Name, select Sales Analysis as the Region, and click Next. e. Accept the defaults for the item source and click Create Item. Result: The Items pane on the Page Definition window looks like this:
3.	Using the same techniques as in steps 2a - 2e, create a second hidden item named P1_GEOGRAPHY.
4.	Using the same techniques as in steps 2a - 2e, create a third hidden item named P1_TIME. When you are done, the Items pane should look like this:
5.	Next, you create an interactive report region. On the Page Definition page, find the Regions pane and click the Create button.
6.	In the Region - Type of Region step, select the Report option and click Next.
7.	In the Region - Report Implementation step, select Interactive Report and click Next.
8.	In the Display Attributes step (shown below), enter Explore as the title and click Next. Result: the Source step appears:
9.	In the Source step, you will use the predefined SQL query that you downloaded earlier (from the Prerequisites section) as the source for the report region. Perform the following: a. Select File > Open File from the browser’s main menu. b. Open the <download_location>/apex-olap.sql file, that you downloaded previously. c. Select the query and copy it to the clipboard. Note: This single SELECT statement, explained in the following image, is used to navigate vast regions of the OLAP cube. Notice that the parameter names match the hidden items you created previously. For more information on how to create OLAP Cube queries, see Querying OLAP 11g Cubes. This tutorial uses the same OLAP data model. d. Paste the query into the SQL SELECT statement box, and then select No for the Link to Single Row View option, as shown here: e. Click Create Region. The Regions pane should now look like this: