11
Creating and maintaining calculated items

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?"

• "What are derived items?"

• "What are aggregate calculated items?"

• "What restrictions apply to aggregate calculated items?"

• "What are the differences between analytic functions and aggregate calculated items?"

• "What are aggregate derived items?"

• "Example of an aggregate derived item"

• "How to create calculated items"

• "How to edit calculated item properties"

• "How to edit calculated items"

• "How to delete calculated items"

• "Why do you need PL/SQL functions?"

• "What are custom PL/SQL functions?"

• "About registering custom PL/SQL functions"

• "How to register custom PL/SQL functions automatically"

• "How to register custom PL/SQL functions manually"

What are calculated items?

A calculated item is an item that uses 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:

• profit margins

• average revenues per month

• expected sales

• percent of profit by product type

Calculated items behave like any other item in a folder and 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:

• Discoverer end users can include a complicated calculation in their worksheets simply by selecting a calculated item

• you can add new items to a folder that do not exist as columns in the underlying database tables

You create calculated items using expressions that can contain:

• existing items

• operators

• literals

• functions

There are three types of calculated items:

• derived items (for more information, see "What are derived items?")

• aggregate calculations (for more information, see "What are aggregate calculated items?")

• aggregate derived items (for more information, see "What are aggregate derived items?")

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

• for worked examples of calculations, see the Oracle 9iAS Discoverer Plus User's Guide

• for information on function syntax, see the latest Oracle SQL Reference documentation

What are derived items?

A derived item is an expression that behaves in the same way as 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 depends only on the value of the other items in the same row, and will be the same regardless of which other items are included in the workbook.

The following are examples of derived items:

• Sal*12+NVL(Comm,0) - returns annual salary plus commission

• Initcap(Ename) - capitalizes the first letter of Ename

• 1 - returns the value 1

• Sysdate-7 - returns today's date minus seven days

What are aggregate calculated items?

An aggregated calculated item is a calculated item to which a GROUP function is applied (e.g. SUM, COUNT, MAX, MIN, AVG, DETAIL).

The following are examples of aggregate calculations:

• SUM(Sal)*12

• SUM(Comm)/SUM(Sal)

• AVG(Monthly Sales)

Aggregate calculation values depend on other items used in the Discoverer Plus worksheet. 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 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 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 to aggregate calculations. Aggregate calculated items:

• must have their Default Position property set to data point

• must have their Default Aggregate property set to detail

• must reference items in the current folder only

• cannot be used in a join

• cannot be used in a mandatory condition

• cannot be used in a hierarchy

• cannot have an item class

• cannot be dragged into a complex folder

• cannot have further aggregation functions applied to them in Discoverer Plus

Aggregate calculations do not affect the number of rows of data referenced by the folder. They only affect generated SQL when selected in Discoverer Plus.

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

Analytic functions behave like aggregate calculations except for the following (for more information, see the Glossary entry "analytic functions"):

• Analytic functions do not require aggregation in Discoverer. All other restrictions that apply to aggregate functions also apply to analytic functions (for more information, see "What restrictions apply to aggregate calculated items?").

• Analytic function aggregation is derived from the database server, not the memory cache. Therefore analytic function aggregation has a performance overhead.

• Analytic functions are only available in Oracle 8.1.7 databases or later.

For more information about analytic functions see the Oracle 9iAS Discoverer Plus User's Guide.

What are aggregate derived items?

Aggregate derived items are calculated items created 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 "What are derived items?").

Example of an aggregate derived item

This example explains how an aggregate derived item is used 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 are created when you complete the Discoverer Administrator Tutorial exercises (for more information, see the Discoverer Administrator Tutorial).

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 (for more information, see the Discoverer Administrator Tutorial):

• Department

• Region

• City

• Store Name

• Year

• Quarter

• Month

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)

Text description of the illustration clcadit1.gif

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)

Text description of the illustration clcadit2.gif

Notes

The Average Monthly Sales per Store item shows the average monthly sales and can be analyzed over region, quarter, year, which is useful to compare trends. This form of nested aggregate is only possible by creating the aggregate item in a new folder. The aggregate item must be created in a new folder because the new folder now represents monthly sales, not the individual sales that were in the original complex folder.

Aggregate derived items cause the SQL created by Discoverer to contain a GROUP BY clause that forces Discoverer end user queries to return one row for each combination of the non-aggregated items, regardless of whether the calculation is used in the query. This reduces the number of rows returned by the folder, because the rows are aggregated at the level of the other items.

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

   
   
   Text description of the illustration clcnitm.gif

3. Specify a Name for the new calculated item.

4. Enter the calculation in the Calculation field as follows:

• if you know the calculation syntax, type the calculation directly into the Calculation field

• if you do not know the calculation syntax, you can build up the calculation in stages by:
  • selecting the Items button to display the items in the selected folder and selecting the item to use in the calculation (if the folder is a complex folder, all of the source folders and their items are displayed)

  • selecting the Functions button to display the available functions grouped by Type, and selecting the functions to use in the Calculation (registered custom PL/SQL functions are displayed in the database group)

  • selecting the appropriate Operator button

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

5. (optional) Click either Items or Functions and use the Operator buttons to construct your calculation (if you don't know the function syntax):
   • Items

     Displays a list of the items in the selected folder (if a complex folder, the source folders and their items are also displayed here).

     You can insert an item into your calculation (double-click it or use Paste).

   • Functions

     Displays a list of the functions (grouped by type) available for use in your calculation.

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

     You can insert a function into your calculation (double-click it or use Paste).

   • Operators

     Operators are listed along the bottom of the Calculation area. Click an operator to insert one into your calculation.

   Hint: You can resize the "New Item dialog" to give you more room to specify your calculation.

6. Click OK when you have finished specifying the formula.

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

   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 Chapter 9, "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. Edit the Calculation as required.

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

   How to delete calculated items

   Use this option to 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) You can review the objects that might be affected by deleting this item. To see the potential impact of the deletion:
      1. Click Impact to display the "Impact dialog".

         The Impact dialog shows other objects that might be affected by the deletion and helps you to make the right choice.

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

         Figure 11-4 The Impact dialog

         
         
         Text description of the illustration impacta.gif
      3. When you have finished reviewing the impact of deleting the item, click OK to close the Impact dialog.

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

   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:

   • Import automatically, by importing the functions (recommended)

     We recommend you register PL/SQL functions by importing automatically (especially if you have many functions to register), because it is easy to make mistakes when manually entering information about functions. When you import functions, all of the information about each function (e.g. names, database links, return types, lists of arguments) is imported. Importing ensures correct information about the function, because the information does not have to be manually entered on a function-by-function basis.

   • Manually

     Manual registration requires that you register each function individually by supplying all of the information about 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.

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

   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

      
      
      Text description of the illustration plsqlfun.gif
   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

         
         
         Text description of the illustration plsqlarg.gif
   6. Click OK when you have finished defining the function.

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