If you are unable to create a new account, please email support@bspsoftware.com



MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Ability to concatenate strings when aggregating data

Started by rory, 07 Aug 2006 08:45:19 PM

Previous topic - Next topic


I haven't seen this in any product, but would love to have the ability to to specify concatenate as an aggregation method for strings.

Suppose you have 1000 clients, and a hundred employees have relationships with them, typically two or three per client (but maybe 20).  You want to get a list of clients, revenue generated from them and employees who know them, one line per client.  If you try and pivot this you get an unmanageable large number of columns, and you may not be interested in this, since the first thing you do is concatenate them back to a single field.  However, you're then tied to the data when doing the concatenate so when the new sales person comes on board you're modifying code.

I figure you would need to have two additional parameters for this function - max string length, and separator character.

Incidentally, does anyone have a generic solution to this?  I find myself looking at stored procedures and cursors to get the required results.