Parameter Maps

This chapter introduces session parameters and parameter maps, both of which can be used to customize reports. Session parameters are values pulled from the user session. Parameter maps can be thought of as simple data look-up tables. Each parameter map has two columns, a key column and a value column holding the value that the key represents. A parameter map can be used to create a conditional query that allows for substitutions when a report is run. The following topics will be covered in this chapter:

• Creating and using session parameters
• Creating and using parameter maps

By the end of this chapter, you will understand how session parameters and parameter maps can be created and used within Framework Manager.

Session parameters

Every Framework Manager model has a set of built-in parameters that contain values related to the current session. These session parameters can be used by modelers in filters, calculations, and on reports.

The default session parameters include the following:

• account.defaultName: This returns the name of the current user as defined in the authentication provider, and examples of the values returned are the user's first and last name.
• account.personalInfo.userName: This returns the user ID used to log on to IBM Cognos BI if the user is logged in as Anonymous then this is not set.
• runLocale: This returns a code for the current active language in Framework Manager, and examples of the values returned by this parameter are en-gb, en-us, zh-cn, and zh-tw, which indicate English(United Kingdom), English(United States), Chinese(Simplified), and Chinese(Traditional) respectively.
• current_timestamp: This returns the current date and time.
• machine: This returns the name of the computer where Framework Manager is installed.

Some authentication sources support other parameters and if information about them has been entered, you may also see other session parameters. Common session parameters that you may see include account.personalInfo.email, account.personalInfo.firstname, andaccount.personalInfo.surname.

Additional parameters can be defined by using model session parameters. The model session parameters are always stored in a map named _env, and because they are set within the project they can be published with any packages.

Every session parameter created in the model must have a unique name and a default value. An override value can also be defined to allow the session value to be tested. If an override value is provided, it is not saved with the model and is only valid until the model is closed. Framework Manager will always use the default value when executing any query containing the session parameter if an override value is not set.

There are two important rules governing the use of session parameters:

1. All session parameters must return values of the same data type.
2. A key must only return a single value.

Creating a session parameter

Creating session parameters takes a few simple steps:

1. From the Project menu, click on Session Parameters.
2. Click on New Key and type a session parameter and its value:
3. 
4. You can set the session parameter value to avoid setting the override value each time you edit the project, or you can also set the session parameter override value to avoid removing the parameter setting before you publish your package.

5. TIP

6. You cannot alter the default value of session parameters; you can only set an override value for any of them.
7. Click on OK to save your changes.

MODIFYING SESSION PARAMETERS

You can modify the session parameters in a way similar to setting session parameters:

1. From the Project menu, click on Session Parameters.
2. To change a parameter value, click on the row that contains the value you want to change, and click on Edit to type a new value.
3. To assign a default value in the Default Value box, type a value. Framework Manager will use the default value if a key has an invalid value.
4. To remove a parameter, click on a row and then click on the Delete button.
5. To clear an override value, click on a row and then click on Clear Override.

Using session parameters

When you add a session parameter to your data source query subject you need to assign an alias to the parameterized object, and this ensures that the object returns the correct results when the parameter value changes.

The following SQL example defines a data source query containing the session parameter namedrunLocale. The runLocale parameter value is used to specify which column the query retrieves. The alias behaves like a shortcut so that when a model object references the alias, Framework Manager will retrieve the value to which the alias is assigned:

Select
 #$ColumnMap{$runLocale}# as CountryNameAlias
From
 [GoSales].Country

If the runLocale parameter returns the en value, then this will translate the SQL statement into the following code:

Select
 Countryen as CountryNameAlias
From [Goales].Country

It is often desirable to include the name of the user who has run a report. To do this, you can create a calculated item containing the following:

#sq ( $account.defaultName ) #

This will return the value of the account.defaultName session parameter, surrounded by singlequote (') characters. With this calculated item available in your package your report authors can simply add it to their reports to display the login name of the user running the report.