GETPROMPTFILTER and GETPROMPTVALUE

BQL includes the following functions to pass prompt filters and values that a user selects to other expressions and to formulas that define custom attributes and measures.

GETPROMPTFILTER and GETPROMPTFILTERS

  • Use GETPROMPTFILTER to pass a filter (prompt) name and its user-selected value or values from a dashboard into an expression. GETPROMPTFILTERS passes all filter names and values for that specific dashboard.

  • GETPROMPTFILTERS is also useful in situations where the set of filters may change, and the measure expression would not have to change.

  • Passing Prompt Filters to Report Expressions

  • Syntax:

  • [measure] WHERE GETPROMPTFILTER('filter_column_name')...

  • [measure] WHERE GETPROMPTFILTERS()...

  • Examples:

  • SELECT [Order_Date: Sum: Quantity] WHERE GETPROMPTFILTER('Time.Year')...

  • SELECT [Order_Date: Sum: Quantity] WHERE GETPROMPTFILTER('Time.Year') AND GETPROMPTFILTER(Products.Categories...

  • SELECT [Order_Date: Sum: Quantity] WHERE GETPROMPTFILTERS()...

  • For example, to divide the quantity sold for a particular retail region by the total sold, where the region value is a user-selected filter value.

  • ([Order_Date: Sum: Quantity] WHERE GETPROMPTFILTER('Retail_Stores.Retail Region'))/[Order_Date: Sum: Quantity]

  • Important: For Designer you must use the name in the Prompt Properties Parameter Name field to refer to the prompt, not the Display Name.

GETPROMPTVALUE

GETPROMPTVALUE syntax uses either a column filter (called a prompt in Original Dashboards) or a parameter filter.

Syntax:

GETPROMPTVALUE('filter_column_name|parameter_name', 'optional_default_value')

Tips:

  • When prompt filter values are used in report expressions, they are by default returned as strings and must be cast to numeric data types if they are to be used in arithmetic functions.
  • The optional second argument, the default value, must be either a VARCHAR data type or NULL.
  • The optional second argument, the default value, can also be supplied by a variable using GETVARIABLE.
    GETPROMPTVALUE('Products.Products', VARCHAR(GetVariable('Default Product')))

Best Practice: Define the optional second argument to be used in the event that the first argument is not a currently defined prompt.

Column Filters versus Parameter Filters

The important distinction between a column filter and a parameter filter is whether or not Birst will add the filter into the WHERE clause. Column filters affect the WHERE clause and update the result set appropriately. Use a parameter filter if you want to use the response value for other capabilities, like controlling colors or doing "what-if" analysis using constants.

Creating Parameter Filters

In Dashboards 2.0 you explicitly create parameter filters. See Creating Dashboard Filters.

To create a parameter prompt in Original Dashboards, click a prompt to open the Prompt Properties dialog. Click Change parameter name (for report expression prompts) and enter a new name in the Parameter name field.

Passing Column Filter Values in Report Expressions

Use GETPROMPTVALUE to pass a user-selected value from a prompt or filter into an expression. When passing a column name, Birst adds the filter to the WHERE clause.

Syntax:

GETPROMPTVALUE('filter_column_name', 'default_value')

The column name for Original Dashboards is the column name in the Parameter name field, not the display name. For Dashboards 2.0 it is the column name, not the filter name. Both of these names use the format dimension_name.attribute_name.

For example:

GETPROMPTVALUE('Time.Year','2014')

Passing Parameter Filter Values in Report Expressions

Use GETPROMPTVALUE with a parameter to pass a value that is used to control other functions, without using a WHERE clause.

Syntax:

GETPROMPTVALUE('parameter_name', 'default_value')

The prompt name for Original Dashboards is the Prompt Parameter Name/Change parameter name (for report expression prompts) .

For Visualizer and Dashboards 2.0 expressions it is a parameter filter name.

Simple Example of a Parameter Filter

For example, in Dashboards 2.0 create a parameter filter called ConstantsParameter with the radio button values 1,2,3. Then create a KPI with the following expression:

(GETPROMPTVALUE('ConstantsParameter', '1'))

On the dashboard when you change the filter, the value of the KPI also changes.

To take the example one step further, create a Visualizer table with a quantity column and a column based on an expression that multiplies quantity by the user-selected constant:

[Order_Date: Sum: Quantity]*INTEGER(GETPROMPTVALUE('ConstantsParameter', '1'))

Tip: Remember that you have to cast the parameter as an integer, and that supplying a default value is a best practice.

On the dashboard when you change the filter, the value of the expression column reflects the multiple.

Passing Prompt Filters and Values to Custom Attributes or Measures

Use GETPROMPTVALUE for dynamic custom attributes and measures based on user selections in prompts at run time. GETPROMPTVALUE inserts the user’s selected value into the formula definition before sending the query to the database. 

Tip: Unlike report expressions, casting the prompt value is not necessary as the user’s selection is inserted and the formula is sent to the database.

Example custom attribute MyCustomAttr:

DatePart(Year,[Custom Week.Week End Date])

Example filter:

[Custom Week.MyCustomAttr] = INTEGER(GETPROMPTVALUE('Time.Year'))

See Also
Defining Expressions
Creating Custom Attributes
Creating Custom Measures