Creating business rules and storing them in the model instead of in reports has many advantages. You save time because you and your users do not have to re-create the business rules every time they are needed. The business rules ensure consistency because your users all use the same definitions. For example, Low Margin means the same thing throughout the organization. They are easy to update because you maintain the business rules centrally so that all reports are updated automatically as the rules evolve. For example, if the definition for Low Margin changes, all reports that use the Low Margin calculation are updated automatically. The business rules enhance security. For example, you can use filters to limit the data that your users can see.

You can create calculations to provide your users with calculated values that they regularly use. Calculations can use query items, parameters, variables, calculated members, expressions, and expression components, such as functions.

Punctuation characters, such as the question mark (?), must be in 7-bit ASCII character code. If you type a punctuation character from a multi-byte enabled keyboard, ensure that you type the 7-bit ASCII representation of the character. For example, type Alt+063 for the question mark.

Avoid using characters that are used for expression operators in the name of the calculation. Syntax errors may occur when the expression is evaluated. For example, a calculation named Margin * 10 causes errors when used in an expression such as [Margin * 10]< 20.

At query time, Framework Manager returns a null value for any calculation that contains a divisor whose value is zero. Framework Manager cannot detect zero-division errors in functions such as average and mod, because the division operator is not explicit.

Framework Manager supports stand-alone calculations and embedded calculations.

• Use a stand-alone calculation when you want to reuse the expression.

  You can apply a stand-alone calculation to one or more dimensions or query subjects to provide calculated data to a report, or include it in a package to make it available to your users. By moving a stand-alone calculation or a shortcut to it into a folder, you can better organize the model objects.

  You cannot use stand-alone calculations in Analysis Studio. Instead, use an embedded calculation.

• Use an embedded calculation when you want to use a calculation with only one dimension or query subject.

  You can create an embedded calculation when modifying a relational data source query subject, model query subject, or dimension.

  If you start with an embedded calculation, you can later convert it into a stand-alone expression that you can apply to other dimensions or query subjects. Tip: Right-click the calculation expression in the Calculations tab and click Convert to Stand-Alone Calculation.

  When you embed a calculation, the data source query subject must have a relationship to any query subject referenced by the expression. This relationship is necessary even if the expression references a model query subject based on the same table as the data source query subject in which you are embedding the expression.

  To create a calculation on an unrelated query subject, do one of the following:

  • Ensure that there is a join path between the new query subject and the one that contains the calculation.

  • Base the embedded calculation on a query item that is based on the data source query subject you want.

  • Convert the calculation to a stand-alone calculation, so that it is not part of the query subject.

  • Create a stand-alone calculation that references the embedded object.

A filter is an expression that specifies the conditions that rows or instances must meet to be retrieved for the dimension, query subject, calculation, or report to which the filter is applied. A filter returns a boolean value so that you can limit the rows returned by a dimension or query subject.

For example, you can use the in_range function to create a filter that retrieves data for products introduced in a specific time frame. The syntax for this example looks like this:

[gosales_goretailers].[Products].[Introduction
date]
in_range {Feb 14, 1999 : July 14, 2007}

Note: When using a date or time function, you must use a 24-hour clock. Framework Manager does not support "a.m." or "p.m." in expressions. For example, use 20:00 to signify 8 p.m.

You can restrict the data represented by dimensions or query subjects in a project by creating a security filter. The security filter controls the data that your users can see when they set up their reports.

You can also apply governors to restrict the data that the queries in a package retrieve.

Framework Manager supports stand-alone filters and embedded filters.

• Use a stand-alone filter when you want to reuse the expression.

  You can add a stand-alone filter to one or more dimensions or query subjects to limit the data that the query retrieves when the filtered dimension or query subject is used in a report, or you can include it in a package to make it available to your users. By moving a stand-alone filter or a shortcut to it into a folder, you can better organize the model objects.

• Use an embedded filter when you want to use a filter with only one dimension or query subject.

  You can create an embedded filter when modifying a dimension, relational data source query subject, or model query subject.

  If you start with an embedded filter, you can later convert it into a stand-alone expression that you can apply to other dimensions or query subjects. Tip: Right-click the filter expression in the Filters tab and click Convert to Stand-alone Filter.

  When you embed a filter, the data source query subject must have a relationship to any query subject referenced by the expression. This relationship is necessary even if the expression references a model query subject based on the same table as the data source query subject in which you are embedding the expression.

  To create a filter on an unrelated query subject, do one of the following:

  • Ensure that there is a join path between the new query subject and the one that contains the filter.

  • Base the embedded filter on a query item that is based on the data source query subject you want.

  • Convert the calculation to a stand-alone filter, so that it is not part of the query subject.

  • Create a stand-alone filter that references the embedded object.

To apply a filter, you must modify the dimension, data source query subject, or model query subject. The query subject must either contain the query items that the filter references, or have a relationship path to the query subjects that contain the query items.

You can embed a stand-alone filter in dimensions or query subjects, but if you want a different usage for each embedded filter, you must create different versions of the stand-alone filter. Otherwise, your users could be required to fill in a prompt that you thought was optional if there is any instance where the usage is set to mandatory.

For example, in query subject A, you embed a stand-alone filter and define it as optional. In query subject B, you define it as mandatory. When your users create a report that uses both query subjects, they are required to choose values in both filters, even the one defined as optional. All instances of the filter are considered to be mandatory when used in the same query. The solution is to create different versions of the filter, each with its own name.

Use parameters to create conditional query subjects that allow for substitutions when the report is run. 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. You can manually enter the keys and values, import them from a file, or base them on existing query items in the model.

All parameter map keys must be unique so that Framework Manager 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.

The value of a parameter can be another parameter. However, you must enclose the entire value in number signs (#). 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 percentage sign (%) instead of a dollar sign ($).

We recommend that you assign an alias to a query item that uses a parameter map as part of its name and to add the multilingual names to the object in the Language tab (Properties pane).

Constraint

We recommend that you do not base a parameter map on a query item or table with a large result set, such as 50,000 rows. Each time you use the parameter map in an expression or in SQL, Framework Manager executes this large query. Performance is then slowed. Parameter maps are recommended for smaller lookup tables.

A session parameter is a variable that Framework Manager associates with a session. For example, user ID and preferred language are both session parameters. Because session parameters are key and value pairs, you can think of each session parameter as an entry in a parameter map named Session Parameters. 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 Content Manager. By default, the following session parameters appear in Framework Manager:

• runLocale

• account.defaultName

• account.personalInfo.userName

If you log on anonymously, you will see only runLocale and account.defaultName(Anonymous).

If your authentication source supports other parameters and you entered information about them in the authentication source, you see other session parameters, such as account.personalInfo.email.

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, as well as static values.

Each session parameter must have a name and a default value. You can define an override value to test the results that value returns. The override value is valid only when you have the model open, and is not saved when you save the model. If no override value exists, Framework Manager uses the default value when it executes a query that contains a session parameter.

The rules governing the use of parameters include the following:

• All possible return values must have the same data type.

• Only one value can be defined.

Constraints

When you reference a parameter, you must do the following:

• use a number sign (#) at the beginning and end of each set of one or more parameters.

  Everything between the number signs is treated as a macro expression, which is processed at runtime. Framework Manager removes anything that is outside the number signs.

• precede each parameter map entry with a dollar sign ($)

• use a name that starts with an alpha character (a..z, A..Z)

Do not insert macros between existing quotation marks or square brackets because Framework Manager does not execute anything within these elements.

Mandatory Prompt with No Data Type Specified

Note the following:

• The Datatype argument is not specified. Therefore, it is a string, which is correct in this case.

• The DefaultText argument is not specified. Therefore, it is a mandatory prompt.

select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
where COUNTRY_MULTILINGUAL.COUNTRY = #prompt('CountryName')#

Mandatory Prompt with the Data Type Specified

Note the following:

• This prompt requires a valid integer value as response.

• The DefaultText argument is not specified. Therefore, it is a mandatory prompt.

select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 
	#prompt('Starting CountryCode',
			'integer',
			'',
			'',
	'[gosales].[COUNTRY_MULTILINGUAL].[COUNTRY_CODE]')#

Optional Prompt and Mandatory Filter with the Data Type and Default Value Specified

Note the following:

• This prompt allows the user to supply a valid integer response.

• The DefaultText argument is specified. Therefore, the user may omit entering a value, in which case the value 10 is used. This makes it an optional prompt, but not an optional filter.

select
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 
#prompt('Starting CountryCode',
			'integer',
			'10'
)#

Prompt That Appends Text to the Value

Note the following:

• The DefaultText argument is specified as a space character. In this case, the generated text is just the space character, which eliminates the Where clause from the query.

• The Text argument is specified, which is written into the generated SQL before the user-provided prompt value.

select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
#prompt('Starting CountryCode',
		'integer',
		' ',          // < = = this is a space
	'where COUNTRY_MULTILINGUAL.COUNTRY_CODE >'
)#

Syntax Substitution

Note the following:

• The Datatype argument is set to token, which means that the user-provided value is entered into the SQL without any checking on the provided value.

• Token should be used only if there is a list of pick-values for the user.

• The DefaultText argument is specified. Therefore, this is an optional prompt and group by COUNTRY is used in the generated SQL.

Select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL	
#prompt('Sort column',
			'token',
			'group by COUNTRY',
	'group by '
)#

Prompt That Uses a Session Variable

Note the following:

• The name of the prompt is specified using a lookup in the parameter map named PromptLabels. The key value is the session variable $language.

• The Where clause is using a parameterized column.

select 
	ORDER_METHOD.ORDER_METHOD_CODE as ORDER_METHOD_CODE,
	ORDER_METHOD.ORDER_METHOD_#$language#
	as ORDER_METHOD_EN 
from 
	gosales.gosales.dbo.ORDER_METHOD ORDER_METHOD
#prompt($PromptLabels{$language},
	'',
	' ',
	'where ORDER_METHOD.ORDER_METHOD_' + $language + '
>'
)#

A Parameter Map That Nests Prompts

Note the following:

• In the model, there is a parameter map DynPromptLabels with #$PromptLabels{$language}#

• Part of the prompt information is run from a parameter map instead of being coded directly inside the SQL.

• The whole macro containing the prompt can be a value in a parameter map.

select 
	ORDER_METHOD.ORDER_METHOD_CODE as ORDER_METHOD_CODE,
	ORDER_METHOD.ORDER_METHOD_#$language#
	as ORDER_METHOD_EN 
from 
	gosales.gosales.dbo.ORDER_METHOD ORDER_METHOD
#prompt($DynPromptLabels{'ex9'},
	'',
	' ',
	'where ORDER_METHOD.ORDER_METHOD_' + $language + '
>'
)#

Prompt with a Required Minimum

Note the following:

Prompt with a Required Minimum with the Data Type Specified

Note the following:

Optional Prompt with the Data Type and Default Value Specified

Note the following:

• The In clause and both parentheses are part of the SQL statement.

select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME
from
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
where COUNTRY_MULTILINGUAL.COUNTRY_CODE IN (
	#promptmany('Selected CountryCodes',
					'integer',
					'10'
	)#
)

Prompt That Adds Text Before the Syntax

Note the following:

• This example uses the TextFollowing argument.

select
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME
from
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
#promptmany('Selected CountryCodes',
				'integer',
				' ',     // < = = this
is a space
				'where COUNTRY_MULTILINGUAL.COUNTRY_CODE IN
( ',
				'',
				')'
)#

Optional Prompt That Adds Text Before the Syntax

Select 
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
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 (',
					'',
					')'
	)#