Skip Headers
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
B13916-04
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

11 Creating and maintaining calculated items

This chapter explains how to create and maintain calculated items using Discoverer Administrator, and contains the following topics:

What are calculated items?

Calculated items are items that use a formula to derive data for the item.

Calculated items enable Discoverer end users to apply business calculations to the data. For example, typical business calculations might include:

omission Calculated items (like other items in a folder) can be used in conditions, summary folders, lists of values, joins, and other calculated items.

As the Discoverer manager, you can create calculated items and make them available for inclusion in workbooks.

Creating calculated items provides the following benefits:

You create calculated items using expressions that can contain:

There are three types of calculated items:

You can obtain more information about calculated items in Discoverer from the following sources:

Notes

Calendar Year||CHR(10)||Department

Worksheets containing this item will display Calendar Year and Department in a single column. For example:

2002 Sales Department

What are derived items?

Derived items are expressions used in calculated items that behave like any other item in a folder. Derived items can be axis items or data points and can be used anywhere that you would use an ordinary item. The value of a derived item remains the same regardless of which other items are included in a workbook.

The following are examples of derived items:

What are aggregate calculated items?

Aggregate calculated items are derived items (for more information, see "What are derived items?"), to which the GROUP function is applied (e.g. SUM, COUNT, MAX, MIN, AVG, DETAIL). The value of an aggregated calculated item depends on which other items are included in a worksheet.

The following are examples of aggregate calculated items:

How the axis items are grouped together affects the number of rows that are aggregated. This is particularly important in the case of calculations that are ratios of two aggregates.

For example, to calculate a margin, you would use the calculation SUM(Profit)/SUM(Sales) rather than Profit/Sales. Used in a query, the latter would result in SUM(Profit/Sales), which produces a different result from SUM(Profit)/SUM(Sales). The example uses the SUM aggregate, but the same can be applied for any of the other aggregates (e.g. SUM, COUNT, MAX, MIN, AVG, DETAIL).

Note: If you want to compute the sum of a ratio of two data points, always sum the data points before computing the ratio.

What restrictions apply to aggregate calculated items?

When creating aggregate calculated items, note that a number of restrictions apply. Aggregate calculated items:

Aggregate calculated items do not influence the number of rows of data referenced by the folder they are in. They only influence the generated SQL when selected in worksheets.

What are the differences in behaviour between analytic functions and aggregate calculated items?

Analytic functions are advanced mathematical and statistical calculations that you can use to analyse business intelligence data. For example, to answer questions such as:

Analytic functions behave differently from aggregate calculated items as follows:

For more information about:

Note: Discoverer only supports analytic functions when running against an Oracle Enterprise Edition database.

What are aggregate derived items?

Aggregate derived items are aggregate calculated items in a complex folder that aggregate another aggregate calculated item (in the same complex folder). In other words, an aggregate derived item is simply an aggregate calculated item nested inside another aggregate calculated item.

Aggregate derived items enable you to apply an aggregation (e.g. AVG, SUM, COUNT) to the current level of aggregation to derive additional information.

Aggregate derived items behave in all respects like ordinary derived items. For more information, see:

Example of an aggregate derived item

This example explains how you can use an aggregate derived item to display the average monthly sales per store over one year, for a video stores chain. The aggregate derived item in this example uses an aggregate calculated item created in the same folder. For more information about aggregate calculated items, see "What are aggregate calculated items?".

This example uses two complex folders, Video Analysis and Monthly Sales Analysis, that you can create using the sample data (for more information, see the Chapter 27, "Installing the Discoverer sample data EUL, data, and workbook").

An aggregate derived item is created in the complex folder Monthly Sales Analysis. The Monthly Sales Analysis complex folder is built by dragging the following items from the Video Analysis complex folder:

The complex folder Monthly Sales Analysis references a row of data for every store, for every month.

An aggregate calculated item (Monthly Sales Per Store) is created in the Monthly Sales Analysis complex folder using the following formula:

SUM(Video Analysis.Sales)

This item shows the total sales for a given store in a given month.

Figure 11-1 Result of query using the aggregate calculated item SUM(Video Analysis.Sales)

Description of Figure 11-1  follows
Description of "Figure 11-1 Result of query using the aggregate calculated item SUM(Video Analysis.Sales)"

An aggregate derived calculated item (called Average Monthly Sales per Store), is created in the Monthly Sales Analysis complex folder using the following formula:

AVG(Monthly Sales Per Store)

This item shows the average total sales for a given store in a given month.

Figure 11-2 Result of query using the aggregate derived calculated item AVG(Monthly Sales Per Store)

Description of Figure 11-2  follows
Description of "Figure 11-2 Result of query using the aggregate derived calculated item AVG(Monthly Sales Per Store)"

Notes

How to create calculated items

You can create calculated items, derived items, aggregate calculated items and aggregate derived items using this task.

To create a new calculated item:

  1. Select the folder that you want to contain the new calculated item on the "Workarea: Data tab".

  2. Choose Insert | Item… to display the "New Item dialog".

    This dialog enables you to create a new calculated item and add it to the selected folder.

    Note: If you did not select a folder, Discoverer Administrator displays the "New Item dialog" where you can select a folder to contain your new calculation (you can select any folder from any open business area).

    Figure 11-3 New Item dialog

    Description of Figure 11-3  follows
    Description of "Figure 11-3 New Item dialog"

  3. Specify a name for the new calculated item.

  4. Enter the calculation formula in the Calculation field.

    If you are familiar with calculation syntax, you type the formula in the Calculation field.

    Note: If you type a formula in the Calculation field, you must prefix the formula with an equals sign (i.e. =).

    If you prefer, you can build the calculation in stages using any of the following methods:

    • To add an item from the business area to the calculation, click the Items radio button and select an item from the Show list, then click Paste to copy the item into the Calculation field.

    • To add a function to the calculation, click the Functions radio button and select a function from the Show list, then click Paste to copy the function into the Calculation field.

    • To include a mathematical operator in the calculation, click the appropriate operator button below the Calculation field.

    Hint: Before pasting items in the Calculation field, position the cursor in the Calculation field to where you want to insert the item.

    Note: Calculations follow the standard Oracle calculation syntax. For a full description of this syntax, see the Oracle SQL Language Reference Manual.

    Note: Registered custom PL/SQL functions are displayed in the Database group. For more information, see "What are custom PL/SQL functions?"

  5. Click OK when you have finished specifying details in the Calculation field.

    If there are no errors in the Calculation field, the new item is created. If there are errors in the Calculation field, Discoverer Administrator displays the first error and returns you to the New Item dialog so that you can correct the details.

You can now use the new calculated item to create joins or conditions, even new calculations. You can also include the new calculated item in other calculated items.

How to edit calculated item properties

To edit calculated item properties see "How to edit item properties" for more information.

How to edit calculated items

To edit an existing calculation:

  1. Select the calculated item on the "Workarea: Data tab" and choose Edit | Edit… to display the "Edit Calculation dialog".

  2. Change the calculation as required.

    For example:

    • change the name of the calculation

    • add or remove items, functions, or operators from the calculation text

  3. Click OK to save the changes you have made and close the dialog.

How to delete calculated items

You can delete one or more calculated items. Note that when you delete a calculated item, other EUL objects might be affected if they use the calculated item you want to delete. The Impact dialog enables you to review the other objects that might be affected when you delete a calculated item.

To delete a calculated item:

  1. On the "Workarea: Data tab" select the calculated item that you want to delete.

    You can select more than one item at a time by holding down the Ctrl key and clicking another item.

  2. Choose Edit | Delete to display the "Confirm Delete dialog".

  3. (optional) To see the objects that might be affected by deleting this item:

    1. Click Impact to display the "Impact dialog".

      Figure 11-4 The Impact dialog

      Description of Figure 11-4  follows
      Description of "Figure 11-4 The Impact dialog"

      The Impact dialog enables you to review the other EUL objects that might be affected when you delete an item.

      Note: The Impact dialog does not show the impact on workbooks saved to the file system (i.e. in .dis files).

    2. (optional) Select a row to view text at the bottom of the list, indicating what affect the current action will have on the EUL object.

    3. When you have finished reviewing the impact of deleting the item, click Close to close the Impact dialog.

  4. Click Yes if you still want to delete the selected items(s).

How to create a calculated item that enables end users to drill out from one worksheet to display another worksheet in Discoverer Viewer

You might want to create a worksheet that end users can drill out from, to display additional or related information in another worksheet in Discoverer Viewer.

You can create a calculated item in Discoverer Administrator that includes an internet address (a URL), and include the same item in a worksheet. End users can click the item to display a pre-defined Discoverer Viewer worksheet.

To create a calculated item that enables end users to drill out from one worksheet (the source) to display another worksheet (the target) in Discoverer Viewer:

  1. Create the target worksheet in Discoverer Plus or Discoverer Desktop and save it to the database.

    This is the pre-defined worksheet that end users will drill out to, from the source worksheet.

    For more information about creating worksheets, see the Oracle Business Intelligence Discoverer Plus User's Guide.

    Note: If you want the target worksheet to display context-sensitive information (i.e. information related to the source worksheet row or column that end users drill out from), the target worksheet must use a method of filtering the data (e.g. parameters or page items).

  2. Use Discoverer Viewer to display the target worksheet that you created in Step 1.

  3. Copy the URL of the target worksheet onto the clipboard.

    You will paste the URL of the target worksheet into a new calculated item in Discoverer Administrator, and then modify the formula of the calculated item.

  4. Display the "New Item dialog" in Discoverer Administrator (see, "How to create calculated items").

    You will use the new calculated item in the source worksheet to drill out to the target worksheet in Discoverer Viewer.

  5. Paste the URL (that you copied onto the clipboard) into the Calculation field of the "New Item dialog".

    You must now edit the URL in the new calculated item and replace each parameter value with its corresponding EUL item name. The EUL items supply the dynamic value that are used by the parameters in the target worksheet.

  6. In the Calculation field of the "New Item dialog", enter a single quotation mark (i.e. ') at the beginning and at the end of the URL.

    For example:

    'http://mymachine.com/discoverer/viewer?&cn=cf_a208&pg=1&wbk=PARAMETERS&wsk=26&qp_myRegion=CENTRAL'

    Using single quotation marks enables Discoverer to treat the text of an URL correctly.

  7. Edit the formula in the Calculation field to replace each parameter value in the URL with its corresponding EUL item name.

    For example, if the target worksheet uses a parameter named myRegion (that represents the EUL item Region), the URL that you paste into the Calculation field might appear as follows:

    'http://mymachine.com/discoverer/viewer?&cn=cf_a208&pg=1&wbk=PARAMETERS&wsk=26&qp_myRegion=CENTRAL'

    To replace part of the URL with a value determined from an EUL item, use the syntax '||<ItemName>||'. For example, in the URL above you might replace the value CENTRAL as follows:

    'http://mymachine.com/discoverer/viewer?&cn=cf_a208&pg=1&wbk=PARAMETERS&wsk=26&qp_myRegion='||Region||''

    Where Region is the corresponding EUL item that dynamically supplies the value required by the myRegion parameter in the target worksheet.

    Note: You use single quotation marks and the || operator to correctly build the resulting URL.

  8. Enter a suitable name for the new calculated item in the Name field.

    For example, you could name the new calculated item Drill_to_myRegion.

  9. Click OK to close the dialog and save your changes.

  10. In the Workarea: Data tab, highlight the calculated item you just created and choose Edit | Properties.

    Discoverer displays the "Item Properties dialog" for the calculated item you just created.

  11. Click the Content Type attribute and choose FILE from the drop down list.

    The Content Type attribute tells Discoverer to launch another application. In this case, Discoverer Plus, Discoverer Desktop or Discoverer Viewer will launch a Web browser to display the target worksheet in Discoverer Viewer.

  12. Click OK to close the dialog and apply the changes you have made.

  13. Start a new Discoverer Plus session (or reconnect to Discoverer Desktop).

  14. Create a worksheet (the source worksheet) in Discoverer Plus or Desktop and include the Drill_to_myRegion calculated item (that you just created in Discoverer Administrator) in the worksheet.

    For more information about creating worksheets, see the Oracle Business Intelligence Discoverer Plus User's Guide.

    When an end user displays this source worksheet in Discoverer Plus, Discoverer Desktop or Discoverer Viewer, they can click the Drill_to_myRegion calculated item (that you just created) to display the target worksheet in Discoverer Viewer. The context-sensitive value for the EUL item Region is dynamically passed to the target worksheet using the myRegion parameter to display the correct results data.

The example URL used in this task can be broken down into the following components:

Section of internet addres What the section of the internet address represents
http://mymachine.com/discoverer/viewer? the internet address (URL) of Discoverer Viewer
&cn=cf_a208 the connection string used to connect to the database
&wbk=PARAMETERS the workbook identifier
&wsk=26 the worksheet identifier
&qp_myRegion the parameter myRegion (created in Discoverer Plus or Discoverer Desktop)
=CENTRAL the value of the target worksheet parameter myRegion (i.e. the CENTRAL region)

Why do you need PL/SQL functions?

PL/SQL functions are one of Oracle's procedural extensions to SQL. PL/SQL functions offer access through PL/SQL references in the SQL, to PL/SQL functions that run in the Oracle server. PL/SQL functions enable you to compute values in the database. For more information about PL/SQL functions, see the PL/SQL User's Guide and Reference.

What are custom PL/SQL functions?

Custom PL/SQL functions are PL/SQL functions created by the Discoverer manager that are designed to meet additional Discoverer end user requirements (e.g. to provide a complicated calculation). Custom PL/SQL functions supplement the PL/SQL functions provided by Oracle and are available to all database processes.

You create custom PL/SQL functions using SQL*Plus, or a procedural editor. You do not create custom PL/SQL functions directly in Discoverer Administrator. For more information see the SQL*Plus User's Guide and Reference.

Note: In Discoverer Plus, folders containing derived items (for more information, see "What are derived items?") that use PL/SQL functions will be visible only to users who have EXECUTE database privileges on those functions.

About registering custom PL/SQL functions

To access custom PL/SQL functions using Discoverer, you must register the functions in the EUL. When you have registered a custom PL/SQL function, it appears in the list of database functions in the "Edit Calculation dialog" and can be used in the same way as the standard Oracle functions.

You can register custom PL/SQL functions in two ways:

Note: To register a PL/SQL function you must have EXECUTE privilege on the function.

How to register custom PL/SQL functions automatically

To register PL/SQL functions automatically you must import them in the following way:

  1. Choose Tools | Register PL/SQL Functions to display the "PL/SQL Functions dialog: Functions tab".

  2. Click Import to display the "Import PL/SQL Functions dialog".

    This dialog enables you to select the PL/SQL functions that you want to import.

  3. Select the functions that you want to import

    You can select more than one function at a time by holding down the Ctrl key and clicking another function.

  4. Click OK.

    Discoverer imports the selected functions and displays the function details in the "PL/SQL Functions dialog: Functions tab".

    Information about the selected functions is imported automatically. In other words, you do not have to manually enter information or validate the information.

  5. Click OK.

    The PL/SQL function is now registered for use in Discoverer.

How to register custom PL/SQL functions manually

To manually register a PL/SQL function for use in Discoverer:

  1. Choose Tools | Register PL/SQL Functions to display the "PL/SQL Functions dialog: Functions tab".

    Figure 11-5 PL/SQL Functions dialog: Functions tab

    Description of Figure 11-5  follows
    Description of "Figure 11-5 PL/SQL Functions dialog: Functions tab"

  2. Click New and specify the function attributes.

  3. Click Validate to check the validity and accuracy of the information you have entered.

  4. If the function is invalid, correct the attributes and click Validate again.

  5. (optional) If the function accepts arguments:

    1. Display the "PL/SQL Functions dialog: Arguments tab".

    2. On the Arguments tab, click New and specify the argument attributes.

      Figure 11-6 PL/SQL Functions dialog: Arguments tab

      Description of Figure 11-6  follows
      Description of "Figure 11-6 PL/SQL Functions dialog: Arguments tab"

  6. Click OK when you have finished defining the function.

The custom PL/SQL function is now registered for use in Discoverer.