10 Developing Reports and Dashboards

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

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

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

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

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.

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

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

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