9
Using parameters

This chapter explains how to use Discoverer parameters to answer typical business questions, and contains the following topics:

• "What are parameters?"

• "About parameters and conditions"

• "What are the benefits of using parameters?"

• "About using parameters"

• "About creating parameters"

• "About using parameters to collect dynamic user input"

• "How to set parameters"

• "How to activate parameters"

• "How to deactivate parameters"

• "How to create parameters"

• "How to edit parameters"

• "How to delete parameters"

• "Examples of parameters"

What are parameters?

Parameters are workbook items that enable Discoverer users to enter dynamic input values used to analyze worksheets (see figure below). Input values are typically used to:

• provide input to conditions that are used to filter worksheets - for example, when a workbook or worksheet is opened or refreshed, the parameter is used to first ask the worksheet user 'What month do you want to analyze?'. A worksheet user can choose to look at data for the month of January only.

• provide input to calculations - for example, a worksheet user can enter the value '3' when prompted, which is then used to divide data into three bands using a predefined calculation containing a banding function (see "About using parameters to collect dynamic user input")

  Figure 9-1 Edit Parameter Value dialog used to set parameters

  
  
  Text description of the illustration param1a.gif

About parameters and conditions

Parameters are runtime variables that can be used in conditions and calculations. Conditions restrict worksheet data that is displayed (for more information, see "Using conditions"). Parameters compliment conditions, and are typically used to provide run-time user input to conditions. Conditions can be static or dynamic:

• static conditions always use the same condition statement

• dynamic conditions use parameters to collect user input, which is used to create different condition statements for different parameter values

What are the benefits of using parameters?

The main benefits of using parameters to filter worksheets are:

• Worksheet data can be analyzed using dynamic user input.

• Workbooks can be targeted easily to specific groups of users.

• Worksheets open more quickly because the amount of data on a worksheet is minimized.

• If several Discoverer users are using a worksheet, each user can open the worksheet and display only the data that they are interested in. This enables users to customize worksheets to match their needs.

About using parameters

When opening or refreshing a workbook or worksheet with active parameters, the "Edit Parameter Values dialog" is displayed so that you can enter parameter values.

• You can change the parameter value at any time by choosing Sheet | Refresh Sheet and entering a different parameter value (or choose Sheet | Edit Parameter Value).

• Parameters that are part of an active condition are automatically activated.

• If you do not need to use parameters, you can deactivate them (see "How to deactivate parameters")

• You can create your own parameters (see "How to create parameters").

About creating parameters

When creating parameters, the following points apply:

• You can create parameters at two levels:
  1. Workbook level - here, the parameter applies to all worksheets in a workbook. Changes to the parameter in any worksheet apply to all worksheets in the workbook that use the same parameter.

  2. Worksheet level - here, the parameter applies to the current worksheet only.

• When you create a parameter for filtering worksheets, you typically create a condition also. The Create condition check box is selected by default on the "New Parameter dialog".

• When a condition is created with a parameter, you can deactivate the parameter by deactivating the condition. Deleting the condition deletes the parameter and vice versa.

• If you select the Create Condition check box in the "New Parameter dialog", a new condition is created and activated. Therefore, the parameter is also activated.

About using parameters to collect dynamic user input

Sometimes you want worksheet users to enter a dynamic value, typically for use in calculations. For example, to enter a value to specify the number of bands in which to group data (for more information, see "Examples of parameters").

To collect dynamic user input, do the following:

• Create a new parameter, setting the Which item do you want to base your parameter on? field to <NONE> (for more information, see "New Parameter dialog").

  Notice that you are not allowed to activate the parameter. This is because a parameter not based on a worksheet item must be used in a calculation or condition before it can be activated.

• Create a calculation and insert the parameter name as an argument.

  For example, if you create a parameter called Band Value for use in a sales banding function, you might create a calculation called Banded Sales based on the following function:

  NTILE(:Band Value) OVER(ORDER BY SUM(Sales) )

  Notice that the Band Value parameter is prefixed with a colon ':' to indicate that it is a parameter value (e.g. :Band Value).

  When the worksheet is opened or refreshed, the worksheet user is prompted to enter a banding value. If they enter the parameter value '3', the Sales SUM values on the worksheet are grouped into three bands.

  For an example of a parameter being used in a calculation, see "Example: Calculate hypothetical rank".

How to set parameters

When you open or refresh a worksheet that contains active parameters, you must enter parameter values to set the parameters. You can also accept default values. The values entered are typically used to filter the data displayed on the worksheet, or are used to provide dynamic input to calculations.

To set parameters:

1. Open a worksheet.

   If the worksheet has active parameters, these are displayed by the "Edit Parameter Values dialog". If defined, a default value is displayed in the text field next to each parameter.

   
   
   Text description of the illustration param1.gif

2. Enter a value for each parameter by doing one of the following:

• Type in a value as prompted.

• (optional) Accept the default value, if a default value is defined.

• (optional) Click the down arrow next to the field and select a value from the drop down list of values next to each parameter (where available).

  If the list of values in the drop down list is too long to display on screen, the "Select Value dialog" or "Select Values dialog" is displayed. These dialogs enable you to search for and select the values that you want to use. For more information, see "Using lists of values (LOVs)".

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

The worksheet is updated according to the parameter values selected. For example, if the parameter value Central is used to filter the worksheet data on Region, the worksheet displays only data for the Central region 2000 (see figure below).

Figure 9-2 A worksheet filtered by a parameter value

Text description of the illustration param4.gif

How to activate parameters

You activate parameters when you want Discoverer users to be prompted to enter parameter values when they open or refresh worksheets. For example, to choose how to filter worksheet data.

Parameters are activated by association. If parameters are included in active conditions or calculations, the parameters become active. When you activate parameters, they remain active until they are deactivated (see "How to deactivate parameters").

To activate parameters:

1. Open the worksheet containing the parameter.

2. To see which parameters are available, choose Tools | Parameters to display the "Edit Worksheet dialog: Parameters tab".

   
   
   Text description of the illustration param9.gif

   The Parameters tab lists parameters available to the worksheet. The check box beside each item indicates whether it is activated.

3. Activate the condition or calculation that includes the parameter:
   • If the parameter is included in a condition, display the Conditions tab and select the check box next to the condition containing the parameter, then click OK to close the dialog.

   • If the parameter is included in a calculation, display the Calculation tab and select the check box next to the calculation containing the parameter, then click OK to close the dialog.

4. If the "Edit Parameter Values dialog" is displayed, enter parameter values as prompted, then click OK.

   The worksheet is updated according to parameter values entered.

Notes

• To update the workbook or worksheet with a different parameter value, choose Sheet | Refresh Sheet to display the "Edit Worksheet dialog: Parameters tab" and enter a new value.

• On the "Edit Worksheet dialog: Parameters tab", although the check boxes show the status of the parameters, the check boxes are greyed out. This is because you cannot activate and deactivate parameters by selecting and de-selecting check boxes. You must modify the condition or calculation using a parameter to affect the parameter status.

How to deactivate parameters

You deactivate parameters when you do not want Discoverer users to be prompted to enter parameter values when they open or refresh workbooks or worksheets.

Parameters become deactivated when they are not included in conditions or calculations.

Note: If you want to disable the parameter permanently, delete the parameter (see "How to delete parameters").

To deactivate parameters:

1. Open the worksheet containing the parameter that you want to deactivate.

2. To see which parameters are available, choose Tools | Parameters to display the "Edit Worksheet dialog: Parameters tab".

   
   
   Text description of the illustration param9.gif

   The Parameters tab lists parameters available to the worksheet. The check box beside each item indicates whether it is activated.

3. Deactivate the condition or calculation that includes the parameter:
   • If the parameter is included in a condition, display the Conditions tab and clear the check box next to the condition containing the parameter, then click OK to close the dialog.

   • If the parameter is included in a calculation, display the Calculation tab and clear the check box next to the calculation containing the parameter, then click OK to close the dialog.

4. (optional) If the worksheet has active parameters, enter parameter values as prompted in the "Edit Parameter Values dialog".

5. Click OK.

   The worksheet is updated according to parameter values entered.

Notes

• To update the workbook or worksheet with a different parameter value, choose Sheet | Refresh Sheet to display the "Edit Worksheet dialog: Parameters tab" and enter a new value.

• On the "Edit Worksheet dialog: Parameters tab", although the check boxes show the status of the parameters, the check boxes are greyed out. This is because you cannot activate and deactivate parameters by selecting and de-selecting check boxes. You must modify the condition or calculation using a parameter to affect the parameter status.

How to create parameters

You create parameters to enable Discoverer users to enter input values when a worksheet is opened or refreshed. For example, to provide dynamic input to a condition or calculation.

To create a parameter:

1. Open the Discoverer workbook containing the worksheet to which you want apply a parameter.

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

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

   
   
   Text description of the illustration param7.gif

4. Enter a parameter name into the What do you want to name this Parameter? field. If you do not enter a name, Discoverer creates a default Parameter name for you.

5. Select an item for the parameter from the Which item would you like to base your Parameter on? drop down list.

   For example, to create a parameter for selecting a city, select the data item that contains the city names. The list shows the items available for use in the parameter.

   Note: To create a parameter for entering dynamic user input, choose <NONE>. For more information, see "About using parameters to collect dynamic user input".

6. (optional) Enter an instruction or question into the What prompt do you want to show to other users? field. This prompt is displayed to Discoverer users when they open or refresh the worksheet, and tells them what value to enter.

7. (optional) Enter a brief description into the What description do you want to show to other users? field. This text is displayed on the Edit Parameter Values dialog and helps users decide what parameter value to enter.

8. (optional) If required, enter a default value in the What default value do you want to give this Parameter? field. Here, you can either:
   • Type a default value directly into the field.

   • If a list of values is available for this value, click the drop down arrow and select a parameter value from the list.

   If the list of values in the drop down list is too long to display on screen, the "Select Value dialog" or "Select Values dialog" is displayed. These dialogs enable you to search for and select the values that you want to use. For more information, see "Using lists of values (LOVs)".

9. Select the Let other users select multiple values check box if you want worksheet users to be able to select multiple parameter values for the Parameter. For example, if a parameter is used to filter a worksheet on year, a user might want to look at 2001 and 2002.

10. Do one of the following:
    • Select the Allow only one value for all Sheets radio button to make the parameter value apply to all worksheets in the workbook that use this parameter.

    • Select the Allow a different value in each Sheet radio button to make the parameter value apply to the current worksheet only.

    For more information, see "About creating parameters".

11. (optional) Select the Create Condition check box if you want to create a condition in the conditions list based on the parameter. Select an operator for the condition from the drop list. For example, =, <, >.

    This enables you to filter worksheets according to arbitrary parameter values. For example, if you create a parameter on year called Choose Year and select the > operator, a condition is created: Year > :Choose Year. The :Choose Year value is the value entered by the worksheet user.

12. Click OK to save the details and display the "Edit Worksheet dialog: Parameters tab".

    Notice that the parameter that you created is selected by default.

13. Click OK to close the Edit Worksheet dialog and return to the worksheet.

    If the new parameter is active, enter parameter values at the "Edit Parameter Values dialog". The worksheet is updated according to parameter values entered (see "How to set parameters").

How to edit parameters

You edit parameters to change the way that they behave. For example, to change the default parameter value, or change the prompt displayed to Discoverer users when they enter parameter values.

To edit a parameter:

1. Display the worksheet that contains the parameter that you want to edit.

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

   
   
   Text description of the illustration param9a.gif

3. Select the parameter that you want to edit from the Available Parameters list.

4. Click Edit to display the "Edit Parameter dialog".
   
   Text description of the illustration param8.gif

5. Make changes to the parameter as required.

6. Click OK to save changes and return to the Parameters tab.

7. Click OK to close the Parameters tab and return to the worksheet.

   If parameters are active, enter parameter values at the "Edit Parameter Values dialog". The worksheet is updated according to parameter values entered (see "How to set parameters").

Notes

• When you are editing a parameter used in a condition, the "Edit Parameter dialog" does not allow you to change the condition settings. You can only edit conditions created for parameters using the "Edit Worksheet dialog: Conditions tab".

How to delete parameters

You delete a parameter when you no longer want to use it, and want to remove it permanently from the worksheet.

Note: If you only want to disable the parameter temporarily, deactivate the parameter (see "How to deactivate parameters").

To delete a parameter:

1. Display the worksheet that contains the parameter that you want to remove.

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

   
   
   Text description of the illustration param9a.gif

3. Select the parameter that you want to remove from the Available Parameters list.

4. Click Delete to remove the parameter from the worksheet, and click Yes at the confirmation dialog.

5. Click OK to close the Edit Worksheet and return to the worksheet.

   When you open this workbook again, or refresh the worksheet, Discoverer will not prompt you to enter a parameter value for this parameter.

Notes

• If the parameter that you delete was included in conditions or calculations, those conditions and calculations are also deleted.

Examples of parameters

Example 1: In this example a parameter is used to filter a worksheet. For example, you might want worksheet users to be able to select which region's data they want to analyze. In the figure below, the value Central is entered in the Edit Parameter Values dialog. This displays only data for the Central region on the worksheet.

Figure 9-3 A parameter value being used to filter a worksheet

Text description of the illustration param11.gif

Example 2: In this example a parameter that enables multiple values to be specified is used to filter a worksheet. For example, you might want worksheet users to be able to select which region's data they want to analyze. In the figure below, the values Central and East are entered at the Edit Parameter Values dialog. This displays only data for the Central and East region on the worksheet.

Figure 9-4 A parameter enabling multiple values being used to filter a worksheet

Text description of the illustration param3.gif

Example 3: This example shows how you can use more than one parameter to filter a worksheet. For example, you might want worksheet users to be able to select which region and which department's data they want to analyze. In the figure below, the values Central (Region) and Video Rental (Department) are selected.

Figure 9-5 A worksheet with more than one parameter defined

Text description of the illustration param5.gif

Example 4: This example shows how you can use a parameter to collect dynamic user input. For example, you might want worksheet users to be able to select how many bands worksheet data is arranged into.When the value '2' is entered, the Profit SUM figures are placed into two bands.

Figure 9-6 A parameter used to provide dynamic input to a banding calculation

Text description of the illustration param10.gif