prompt

Prompts the user for a single value or member. Only "prompt_name" is required. The datatype defaults to 'string' when it is not specified. The prompt is optional when "defaultText" is specified. "Text", when specified, will precede the value. "QueryItem" can be specified to take advantage of the prompt information properties of "queryItem". "Trailing_text", when specified, will be appended to the value. When the data type is 'memberuniquename', a single select member prompt is created. It is good practice to provide a default value, as 3rd argument. The default value must be a valid member unique name; it can also be an alias that references a member unique name as done in Report Studio.

Syntax

prompt ( prompt_name , datatype , defaultText , text , queryItem ,
 trailing_text )

Example 1

select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE  > #prompt (
 'Starting CountryCode' , 'integer' , '10' ) #

Result

select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 10

Example 2

[gosales].[COUNTRY].[COUNTRY] = # prompt ( 'countryPrompt' ,
 'string' , '''Canada''' ) #

Result

[gosales].[COUNTRY].[COUNTRY] = 'Canada'

Example 3

set ( #prompt ('myProduct', 'memberuniquename', '[Camping 
Equipment]', '', '[Sales].[Product].[Product].[Product line]')#)

Result

set([Sales].[Product].[Product].[Product line]->[all].[1])

Notes

The "defaultText" parameter must be specified such that it is literally valid in the context of the macro since no formatting takes place on this value. The default string '''Canada''' in Example 2 is specified as a string using single quotes, in which the embedded single quotes are doubled up, thus 3 quotes. This results in the string being properly displayed within single quotes in the expression. As a general rule for the string datatype, "defaultText" should always be specified like this, except in the context of a stored procedure parameter. For "defaultText" of types 'date' or 'datetime', a special format should be used in the context of SQL. Examples of these formats are 'DATE ''2001-12-25''' and 'DATETIME ''2001-12-25 12:00:00'''. In all other contexts, use the date/datetime without the keyword and escaped single quotes (e.g., '2001-12-25').