A report lists a number of different groups of related records. Each listing is a summary of the records in the group. The sumary information for each group requires looping through all the records for the group and concatenating the content of different columns in different records based on the result of some logic. If I were to do this in access, for example, I would fill different fields on the report with the result of a function that would loop through all the records for a group, pick out the required information based on the logic and return a concatenated string. How can this be done in a SSRS report. Using custom code is pretty basic and difficult to trace through etc... however, is it possible to open a records set and loop through it in a custom code function?

Hi RobGMiller, Looks like the LookupSet function will help you get thru your requirement. This function lets you run through all the records of a dataset based on a Lookup expression and then returns an array of values of a certain field. You can them simply do a Join of the array elements to display a concatenated string. Here's more info on the LookupSet function: http://msdn.microsoft.com/en-us/library/ee240819.aspx Let me know if you need more info. HTH. Cheers, IceQB Please mark correct answers :)

Personnally, I would probably do a stored procedure, make all my data manipulation there and then return the correct dataset, else, the above poster seems like the good solution!

Thanks for your time gentlemen, The LookupSet function mentioned above appears to be a little too limited. It appears to return a single column of data for all records in a set that is based on the value of one key. The returned data can be transferred to an array which can then be examined to extract specific records and concatenated in a string. Unfortunately the set of records to be examined requires a filter with about 8 bits of data so I don't think the LookupSet function will do. A stored procedure returning a single scalar string which is a result of loop process using a cursor is possible. How can the result of a stored procedure be bound to a report text box control?

Hi RobGMiller, Considering that your scalar function returned value is named ConcatenatedField, you can do the following steps to bind the function returned value to the textbox on your report: 3. OK your way out. Please Note: Replace the DataSet name with your actual SSRS dataset name and also the field name with your actual field name. Let us know if you need more info. HTH. Cheers, IceQBPlease mark correct answers :)

to clarify, Create another dataset for the report. Base it on the scalar StoredProcedure and use it to feed the textbox using an expression. I'll get back to you soon with the result. Thanks,

I managed to find a way to have a stored paramenter retrun a single text string. However, the stored procedure must be called with parameters that are related to the current group. =First(Fields!DaysWorked.Value, "BuildDaysWorked"). The stored procedure is automatically fed report parameters. However, it needs to be fed data that varies depending on current group records. The content of the text box control the stored procedure feeds depends on values which differ by group. It appears that the parameters of a dataset are directly linked to report parameters. I've tried the following as the text box expression as a test: =First(Fields!DaysWorked.Value, "BuildDaysWorked(Parameters!MajorAccountCode.Value, Parameters!Reference.Value, Parameters!Labour.Value, Parameters!WorkOrderID.Value, Parameters!UnitPrice.Value)") It produced the following error: The Value expression for the text box TextBoxTest has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset. Untimately the parameters of the BuildDaysWorked stored procedure should be set to columns of the base dataset which returns all records to be included in this report. Those values will differ as different groups are processed.