November 30, 2008 at 8:29 pm
This is the 2nd time i have encountered this issue in the last week, so i thought i would post and see if anyone has any suggestions...
I am working on a report which includes customer and product info...basically, i want one row with all the customer info, and a comma delimited list of all the products they have. I know several ways i could return the data like this from the database, but am trying to handle this part of the processing in Reporting Services to keep the query time down...
My theory was I could group by customer and use some kind of concatenation function to list the products at the group level (and would then hide the detail level which contains a row for each product), but i can't find a function to do this. Using the Join function to list multi-valued parameters is an example of what i am trying to do, but this doesnt seem to work in the group.
eg
CustNo Name Product
123 Bob ProdA
123 Bob ProdB
456 Jane ProdA
Desired Output
CustNo Name Product
123 Bob ProdA, ProdB
456 Jane ProdA
As i said, i know i can do this in the database query, but am trying to achieve it in RS if there is a way!
Thanks,
Ben
December 1, 2008 at 9:26 am
There is no built-in SSRS 2003 or 2005 function in the group area to concatenate that I am aware of, other than the Join method in an SSRS code block (but I don't know of a way to get it to do what you need). What I have found out and seen over the years however is that the SSRS processor engine is actually far slower than the native SQL Server DB engine when it comes to complicated formatting and \or data handling. Because of this I always do the complicated stuff at the database level for the data source and just have the SSRS spit it out the pre-formatted result set in a pretty format. Just my 2c.
Cheers,
maddog
December 1, 2008 at 4:40 pm
Bugger! Not the answer i was hoping for, but i guess it was the one that i expected! Am suprised MS doesnt offer a concatenate function in either SQL Server or RS, particularly given that the provide CLR stored proc code for one as a code sample!
May 17, 2011 at 7:23 pm
These CLR aggregates are inspired by the MSDN code sample however they perform much better and allow for sorting (as strings) and alternate delimiters if needed:
http://groupconcat.codeplex.com
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply