Creating prompts using query macros

You can create mandatory and optional prompts in reports using query macros.

Use the prompt macro functions prompt and promptmany to create single-value and multiple-value prompts. You can use prompt macro functions when working with a relational data source or a dimensionally-modeled relational (DMR) data source.

If you want to use a prompt macro in an expression such as a calculation, you must specify the data type when using an overloaded operator, such as a plus sign (+). You can use the plus sign to concatenate two items and to add two items.

If you want to define a filter on a dimension level and have the filter use the prompt or promptmany macro, you must provide the data type as memberuniquename and a default value.

Here is an example:

members( [MS_gosales].[New Dimension].[PRODUCTLINE].[PRODUCTLINE])
 in ( set( #promptmany('what', 'memberuniquename', 
'[MS_gosales].[PROD1].[PRODUCTLINE].[PRODUCTLINE]
->[all].[1]')# ) )

Here is an example of a mandatory prompt:

select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
where COUNTRY_MULTILINGUAL.COUNTRY = #prompt('CountryName')#

When default values are specified in the syntax of macro prompts, you may see an error. Use prompt syntax such as where Country = ?Enter Country?.

The prompt and promptmany functions have the following mandatory and optional parameters. All argument values must be specified as strings.

Name

This mandatory parameter is the name of the prompt. Name can also refer to the name of a parameter on a user-created prompt page, in which case the user-created prompt page appears when the report is run instead of the default prompt page that the macro would generate.

Datatype

This optional parameter is the prompt value data type. The default value is string. Prompt values are validated. In the case of strings, the provided value is enclosed in single quotation marks and embedded single quotation marks are doubled.

Values include the following:

  • boolean
  • date
  • datetime
  • decimal
  • double
  • float
  • int
  • integer
  • interval
  • long
  • memberuniquename

    Memberuniquename is not an actual data type. This value must be used when the data type of the parameter is member unique name (MUN).

  • numeric
  • real
  • short
  • string
  • time
  • timeinterval
  • timestamp
  • token

    Token is not an actual data type. It is a way to pass SQL. A token does not pass values.

DefaultText

This optional parameter is the text to be used by default. If a value is specified, the prompt is optional.

If you use a space and no values are provided in the Prompt Value dialog box, a Where clause is usually not generated.

If you use text and no values are provided in the Prompt Value dialog box, a Where clause is usually generated using the default value.

Ensure that the text you provide results in a valid SQL statement.

Note: If the data type is memberuniquename, a value for the DefaultText parameter must be provided. For example:

(#prompt('WhichLevel', 'memberuniquename', '[goSalesAgain].[PRODUCT1].[PRODUCT].[PRODUCT(All)]->[all]')#)

Text

This optional parameter is text that precedes any user-provided values, such as 'and column1 = '.

QueryItem

This parameter is optional. The prompt engine can take advantage of the Prompt Info properties of the query item. Descriptive information can be shown, although the prompt value is a code.

TextFollowing

This optional parameter is the closing parenthesis that is used most often for the promptmany function. This parameter is also useful when the prompt is optional and is followed by hardcoded filters in the SQL statement.