Macros
This chapter provides guidance on the usage of macro expressions in the metadata modeling and authoring interfaces of IBM Cognos Business Intelligence (BI).
Macros allow the Cognos BI applications you develop to be dynamically customized to the needs of the user immediately before the associated queries are submitted to the database. With the help of macros, you can author a single report that addresses the requirements of many different business scenarios, instead of authoring separate reports for each scenario. The chapter also describes ways that you can employ macros, combining macros with session parameters and parameter maps, and provides a variety of advanced examples.
The chapter contains the following sections:
4.1 Macros explained
Macros are fragments of code that you can insert in the expression editor interfaces of Cognos BI, including within the Select statement that defines a query subject. Macros can include references to session parameters, parameter maps, and parameter map entries. For example, you can use the language session parameter to show only the data that matches the language setting for the current user.
Several facts about macros are summarized in the following list:
•Macros can give significant performance improvements in some reports.
•Macros show up in expressions as the text between two number sign (#) characters.
•An expression can contain more than one macro.
•Macros are expressions that are evaluated during query planning in such a way that the macro has been fully expanded before query execution.
•Using macros in appropriate places allows the application to be more flexible.
•There are several non-expression areas in Framework Manager where macros can be used.
•You can use macros in the data source connection command blocks defined in Cognos Administration.
The expression editors that are part of Report Studio and Framework Manager have a collection of functions that are categorized as macros. Figure 4-1 shows the macro tab within the expression editor of Report Studio.
Figure 4-1 Macro tab within the expression editor of Report Studio
In working with macros, you might find that writing them is sometimes easier than reading them. Describing them accurately with comments will significantly help the next user to understand the intent of the macro.
You can use macros in a variety of ways:
•They can be inserted in SQL, as the following example shows:
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. This filter is an example:
[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.
This is an example using the Content Manager Datasource property:
#$DataSourceMap{$UserId}#
•They can be used as a parameter wizard. When used in this context, parameters can reference other parameters, as in the following example:
Map1, Key = en-us, Value = #$myMap{$UserId}#
4.2 Macro language
This section explains the syntax to follow when writing macro expressions and presents some options you can use inside your macro expressions.
4.2.1 Operator
The macro language has only one operator, the plus sign (+) character, which is used to concatenate two strings. So the following example resolves to the value abcxyz:
# ‘abc’ + ‘xyz’ #
4.2.2 List separator character
The macro language recognizes both the comma (,) and the semicolon (;) characters as list separators. This is independent of any locale setting, as in the following example:
# array (‘a’ , ‘b’ ; ‘c’) #
4.2.3 Functions
All function names are case-insensitive. Only alpha characters are used in the names of functions. Some function names are short. Macro functions are used more by programmer-type report authors than casual report authors.
The expression editors in Report Studio and Framework Manager have a collection of functions that are categorized as macros and are displayed for drag-and-drop use. All of these macro functions have screen tips with examples.
4.2.4 Comments
Use comments to explain macros for other individuals who will use them. Adding comments is useful because they help to more easily maintain and support models. There are two rules for comments:
•Any text between the /* and */ strings, including new lines, is considered a comment.
•Any text between the // string and the end of a line is considered a comment.
The macro expression in Example 4-1 resolves to the value 2012 and demonstrates how comments help other users understand the intent of an expression.
Example 4-1 Commenting a macro expression
# // a macro is used to get the previous year
timestampMask( // 3: extract the year portion
_add_years( // 2: subtract one year
$current_timestamp,// 1: 2013-01-29 22:39:14.135-05:00
-1),
'yyyy')
#
4.2.5 Simple case construct
The case construct is used in programming to identify different sets of instructions corresponding to various conditions. The case macro construct allows you to specify values or functions to be returned under different conditions or cases.
Using the simple case construct in a macro in combination with the prompt function is sometimes challenging. Example 4-2, Example 4-3 on page 47, and Example 4-4 on page 47 show three examples of employing the simple case construct.
Example 4-2 Macro prompt with token data type
# // example 1
case prompt('option', 'token')
when 3 then '[gosl].[PRODUCT_LINE].[PRODUCT_LINE_CODE] > 3'
else '[gosl].[PRODUCT_LINE].[PRODUCT_LINE_CODE] is not null'
end #
Example 4-3 Macro prompt with unspecified data type
# // example 2
case substitute("'","",substitute("'","",prompt('option')))
when 3 then '[gosl].[PRODUCT_LINE].[PRODUCT_LINE_CODE] > 3'
else '[gosl].[PRODUCT_LINE].[PRODUCT_LINE_CODE] is not null'
end #
Example 4-4 Macro expecting a specific input value
# // example 3
case prompt('option')
when "'3'" then '[gosl].[PRODUCT_LINE].[PRODUCT_LINE_CODE] > 3'
else '[gosl].[PRODUCT_LINE].[PRODUCT_LINE_CODE] is not null'
end #
The prompt macro function returns a string by default. The entered value is surrounded by single quotation marks, which makes it useful in most expressions. So if the user enters the value abc, the default result will be 'abc' in this context.
In Example 4-2 on page 46, the data type of the prompt function is specified as token. The response will not be surrounded by single quotation marks. The literal 3 is used in the when clause and will match the user-entered value 3. There are circumstances when the token should not be used to prevent SQL injection, but that is not the case here.
In Example 4-3, the data type of the prompt function is not specified and thus defaults to string. The code that deals with the quoted return value of the prompt function removes the single quotes at the beginning and end of the response.
In Example 4-4, the when clause specifies the value 3. The easiest way to do this in the macro language is to surround the value with double quotation marks, as in the following example. This value will match the user-entered value 3:
"'3'"
4.3 Parameter maps
Parameter maps are objects that store key-value pairs. Parameter maps are similar to data source look-up tables. Each parameter map has two columns, one for the key and one for the value that the key represents.
Parameter maps can be defined in Framework Manager in various ways:
•Manually enter them as name-value pairs
•Load the name-value pairs from a file
•Base them on query items in the current model
To modify the parameter map, you can export the map values to a file, perform any additions or modifications, and then import the map values back into Framework Manager. This is especially useful for manipulating large, complex parameter maps.
Normally, parameter map keys must be unique so that the query service can consistently retrieve the correct value. Do not place quotation marks around a parameter value. You can use quotation marks in the expression in which you use the parameter.
You can create a parameter map that is based on query items with different values associated with the same key. For example, consider the parameter map definition for a parameter map named PLC2PTName, which is shown in Figure 4-2. This parameter map can be referenced in the following macro:
#sq(join('---', @PLC2PTName{'1'}))#
It returns the following result:
'Cooking Gear---Lanterns---Packs---Sleeping Bags---Tents'
Note that the at (@) symbol in @PLC2PTName indicates that an array of values is to be returned.
Figure 4-2 Parameter map with multi-valued keys
The value of a parameter can be another parameter. However, you must enclose the entire value between number sign (#) characters. The limit when nesting parameters as values is five levels.
When you use a parameter map as an argument to a function, you must use a percent sign (%) character instead of a dollar sign ($) character.
Do not base a parameter map on a query item or table with a large result set (50,000 rows or more). Each time you use the parameter map in an expression or in SQL, the query service executes the large query and performance is slowed. Parameter maps should be used for smaller lookup tables only.
4.4 Session parameters
A session parameter is a variable that Cognos BI associates with a session. For example, user ID and preferred language are both session parameters. Because session parameters are key value pairs, you can think of each session parameter as an entry in a parameter map. You use a session parameter in the same way that you use a parameter map entry, although the syntax for session parameters is slightly different.
There are two types of session parameters: environment and model. Environment session parameters are predefined and stored in the Cognos BI content store database. By default, the following session parameters are displayed in Framework Manager:
•runLocale: Returns the code for the current active language in Framework Manager. The model content is shown in this language. You can change the active language at any time for your current session only. In future sessions, the model continues to open in the design language.
•account.defaultName: Specifies the name of the current user as defined in the authentication provider (for example, user’s first and last name). If you log on anonymously, you will see Anonymous.
•account.personalInfo.userName: Specifies the user ID used to log on to Cognos BI. If you log on anonymously, you will not see this parameter.
•current_timestamp: Specifies the current date and time.
•machine: Specifies the name of the computer where Framework Manager is installed.
If your authentication source supports other parameters and you entered information about them in the authentication source, you can use other session parameters, such as account.personalInfo.email or account.personalInfo.surname.
Figure 4-3 depicts some of the session parameters shown in the Insertable objects pane of the expression editor in Framework Manager.
Figure 4-3 Session parameters shown in Framework Manager
Additional session parameters are available in Report Studio. Similar to the parameters in Framework Manager, the session parameters in Report Studio give access to information about the report, such as report start time, report name and report path.
Table 4-1 shows session parameters that are available to report authors but do not appear in the Report Studio user interface.
Table 4-1 Additional session parameters not shown in Report Studio interface
|
Name
|
Sample Value
|
|
contextID
|
/content/package[@name='gosales']/report[@name='a_macro_session_parms'];reportRender_Request;
|
|
reportPath
|
/content/package[@name='gosales']/report[@name='a_macro_session_parms']
|
|
REMOTE_ADDR
|
127.0.0.1
|
|
HTTP_HOST
|
localhost:81
|
|
queryName
|
Query1
|
|
report
|
a_macro_session_parms
|
|
startTime
|
2013-01-31T18:21:29.455Z
|
|
modelPath
|
/content/package[@name='gosales']/model[@name='model']
|
You can define additional parameters by using model session parameters. Model session parameters are stored in a parameter map named _env. They are set in the project and can be published with a package. Model session parameters must have their values set within the scope of objects in the Framework Manager model. The scope can include the use of existing environment session parameters, and also static values.
You can map user attributes from your LDAP authentication provider into new session parameters. To configure this, you must add these attributes as custom properties for the LDAP namespace in Cognos Configuration. For the procedure, see the product documentation at the following location:
Each session parameter must have a name and a default value. You can define an override value to test the results that the value returns. The override value is valid only when you have the model open, and it is not saved when you save the model. If no override value exists, the query service uses the default value when it executes a query that contains a session parameter.
The following rules, in addition to others, govern the use of parameters:
•All possible return values must have the same data type.
•Only one value can be defined.
4.5 Advanced examples
This section describes some advanced ways to use macros effectively, all of which have been used by Cognos BI customers in the past. The intent of these examples is to give you inspiration to solve other problems.
4.5.1 Member unique name for next year
This is an example of creating a member unique name (MUN) based on the current year. Generating a MUN is particularly useful for drill-through or master-detail relationship-based applications when relating pure relational queries to OLAP queries.
OLAP sources organize data into dimensions. These dimensions contain hierarchies. The hierarchies contain levels and the levels contain members. An example of a dimension is Locations. A Locations dimension may contain two hierarchies: Locations by Organization Structure and Locations by Geography. Either of these hierarchies may contain levels such as Country and City.
Members are the instances in a level. For example, New York and London are members in the City level. A member may have multiple properties, such as Population, Latitude, and Longitude. Internally, a member is identified by a Member Unique Name (MUN). The method by which a MUN is derived depends on the cube vendor. When authoring reports, referencing a member through its MUN will typically perform faster than referencing a member through dimensional (MDX) functions.
The generated MUN for this example needs to be in the format shown in Example 4-5, which is based on the Great Outdoors Warehouse sample database that is included with all Cognos BI products.
Example 4-5 Example member unique name (MUN)
[Great Outdoors].[Years].[Years].[Year]->:[PC].[@MEMBER].[20040101-20041231]
The [20040101-20041231] section of the MUN shown in Example 4-5 identifies the member that represents data for all the days in 2004. This MUN can be generated using the date at run time with the macro expression shown in Example 4-6.
Example 4-6 Macro expression generating MUN dynamically based on current time
#
'[Great Outdoors].[Years].[Years].[Year]->:[PC].[@MEMBER].['
+
timestampMask(_add_years($current_timestamp,-8),'yyyy')
+
'0101-'
+
timestampMask(_add_years($current_timestamp,-8),'yyyy')
+
'1231]'
#
4.5.2 Turning promptmany result into a rowset
The result of the macro function promptmany is a single value, not an array of values. This limitation is a side effect of needing to remain compatible with macros that were written before the array data structure was introduced.
Therefore, consider the following expression:
# join ( '**' , promptmany('pp', 'string') )#
When the values for parameter pp are aa and bb, then the result of this expression is as follows:
'aa';'bb'
Notice that the values are surrounded by quotation marks and separated with a semicolon. The semicolon is the typical way that the promptmany function generates the list separator. If the macro had been defined for an SQL statement, then the separator would have been a comma instead of a semicolon. To get the result 'aa'**'bb' you can use the macro expression shown in Example 4-7.
Example 4-7 Macro expression joining string values
#
join('**', // 3 -> string 'aa'**'bb'
split(';', // 2 -> array with 2 elems: 'aa' and 'bb'
promptmany('pp','string') // 1 -> 'aa';'bb'
)
)
#
Example 4-8 displays the expression for a data source query subject that takes the response to the promptmany macro function and transposes it into the rows of an inline values clause.
Example 4-8 Macro expression transposing inputted values into rows of a result set
with
inputSet as
(select * from ( values
#
'( ' +
join ( ' ),( ',
split (',', promptmany ('setofdata') ) // split on , not on ;
)
+ ' )'
#
) T ( C1 ) )
select
inputSet.C1 as C1
from
inputSet
Assuming that the values for the setofdata parameter from Example 4-8 on page 52 are the strings FL, NY, BLAH and JAH, the macro will expand to what is shown in Example 4-9.
Example 4-9 Expanded result of the macro expression in Example 4-8 on page 52
with
inputSet as
(select * from ( values
( 'FL' ),( 'NY' ),( 'BLAH' ),( 'JAH' )
) T ( C1 ) )
select
inputSet.C1 as C1
from
inputSet
You can apply this technique in various circumstances, such as in part of a filter or by combining it using an EXCEPT operation with another query that is compatible with the union operation. Bear in mind, however, that this technique is using a row constructor in a select statement, which IBM DB2 supports but many other database vendors may not. If your vendor does not support such processing, then the Cognos BI server performs it, although this action is at a cost to performance.
The split/join macro functions do not allow you to define different leading and trailing character strings (such as ‘timestamp(‘and ‘)’) so ensure that you embed the appropriate repeating text in the join, as shown in Example 4-10.
Example 4-10 Macro expression with join and split functions
column in (
#
'timestamp(''' +
join ( '''),timestamp(''',
split (',', 'abc,def,ghi,jkl' )
)
+ ''')'
# )
The code in Example 4-10 expands to what is shown in Example 4-11 before any query is sent to the database.
column in ( timestamp('abc'),timestamp('def'),timestamp('ghi'),
timestamp('jkl') )
4.5.3 Dynamic column drill
The scenario in this example requires a list report with hierarchy and measure columns. The hierarchy column is a dynamic hierarchy using a data item expression. It shows measure values by different hierarchies based on the user’s selection of a View by prompt. Changing these measure values can be accomplished either by using a parameter map lookup or a case statement macro expression.
Example 4-12 shows a parameter map-based solution that can be called upon by a calculation in the model or in the report whose expression is as follows:
#$pmap{prompt('View by' ,'string')}#
Example 4-12 Parameter map-based solution
parameterMap : pmap
default: [Provider].[Provider].[Provider].[Provider]
key: Provider entry: [Provider].[Provider].[Provider].[Provider]
key: Practitioner entry: [Practitioner].[Practitioner].[Specialty].[Specialty]
Cognos BI version 10.2 introduced support for a case statement macro function. An expression such as the one shown in Example 4-13 can be employed instead of the parameter map.
Example 4-13 Expression to use instead of a parameter map
# case prompt('View by', 'string')
when 'Provider'
then [Provider].[Provider].[Provider].[Provider]
when 'Practitioner'
then [Practitioner].[Practitioner].[Specialty].[Specialty]
else ([Provider].[Provider].[Provider].[Provider])
end
#
Alternatively, you can use the macro expression shown in Example 4-14.
Example 4-14 Alternate macro expression
# case prompt('View by', 'token')
when 'Provider'
then [Provider].[Provider].[Provider].[Provider]
then [Provider].[Provider].[Provider].[Provider]
when 'Practitioner'
then [Practitioner].[Practitioner].[Specialty].[Specialty]
else ([Provider].[Provider].[Provider].[Provider])
end
#
Note the use of the data type token in the second argument to the prompt function. This is used to match against the simple strings in the when clauses. The default data type is ‘string,’ which results in a value surrounded by single quotation marks that would not match the values 'Provider' or 'Practitioner' in the when clauses. The values in the when clauses must be be changed to ”'Provider'” and ”'Practitioner'” (notice the quotation marks).
The equivalent macro expression using the data type string is shown in Example 4-15.
Example 4-15 Macro based solution with string data type in prompt
# case prompt('View by')
when ”'Provider'”
then [Provider].[Provider].[Provider].[Provider]
then [Provider].[Provider].[Provider].[Provider]
when ”'Practitioner'”
then [Practitioner].[Practitioner].[Specialty].[Specialty]
else ([Provider].[Provider].[Provider].[Provider])
end
#
4.5.4 Filtering for internal and external customers
A common scenario is a report that must handle various types of parameters depending on the privileges of the user who is running the report. This example involves a session parameter that has the pattern 1234_FMUSER (for external users that have logged in) or SYSADMIN01 (for internal users that have logged in). For internal users, the application must prompt the user for the customer number. For external users, the application must restrict all data to just the leading digits that represent the customer number in the database, so the user is not prompted for a customer number.
These requirements can be satisfied by creating an embedded filter in a query subject with the expression shown in Example 4-16.
Example 4-16 Macro expression with case construct based on session parameter
# '[gosales_8_2].[CUSTOMER_HEADER_10_2].[CUSTOMER_NUMBER] = ' +
// isolate the customer number from 123_FMUSER or issue a prompt
// when there is no underscore in session parameter pc1
case join('', grep ('_', array($pc1)))
when '' then
// session parameter pc1 has no underscore
'?start_number? '
else
// isolate the number before the underscore e.g. 123 from 123_FMUSER
'cast (' +
join ('', grep('!|', split( '_', join('_|', split('_', $pc1 ) ) ) ) ) +
', varchar(10)) '
end
#
Other examples
Additional examples of using macros, session parameters, and parameter maps are in the product documentation at the following location:
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.