To use Cognos product documentation, you must enable JavaScript in your browser.

Macro Functions

This list contains functions that can be used within a macro. A macro may contain one or more macro functions. A macro is delimited by a number sign (#) at the beginning and at the end. Everything between the number signs is treated as a macro expression, which is executed at run time.

+

Concatenates two strings.

Syntax
value1 + value2
Example
# '{' + $runLocale + '}'#

Result: {en-us}

array

Constructs an array out of the list of parameters.

Syntax
array ( string_exp | array_exp { , string_exp | array_exp } )
Example
#csv ( 'x1' , 'x2' , array ( 'a1' , 'a2' ) )#

Result: 'x1' , 'x2' , 'a1' , 'a2'

csv

Constructs a comma separated values string from the elements of the array. Optionally the separator and quote strings can be specified. The default separator is a comma ( , ) and the default quote character is a single quote ( ' ).

Syntax
csv ( array_exp [ , separator_string [ , quote_string ] ] )
Example
#csv ( array ( 'a1' , 'a2' ) )#

Result: 'a1' , 'a2'

dq

Surround the passed string with double quotes.

Syntax
dq ( string_exp )
Example
#dq ( 'zero' )#

Result: "zero"

grep

Searches for elements of an array that match the pattern specified in the first argument. It returns an array with the elements that pass the pattern.

Syntax
grep ( pattern_string , array_exp )
Example
#csv ( grep ( 's' , array ( 'as', 'an', 'arts' ) ) )#

Result: 'as', 'arts'

join

Joins the elements of an array using the separator string.

Syntax
join ( separator_string , array_exp )
Example
# sq ( join ( ' | | ' , array ( 'as', 'an', 'arts' ) ) )#

Result: 'as | | an | | arts'

prompt

Prompt the user for a single value. Only the prompt_name argument is required. The datatype defaults to string when not specified. The prompt is optional when defaultText is specified. The text, when specified, will precede the value. A queryItem can be specified to take advantage of the Prompt Info properties of the query item. The trailing_text, when specified, will be appended to the value.

Syntax
prompt ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
Example
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE  > #prompt('Starting CountryCode',
                   'integer',
                   '10'
)#

Result: select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 10

promptmany

Prompt the user for one or more values. Only the prompt_name argument is required. The datatype defaults to string when not specified. The prompt is optional when defaultText is specified. The text, when specified, will precede the list of values. A queryItem can be specified to take advantage of the Prompt Info properties of the query item. The trailing_text, when specified, will be appended to the list of values.

Syntax
promptmany ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
Example 1
select . . . where COUNTRY_MULTILINGUAL.COUNTRY IN ( #promptmany ( 'CountryName' ) # )

Result: select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE IN ('Canada' , 'The Netherlands' , 'Russia')

Example 2
select . . . 
from 
gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL,
gosales.gosales.dbo.COUNTRY XX
where COUNTRY_MULTILINGUAL.COUNTRY_CODE = XX.COUNTRY_CODE
    #promptmany('Selected CountryCodes',
                   'integer',
                   ' ',
   ' and  COUNTRY_MULTILINGUAL.COUNTRY_CODE IN (',
                   '',
                   ')'
)#

Result: select . . . from gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL, gosales.gosales.dbo.COUNTRY XX where COUNTRY_MULTILINGUAL.COUNTRY_CODE = XX.COUNTRY_CODE and COUNTRY_MULTILINGUAL.COUNTRY_CODE IN ('Canada' , 'The Netherlands' , 'Russia')

sb

Surround the passed string with square brackets.

Syntax
sb ( string_exp )
Example
#sb ( 'abc' )#

Result: [abc]

sq

Surround the passed string with single quotes.

Syntax
sq ( string_exp )
Example
#sq ( 'zero' )#

Result: 'zero'

sort

Sorts the elements of the array in alphabetical order. Duplicates are retained.

Syntax
sort ( array_exp )
Example
#csv ( sort ( array ( 's3', 'a', 'x' ) ) )#

Result: 'a', 's3', 'x'

split

Splits a string or the string elements of the array into separate elements.

Syntax
split ( pattern_string, string_exp | array_exp )
Example 1
#csv ( split ( '::', 'ab=c::de=f::gh=i' ) )#

Result: 'ab=c' , 'de=f', 'gh=i'

Example 2
#csv ( split ( '=' , split ( '::', 'ab=c::de=f::gh=i' ) ) )#

Result: 'ab' , 'c' , 'de' , 'f', 'gh' , 'i'

substitute

Search for a pattern in a string or in the string elements of an array and substitute the first occurrence of the found text with other text.

Syntax
substitute ( pattern_string, replacement_string, string_exp | array_exp )
Example 1
#sq ( substitute ( '^cn=', '***', 'cn=help' ) )#

Result: '***help'

Example 2
#csv ( substitute ( '^cn=', '***', array ( 'cn=help' , 'acn=5' ) ) )#

Result: '***help' , 'acn=5'

Example 3
#csv ( substitute ( 'cn=', '', array ( 'cn=help' , 'acn=5' ) ) )#

Result: 'help' , 'a5'

unique

Removes duplicate entries from the array. The order of the elements is retained.

Syntax
unique ( array_exp )
Example
Example:
#csv ( unique ( array ( 's3', 'a', 's3', 'x' ) ) )#

Result: 's3', 'a', 'x'

urlencode

URL encodes the passed argument. Useful when specifying XML connection strings.

Syntax
field_one=urlencode(prompt('userValue'))
Example
urlencode(prompt('some_val'))

Result: %27testValue%27

CSVIdentityName

Use the identity information of the current authenticated user to lookup values in the specified parameter map. Each individual piece of the user's identity (account name, group names, role names) is used as a key into the map. The unique list of values that is retrieved from the map is then returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.

Syntax
CSVIdentityName ( %parameter_map_name [ , separator_string ] )
Example
#CSVIdentityName ( %security_clearance_level_map )#

Result: 'level_500' , 'level_501' , 'level_700'

CSVIdentityNameList

Returns the pieces of the user's identity (account name, group names, role names) as a list of strings. The unique list of values is returned as a string, where each value is surrounded by single quotes and where multiple values are separated by commas.

Syntax
CSVIdentityNameList ( [ separator_string ] )
Example
#CSVIdentityNameList ( )#

Result: 'Everyone' , 'Report Administrators' , 'Query User'

CAMPassport

Returns the passport.

Syntax
CAMPassport ( )
Example
#CAMPassport ( )#

Result: 111:98812d62-4fd4-037b-4354-26414cf7ebef:3677162321

CAMIDList

Returns the pieces of the user's identity (account name, group names, role names) as a list of values separated by commas.

Syntax
CAMIDList ( [ separator_string ] )
Example
#CAMIDList ( )#

Result: CAMID("::Everyone"), CAMID(":Authors"), CAMID(":Query Users"), CAMID(":Consumers"), CAMID(":Metrics Authors")

CAMIDListForType

Returns an array of the user's identities based on the identity type (account, group, or role). It can be used with the macro functions csv or join.

Syntax
CAMIDListForType ( identity type )
Example
[qs].[userRole] IN ( #csv ( CAMIDListForType ( 'role' ) ) # )

Result: [qs].[userRole] IN ( 'CAMID("::System Administrators")', 'CAMID(":Authors")' )