Skip Headers
Oracle® OLAP Application Developer's Guide,
10g Release 2 (10.2)

Part Number B14349-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Developing Reports and Dashboards

You can use any SQL development tool or application to create reports and dashboards populated with data from OLAP cubes. This chapter shows the basic steps for working with the tools provided with Oracle Database: Oracle Business Intelligence Publisher (BI Publisher) and Oracle Application Express. You can try these tools, or you can apply the methods shown here to your favorite SQL tool.

This chapter contains the following topics:

See Also:

Chapter 4, "Querying Dimensional Objects Using SQL"

Developing SQL Applications for Dimensional Data

You can use any SQL query against a cube as the content for a report or dashboard. Both BI Publisher and Application Express contain a Query Builder, which you can use to develop queries against both relational and dimensional objects. You can also cut-and-paste queries from a SQL script or another source, which is the method used in this chapter.

If your goal is to create static reports and dashboards, then you do not need to read any further. You can start developing OLAP applications immediately using your favorite tool. This chapter explains how to create applications with dynamic content. It focuses on ways to leverage the unique capabilities of cubes and dimensions to create drillable reports and graphs using a single query. In this chapter, you learn how to create two types of drillable interfaces:

These user interfaces set the values of bind variables in the WHERE clause of the source query. When a user changes the current selection in a choice list or clicks a link in a crosstab, that action dynamically changes the value of the variable. When the variable changes, so does the condition of the query and the contents of the report or dashboard.

When the variable sets the value of a parent column in a cube view, users can drill on a parent to view its children.

Example 10-1 shows a basic SQL query against a cube view of the Units Cube in the Global sample schema. The query selects the SALES measure and three calculated measures that use SALES as the basis for the calculations:

This query is used in the sample applications developed in this chapter. The parent columns for the Product, Customer, and Time dimensions support drilling in these applications. The Channel dimension remains anchored at the TOTAL_CHANNEL level.

Example 10-1 SQL Query Against the Sales Cube

SELECT product_ldsc "Product",
       customer_ldsc "Customer",
       time_ldsc "Time",
       round(sales) "Sales",
       round(sales_pp) "Prior Period",
       round(sales_chg_pp) "Change",
       round(sales_pct_chg_pp * 100) "Percent Change"
/* From cube view */
    FROM units_cube_cubeview
/* Filters on all dimensions */
    WHERE product_primary_prnt = 'TOTAL_PRODUCT_1' 
       AND customer_shipments_prnt = 'TOTAL_CUSTOMER_1'
       AND time_calendar_yea_prnt = 'YEAR_4'
       AND channel_level = 'TOTAL_CHANNEL'
    ORDER BY product, customer, time_end_date;

Product         Customer        Time          Sales Prior Period     Change Percent Change
--------------- --------------- -------- ---------- ------------ ---------- --------------
Hardware        North America   Q1-01      15029369     16225669   -1196300             -7
Hardware        North America   Q2-01      14873260     15029369    -156108             -1
Hardware        North America   Q3-01      15951726     14873260    1078465              7
Hardware        North America   Q4-01      17228528     15951726    1276802              8
Hardware        Asia Pacific    Q1-01       6282186      7040505    -758319            -11
Hardware        Asia Pacific    Q2-01       6661802      6282186     379616              6
Hardware        Asia Pacific    Q3-01       6936356      6661802     274554              4
Hardware        Asia Pacific    Q4-01       7371841      6936356     435486              6
Hardware        Europe          Q1-01       4183166      4509289    -326123             -7
Hardware        Europe          Q2-01       4165212      4183166     -17954              0
Hardware        Europe          Q3-01       4521921      4165212     356710              9
Hardware        Europe          Q4-01       4785698      4521921     263777              6
Software/Other  North America   Q1-01       1229455      1260900     -31445             -2
Software/Other  North America   Q2-01       1233716      1229455       4261              0
Software/Other  North America   Q3-01       1333084      1233716      99368              8
Software/Other  North America   Q4-01       1446759      1333084     113675              9
Software/Other  Asia Pacific    Q1-01        519562       517674       1888              0
Software/Other  Asia Pacific    Q2-01        509705       519562      -9857             -2
Software/Other  Asia Pacific    Q3-01        566745       509705      57040             11
Software/Other  Asia Pacific    Q4-01        596995       566745      30250              5
Software/Other  Europe          Q1-01        351592       364827     -13235             -4
Software/Other  Europe          Q2-01        354732       351592       3140              1
Software/Other  Europe          Q3-01        381837       354732      27105              8
Software/Other  Europe          Q4-01        416232       381837      34395              9
 
24 rows selected.

See Also:

Chapter 6, "Enhancing Your Database With Analytic Content," for information about calculated measures

Developing a Report Using BI Publisher

BI Publisher is an efficient, scalable reporting solution for generating and delivering information through a variety of distribution methods. It reduces the high costs associated with the development and maintenance of business documents, while increasing the efficiency of reports management. BI Publisher generates reports in a variety of formats, including HTML, PDF, and Excel.

If you have not used BI Publisher, you can download the software, tutorials, and full documentation from the Oracle Technology Network at

http://www.oracle.com/technetwork/middleware/bi-publisher/overview/index.html

Figure 10-0 shows a report in PDF format based on the query shown in Example 10-1. When generating a report for distribution, you can select any combination of Products, Customers, and Time Periods from the choice lists. The selection for this report is Hardware products, customers in Europe, and months in Q2-01. This chapter explains how you can create a report like this one using drillable dimensions.

Figure 10-1 Sales Report in BI Publisher

BI Publisher HTML Report
Description of "Figure 10-1 Sales Report in BI Publisher"

Creating an OLAP Report in BI Publisher

A report consists of a report entry, which you create in BI Publisher, and a layout template, which you create using an application such as Microsoft Word or Adobe Acrobat. You can organize your reports in folders.

BI Publisher is a middleware application and can derive data from multiple sources. These procedures assume that you can access one or more cubes from BI Publisher. If you cannot, contact your BI Publisher administrator about defining a new data source.

To create a report entry:

  1. Open a browser to the BI Publisher home page and log in.

  2. Click My Folders.

  3. Open an existing folder.

    or

    To create a new folder:

    1. Click Create a New Folder.

    2. Enter a name for the folder in the text box, such as OLAP Reports.

    3. Click Create.

  4. Click the new folder to open it.

  5. Create a new report:

    1. Click Create a New Report.

    2. Enter a report name in the text box.

      This example creates a report named Global Sales.

    3. Click Create.

    The new report appears in the folder, as shown in Figure 10-2.

Figure 10-2 Creating a New Report

BI Publisher Report Page
Description of "Figure 10-2 Creating a New Report"

To configure the report entry:

  1. To define the contents of the report, click Edit.

    The Report Editor opens.

  2. For General Settings, enter a description and select a default data source.

    If the list does not include a connection to the database and schema containing your cubes, contact your BI Publisher administrator.

  3. Select Data Model, then click New.

    The Data Set page opens.

  4. Enter a name for the data set and enter a SQL query like the one shown in Example 10-1. Do not end semicolon.

  5. Click Save.

  6. Click View.

    BI Publisher checks the report definition for errors. If there are none, then it generates the XML for the report.

Figure 10-3 shows the Report Editor with the Data Set page displayed.

Figure 10-3 Creating a Data Model in the BI Publisher Report Editor

BI Publisher Report Editor
Description of "Figure 10-3 Creating a Data Model in the BI Publisher Report Editor"

Creating a Template in Microsoft Word

BI Publisher does not contain formatting tools. Instead, it enables you to design a report using familiar desktop applications. This example uses Microsoft Word. A report template can contain:

  • Static text and graphics that you enter like any other Word document.

  • Dynamic fields such as the date and time or page numbers, which are processed by Word.

  • Codes that identify the XML tags for your data, which are processed by BI Publisher. When BI Publisher generates a report, it replaces the codes with the data identified by these tags.

You can format all parts of the report template in Word, selecting the fonts, text and background colors, table design, and so forth.

Example 10-2 shows the XML for a row of data returned by the sample query. The tags match the column names in the select list, except that underscores replace the spaces. The tags are Product, Customer, Time, Sales, Prior_Period, Change, and Percent_Change. XML tags are case-sensitive. You use the HTML tag names as the codes in the Word document.

Example 10-2 XML for a SQL Query

<ROW>
   <Product>Hardware</Product>
   <Customer>North America</Customer>
   <Time>Q1-01</Time>
   <Sales>15029369</Sales>
   <Prior_Period>16225669</Prior_Period>
   <Change>-1196300</Change>
   <Percent_Change>-7</Percent_Change>
</ROW>

Figure 10-4 shows the Word document that is used as the template for the sample report. It contains these elements:

  • A table used to format the banner, which consists of a graphic, the company name, and a horizontal line. (Static)

  • The name of the report. (Static)

  • A table for the query results that contains two rows:

    • A heading row. (Static)

    • A body row containing text form fields, which identify the XML tags and the appropriate formatting for the data. BI Publisher replaces these fields with data from the query. Note that the first and last columns contain two fields. The first (for each) and last (end) fields identify the range of repeating columns. (Dynamic)

  • A date field. Word updates this field with the current date. (Dynamic)

This example uses a blank Word template, but you could use a template with, for example, the banner already defined.

Figure 10-4 Sample Report Template Created in Word for BI Publisher

Sample BI Publisher report template
Description of "Figure 10-4 Sample Report Template Created in Word for BI Publisher "

The following procedure defines the template manually. Alternatively, you can use a Word plugin called Oracle BI Publisher Desktop. On the BI Publisher My Folders page, click Template Builder to download the plugin.

To create a BI Publisher template in Word:

  1. Open a new document in Word.

  2. Compose the page according to your preferences.

  3. For the query results, create a table.

    The table shown in Figure 10-4 is very simple. You can use much more elaborate formatting if you wish, including nested columns and tables.

  4. From the View menu, choose Toolbars, then Forms.

    The Forms toolbar opens.

  5. Enter a field in the body row of each column:

    1. Position the cursor in the appropriate cell.

    2. On the Forms toolbar, click the Text Form Field icon.

      The Text Form Field Options dialog box opens.

    3. Choose an appropriate Type, generally Regular Text for dimension labels and Number for measures.

    4. Enter a default value and a format.

    5. Click Add Help Text.

      The Form Field Help Text dialog box opens.

    6. Type the appropriate XML tag in the Type Your Own box, using the format <?tag?>.

      Enter the tag name exactly as it appears in the XML report. For example, enter <?Product?> for the XML tag <Product>.

    7. Click OK to close the Form Field Help dialog box.

    8. Click OK to close the Text Form Field Options dialog box.

  6. Insert an additional form field at the beginning of the first column:

    1. In the Text Form Field Options dialog box, enter any default value, such as For-Each.

    2. In the Form Field Help Text dialog box, enter this text:

      <?for-each:ROW?>
      
  7. Insert an additional form field at the end of the last column:

    1. In the Text Form Field Options dialog box, enter any default value, such as End.

    2. In the Form Field Help Text dialog box, enter this text:

      <?end for-each?>
      
  8. Make any additional formatting changes in Word, such as the appropriate justification of the table headings and data columns.

  9. Save the document as an RTF file.

Generating a Formatted Report

After creating a report template in Word, you can upload it to BI Publisher and associate it with your report definition. Then you can generate reports in a variety of formats.

Create a Report Layout:

  1. Open the report editor in BI Publisher.

  2. Select Layouts.

    The Create Layouts page opens.

  3. Click New.

    The Layout page opens.

  4. Enter a name and select RTF for the template type.

  5. Select Layouts again, and select the new layout as the default template for this report.

  6. Under Manage Template Files, click Browse. Select the RTF file you created.

  7. Click Upload.

    The uploaded file is listed under Manage Template Files. Whenever you change the file in Word, upload it again. Otherwise, BI Publisher continues to use its copy of the previous version.

  8. Click Save.

  9. Click View.

    The report is displayed.

  10. To change the format, select a new format from the list and click View.

    To see the XML, select Data.

Figure 10-5 shows the report in HTML format.

Figure 10-5 BI Publisher Report Displayed in HTML Format

Global Enterprises report
Description of "Figure 10-5 BI Publisher Report Displayed in HTML Format"

Adding Dimension Choice Lists

You can add choice lists for the dimensions to a report. When generating a report, you can change the selection of data without changing the query. To add choice lists, take these steps:

  • Create one or more Lists of Values (LOV) to be displayed in the menu.

  • Create menus for displaying the LOVs.

  • Edit the query to use the bind variables created for the menus.

Creating a List of Values

For an LOV, use a SQL query that selects the dimension keys that you want to display. Include the long description and dimension key columns. This example creates a list for the Product Primary hierarchy using a dimension view:

SELECT product_ldsc, product
    FROM product_dimview
    WHERE product_primary_prnt = 'TOTAL_PRODUCT_1'
       OR product = 'TOTAL_PRODUCT_1'
    ORDER BY product_level, product_ldsc;

PRODUCT_LDSC         PRODUCT
-------------------- --------------------
Hardware             CLASS_2
Software/Other       CLASS_3
Total Product        TOTAL_PRODUCT_1

To create a list of values:

  1. Open the Report Editor in BI Publisher.

  2. Select List of Values, then click New.

    The List of Values page opens.

  3. Define the list:

    1. Enter a name for the list, such as Product_LOV.

    2. For the type, select SQL Query.

    3. Enter a query against a dimension view, as shown previously.

  4. Click Save.

Repeat these steps for the other dimensions. This example uses lists for Product, Customer, and Time.

Creating a Menu

In BI Publisher, a menu is a type of parameter. Creating a parameter automatically creates a bind variable that you can use in the query for the report.

To create a menu:

  1. Select Parameters, then click New.

    The Parameter page opens.

  2. Define the parameter:

    1. For the Identifier, enter a name such as product.

      This is the case-sensitive name of the bind variable that you use in the query.

    2. Select an appropriate data type, typically String.

    3. For the Default Value, enter the dimension key used in the WHERE clause of the LOV query.

      The menu initially displays the label for this key.

    4. For the Parameter Type, select Menu.

    5. Select the appropriate List of Values.

    6. Clear all options.

  3. Click Save.

Repeat these steps for the other dimensions. This example uses menus for Product, Customer, and Time.

Editing the Query

To activate the menus, you change the WHERE clause in the query so that the report uses the bind variables. The value of a bind variable is the current menu choice.

This is the format for the conditions of the WHERE clause:

parent_column = :bind_variable

In this example, the WHERE clause uses the bind variables for Time, Product, and Customer:

WHERE product_primary_prnt = :product
    AND customer_shipments_prnt = :customer
    AND time_calendar_yea_prnt = :time
    AND channel_level = 'TOTAL_CHANNEL'

To edit the query:

  1. Under Data Model, select the data set you defined for this report.

    The Data Set page opens.

  2. In the SQL Query box, edit the WHERE clause to use the bind variables created by the parameter definitions.

  3. Click Save.

Figure 10-6 shows a report in HTML format displayed in BI Publisher. The choice lists for Product, Customer, and Time appear across the top. The crosstab lists the Hardware products, the countries in Asia Pacific, and the months in Q4-01. To see a different selection of data, you can choose a Time Period, Product, and Customer from the menus, then click View. This report was generated by the same report entry, using the same query, as the reports shown in Figure 10-1 and Figure 10-5.

You can continue working on this report, adding charts and other tables.

Figure 10-6 Sales Report With Choice Lists in BI Publisher

Sales report with dimension lists
Description of "Figure 10-6 Sales Report With Choice Lists in BI Publisher"

Developing a Dashboard Using Application Express

Oracle Application Express is a rapid Web application development tool for Oracle Database. Application Express offers built-in features such as user interface themes, navigational controls, form handlers, and flexible reports, which simplify the development process. You can easily create dashboards from your cubes that display the rich analytical content generated by Oracle OLAP.

If you have not used Application Express, you can download the software, tutorials, and full documentation from the Oracle Technology Network at

http://www.oracle.com/technetwork/developer-tools/apex/overview/index.html

Figure 10-7 shows a crosstab with display lists for Product and Customer, and links in all three dimension columns. Choosing a new Product or Customer changes the related column to show the children for the selected key. Clicking a dimension key in any column displays its children. The Reset button refreshes the page with the initial selection of data.

Figure 10-7 Drillable Dimensions in Application Express

Sales Report in Application Express
Description of "Figure 10-7 Drillable Dimensions in Application Express"

Creating an OLAP Application in Application Express

In Application Express, the Administrator creates a workspace in which you can develop your Web applications. An application consists of one or more HTML pages, a page consists of regions that identify specific locations on the page, and a region contains a report (crosstab), a chart, or some other item.

Application Express runs in Oracle Database. If your dimensional objects are stored in a different database, then you must use a database link in your queries. The following procedure assumes that you have a workspace and access to at least one cube. It creates an application with one page containing a crosstab.

To create a Web page from a SQL query:

  1. Open a browser to the Application Express home page and log in.

  2. Click the Application Builder icon.

    The Application Builder opens.

  3. Click Create.

    The Create Application wizard opens.

  4. Select Create Application, then Next.

  5. On the Name page, enter a title for the application such as Global Dashboard and select From Scratch.

  6. On the Pages page, select the Report page type, then define the page:

    1. For Page Source, select SQL Query.

    2. For Page Name, enter a name such as Sales Analysis.

      This title is displayed on the page.

    3. For Query, enter a SQL SELECT statement for your cube, like the one shown in Example 10-1. Do not include an ORDER BY clause or a semicolon.

    4. Click Add Page.

      The page definition appears in the Create Application Box.

  7. Click Next, then complete the Create Application wizard according to your own preferences.

    This example was created with no tabs, no shared components, no authentication, and Theme 15 (Light Blue).

  8. On the Confirm page, click Create.

  9. On the Application Builder home page, click the Run Application icon.

Tip:

To continue working on this page, click the Edit Page 1 link at the bottom of the display.

Figure 10-8 shows the results of the query displayed in Application Express. Several items are automatically added to the page: breadcrumbs, Search box, Display list, Go button, Reset button, and Spread Sheet link. This application only needs the Reset button, so you can delete the other items if you wish.

Figure 10-8 Basic Sales Report in Application Express

Application Express Sales Report
Description of "Figure 10-8 Basic Sales Report in Application Express"

Adding Dimension Choice Lists

Like BI Publisher, Application Express enables you to drill on the dimensions by adding choice lists of dimension keys. The dashboard user can choose a particular item from the list and dynamically change the selection of data displayed in one or more graphics and crosstabs on the page. To implement a choice list, take these steps:

  • Create a new region on the page to display the list.

  • Create a list of values (LOV).

  • Create a list item with a bind variable to display the LOV.

  • Create an unconditional branch for the list.

  • Edit the query to use the bind variable.

The Page Definition is where you can create new pages and edit existing ones, including adding new graphical items and modifying existing ones. The items are organized in three columns: Page Rendering, Page Processing, and Shared Components.

To open the Page Definition:

After running the application, click the Edit Page link at the bottom of the page.

or

On the Application home page, click the icon for the page where the report is defined.

Figure 10-9 shows an area of the Page Definition.

Figure 10-9 Application Express Page Definition

Application Express Page Definition
Description of "Figure 10-9 Application Express Page Definition"

Creating a Region

You can create the choice list in a plain HTML area at the top of the page.

To create an empty HTML region:

  1. On the Page Definition under Regions, click the Create icon.

    The Create Region wizard opens.

  2. On the Region pages, select HTML, click Next, then select HTML again.

  3. On the Display Attributes page, enter a descriptive title and select an appropriate template and location on the page for the lists.

    For this example, the title is lov_region, the region template is No Template, and the location is Page Template Body (1 items below region content). The name can be displayed on the rendered page, but it is hidden in this example.

  4. Click Next, then Create Region.

    The new region appears on the Page Definition under Regions.

Creating a List of Values

For a list of values, use a SQL query like the one shown here. Include the description and key columns from the dimension view. This query creates a list for the Global Customer Shipments hierarchy:

SELECT customer_ldsc, customer
    FROM customer_dimview
    WHERE customer_shipments_prnt = 'TOTAL_CUSTOMER_1'
    OR customer = 'TOTAL_CUSTOMER_1'
    ORDER BY customer_level, customer_ldsc;

CUSTOMER_LDSC        CUSTOMER
-------------------- --------------------
Asia Pacific         REGION_8
Europe               REGION_9
North America        REGION_10
All Customers        TOTAL_CUSTOMER_1

To create a List of Values

  1. On the Page Definition under Lists of Values, click the Create icon.

    The Create List of Values wizard opens.

  2. On the Source page, select From Scratch.

  3. On the Name and Type page, enter a descriptive name and select Dynamic.

    This example uses the name CUSTOMER_LOV.

  4. On the Query page, enter a query like the one shown previously. Do not use a semicolon.

  5. Click Create List of Values.

    The new LOV appears in the Page Definition under List of Values.

For additional LOVs, repeat these steps. This example creates LOVs for the Product and Customer dimensions.

Creating the Choice List

For a choice list, you create a list item that displays the LOV.

To create a list item:

  1. On the Page Definition under Items, click the Create icon.

    The Create Item wizard opens.

  2. On the Item Type page, select Select List.

  3. For Control Type, select Select List with Submit.

  4. On the Display Position and Name page:

    • Enter a name that identifies the dimension, such as P1_CUSTOMER for the name of the Customer bind variable. P1 is the page number, and CUSTOMER identifies the Customer dimension.

    • Choose the new HTML region for the location of the list.

  5. On the List of Values page, set these values:

    • Named LOV to the List Of Values created for this dimension, such as CUSTOMER_LOV.

    • Display Null Option to No.

  6. Select the Item attributes according to your own preferences.

  7. On the Source page, enter the name of the top dimension key for the default value.

    For the Global Customer dimension, the value is TOTAL_CUSTOMER_1.

  8. Click Create Item.

Repeat these steps for other lists. This example creates lists for the Product and Customer dimensions.

To activate the list item:

  1. On the Page Definition under Branches, click the Create icon.

    The Edit Branch wizard opens.

  2. On the Point and Type page, accept the default settings.

  3. On the Target page:

    • Set Branch Target to Page in This Application.

    • Set Page to the page with the list item, which is 1 in this example.

    • Select Reset Pagination For This Page.

  4. On the Branch Conditions page, accept the default settings to create an unconditional branch.

  5. Click Create Branch.

    The Edit Branch page closes, and you return to the Page Definition. The new unconditional branch is listed under Branches.

Editing the Query

This is the format for the dynamic conditions in the WHERE clause:

parent_column = NVL(:bind_variable, 'top_key')

The NVL function substitutes the name of the top dimension key in the hierarchy for null values. The dimension keys at the top have no parent key.

To edit the query:

  1. Open the Page Definition.

  2. Under Regions, click the Edit Region link. In this example, the region is named Sales Report.

    The Edit Region page opens.

  3. Under Source, modify the query:

    • Change the WHERE clause to use the bind variables.

    • Delete the outer SELECT added by Application Express.

  4. Click Apply Changes.

For this example, the WHERE clause now looks like this:

WHERE product_primary_prnt = NVL(:P1_PRODUCT, 'TOTAL_PRODUCT_1')
       AND customer_shipments_prnt = NVL(:P1_CUSTOMER, 'TOTAL_CUSTOMER_1')
       AND time_calendar_yea_prnt = 'YEAR_4'
       AND channel_level = 'TOTAL_CHANNEL'

Figure 10-10 shows the modified page with choice lists for Product and Customer.

Figure 10-10 Dashboard With Choice Lists for Drilling

Dashboard with choice lists
Description of "Figure 10-10 Dashboard With Choice Lists for Drilling"

Drilling on Dimension Columns

You can enable users to drill down from the top of a hierarchy to the detail level using a single query. To implement drilling in Application Express, take these steps:

  • Create hidden items with bind variables.

  • Edit the query to use the bind variables.

  • Add links to the dimension columns of the crosstab.

This example adds drilling to all displayed dimensions.

Creating Hidden Items

You can create various types of items in Application Express that provide bind variables. They store the session state for a particular element, in this case, the current selection of a parent dimension key.

Each dimension that supports drilling needs a bind variable. In this example, Product and Customer already have bind variables created with the list items. Time is the only displayed dimension in the report that does not have a bind variable. Because links in the Time dimension column provide the user interface for changing the session state, Time does not need any other graphical user interface. A hidden item serves the purpose.

To create a hidden item:

  1. Open the Page Definition.

  2. Under Items, click the Create icon.

    The Create Item wizard opens.

  3. On the Item Type page, select Hidden.

  4. On the Display Position and Name page:

    • Enter a name that identifies the dimension, such as P1_TIME for the name of the Time bind variable.

    • Choose the region where the report is defined.

  5. On the Source page, enter the dimension key at the top of the hierarchy as the default value.

    TOTAL is the top of all hierarchies in the Global schema. For this example, Time is set to YEAR_4 to restrict the selection to 2001.

  6. Click Create Item.

  7. Repeat these steps for any other dimensions that supports drilling only on the column links.

    For this example, a hidden item is defined for Time.

Editing the Query

To add column links to a report, you must change two areas of the SELECT statement:

  • Select list: Application Express manages only those columns that appear in the select list. You can choose to display or hide the columns. For defining the column links, add the key and parent columns in the cube view to the query select list.

  • WHERE clause: Add the bind variables for the hidden items like you did for the choice lists in "Editing the Query".

Example 10-3 shows the modified sample query.

Example 10-3 Revised Query for Column Links in Application Express

SELECT product_ldsc "Product",
       customer_ldsc "Customer",
       time_ldsc "Time",
       round(sales) "Sales",
       round(sales_pp) "Prior Period",
       round(sales_chg_pp) "Change",
       round(sales_pct_chg_pp * 100) "Percent Change",
/* Add dimension keys and parents */
       product product_key,
       product_primary_prnt product_parent,
       customer customer_key,
       customer_shipments_prnt customer_parent,
       time time_key,
       time_calendar_yea_prnt time_parent
/* From cube view */
    FROM units_cube_cubeview
/* Use parent columns and bind variables for drilling */
    WHERE product_primary_prnt = NVL(:P1_PRODUCT, 'TOTAL_PRODUCT_1')
       AND customer_shipments_prnt = NVL(:P1_CUSTOMER, 'TOTAL_CUSTOMER_1')
       AND time_calendar_yea_prnt = NVL(:P1_TIME, 'YEAR_4')
       AND channel_level = 'TOTAL_CHANNEL'

Adding Links to the Dimension Columns

When a dashboard user clicks a linked dimension key in the crosstab, the value of the bind variable changes, causing the crosstab to change also. After drilling down a hierarchy, the user can restore the display to its original selection of data by pressing the Reset button. To implement these column links, you must add the column links and activate the Reset button.

To add a link to a dimension column:

  1. Open the Page Definition.

  2. Under Regions, click the Report link.

    The Report Attributes page opens.

  3. Under Column Attributes, modify the report display:

    • Clear the Show check boxes for columns that you want to hide, such as the dimension key and parent columns.

    • Set the Sort and Sort Sequence check boxes for appropriate sorting for the report. In this example, the sort order is Product (1), Customer (2), and Time (3).

  4. Click the Edit icon for a dimension column.

    The Column Attributes page opens.

  5. Under Column Link, define the link as follows:

    • Link Text: Choose the dimension name.

    • Page: Enter the page number.

    • Name: List the dimensions in the order they appear in the report. Item is the name of the bind variable. Value is the key column for the dimension being defined or the parent column for the other dimensions.

    Figure 10-11 shows the link definition for the Time dimension.

  6. Click Apply Changes.

    The Column Attributes page closes, and you return to the Report Attributes page.

  7. Define links on the other dimension columns.

  8. Click Apply Changes.

    The Report Attributes page closes, and you return to the Page Definition.

Figure 10-11 Definition of the Time Link

Column Link Definition
Description of "Figure 10-11 Definition of the Time Link"

To activate the Reset button:

  1. Open the Page Definition.

  2. Under Branches, click the Go to Page conditional link.

    The Reset button was created on the page automatically along with its conditional branch. The Edit Branch page opens.

  3. Under Action, set Clear Cache to the page number (in this example, 1).

  4. Under Conditions, set When Button Pressed to RESET.

  5. Click Apply Changes.

    The Edit Branch page closes, and you return to the Page Definition.

  6. Click Run to display the page.

Figure 10-12 shows the finished page displaying months in Q3.01. You can continue working on this application, adding more reports and charts to the page. For the SQL queries providing data to those reports and charts, you can reuse the same bind variables for the dimensions.

Figure 10-12 Sales Report With Column Links in Application Express

Dimension List in Application Express
Description of "Figure 10-12 Sales Report With Column Links in Application Express"