. Information about query items for relational metadata
appears in a different topic .
A query item is the smallest piece of the model that can be placed in a report. It represents a single instance of something, such as the date that a product was introduced.
Presentation hierarchy levels, key figures, and attributes are imported as query items in Framework Manager.
Only one hierarchy from a dimension should be used in the same report.
For SAP BW metadata, you can modify only text-based properties, such as the name or screen tip.
Because reports can contain different query items from one or more objects in the model, query item properties control many aspects of the final report. When you create a model dimension or model query subject, the query items inherit the properties of the data source query items on which they are based.
The properties for query items or measures include the following.
Query item property | Description |
Name | The name of the query item or measure. |
Description | A description of the query item or measure. |
Last Changed | The date that the query item or measure was last changed. |
Screen Tip | A description that can appear in the published package for your users. |
Expression | Used to create embedded calculations that provide your users with calculated values that they regularly use. This property is for measures only. Note: The Expression property is not used by SAP BW. |
External Name | The name that appears in the data source. |
Is Hidden | Whether to hide or show the query item or measure in the published package. Even when Is Hidden is set to True and the query item or measure is invisible to your users, it is always present in the published package because the query item or measure may be needed by other objects in the model. You do not see the query item or measure in the Package Publish wizard. For example, a calculation may make use of a hidden query item. |
Usage | The intended use for the data represented by the query item. This property is for query items only. |
Format | How information appears in a report. |
Currency | Which currency is used. This property cannot be changed in the Property pane. Use the Format property to change the currency. |
Data Type | The data type that was set in the data source. Because this property is set in the data source, it is read-only in Framework Manager. |
Precision | The total number of digits. Because this property is set in the data source, it is read-only in Framework Manager. |
Scale | How many digits are represented in the scale. For example, you can show numbers in thousands so that 100,000 means 100,000,000. Because this property is set in the data source, it is read-only in Framework Manager. |
Size | The size of the query item or measure. Because this property is set in the data source, it is read-only in Framework Manager. |
Is Nullable | Whether the query item or measure can contain a null value. Because this property is set in the data source, it is read-only in Framework Manager. |
Display Type | How the query item is shown. The column value can appear in the Cognos 8 studios as a picture, as a link, or as a value. The default is value. This property is for query items only. |
MIME Type | The format that the column value uses. For example, if Display Type is set to picture, MIME Type could be jpeg. This property is for query items only. Note: The MIME Type property is not used by SAP BW. |
Prompt Info | Prompt behavior. |
Regular Aggregate | The type of aggregation that is associated with the query item, measure, or calculation in the published package. |
Aggregate Rules | For dimensionally modeled relational metadata, the rules for semi-additive aggregation. For SAP BW metadata, the Aggregate Rules property is read-only. |
Is Unsortable | Whether the values of this query item can be sorted. This property is for query items that contain large objects such as BLOBs. |
You may be interested in the following related topics:
the Usage and Regular
Aggregate properties
changing the currency symbol
prompts
modifying the properties for multiple query items at the same
time
Information about aggregation of relational query items
appears in a different topic .
You can change how some query items and measures are aggregated in reports. Framework Manager applies aggregate rules when your users create a report that summarizes a query item or measure.
When you import metadata, Framework Manager assigns values to
the Usage and Regular Aggregate properties
for query items and measures depending on the type of object that
the query item or measure is in. The Usage property
identifies the intended use for the data represented by the query
item .
The Regular Aggregate property identifies the
type of aggregation that is applied to the query item or measure .
Your users can override the values of the Regular Aggregate property.
For semi-additive measures, you can specify additional aggregate
rules by modifying the Aggregate Rules property .
When modifying the Regular Aggregate property, you can select values that are not available through importing, such as average and maximum. You must understand what the data represents to know which aggregate rule is required. For example, if you aggregate a part number, the only aggregate values that apply are count, count distinct, maximum, and minimum.
Framework Manager uses the following rules to set the Usage and Regular Aggregate properties.
| Object | Usage property | Regular Aggregate property |
| Query item in a regular dimension | Attribute | Unsupported |
| Query item in a measure dimension | Identifier | Count |
| Measure in a measure dimension | Fact | Automatic if the measure is a calculation Sum if the measure is not a calculation |
If the measure is semi-additive, use the Aggregate
Rules property to define rules for semi-additive aggregation .
For SAP BW metadata, you cannot change these properties for dimensions.
The Regular Aggregate property for a calculation in SAP BW metadata is set to Automatic. To determine what automatic means, these rules apply.
Calculation | Aggregation type |
key items | unsupported |
all other items | calculated |
For model query subjects, Framework Manager uses the settings for the object that the model query subject is based on.
Note: If you change an aggregation value for SAP BW metadata, the aggregation cannot perform time-based queries because the aggregation rules are not applied.
The Usage property identifies the intended use for the data represented by each query item. During importing, the Usage property is set according to the type of data that the query items represent in the data source.
You need to verify that this property is set correctly. For example, if you import a numeric column that participates in a relationship, the Usage property is set to identifier. You can change the property.
For SAP BW query items, the value of the Usage property depends on the type of dimensional item the query item is based on.
Usage property value | SAP BW object | Description |
Identifier | hierarchy level | Uniquely identifies characteristic values at a particular level in a hierarchy. |
Fact | key figure | Represents a key figure that typically is numeric data. Date and time data are also supported. |
Attribute | display attribute | Represents a display attribute that is associated with a characteristic. |
The Regular Aggregate property identifies the type of aggregation for the query item or calculation when you publish it. Your users can either use this default setting to perform calculations on groups of data, or apply a different type of aggregation.
For example, if the Regular Aggregate property value of the Quantity query item is sum, and it is grouped by Product Name in a report, the Quantity column in the report shows the total quantity of each product.
The following aggregation types are supported for SAP BW data sources:
automatic
average
average non-zero is supported only when it is set in the data source. You cannot change the property to average non-zero in Framework Manager.
calculated
count
count distinct
count non-zero is supported only when it is set in the data source. You cannot change the property to count non-zero in Framework Manager.
maximum
median
minimum
standard deviation
sum
variance
If the calculation is in an SAP BW object, these rules apply.
Calculation | Aggregation type |
key items | unsupported |
all other items | calculated |
For dimensionally modeled relational metadata, the method by which a semi-additive measure is aggregated for the dimensions that you select.
For SAP BW metadata, the Aggregation Rules property is read-only. The Semi-Aggregate property is used instead.
For SAP BW metadata, the Semi-Aggregate property shows the value that is set in the data source, and the property is read-only.
If the value is set to unsupported in Framework Manager, the semi-aggregate behavior is ignored in the Cognos 8 studios.
The Semi-Aggregate property will not be supported in future releases. Instead, use the Aggregate Rules property for semi-additive measures.
Information about formatting relational items appears
in a different topic .
You can specify how query item values appear in reports. Use the Format property to choose a format type, such as text, date, and currency. Each format type contains properties that further specify how the data appears.
For example, you can assign the Currency format type to a numeric query item, and then use the No. of Decimal Places property in the Data Format dialog box to specify how many decimal places appear in reports.
Some characters are language-sensitive and appear properly only when your locale supports the applicable font. For example, for Japanese currency symbols to appear correctly, your locale must be set to Japanese.
If Framework Manager does not show the currency you require, you must ensure that you install the appropriate language packs to support the currency symbols. For example, to have the Indian currency symbol (rupee) appear, you must run an operating system or install a language pack that can show this symbol. The Japanese operating system or Japanese language is one that can show the Indian currency symbol.
You can define properties for several query items at the same time. However, if the query items have different format types, all properties that were previously specified are overridden and the default values from the data source are used. If the original format types of the selected query items are the same, all the properties for the selected query items are set identically.
For example, to use the same decimal separator for two query items and to keep the number of decimals different, each query item must be changed individually. If both are selected and changed at the same time, all properties including the number of decimals are set identically for both query items.
In the Project Viewer pane, click the query item you want to format.
In the Properties tab of the Properties pane, click the Format property.
Set the format type to currency to ensure that currency formatting is applied to all types of reports.
In the Currency scope box, specify the type of currency. If you do not see the currency you want to use, click the Add button.
In the Properties box, select or type the appropriate property value.
Click OK.
Information about prompt controls for relational metadata
appears in a different topic.
Prompts help your users quickly find the information they need in a report. Prompts are generally defined in reports. However, you can change the definition of the dimension or query subject in the model so that a prompt appears automatically when your users create filters. This is useful for query items, such as ProductTypeCode, whose values are not shown in a report but are useful for filtering data.
In general, it is better to define type-in prompts in the reports to make use of the additional prompt features. However, your users cannot modify some variables. For these variables, you can use Framework Manager to define type-in prompts.
The syntax for using a prompt as a value is
?<PromptName>?
You can use prompts in
parameter maps
session parameters
expressions, including filters and calculations
Click the query item.
In the Properties pane, click the Properties tab.
Click the plus sign (+) next to the Prompt Info property.
This is a compound query item property.
Modify the following properties to reflect the behavior you require.
| Goal | Property |
| Set the type of prompt control that is generated when the report is run. | Prompt Type |
| Set the generated prompt as part of a series of generated cascading prompts. | Cascade On Item Reference |
| Specify that a prompt defined for one query
item will use a different query item, and its rules, in the prompt. The query item specified in the Display Item Reference property will display a list of values in the prompt. Each value in the list will be associated with a value in the query item specified in the Use Item Reference property. | Display Item Reference |
| Specify that a prompt defined for one query
item will use a different query item, and its rules, in the prompt. The query item specified in the Use Item Reference property will be used in the prompt. | Use Item Reference |
| Set an alternative query item that is used by any Report Studio or Query Studio filter. | Filter Item Reference |
The Prompt Type property sets the type of prompt control that is generated when the report is run, such as an edit box or a pull-down list. Use this property to have the prompt show one query item but use a different one. For example, the prompt can show Employee Name but use Employee Number.
The default value for this property is Server Determined.
Value | Prompt Control |
Server Determined | The type of prompt control is based on information in the server, such as the data type of the query item. |
Edit Box | A simple text box. If the data type of the column is date or dateTime, this value generates a date or date-time control as well as the text box. |
Select Date | A date control with a calendar interface. |
Select Date/Time | A date-time control with a calendar interface. For SAP BW metadata, this value is not relevant. |
Select Interval | A date-time interval control. For SAP BW metadata, this value is not relevant. |
Select Time | A time control that filters data based on the selected time period. For example, if you define a Select Time prompt for Order Time, the user can use the time control to show all orders placed after 1:00, or all the orders placed between 10:00 and 11:00. If you are referring to a time member, you must use the exact values only. If you are using a range, the end points of the range must correspond to values in the data source. |
Select Value | A drop-down list. |
Select with Search | A list control so that users can search for values. For SAP BW metadata, this value is not relevant. |
Select with Tree | A tree prompt control for prompts that are based on a hierarchy node. |
The Cascade On Item Reference property indicates that the generated prompt is part of a series of generated cascading prompts. The query item that you reference in this property is the parent item in the cascade. The system prompts the user for the cascade item before prompting them for the current query item.
For example, if you want to prompt for Product Line and then Product within the selected line, set the Cascade On Item Reference property of the Product query item to Product Line.
These properties specify that a prompt defined for one query item will use a different query item, and its rules, in the prompt.
The query item specified in the Display Item Reference property will display a list of values in the prompt. Each value in the list will be associated with a value in the query item specified in the Use Item Reference property.
The query item specified in the Use Item Reference property will be used in the prompt.
For example, you want the prompt to display Country Name while using Country Code to retrieve data. Set the Display Item Reference property to Country Name and set the Use Item Reference property to Country Code.
These properties are used only for data driven prompt controls whose Prompt Type property is set to either Select Value or Select with Search.
The Filter Item Reference property identifies an alternative query item that is used by any Report Studio or Query Studio filter. This property is used only when Report Studio or Query Studio generates a filter. This property helps create more efficient queries because it is faster to filter on an indexed numeric column than a non-indexed string column.
For example, a report author wants to create a filter for the Country query item. You set the Filter Item Reference property to use Country Code for any filter that uses the Country query item.
Another example is a report author wants to create a filter for the Country Code query item that appears in the Orders table. You want that filter to use the Country Code in the Country table because there are fewer rows to read in the Country table.
When you test a model object that references a prompt, Framework Manager asks you to enter the prompt value. Framework Manager uses this value for either the duration of the session, or until you clear the prompt value.
You can change the session value of prompt values through the Options dialog box. This dialog box is available when you modify a dimension or query subject, or define a calculation, filter, query set, or complex relationship. You can change the prompt value at the time that you are testing the expression that references that value.
If you select the Always prompt for values when testing check box in the Prompt dialog box, Framework Manager prompts you for a value every time you test the object. When updating the object or performing a count, Framework Manager uses the existing prompt value, if one exists.
A prompt on a query item in a model query subject is associated only with that query item. A prompt on a query item in a data source query subject is associated with the entire query subject and therefore, the prompt appears when you test any query item in the query subject.
SAP BW variables are parameters of an SAP BW Query that are set up during query definition. When you run the query, the SAP BW variables are filled with values. They function as placeholders and can be processed in different ways. They are automatically exposed as prompts at run time.
SAP BW variable information is included in a composite custom property named SAP BW Variables that exists only if a data source has one or more variables associated with it. The SAP BW Variables property contains one or more composite properties, each of which must be assigned a unique name. Each property represents a description of a single SAP BW variable. Because the variable information is specified in a custom property, Framework Manager does not validate these properties.
Framework Manager supports these types of SAP BW variables:
characteristic
There are two kinds of characteristic variables: characteristic value and hierarchy node. Characteristic values variables select characteristic values. Hierarchy node variables select values from any position in a presentation hierarchy.
hierarchy
The user is not prompted for a value because Cognos 8 automatically populates it at run time based on the selected hierarchy.
formula
The user types a numeric value at run time. Use formula variables if a formula component should be entered only when a query is run. For example, you can use a formula variable for a value-added tax rate to process the current rate at run time.
authorization
Authorization variables are like other variables, but Cognos 8 automatically populates the variable values with the user’s credentials. SAP BW uses these credentials to supply the information needed by an SAP BW Query that has security applied to it.
Variables for hierarchies function as placeholders for the hierarchy of a characteristic. All the values for hierarchy variables are read-only.
The SAP BW variable information is obtained using the SAP BW BAPI MDDataProviderBW::GetVariables.
This property is a string value.
SAP BW equivalent: VARIABLE_NAME
Restrictions: Read-only.
The string value for this property is a composite and locale-dependent. Each locale in the model should be represented by a custom property whose value is the locale name. For example, if the locales en-ca and fr-fr exist in the model, define two custom properties named en-ca and fr-fr.
The default value is obtained from SAP BW.
The value for each of these properties is a composite, locale-dependent string value. Each locale in the model should be represented by a custom property whose value is the locale name. For example, if the locales en-ca and fr-fr exist in the model, define two custom properties named en-ca and fr-fr.
The default value is obtained from SAP BW.
Restrictions: The Default High Caption property is applicable only for variables with a Selection Type of interval.
This property is a boolean property that determines whether the value of the variable can be changed. If this property is set to true, your users are not prompted for the associated variable, and the default value is always applied.
The default value is false.
Restrictions: This property is not applicable to hierarchy variables.
The possible values are value, interval, complex, multiValued.
Value | SAP BW Equivalent |
value | SAP_VAR_SEL_TYPE_VALUE |
interval | SAP_VAR_SEL_TYPE_INTERVAL |
complex | SAP_VAR_SEL_TYPE_COMPLEX |
multiValued | SAP_VAR_SEL_TYPE_COMPLEX |
Restrictions: Read-only.
The default value is obtained from SAP BW.
Value | SAP BW Equivalent |
optional | SAP_VAR_INPUT_TYPE_OPTIONAL |
mandatory | SAP_VAR_INPUT_TYPE_MANDATORY |
mandatoryNotInitial | SAP_VAR_INPUT_TYPE_MANDATORY_NOT_INITIAL |
Restrictions: Read-only.
The default value is obtained from SAP BW.
Value | SAP BW Equivalent |
xsdString | CHAR |
xsdDate | CHAR The VAR_TYPE value is SAP_VAR_TYPE_MEMBER and the reference dimension is based on 0CALDAY. |
xsdFloat | FLTP |
xsdDatetime | DATS |
xsdInt | NUMC, DEC, INT1, INT2, or INT4 |
xsdLong | NUMC, DEC, INT1, INT2, or INT4 |
xsdMUN | CHAR |
Restrictions: Read-only.
Each of these properties specifies a range of values.
The default value is obtained from SAP BW.
Restrictions: The Default High Value property is applicable only for variables with a Selection Type of interval.
This property is a string value.
The possible values are numeric, characteristic, hierarchy, or hierarchicalNode.
The default is obtained from SAP BW.
Restrictions: Read-only.
The default value depends on the type of the variable. If the value of this property is not one of the predefined values, it is assumed to be hierarchyPickList.
Value | Restrictions |
typeIn | Required for numeric variables and optional for characteristic values |
pickList | Optional for characteristic variables |
calendar | Only for characteristic variables based on 0CALDAY |
hierarchyPickList | Optional for all presentation hierarchies |
notApplicable | Required for hierarchy variables |
Restrictions: Read-only for some types of variables such as characteristic and formula.
This property is a numeric value.
The default value is 1.
Restrictions: Applicable only for hierarchical node variables with a Prompt Type of hierarchyPickList.
This property is a string value that reduces the number of members in a hierarchical picklist. If the property is set to zero (0), members from all levels of a hierarchy are included in the prompt. You can also specify a range such as 2:4 to include only the members from certain levels. If the starting and ending ranges are the same, such as 3:3, only members from that level will be included.
The default value is zero (0).
Restrictions: Applicable only for characteristic variables with a Prompt Type of hierarchyPickList.
This property is a boolean property that determines whether the default values will be used. If this property is set to true, your users are not prompted for the associated variable, and the default value is always applied.
The default value is false.
A numeric value that specifies the initial number of values used to populate a picklist, hierarchical picklist, or prompt.
The default value is zero (0), which means all.
To show keys and captions for SAP BW variables, set this property to true. This property is applicable only for pick list prompts and hierarchy node prompts.
The default value is false.
The following variable properties are applicable to numeric variables:
Property | Default value |
Type | numeric |
Caption | |
Selection Type | value |
Entry Type | |
Data Type | |
Default Low | |
Default High | |
Prompt Type | typeIn |
Fixed Value | false |
You can change the default values for a numeric variable except for the Prompt Type property, which is read-only.
The following variable properties are applicable to hierarchy variables:
Property | Default value |
Type | hierarchy |
Caption | |
Selection Type | value |
Entry Type | obtained from SAP BW |
Data Type | xsdString |
Default Low | |
Default High | |
Prompt Type | not applicable |
Because hierarchy variables are resolved entirely at run time, all these properties are read-only.
There are two kinds of characteristic variables: characteristic value and hierarchy node. Characteristic values variables select characteristic values. Hierarchy node variables select values from any position in a presentation hierarchy.
The following variable properties are applicable to characteristic value variables:
Property | Default value |
Type | characteristic |
Caption | |
Selection Type | obtained from SAP BW |
Entry Type | obtained from SAP BW |
Data Type | xsdString |
Default Low | If the entry type is value or complex, the default property is shown. If the entry type is interval, the default low property is shown. This value is obtained from SAP BW. |
Default High | If the entry type is value or complex, the default property is shown. If the entry type is interval, the default high property is shown. This value is obtained from SAP BW. |
Prompt Type | typeIn or pickList This depends on the number of members in the referenced dimension. If the value is invalid, typeIn is used. |
Fixed Value | false |
A characteristic value variable for the 0CALDAY dimension is shown in the model as a date. The Data Type property is set to xsdDate and the Prompt Type property is set to calendar. The Prompt Type property is read-only for the 0CALDAY dimension.
The following variable properties are applicable to hierarchy node variables:
Property | Default value |
Type | characteristic |
Caption | |
Selection Type | value |
Entry Type | obtained from SAP BW |
Data Type | xsdString |
Dimension | obtained from SAP BW |
Hierarchy | obtained from SAP BW |
Default Low | |
Default High | |
Prompt Type | hierarchy PickList You can change the Prompt Type property to typeIn or pickList. |
Trim Levels | zero (0) |
Fixed Value | false |
Maximum Number of Values | 100 |
Each picklist prompt contains a pre-defined number of values. These values are determined by the Maximum Number of Values property.
If the number of actual values is less than or equal to the default number of values, the prompt is generated as a single picklist prompt. If the number of actual values exceeds the default number, two prompts are generated in this order:
a bound range parameter with a starting value of 1 and an ending value determined by the Maximum Number of Values property
This parameter is of the type xsdUnsignedLong and is optional. The name of the parameter is the name of the original prompt followed by _range_prompt. The caption is locale-specific. If this is a multilingual model, you must store the template for the caption in a message file.
a picklist prompt containing the default number of values
