Cognos 8 Macro functions list

We have seen that cognos macro functions available in framework manager are also available in report studio.

Here is a simple list of existing Cognos Macro with description.

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.
+

value1 + value2
Concatenates two strings.
Example: # '{' + $runLocale + '}'#
Result: {en-us}
CAMIDList

CAMIDList ( [ separator_string ] )
Returns the pieces of the user's identity (account name, group names, role names) as a list of values separated by commas.
Example: #CAMIDList ( )#
Result: CAMID("::Everyone"), CAMID(":Authors"), CAMID(":Query Users"), CAMID(":Consumers"), CAMID(":Metrics Authors")
CAMIDListForType

CAMIDListForType ( identity type )
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.
Example: [qs].[userRole] IN ( #csv ( CAMIDListForType ( 'role' ) ) # )
Result: [qs].[userRole] IN ( 'Administrator', 'developer' )
CAMPassport

CAMPassport ( )
Returns the passport.
Example: #CAMPassport ( )#
Result: 111:98812d62-4fd4-037b-4354-26414cf7ebef:3677162321
CSVIdentityName

CSVIdentityName ( %parameter_map_name [ , separator_string ] )
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.
Example: #CSVIdentityName ( %security_clearance_level_map )#
Result: 'level_500' , 'level_501' , 'level_700'
CSVIdentityNameList

CSVIdentityNameList ( [ separator_string ] )
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.
Example: #CSVIdentityNameList ( )#
Result: 'Everyone' , 'Report Administrators' , 'Query User'
array

array ( string_exp | array_exp { , string_exp | array_exp } )
Constructs an array out of the list of parameters.
Example:
#csv ( 'x1' , 'x2' , array ( 'a1' , 'a2' ) )#
Result:
'x1' , 'x2' , 'a1' , 'a2'
csv

csv ( array_exp [ , separator_string [ , quote_string ] ] )
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 ( ' ).
Example:
#csv ( array ( 'a1' , 'a2' ) )#
Result:
'a1' , 'a2'
dq

dq ( string_exp )
Surround the passed string with double quotes.
Example: #dq ( 'zero' )#
Result: "zero"
grep

grep ( pattern_string , array_exp )
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.
Example:
#csv ( grep ( 's' , array ( 'as', 'an', 'arts' ) ) )#
Result:
'as', 'arts'
join

join ( separator_string , array_exp )
Joins the elements of an array using the separator string.
Example:
# sq ( join ( ' | | ' , array ( 'as', 'an', 'arts' ) ) )#
Result:
'as | | an | | arts'
prompt

prompt ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
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.
Example:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > #prompt('Starting CountryCode',
'integer',
'10'
)#
Result:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 10
promptmany

promptmany ( prompt_name , datatype , defaultText , text , queryItem , trailing_text )
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 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.
Example:
select . . . where COUNTRY_MULTILINGUAL.COUNTRY IN ( #promptmany ( 'CountryName' ) # )

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

sb ( string_exp )
Surround the passed string with square brackets.
Example: #sb ( 'abc' )#
Result: [abc]
sort

sort ( array_exp )
Sorts the elements of the array in alphabetical order. Duplicates are retained.
Example:
#csv ( sort ( array ( 's3', 'a', 'x' ) ) )#
Result:
'a', 's3', 'x'
split

split ( pattern_string, string_exp | array_exp )
Splits a string or the string elements of the array into separate elements.
Example:
#csv ( split ( '::', 'ab=c::de=f::gh=i' ) )#
Result:
'ab=c' , 'de=f', 'gh=i'
Example:
#csv ( split ( '=' , split ( '::', 'ab=c::de=f::gh=i' ) ) )#
Result:
'ab' , 'c' , 'de' , 'f', 'gh' , 'i'
sq

sq ( string_exp )
Surround the passed string with single quotes.
Example: #sq ( 'zero' )#
Result: 'zero'
substitute

substitute ( pattern_string, replacement_string, string_exp | array_exp )
Search for a pattern in a string or in the string elements of an array and substitute the found text with other text.
Example:
#sq ( substitute ( '^cn=', '***', 'cn=help' ) )#
Result:
'***help'
Example:
#csv ( substitute ( '^cn=', '***', array ( 'cn=help' , 'acn=5' ) ) )#
Result:
'***help' , 'acn=5'
Example:
#csv ( substitute ( 'cn=', '', array ( 'cn=help' , 'acn=5' ) ) )#
Result:
'help' , 'a5'

Example :

Transform :
[Ph_Quality].[Manufacturing Business Unit].[Manufacturing Business Units].[Business Units]->:[YK].[[Manufacturing Business Unit]].[Manufacturing Business Units]].[Business Units]].&[FOOKEY]]]
to
[Ph_Quality].[Inventory Business Unit].[Business Units].[Business Units]->:[YK].[[Inventory Business Unit]].[Business Units]].[Business Units]].&[FOOKEY]]]

the macro will be like that : (regex need the brackets to be escaped with \)
#
substitute('\[\[Inventory Business Unit\]\]\.\[Business Units\]\]','[[Manufacturing Business Unit]].[Manufacturing Business Units]]',
substitute('\[Inventory Business Unit\]\.\[Business Units\]\.\[Business Units\]','[Manufacturing Business Unit].[Manufacturing Business Units].[Business Units]'
,prompt('BU Inventory','MUN'))
)
#
unique

unique ( array_exp )
Removes duplicate entries from the array. The order of the elements is retained.
Example:
#csv ( unique ( array ( 's3', 'a', 's3', 'x' ) ) )#
Result:
's3', 'a', 'x'
urlencode

field_one=urlencode(prompt('userValue'))
URL encodes the passed argument. Useful when specifying XML connection strings.
urlencode(prompt('some_val'))
%27testValue%27