11 Creating and maintaining calculated items

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:

• profit margins

• average revenues per month

• expected sales

• percent of profit by product type

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:

• 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 calculated items (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 calculated items in Discoverer from the following sources:

• for worked examples of calculated items, see the Oracle Business Intelligence Discoverer Plus User's Guide

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

Notes

• You might want to create a calculation to concatenate two (or more) items. To concatenate items, insert ||CHR(10)|| between items. For example, to create a new worksheet column containing the Calendar Year item and the Department item, create a calculation as follows:

Calendar Year||CHR(10)||Department

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

2002 Sales Department

• You can create a calculated item (containing an internet address) and use the calculated item in a Discoverer Plus worksheet to enable end users to launch a pre-defined Discoverer Viewer worksheet in a Web browser (for more information, see "How to create a calculated item that enables end users to drill out from one worksheet to display another worksheet in Discoverer Viewer".

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:

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

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:

• SUM(Sal)*12 - returns the sum of the annual salary

• SUM(Comm)/SUM(Sal) - returns the sum of commission divided by the sum of the salary

• AVG(Monthly Sales) - returns the average monthly sales

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:

• 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 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:

• what are my best selling products?

• how do current sales compare with last year's sales?

• what is the average sales transaction amount in the region with the largest number of sales transactions per year?

Analytic functions behave differently from aggregate calculated items as follows:

• analytic functions are aggregated in the database

• because analytic functions are aggregated in the database, and not the memory cache, they have a performance overhead

For more information about:

• the restrictions that apply to both analytic functions and aggregate calculated items, see "What restrictions apply to aggregate calculated items?"

• analytic functions, see the Oracle Business Intelligence Discoverer Plus User's Guide.

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:

• "What are derived items?"

• "Example of an aggregate derived item"

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:

• 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)

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 Result of query using the aggregate derived calculated item AVG(Monthly Sales Per Store)"

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

   
   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 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:

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.

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