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® Analytics 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
a crosstab showing revenue by product line for the prior YTD, current YTD, YTD change, and YTD growth percentage 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 Analytics 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

  1. Click the Queries icon Queries icon, and click the query that contains the data item for which to suppress values.
  2. In the Data Items pane, click the data item.
  3. Click the Show properties icon Show properties icon, and in the Properties pane, set the Calculation intersection property to Show "Not applicable".

Results

When a query-defined calculated member and a data source calculated member intersect, the resulting value is suppressed. If the intersecting members do not contain data source calculations, the value is not suppressed.