Report Studio

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, a list report 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 report, you see three months for each year.

In crosstab reports in Cognos, 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 instead of Year, you see the top three months for each country (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.