Creating prompts with query macros

Macros are fragments of code that you can insert anywhere in the Select statement that defines a query subject. You can include references to session parameters, parameter maps, and parameter map entries. Parameter values are set when you run the query.

For example, you can use the language session parameter to show only the data that matches the language setting for the current user.

Macros can be used in these different ways:

They can be inserted in the SQL.
An example is Select * from Country where Country.Name = #$myMap{$runLocale}#
They can supply an argument to a stored procedure query subject.
If a value is not hard-coded for the argument, the stored procedure query subject can be used to return different data.
They can be inserted in expressions, such as calculations and filters.
An example is a filter [gosales].[Sales staff].[Staff name] = #$UserLookUpMap{$UserId}#
They can be used to dynamically complete the properties of a data source query subject.
This enables different users to supply different connection information and thus access different data sources. The properties that can contain macros are: Content Manager Datasource, Catalog, Cube, and Schema.

An example using the Content Manager Datasource property is #$DataSourceMap{$UserId}#
They can be used as a parameter wizard.
Parameters can reference other parameters. An example is Map1, Key = en-us, Value = #$myMap{$UserId}#
They can be used in the Session Parameter dialog box.
An example is MySessionParameter, value = #$myMap{$UserGroup}#

You can replace the following query subject elements with a parameter.

Element	Example
Query items identified in the `Select` list	`#'Product_name_'+ $languageCode#`
Tables identified in the `From` clause	`Product_#$language#`
`Where` clause	`Product_lang = #sq($languageCode)#`
Name of the data source, schema, or source property	`#$data_source#.#$schema#.Products`