Quickie: Aggregating text in a Cognos crosstab

The ability to show text in a crosstab is highly requested feature. Various work-arounds have been found, such as using layout expressions like: case when [Flag] = 1 then ‘Yes’ else ‘No’ end. This works, but doesn’t allow for truly dynamic crosstabs.

Consider the following requirement. The user wants a crosstab that shows Region, Retailer Type, and a list of Company Names in the intersections. He wants to be able to filter by Product Line to limit the companies that are shown.

aggregating text

In the above example, I’m using DB2. DB2 has the function listagg, which aggregates text with a specified delimitter. Oracle has something similar, group_concat. SQL Server has some UDFs that can do the same thing, such as found here.

This can now be accomplished as of 10.2.1 Fix Pack 3. One of the new features of this fix pack is a way of using non-standard aggregation functions. By prepending “aggregate:” before the function, we are instructing Cognos to trust us that this is an aggragative function. It won’t try to put it in the group by. This makes it incredibly easy to get this effect, the expression in it’s entirety is aggregate:listagg([Sales (query)].[Retailers].[Company name],’, ‘)

aggregating-text.txt (901 downloads)