OracleAS Portal Charts

OracleAS Portal Charts give developers the ability to graphically display information as a portlet on their OracleAS Portal pages. Like OracleAS Portal Forms and Reports , OracleAS Portal Charts are developed using a series of wizard pages and can be created with various customization options that can then be granted to certain users. As you will see, OracleAS Portal gives developers great freedom in choosing the type of chart to display and what formatting options are to be used when displaying the chart.

To create a new OracleAS Portal Chart, bring up the Navigator by clicking the Navigator link in the top right of any OracleAS Portal page. Click the Providers tab if it is not already selected. Click the Locally Built Providers link, and then the name of the provider you have been using to build the examples so far. On the top left of the page, click the Create New... Chart link.

The first page gives you the option of creating a chart from the Query Wizard or a chart from a SQL Query. Building the chart using the Query Wizard allows you to quickly build and deploy a chart, but developers are restricted to a limited number of features. Building a chart from a SQL Query gives the developer greater flexibility but requires more work (particularly when constructing the driving SQL query). With either selection, you are presented with the now-familiar first page of the Chart Wizard (Figure 9-19) after choosing what type of chart to build.

Figure 9-19: The first page of the Chart Wizard

If you are building a Chart from Query Wizard (CQW), the next step of the wizard, Step 3 of 10 (again, no Step 2), asks you to specify which table or view will be used to drive the chart (Figure 9-20). As in the wizards before, clicking the small notepad icon to the right of the text box will not display tables or views that can be resolved through database links; it will display only those database tables and views that the schema associated with the database provider of this chart has access to. Since Oracle recommends that production data not be stored in the infrastructure database, we have seen that this is of limited practical use. Most production environments will use the Repository Creation Assistant to install the Portal Repository into their existing Oracle Database, but portals accessing information from multiple Oracle database instances will still need to use database links.

Figure 9-20: The Table or View page of the OracleAS Chart Wizard

Note	You are limited to returning data from one, and only one, table or view when building a CQW.

If you are building a Chart from SQL Query (CSQ), the next step of the wizard, Step 3 of 10, asks you to specify an odd-looking query to retrieve the data that will drive your chart:

 select     null     the_link,     ENAME    the_name,     SAL      the_data from SCOTT.EMP order by SAL desc

In order to build a chart from a SQL query, you must construct the query in the format shown here: a select with three columns , all aliased:

A link name, aliased as the_link (which can be NULL)
A column that will supply the values for the y-axis (for a horizontal chart) or the x-axis (for a vertical chart), aliased as the_name
A column that will provide data for the chart, aliased as the_data

The first time you try to construct a query in this manner, it may seem a little awkward , but after a few attempts, it will become second nature. The biggest difference between a CQW and a CSQ is the flexibility that can be incorporated into the query that drives a CSQ.

As mentioned earlier, a CQW is limited to only one table. It is only possible to apply the standard SQL functions provided for you in Step 4 (discussed shortly) of the chart wizard for a CQW: SUM, COUNT, AVERAGE, MAX, MIN, STDDEV, and VARIANCE. A CSQ can use multiple tables to return data and embed any SQL or user -defined function in the query. As an example, consider the following parent-child relationship between the ORDERS and LINE_ITEMS tables:

 SQL> desc orders  Name                                      Null?    Type  -    ORDER_ID                                           NUMBER  CUSTOMER_NAME                                      VARCHAR2(20) SQL> desc line_items  Name                                      Null?    Type  -    ORDER_ID                                           NUMBER  LINE_ITEM                                          NUMBER  PRODUCT_DESC                                       VARCHAR2(20)  AMOUNT                                             NUMBER

If our requirements were to create an OracleAS Portal Chart that had the customer_name field as the y-axis (for a horizontal chart) and the amount of money each customer spent in our store, we could not use a CQW, as it allows only one table or view to be specified (we could create a view that joins the two tables together and then build a CQW off of that, but I digress). We can construct a query using a CSQ like this:

 select     null                the_link,     o.customer_name     the_name,     sum(li.amount)      the_data from orders@iasdb o, line_items@iasdb li where o.order_id=li.order_id group by o.customer_name order by sum(li.amount) desc

Note	To see the creation of the iasdb database link used in this example, see the section OracleAS Portal Reports earlier in this chapter.

If the data in the ORDERS and LINE_ITEMS tables looked like this:

 SQL> select * from orders;   ORDER_ID CUSTOMER_NAME            1 Smith          2 Jones          3 Brown SQL> select * from line_items;   ORDER_ID  LINE_ITEM PRODUCT_DESC             AMOUNT              1          1 Batteries                  4.99          1          2 Cookies                    3.99          2          1 Magazine                   5.99          2          2 Fountain Drink             1.99          2          3 Hot Dog                    2.99          3          1 Big Gulp                   2.99          3          2 Car Freshener              6.99

the preceding query would produce a chart like Figure 9-21 (after we ve completed the succeeding steps, of course).

Figure 9-21: A chart based on a multi-table query

If you are creating a CQW, Step 4 of the chart wizard (Figure 9-22) asks you to specify what column from our table selected on the previous page will be used to drive the chart. The link field specified here is similar to the link field available to us in the Column Formatting page of the Reports Wizard. The purpose of a link, if you remember, is to create a pathway between OracleAS Portal objects. Since we have not created any links yet (we will do so in the next chapter), there is nothing to select here.

Figure 9-22: The Table or View Columns page of the OracleAS Chart Wizard

Note	After we create the links in the next chapter, we will revisit the reports and charts created in this chapter and update them with link capability.

The group function allows developers to specify what type of grouping function will be applied to the data. The following group functions can be applied: SUM, COUNT, AVERAGE, MAX, MIN, STDDEV, and VARIANCE.

Step 5 of the CQW Chart Wizard allows you to specify conditions for displaying the report. This is the equivalent of adding where clauses to the end of a SQL statement. Step 6 of the CQW Chart Wizard is similar to the Formatting Conditions page of the Report Wizard (if you are building a CSQ, the Formatting Conditions page is Step 4 in the wizard). Depending on the values returned by the query when the portlet is displayed, the bars or column headings that make up the chart can be altered to display specific colors, and the column headings can be changed (bold, italics, etc.) and have their sequence altered , changing the order that the columns of the chart are displayed.

In Step 7 of the CQW Chart Wizard, the Display Options page (Figure 9-23) is displayed (if you are building a CSQ, this is Step 5). It is similar in functionality to the Display Options page of the OracleAS Portal Reports Wizard. It is broken into four sections:

Figure 9-23: The Display Options page of the OracleAS Portal Chart Wizard

Common Options Affects the entire chart, regardless of whether it is displayed in stand-alone (full page) mode or as a portlet. The Expire After field serves the same purpose as the Expire After field on the Display Options page of the reports wizard: if the number specified here is greater than 0, the OracleAS Portal engine will cache the chart; if the number specified here is 0, a new chart will be generated every time it is accessed.
Full Page Options Affects the chart when it is displayed in stand-alone mode. Multiple Summary Options can be selected by holding down the SHIFT key and single-clicking the desired option. The full page options also feature a Chart Type, where the basic method that a chart uses to display data can be altered; this is not available for charts to be displayed as portlets.
Portlet Options Affects the chart when it is displayed as a portlet on an OracleAS Portal page. Page Styles (discussed in the next chapter) can be used as templates for the portlet.
Mobile Display Options Specifies what fields will be displayed for the Wireless component of Oracle Application Server 10 g when a user requests a web page over a mobile device such as a web-enabled phone or personal digital assistant.

Step 8 of the CQW Chart Wizard (step 6 if you are building a CSQ), the Customization Form Display Options page (Figure 9-24), is similar to the corresponding page in the OracleAS Reports Wizard except for the introduction of a new concept: parameters. This page allows us to specify parameters that end users (who have been granted the necessary privileges) can use to alter the data that will drive the chart. If you click the Value Required check box, the end user will be forced to enter a value before the chart will be displayed.

Figure 9-24: The Customization Form Display Options page of the OracleAS Portal Chart Wizard

The final two steps of the CQW and CSQ chart wizards, the Chart and Customization Form Text and Additional PL/SQL Code pages, are identical to their corresponding OracleAS Report Wizards pages. Figure 9-25 shows an example of an OracleAS Portal Chart run as a portlet.

Figure 9-25: An OracleAS Portal Chart run with default values