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, and choose the following:

  • choose <NONE> from the Which item do you want to base your parameter on? drop down list (for more information, see "New Parameter dialog")

  • clear the Let other users select multiple values check box

    Note: If a Discoverer end user enters more than one parameter value, only the first parameter value is used.

  Notice that you cannot activate the parameter. Before it can be activated, a parameter not based on a worksheet item must be used in a calculation or condition.

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