GETPROMPTFILTER and GETPROMPTVALUE

BQL includes these two 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. The GETPROMPTFILTERS function 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 be required 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]

      Note: 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 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.

Parameter Filters

Column filters affect the WHERE clause and update the result set appropriately.

  • Creating Parameter Fillers
    • To create parameter filters, see Adding or embedding a filter in the Infor Birst Dashboards User Guide.

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 uses 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')

For Visualizer and Dashboards 2.0 expressions the prompt name 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'))
      Note: 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.

      Constants Parameter example

  • 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.
      Note: 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'))