Resolve Query Calculations that Intersect with Calculated Measures Defined in Microsoft Analysis Services
Microsoft SQL
Server Analysis Services (SSAS) data sources may include data source
calculations. Because IBM® Cognos® Business Intelligence
cannot predict the presence of a solve order on such calculations,
setting the Solve Order property may not resolve
the solve order correctly. You can suppress potentially meaningless
results when querying dimensional data sources that include data source
calculations. In the following example, the columns YTD
Change and YTD Growth are data
source calculated members and the summary values for these two columns
were suppressed.
Figure 1. Crosstab showing revenue by product line with a summary row
When a query-defined calculated member and a data source calculated member intersect, the value is the result of performing both calculations. The second calculation is performed on the result of the first calculation. Whether the final value is meaningful depends on the order in which the calculations are performed. Because IBM Cognos BI cannot determine the presence of these members ahead of time, it cannot automatically determine the correct solve order.
In most cases, SSAS 2005 gives the expected results when no solve order is defined in the cube. Therefore, when using SSAS 2005, you do not need to set either the Solve Order property in the query or the Calculation Intersection property.
Note: Dimensionally-modeled relational (DMR) data sources query
one or more underlying relational databases, and therefore cannot
contain data source calculations. They are not considered dimensional
data sources in the context of this topic.
Procedure
- Pause the pointer over the query explorer button
and click the query that contains the data item for which to suppress values. - In the Data Items pane, click the data item.
- In the Properties pane, set the Calculation Intersection property to Show "Not applicable".