Considerations when Creating Calculations
Consider the following information and limitations when
creating dimensional calculations.
- Calculation Solve Order
When calculations in the rows and columns of a report intersect, calculations are performed in the following order: addition or subtraction, multiplication or division, aggregation (rollup), and then the remaining arithmetic functions. - Length of Expressions
When creating layout calculations that use complex expressions, limit the length of the expression to less than 1,000 tokens. A token is a keyword, identifier, constant, or special character symbol that the server identifies when it parses the SQL. For example, the expression 1+1+1+1+1+1+1+1+1+1 contains approximately 28 tokens, whereas the expression 1+1+1...+1 (400 times) contains more than 1000 tokens. - Quality of Service Indicators
Not all data sources support functions the same way. The data modeler can set a quality of service indicator on functions to give a visual clue about the behavior of the functions. Report authors can use the quality of service indicators to determine which functions to use in a report. - Using Quotation Marks in Literal Strings
When inserting literal strings in an expression, you must enclose the string in single quotation marks. If the string contains a quotation mark, it must be escaped. For example, if you want to insert the string ab'c, you must type 'ab''c'. - Limitations of calculations in dimensional reporting
You should use only the expressions and functions available in IBM® Cognos® Report Studio or IBM Cognos Workspace Advanced, and follow their syntax. - Creating Expressions Using SAP BW Data Sources
You must consider the following when creating expressions using an SAP BW data source, or you may not get the results you expect. - Using Microsoft Excel Functions with SSAS 2005 Data Sources
If you are working with a Microsoft SQL Server 2005 Analysis Services (SSAS) data source and you want to use Microsoft Excel VBA functions such as ROUNDDOWN in MDX queries, the following Microsoft Office features must be installed on the SSAS server: - Concatenating Strings
When IBM Cognos Business Intelligence concatenates strings locally and if any of the involved strings contain null values, the result of the concatenation is an empty cell or a null value. This occurs because IBM Cognos BI requires that an expression that involves a null value returns a null value. Many databases ignore null strings when they perform concatenations. For example, if you concatenate strings A, B, and C, and if string B is a null value, the database may concatenate only strings A and C. - Intersecting Calculations in Crosstabs and Charts
An intersection point in a crosstab or chart can contain a value that is derived from multiple calculations. - Null (Missing) Values in Calculations and Summaries
The data source that you use determines how null (or missing) values are handled in calculations, summaries, and other expressions. - Dimensional Coercion Rules
IBM Cognos Business Intelligence uses dimensional coercion rules to automatically convert dimensional types to better match other types. These conversions help you build simpler expressions, making them easier to understand. In addition to the implicit rules that IBM Cognos BI provides, you can make explicit data type conversions using various functions, such as children. - Recommendation - Use Member Unique Name (MUN) Aliases
If you are working with a dimensional data source, use MUN aliases to simplify building reports and expressions. In addition, you can tie the MUN back to the member in the package.
Parent topic: Limitations when using dimensional data sources