Parameters

Generate a drop-down menu in a Cube View or Table View column based on selected parameters in the Excel Add-In or Spreadsheet to quickly filter your data. This functionality eliminates the need to manually create drop-down menus. When a parameter in the drop-down menu is changed, the worksheet will refresh to show the updated output. Parameters that are dependent on one another will updated automatically when the related parameter is altered.

NOTE: See Table View Columns to set up your business rule code to generate a nested parameter in a column in a Table View.

Supported Parameters

The following types of parameters are supported: 

  • Literal

  • Input

  • Delimited

  • Bound

  • Member

Watch to learn more (3:06).

Unsupported Parameters

The member dialog parameter is not supported.

Create a Parameter

Follow these steps to create a parameter driven drop-down menu for a Cube View:

  1. Select the cell to display the drop-down menu.

  2. In the Excel ribbon, navigate to OneStream > General > Parameters.

  3. In the Parameters dialog box, click the Add button.

  4. In the Parameter Selector Definition dialog box, populate the following fields: 

    Set up a Parameter Dialog Box

    • Parameter Name: Click the ellipsis to search for and select the parameter. The name is automatically populated based on the selected parameter.

      NOTE: It is best practice when creating parameters that the names be unique, even across Workspaces. See Best Practices.

    • Workspace Name: Name of the application workspace of the parameter. This field automatically populates when a parameter is selected.

    • Refers To: Cell or range that will display the button.

    • Use Display Value: The Use Display Value setting enables you to choose which item to use in your drop-down menus. In Application > Workspaces > [...] > Parameters, you can configure Display and Value Items.

      For example, if you configure the following settings: 

      • Display Items: MI, OH

      • Value Items: Michigan, OH

      Then, if Use Display Value is selected, the Display Items (MI and OH) will display in the drop-down menu. If it is clear, the Value Items (Michigan and OH) will display.

    • On Change Refresh: When you switch parameters, refresh the worksheet or workbook. Select Nothing to keep the sheet as is.

  5. Click the OK button, then click the Close button on the Parameters dialog box.

Parameter Best Practices

  • When working with parameters in Excel and Spreadsheet, remember that nested parameters and input parameters need to be consumed by a Cube View or Table View. Using nested, standalone parameters may generate undesirable results.

  • When adding dependent parameters to a worksheet manually, remember that parameters must be added in the order in which their sequence must be resolved. When using Add Parameter Selectors to Sheet, the parameters will automatically be added in their sequential orde.r

    Example: Year must be added before Month. Region must be added before Sub-Region.

  • For dependent parameters to refresh correctly when the parent parameter is changed, the Workspace containing the Cube View or dependent parameter within the Spreadsheet or the Excel Add-In must be sharable. To enable this, navigate to Application > Workspaces > Sharing and ensure Is Sharable Workspace is set to True.