Limitations When Using Set Expressions in List Reports

In list reports, we recommend that you avoid using set expressions. When in a list, set expressions, such as TopCount, may produce fewer rows than in the corresponding crosstab.

For example, the following list includes Year in the first column, followed by a column containing an expression that returns the top three months by revenue. Only three rows appear in the report and they correspond to the top three months across all years. If you group by Year, the report still shows only three months. However, the corresponding crosstab, you see three months for each year.

Figure 1. List showing the top three revenue months in 2013, and the corresponding crosstab

a list showing the top three revenue months in 2013 and a crosstab showing the top three revenue months for each year

In crosstab reports in IBM® Cognos® Business Intelligence, set evaluation is always done in the context of what the set is nested under in the report. However, in list reports set evaluation is done independently of the grouping. For example, if the first column contains Country or Region instead of Year, you see the top three months for each country or region (across all years) in both cases.

In the case of different dimensions, you can force context independence by replacing [Revenue] in the topCount expression with tuple ([Revenue], X), where X is the default member of the hierarchy that contains Month.

However, for nested levels in the same hierarchy, there is no such workaround at this time.