12
Using percentages

This chapter explains how to use Discoverer percentages to answer typical business questions. For example, what is the sales total for January as a percentage of the annual sales total? This chapter contains the following topics:

• "What are percentages?"

• "How to display or hide percentages"

• "How to create percentages"

• "How to edit percentages"

• "How to delete percentages"

• "Example of percentages"

What are percentages?

Percentages are worksheet items that enable you to quickly and easily calculate percentages of rows and columns. For example, to calculate monthly profit figures as a percentage of the annual profit figure.

The figure below shows a worksheet containing a percentage item called 'Percentage of annual profit'. From this column you can see that the Video Rental department in the Central region contributed 8% of the annual profit total (i.e. $25,157).

Figure 12-1 A Discoverer worksheet with a percentage item (Percentage of annual profit)

Text description of the illustration perc_exp.gif

Notes:

• You can also use Discoverer totals or calculations to calculate percentages (for more information, see "How to create totals" and "How to create calculations").

• When you have defined percentages, you can use them in worksheets just like other items. For example, you can display or hide percentages on worksheets.

How to display or hide percentages

If a worksheet contains existing percentages (e.g. percentages created by other Discoverer users, or created by the Discoverer manager) you can display or hide the percentages on the worksheet.

You display or hide the percentages as follows:

• display percentages on a worksheet to use them to analyze worksheet data

• hide percentages on a worksheet to not use them to analyze worksheet data

To display or hide a percentage:

1. Choose Tools | Percentages to display the "Edit Worksheet dialog: Percentages tab".

   
   
   Text description of the illustration p1.gif

   The Percentages tab lists percentages available to the worksheet. The check box beside each item indicates whether it is displayed on the worksheet. Only selected percentages are currently displayed on the worksheet.

2. Use the View percentages for drop down list to change which percentages you display in the list below.

   For example, choose the Active Only option to display percentages currently displayed on the worksheet.

3. To display or hide percentages:
   • Select the check box next to each percentage that you want to display.

   • Clear the check box next to each percentage that you want to hide.

   Hint: To help you decide which percentages to display, use the Description field. The Description field shows any additional information that exists about the currently highlighted percentage.

4. Click OK to close the Percentages tab and return to the worksheet.

Discoverer refreshes the worksheet according to the options selected.

How to create percentages

You create percentage items to analyze a worksheet in a new way. For example, you might want to display monthly sales figures as a percentage of the annual sales figures.

Worksheets can also contain percentages created by other Discoverer users, or created by the Discoverer manager.

To create a percentage:

1. Choose Tools | Percentages to display the "Edit Worksheet dialog: Percentages tab".

2. Click New to display the "New Percentage dialog".

   
   
   Text description of the illustration p2.gif

Discoverer gives a default name to a new percentage based on what you specify in the "New Percentage dialog".

3. (optional) If you want to change the default name of the new percentage, enter the name in the What do you want to name this percentage? field.

4. Use the Which data point do you want to base your percentage on? drop down list to specify which item to create a percentage for.

   The drop down list displays numeric items currently displayed on the worksheet. If there is only one numeric item on the worksheet, this item is selected by default.

5. Use the Calculate as a percentage of radio buttons to choose where to display the percentage, as follows:
   • Select the Grand total of all values radio button to calculate the values as a percentage of the whole column.

   • Select the Subtotal at each change in radio button, then choose the item on which to group the data from the drop down list below.

     For example, if the worksheet data is grouped by region, select region here to calculate a percentage sub-total for each region.

6. Use the Which page items do you want to include? radio buttons to choose whether to calculate percentages for the currently displayed page item or all page items on the worksheet.

7. Use the Which totals do you want to be shown? check boxes to specify how you display grand totals and subtotals.

   The options available depend on whether you have selected the Grand total of all values radio button, or the Subtotal at each change in radio button.

   • (optional) If you selected the Grand total of all values radio button, select the Show grand total and grand total percentage check box to calculate total values at the bottom of the worksheet.

     You can also specify a label for the total, and click Format Heading and Format Data to specify how it looks on the worksheet.

   • (optional) If you selected the Subtotal at each change in radio button, select the Show subtotal and subtotal percentage check box if you want to calculate subtotal values for each data sub-group.

   • (optional) If you selected the Subtotal at each change in radio button, select the Show the percentage of the grand total for each subtotal option to display the sub-group total as a percentage of the total value for all groups.

8. Click OK to save the details and display the Percentages dialog.

9. Click OK to close the Percentages dialog and return to the worksheet.

   Discoverer calculates the percentages and displays them on the worksheet.

   Notes:

   • If you use the Show the percentage of the grand total for each subtotal option, the grand total value includes all items displayed on the worksheet and also items in the page items area (if any). In other words, the subtotal percentages might not add up to 100% unless you display all page items on the worksheet.

   How to edit percentages

   You edit percentages to change the way that they behave. For example, you might want to change a grand total percentage to a subtotal and grand total percentage.

   To edit a percentage:

   1. Choose Tools | Percentages to display the "Edit Worksheet dialog: Percentages tab".

   2. Select the percentage that you want to edit from list of percentages.

   3. Click Edit to display the "Edit Percentage dialog".

   4. Edit the percentage details as required.

   5. Click OK to save the changes and close the Edit Percentage dialog.

   6. Click OK to close the Edit Percentage dialog and display the worksheet.

      Discoverer refreshes the worksheet to reflect changes to the percentage.

   How to delete percentages

   You delete a percentage when you no longer want to use it, and want to permanently remove it from a workbook. For example, you might have created a temporary percentage to produce an ad hoc report and now want to remove the percentage from the workbook.

   To delete a percentage:

   1. Choose Tools | Percentages to display the "Edit Worksheet dialog: Percentages tab".

   2. Select the percentage that you want to delete from the list of percentages.

   3. Click Delete.

      The percentage that you deleted is removed from the list of percentages.

   4. Click OK to close the Percentages dialog and display the worksheet.

   If the deleted percentage was previously displayed on the worksheet, Discoverer removes the percentage information from the worksheet display pane.

   Notes:

   • To remove a percentage from the worksheet without deleting the percentage permanently, you can hide the percentage (for more information, see "How to display or hide percentages").

   • If the deleted percentage was used to sort a worksheet, the sorting is removed and the items revert to the default order.

   Example of percentages

   Example 1: This example shows how to use the New Percentage dialog to create a new percentage, and how the percentage is displayed on a worksheet. In the figure below, you calculate the profit sum for each region and department as a percentage of total profit.

   Figure 12-2 A Discoverer percentage on a table worksheet

   
   
   Text description of the illustration perc1.gif

   Key to figure:

   1. The New Percentage dialog.

   2. The name of the percentage item.

   3. The item (column) on which the percentage is based.

   4. The percentage type.

   5. The scope of the percentage item. You can create percentages for the currently displayed page item only, or all page items.

   6. The percentage item (Percentage of annual profit) as it appears on the worksheet.

   7. The percentage values calculated by Discoverer. For example, using the new percentage item you can see that the Video Rental department in the Central region generated 12% of total profit (i.e. $47,204).

   Example 2: This example shows how to use the Edit Percentage dialog to produce percentage subtotals. In the figure below, you calculate a subtotal percentage value for each region. In other words, what percentage of profit does each department contribute to the region total.

   Figure 12-3 A percentage based on a grouped total

   
   
   Text description of the illustration perc3.gif

   Key to figure:

   1. The Subtotal at each change in option calculates the value as a percentage of all values in a sub-group. The groups are defined by a change in value in this item.

   2. In this case, the value Region is selected, which calculates a percentage sub-total for each region.

   3. The percentage item (Percentage of regional profit) as it appears on the worksheet.

   4. For example, the Video Rental department in the Central region contributes 41% of the total profit for the Central region (i.e. $47,204). Notice that the percentages for each region add up to 100%.