14
Using calculations

Using calculations

This chapter explains how to use Discoverer calculations to answer typical business questions. For example, what are my top three selling products? This chapter contains the following topics:

• "What are calculations?"

• "About using calculations"

• "What are analytic functions?"

• "What analytic function templates are available in Discoverer"

• "How to display or hide worksheet calculations"

• "How to create calculations"

• "How to create a new calculation using an analytic function template"

• "How to edit calculations"

• "How to delete calculations"

• "Examples of calculations"

What are calculations?

Calculations are worksheet items based on formulas or expressions (e.g. mathematical formulas, or text handling functions). You use calculations to provide additional analysis to worksheets. In the figure below, the worksheet contains the calculation 'Profit (Sales-Costs)', which calculates the value of sales (i.e. the Sales SUM item) minus the value of costs (i.e. the Cost SUM item).

Figure 14-1 A Discoverer worksheet containing a calculation (Profit (Sales-Costs))

Text description of the illustration calc3.gif

For example:

• to calculate a 25% increase in sales, you might create a calculation item with the following formula:

  Sales SUM * 1.25

• to convert the City item into upper-case letters, you might create a calculation item with the following formula:

  UPPER(City)

• to calculate the league table position (i.e. using a Rank function) of values in descending order, you might create a calculation item with the following formula:

  RANK() OVER(ORDER BY Sales SUM DESC)

  Advanced functions such as Rank are known as analytic functions. For more information about analytic functions, see "What are analytic functions?".

  Note: Discoverer provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer").

When you have defined calculations, you can use them in worksheets just like other worksheet items. For example, you can:

• pivot calculations to the page axis

• include calculations in condition statements to filter worksheet data

• display or hide calculations on worksheets

• reuse calculations within other calculations

Notes

• Oracle Discoverer supports all functions that are supported by the version of the Oracle database being used. For example, analytic functions are supported by Oracle 8.1.7 (or later) Enterprise Edition databases. In other words, you have access to hundreds of pre-defined functions that you can use to support all of your business intelligence requirements.

• 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

About using calculations

Calculations can be created by the Discoverer manager or Discoverer users. When a worksheet contains calculations, you can:

• display the calculations (or turn the calculations on)

• hide the calculations (or turn the calculations off)

Calculations are displayed as new columns on worksheets. Calculations can be used in other calculations. Discoverer enables you to use a comprehensive range of pre-defined functions for use in worksheet calculations. Discoverer also provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer" and "Examples of using row-based and time-based intervals").

What are analytic functions?

Analytic functions are advanced mathematical and statistical calculations that you can use to analyze data and make business decisions. For example, these functions can 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?

Note: Analytic functions are a subset of the SQL functions available in the Oracle database.

In the example below, the Rank Sales item contains an analytic function that calculates the league table position of Cities according to sales performance. Using the Rank Sales column, you can see that New York is ranked number 1 with total sales of $85,974.23.

Figure 14-2 Worksheet containing the Rank Sales calculation

Text description of the illustration af_rankc.gif

You can create analytic functions in the following ways:

• using an easy-to-use template to help you build the function (for more information, see "How to create a new calculation using an analytic function template")

  Note: Discoverer provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer").

• creating a worksheet calculation and entering an analytic function as the formula text (for more information, see "How to create calculations")

What analytic function templates are available in Discoverer

Discoverer Plus provides easy-to-use templates for the most popular analytic functions. These templates enable you to perform complex business intelligence analysis quickly and easily.

Note: For examples of analytic functions created using templates, see "Examples of using row-based and time-based intervals".

Discoverer provides templates for the following types of analysis:

• Band by rank - creates a number of bands (e.g. quartiles) and places each value into one of the bands according to its league table position (for more information, see "Band by Rank dialog")

• Band by value - creates a number of bands (sometimes referred to as buckets) and places each value into one of the bands according to the value (for more information, see "Band by Value dialog")

• Difference - typically calculates the change in values across time (for more information, see "Difference dialog")

• Following value - returns the value that is a specified number of rows or a specified time period after each value (for more information, see "Following Value dialog")

• Group total - aggregates values within a group (for more information, see "Group Total dialog")

• Moving total - calculates a total for the specified number of rows or a specified time period before each value (for more information, see "Moving Total dialog")

• Percent contribution - calculates the ratio of a value to the sum of a set of values (for more information, see "Percent Contribution dialog")

• Percent difference - typically calculates the change in values across time as a percentage (for more information, see "Percent Difference dialog")

• Percent rank - calculates the relative position of a value in a group of values (for more information, see "Percent Rank dialog")

• Percent running contribution - can be used in 80-20 analysis (for more information, see "Percent Running Contribution dialog")

• Preceding value - returns the value that is a specified number of rows or a specified time period before each value (for more information, see "Preceding Value dialog")

• Rank - calculates the league table position of values (for more information, see "Rank dialog")

• Running total - calculates the total from the value at the start of the group to each value (for more information, see "Running Total dialog")

Notes

• When you use an analytic function template, you populate the Calculation field with the SQL statement for the analytic function formula. You can manually edit the SQL statement at any time in the Calculation field.

• For more information about analytic functions, see "What are analytic functions?".

• For more information about how to create analytic functions using templates, see "How to create a new calculation using an analytic function template".

How to display or hide worksheet calculations

When a worksheet contains calculations, you can display or hide the calculations. You display calculations on a worksheet when you want to use them to analyze worksheet data. You hide calculations on a worksheet when you do not need to use them to analyze worksheet data. For example, you might display a calculation when you export a worksheet.

To display or hide calculations:

1. Display the worksheet that you want to analyze.

2. Choose Tools | Calculations to display the "Edit Worksheet dialog: Calculations tab".
   
   
   Text description of the illustration calc2.gif

The "Edit Worksheet dialog: Calculations tab" shows calculations available to the worksheet. The check box beside each item indicates whether it is displayed on the worksheet.

3. Use the View Calculations for drop down list to change which calculations you display in the list below.

   For example, choose Active Only to display those calculations that are currently displayed on the worksheet.

4. Display or hide calculations as required, by:
   • selecting the check box next to each item that you want to display

   • clearing the check box next to each item that you want to hide

5. Click OK to close dialog and return to the worksheet.

Discoverer displays and hides the calculations that you specified.

Notes

• If you want to remove a calculation item from the worksheet permanently, delete the calculation (see "How to delete calculations").

How to create calculations

You create calculations to analyze a worksheet in a new way. For example:

• to calculate a 25% increase in sales

• to calculate the rank of sales figures

Note: Discoverer provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer").

To create a calculation:

1. Open the worksheet that you want to analyze.

2. Choose Tools | Calculations to display the "Edit Worksheet dialog: Calculations tab".

3. Click New to display the "New Calculation dialog".

   
   
   Text description of the illustration calc1.gif

4. Enter a name for the calculation in the What do you want to name this calculation? field.

   Hint: Use a short descriptive name, which will be displayed on the worksheet.

5. Enter the calculation formula in the Calculation field.

   If you are familiar with calculation syntax, you can 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, choose Selected Items or Available Items from the Show drop down list, select an item from the item list below, then click Paste to copy the item into the Calculation field.

   • To add a function to the calculation, choose Functions from the Show drop down list, select a function from the list below, then click Paste to copy the function into the Calculation field.

   • To add existing calculations to the calculation, choose Calculations from the Show drop down list, select a calculation from the list below, then click Paste to copy the calculation 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.

   • To use an analytic function template to create the formula, click Insert Formula from Template to display a pop-up list of templates and choose a template (for more information about using analytic function templates, see "How to create a new calculation using an analytic function template").

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

6. Click OK to save the calculation and display the "Edit Worksheet dialog: Calculations tab".

   Discoverer displays the calculation that you created in the calculation list.

7. Click OK to close the dialog and display the worksheet.

   Discoverer displays the new calculation as a column on the worksheet.

   Notes

   • For examples of the most commonly used functions, see "Discoverer calculation examples".

   • When using the Show drop down list to display items:
     • use the Selected option to restrict the list to items in the worksheet

     • use the Available option to display all items in the business area

     For a full list of Show options, see "New Calculation dialog".

   • If you have copied calculation text into memory from another application (for example, an e-mail message), click inside the Calculation field, right click the mouse and choose Edit | Paste to copy the text into the Calculation field.

   • If a calculation contains a syntax error, Discoverer displays an error message. You must correct syntax errors before you can save the calculation.

   • For more information about adding parameters to calculations, see "About using parameters to collect dynamic user input".

   How to create a new calculation using an analytic function template

   Discoverer provides easy-to-use templates for the most popular analytic functions (for more information, see "What analytic function templates are available in Discoverer"). You use templates to build analytic functions that help you analyze data in powerful ways and make business decisions quickly and easily. For example, you might want to calculate the league table position (i.e. rank) of sales outlets based on sales.

   You use a template to create an analytic function formula, which is inserted into the definition of a new or existing Discoverer calculation.

   To create a new calculation using an analytic function template:

   1. Open the worksheet that you want to analyze.

   2. Choose Tools | Calculations to display the "Edit Worksheet dialog: Calculations tab".

   3. Click New to display the "New Calculation dialog".

   4. Enter a name for the calculation in the What do you want to name this calculation? field.

      Hint: Use a short descriptive name, which will be displayed on the worksheet.

   5. Click Insert Formula from Template to display a pop-up list of pre-defined templates.

      
      
      Text description of the illustration calc5.gif

   6. Choose a template from the pop-up list to display a template dialog for the selected analytic function.

   7. Use the template to define the analytic function.

      For example, if you choose the Rank template, you will use the "Rank dialog" to create the formula.

      The underlying SQL statement for the analytic function formula is displayed in the Calculation field at the bottom of the template.

   8. Click OK to save the analytic function and close the analytic function template.

      The SQL statement for the analytic function that you created is transferred to the Calculation field. You might want to modify the SQL statement for the analytic function (e.g. by adding more ORDER BY clauses) or by inserting another function into the Calculation field.

      
      
      Text description of the illustration calc7.gif

      The Calculation field displays the underlying SQL statement for the analytic function that you defined.

      Note: You can subsequently modify the calculation (e.g. to add more PARTITION BY clauses) in any of the following ways:

      • by manually editing the formula in the Calculation field

      • by first deleting the formula in the Calculation field, then clicking Insert Formula from Template and recreating the formula

      • by clicking Insert Formula from Template and appending a new formula to the existing formula in the Calculation field

        Note: If you have more than one functions in the Calculation field, you must associate the functions (e.g. using + or -).

   9. Click OK to save the calculation and close the dialog.

      Discoverer displays the calculation that you created in the calculation list.

   10. Click OK to close the dialog and display the worksheet.

       Discoverer displays the new calculation as a column on the worksheet.

   Notes

   • For more information about analytic functions, see "What are analytic functions?" and "Examples of using row-based and time-based intervals".

   • If you want to use parameter values in analytic functions to collect dynamic input, you must manually prefix the item name with ':' (i.e. colon) character into the Calculation field (for more information, see "About using parameters to collect dynamic user input").

     For example, you might create a Band by rank formula based on the Profit SUM item (e.g. NTILE(4) OVER (ORDER BY Profit SUM DESC). If you want end users to select the number of bands at runtime, you might create a worksheet parameter called Band number. To use the Band number in the Band by rank formula you must manually change the formula to:

     NTILE(:Band number) OVER (ORDER BY Profit SUM DESC)

   • Analytic functions follow the standard Oracle function syntax. For a full description of this syntax, see the Oracle9i SQL Reference Manual.

   How to edit calculations

   You edit calculations to change the way that they behave. For example, to change a percentage increase calculation from 25% to 30%.

   To edit a calculation:

   1. Open the worksheet that you want to analyze.

   2. Choose Tools | Calculations to display the "Edit Worksheet dialog: Calculations tab".

   3. Select the calculation that you want to edit in the calculation list.

   4. Click Edit to display the "Edit Calculation dialog".

      
      
      Text description of the illustration calc4.gif

   5. Change the calculation as required.

      For example:

      • change the name of the calculation

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

   6. Modify the calculation (e.g. to add more PARTITION BY clauses) in any of the following ways:
      • by manually editing the formula in the Calculation field

      • by first deleting the formula in the Calculation field, then clicking Insert Formula from Template and recreating the formula

      • by clicking Insert Formula from Template and appending a new formula to the existing formula in the Calculation field

        Note: If you have more than one functions in the Calculation field, you must associate the functions (e.g. using + or -).

   7. Click OK to save the changes and close the dialog.

   8. Click OK to close the dialog and display the worksheet.

      Discoverer updates the calculation as specified.

   Notes

   • You cannot edit calculations created by the Discoverer manager. Only the Discoverer manager can edit calculations that they have created. If you want to use a similar calculation, do the following:
     1. Create a new calculation.

     2. Cut and paste the calculation text from the Discoverer manager's calculation into the new calculation.

     3. Modify the calculation formula as required.

   • If a calculation contains a syntax error, Discoverer displays an error message. You must correct syntax errors before you can save the calculation.

   How to delete calculations

   You delete a calculation when you no longer need it and want to remove it permanently from a worksheet. For example, you might have created a temporary calculation to answer a question from a colleague. After printing the report, you want to remove the calculation from the worksheet.

   To delete a calculation:

   1. Open the worksheet that contains the calculation that you want to remove.

   2. Choose Tools | Calculations to display the "Edit Worksheet dialog: Calculations tab".

   3. Select the calculation that you want to remove from the calculation list.

   4. Click Delete.

   5. Click OK to return to the worksheet.

   Discoverer removes the calculation that you specified.

   Notes

   • If you want to remove a calculation from a worksheet without deleting it permanently, you can hide the calculation (see "How to display or hide worksheet calculations").

   • You cannot delete calculations created by the Discoverer manager. Only the Discoverer manager can delete calculations that they have created.

   • If you delete a calculation that is used in other calculations, all of the dependent calculations are also deleted.

   Examples of calculations

   For examples of different types of calculation, refer to:

   • "Simple calculation examples"

   • "Oracle8i analytic function examples"

   • "Oracle9i analytic function examples"

   • "Examples of using row-based and time-based intervals"