6 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 DIM_KEY columns from the hierarchy view. This example creates a list for the Product Primary hierarchy:

SELECT long_description, dim_key
     FROM product_primary_view
     WHERE parent = 'TOTAL'
     OR dim_key = 'TOTAL'
     ORDER BY level_name, long_description

LONG_DESCRIPTION     DIM_KEY
-------------------- ------------
Hardware             HRD
Software/Other       SFT
Total Product        TOTAL

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 the dimension hierarchy 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 will 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 will initially display 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 creates menus for Product, Customer, and Time.

Editing the Query

To activate the menus, you change the WHERE clause in the query for the report to use 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  p.parent  = :product
   AND cu.parent = :customer
   AND t.parent  = :time
   AND ch.level_name = 'TOTAL'

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 6-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 months in Q3.06, the Hardware products, and the countries in Europe. To see a different selection of data, you 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 one shown in Figure 6-1.

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

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

Description of "Figure 6-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 name is lov_region, the template is No Template, and the location is Page Template Body (1 items below template content). The name can be displayed on the rendered page, but it is hidden in this example.

4. Click 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 LONG_DESCRIPTION and DIM_KEY columns from the hierarchy view. This query creates a list for the Customer Shipments hierarchy:

SELECT long_description, dim_key
     FROM customer_shipments_view
     WHERE parent = 'TOTAL' 
     OR dim_key= 'TOTAL'
     ORDER BY level_name, long_description;
 
LONG_DESCRIPTION     DIM_KEY
-------------------- ----------
Asia Pacific         APAC
Europe               EMEA
North America        AMER
Total Customer       TOTAL

To create a List of Values

1. On the Page Definition under List 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.

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 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 Apply Changes.

   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 dim_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 p.parent = NVL(:P1_PRODUCT, 'TOTAL')
    AND cu.parent = NVL(:P1_CUSTOMER, 'TOTAL')
    AND t.parent = 'CY2006'
    AND ch.level_name = 'TOTAL'

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

Figure 6-10 Dashboard With Choice Lists for Drilling

Description of "Figure 6-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 will support 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 will 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.

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

6. Click Create Item.

7. Repeat these steps for any other dimensions that will support 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 DIM_KEY and PARENT columns in the hierarchy views 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 6-3 shows the modified sample query.

SELECT p.long_description "Product",
     cu.long_description "Customer",
     t.long_description "Time",
     ROUND(f.sales) "Sales",
     ROUND(f.sales_pp) "Prior Period",
     ROUND(f.sales_chg_pp) "Change",
     ROUND(f.sales_pctchg_pp * 100) "Percent Change",
/* Add DIM_KEY and PARENT columns for column links */
     p.dim_key product_key,
     p.parent product_parent,
     cu.dim_key customer_key,
     cu.parent customer_parent,
     t.dim_key time_key,
     t.parent time_parent
/* From dimension views and cube view */
FROM product_primary_view p,
     customer_shipments_view cu,
     time_calendar_view t,
     channel_primary_view ch,
     units_cube_view f
/* Use parent columns and bind variables for drilling */
WHERE p.parent = NVL(:P1_PRODUCT, 'TOTAL')
    AND cu.parent = NVL(:P1_CUSTOMER, 'TOTAL')
    AND t.parent = NVL(:P1_TIME, 'CY2006')
    AND ch.level_name = 'TOTAL'
/* Join dimension views to cube view */
    AND p.dim_key = f.product
    AND cu.dim_key = f.customer
    AND t.dim_key = f.time
    AND ch.dim_key = f.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 DIM_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 DIM_KEY column for the dimension being defined or the PARENT column for the other dimensions.

   Figure 6-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 6-11 Definition of the Time Link

Description of "Figure 6-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 6-12 shows the finished page displaying months in Q3.06. 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 re-use the same bind variables for the dimensions.

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

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