Report Designer: Need to list fields from multiple result rows as comma seperated list (like a JOIN on parameters)
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter. However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance). For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed). Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac How can I accomplish this? I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell. Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
April 9th, 2008 4:05pm
Hi there, Since there's a bolded title it would have to be 2 fields. Here's your code. Public MyValues as string public function SetValues( value as string)MyValues = MyValues + value + ", "Return ""End Function public function GetValues() as String if len(MyValues) > 3 thenReturn left(MyValues,len(MyValues)-2) else return MyValues end ifEnd Function You would call the code by placing the value =Code.SetValues(MyDBField) in a hidden table. Then in the textbox you would call =Code.GetValues() cheers, Andrew
April 9th, 2008 5:23pm
Thank you! That worked perfectly, I've never used any custom code in Report Designer before, that was an excellent little "trick" (solution, whatever the proper term would be, "magic"). You wouldn't happen to have any great resources, blogs, other links that discuss custom code in Report Designer by chance would you? This is something I will definitely have to familiarize myself with. Again, thank you!
April 9th, 2008 10:29pm
With the "Custom Code" tab in Reporting Services, I would call it a "hack". It does serve its purpose though... One of my favourites is Chris Hays. http://blogs.msdn.com/ChrisHays/ Teo Lachev http://prologika.com/cs/blogs/default.aspx Russell Christopher. http://blogs.msdn.com/bimusings/default.aspx Bob Meyers http://blogs.msdn.com/bobmeyers/default.aspx SSW http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx SQL Server Central http://www.sqlservercentral.com/ MSDN Blogs http://blogs.msdn.com/ I'll self-promote my blog too - http://sqlrs.blogspot.com - search for reporting services & see links to some of the Older Posts from authors above + some of my own. cheers, Andrew
April 9th, 2008 10:43pm
Thanks! Follow up quesiton though: What if I have 3 seperate fields that I want to do this for in the same report? "Perscriptions", "Last 5 visit dates", and "Known Allergies" (I'm just making these up, but it seems reasonable) How would the code know which table to reference to pull the values? Could the code .GetValues() have an argument passed to it that tells it which hidden table to reference for the SetValues part of the code? There must be some way to handle that other than writing the custom code 3 times with 3 seperate names? --- EDIT: Could the two seperate functions be combined into a single function so you would only have to call it inside the text box where you wanted the string to be created? So you'd just pass it the =Code.CombinedFunction(Fields!FieldNameYouNeed.Value) ? It would have to loop though since the table wouldn't be doing that anymore... a FOR EACH ... NEXT loop?
April 9th, 2008 10:50pm
Hack's aren't supposed to be scalable. I have done things in the past by using the Dictionary type to store array information. I've seen problems with security and deployment with this though. Rather than creating 3 functions, it could be as simple as passing in a 3rd parameter (TypeID as int) which would be an integer that could define the 3 fields (TypeID=1, etc). You would have 3 variables (type1value, etc.) outside the function to store the 3 results. You would have a case, switch,or if statement to assign tothe variables based on the id you pass. If there was an easy way to use arrays or dictionaries I would try that instead. Perhaps some custom properties? It is much more powerful (and painful) to setup a custom external assembly to do any kind of complex logic. You might get more results if you publish your question above to a new posting. cheers, Andrew
April 9th, 2008 11:07pm
Sorry to get on this thread. This was very useful but I have a small problem. Since my data rows are conditionally selected the values for the selected field skips rows (for example, rows 1,2,5,7, 10 etc will be populated and the other rows in between are empty). As a result of this the text final text string displays empty commas (for example, Apple,Banana,,,Orange,, Grapes,,,,,,,,,Pears, etc). Is there a way to avoid these extra commas and the string flows from one value to another skipping the blanks (for example - Apple,Banana,Orange,Grapes,Pearts,etc).Thanks in advance.
June 9th, 2009 5:58pm
Sure, just omit the comma if value is blank:public function SetValues( value as string)IfNot String.IsNullOrEmpty(value) Then MyValues = MyValues + value + ", "End IfReturn ""End Function
June 11th, 2009 3:50am
Thank you very much. It worked well. The IF condition syntax was the one messing up. Is there any MS KB libarary we can look up for these since BID is not intellisense.
June 12th, 2009 2:11am
Oops. Spoke too soon. I have a little problem. I am displaying the string at a Group level. It keeps on repeating the string for the number of records returned. For example, I group on ID and the text box is at the Group footer level. If I filter for one ID it is perfect. If the string is supposed to show 10 values there are 10 values in there. But if I filter for multiple IDs, say for 5 IDs, the string multiplies by repeating 5x10 = 50 values. Is there any way to control the string and break for each group(ID). Thanks in advance.
June 12th, 2009 8:32pm
You can do this with an additional variable to track the values. If your values are sorted then you only need to track the last value (compare and don't add to the string if same as last value). If your values are not sorted then you'll want to use a Hashtable to store all the values that you've seen and check if the same one is added again.
June 20th, 2009 12:37am
It's also worth noting that there is a new feature in SSRS 2008 known as 'group variables' that makes this much more reliable. Details here:http://msdn.microsoft.com/en-us/library/bb934256.aspxhttp://blogs.msdn.com/robertbruckner/archive/2008/07/20/Using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspxhttp://blogs.msdn.com/sqlrsteamblog/archive/2008/07/21/group-variables-in-reporting-services-2008.aspx
June 20th, 2009 12:46am
Unfortunately I am using only SSRS 2005 and won't be using 2008 for quite a while for valid internal reasons. Can you please provide me some example codes for adding the additional variable. The values are already sorted in the record set.
June 20th, 2009 5:50am
It's pretty straightforward...Public MyValues as stringPublic LastGroupId As ObjectPublic Function SetValues( value as string, groupId as Object)IfNot String.IsNullOrEmpty(value) AndNot Object.Equals(groupId, lastGroupId)Then MyValues = MyValues + value + ", "LastGroupId = groupIdEnd IfReturn ""End FunctionModify the calling expression to pass the group ID field value as well as the string value.
June 25th, 2009 12:36am
However I try I get an Error or repeated values with out stopping for each group. Would you graciously provide me the syntax for the Code.SetValues(MyDBField)The string field I am using for array concatenation is named Fields!Product.value and the Group field name is Fields!CustomerID.Value. The CustomerID field type is string.Thank you in advance.
January 14th, 2010 2:21am
Oh, I forgot that VB uses & for string concatenation. Try the following, invoked as =Code.SetValues(Fields!Product.Value, Fields!CustomerID.Value)Public MyValues as stringPublic LastGroupId As ObjectPublic Function SetValues( value as string, groupId as Object) If Not String.IsNullOrEmpty(value) And Not Object.Equals(groupId, lastGroupId) Then MyValues = MyValues & value & ", " LastGroupId = groupId End If Return ""End Function
January 15th, 2010 3:14am
Sorry. No luck with this either. Replacing the + with & didn't make any difference. The Code.SetValues is the same as yours. Just to provide additional info - I am not sure whether it matters or not - 1. Code.SetValues is placed in the details section of the hidden table. No grouping on that one. 2. The Code.GetValues() is placed on the CustomerID group footer.
January 16th, 2010 4:28am
Hmm, there might be something about the processing order that is interfering, I'm not really sure :(As mentioned previously, group variables were introduced in SQL 2008 since this method isn't really very reliable. You may want to look into doing the concatenation in the query instead (SQL 2005 supports CLR custom aggregates which provide a straightforward way to do this, or it can be done with T-SQL). Or perhaps you are 6 months closer to upgrading to SQL 2008 now :)As one other note, you might look into the exact error that is occurring by previewing the report in Visual Studio (Error List should show warning/errors occurred in report processing). However as noted a more reliable method would be to do this in the query or using group variables in SSRS 2008.
January 22nd, 2010 2:35am
Usually I handle most of the things as a server side processing in the query itself and leave the minimum to the BIDS for the client side processing. Unfortunately I couldn't build an array to concatenate this string in the SQL query and hence I end up trying the use of code in SSRS. For any individual or at the report level the code works fine but not for each group level. Any way do you have any SQL syntax I can try for creating this array on SQL Server. Thx.These are the two fields I need - 1. Customer.ID (This is the group and each ID will have a bunch of products)2. Customer.Products (this will have the product name. This is a one to many relationship). For example, if ID 101 has five products I will display101 Product1101 Product2101 Product3101 Product4101 Product5I need to take the product field values and concatenate like product1, product2, product3, product4, product5. This should end with ID 101 and restart for 102 for what ever values that ID has.
February 2nd, 2010 2:13am
Andrew, This code is perfect for a list I am working on in SSRS 2008 but, for some reason I cannot get it to work in my Visual Studio IDE for SSRS 2008. I have placed your custom code as you directed in the Report Properties>Code box: Public MyValues as string public function SetValues( value as string) MyValues = MyValues + value + ", " Return "" End Function public function GetValues() as String if len(MyValues) > 3 then Return left(MyValues,len(MyValues)-2) else return MyValues end if End Function Then, in the report I have a textbox in which I call the code '=Code.GetValues()' I have the SetValue code placed in a hidden table below with one column: '=Code.SetValues(Fields!EV_NAME.Value)' However, when I run it the textbox remains blank and [if I set the table to visible for testing] I get a LONG blank column in the table with the same number of rows as the objects I wish to list... this is driving me nuts. Any thoughts? James P
July 23rd, 2010 11:38pm
I had the same problem before, so i thought the report usually refreshes and throws values from top to bottom. So I placed my hidden table before (above) the textbox i need to populate a concatenated string. Its now working ok for me.
October 12th, 2010 10:24am