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

• "How to display or hide worksheet calculations"

• "How to create calculations"

• "How to edit calculations"

• "How to delete calculations"

• "How to display or hide worksheet calculations"

What are calculations?

Calculations are worksheet items based on expressions (e.g. mathematical formulas, or text handling functions). You use calculations to analyze worksheet data in new ways. In the figure below, the worksheet contains the calculation 'Profit (Sales-Costs)', which calculates Sales SUM minus Cost SUM.

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 rank of sales figures in descending order, you might create a calculation item with the following formula:

  RANK() OVER(ORDER BY Sales SUM DESC)

When you have defined calculations, you can use them in worksheets just like other 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

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 Server 8.1.6 and above, including Oracle9i.

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 provides a comprehensive range of pre-defined functions for use in worksheet calculations.

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, when calculations are used to calculate other information.

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" lists 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 the "Edit Worksheet dialog: Calculations tab" 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

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 meaningful name for the calculation in the What do you want to name this calculation? field.

5. Enter the calculation expression in the Calculation field.

   If you are familiar with calculation syntax, you type the expression in the Calculation field. 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 pull 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.

6. Click OK to validate the calculation and close the New Calculation dialog.

   If the calculation is syntactically correct, Discoverer displays the "Edit Worksheet dialog: Calculations tab". Notice that Discoverer displays the calculation that you created in the calculation list and that the calculation item is turned on by default (i.e. the check box next to the item is selected).

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

   Discoverer adds the new calculation to the worksheet.

   Notes

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

   6. Click OK to validate the calculation and close the Edit Calculation dialog.

      If the calculation is syntactically correct, the "Edit Worksheet dialog: Calculations tab" is displayed.

   7. Click OK to close the Calculations dialog and return to 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. amend the calculation expression 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 an ad hoc query from your manager. 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 deleted from the worksheet.

   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"

   • "More about the Discoverer analytic function template"

   • "About analytic functions and sequencing"