Column Wrapping

Question

Is it possible to display all the values of a field in a cell? For example, I have a field in SSRS

FieldName
ABC
DCF
GVT

I want to display it as

FieldName
ABC, DCF,GVT

Is it possible? Please let me know.

Thanks

 

Answer 1

Hi Sam180,

Try this type of senario at script level it makes your SSRS report rendering faster, something like this:-

 

DECLARE @FieldNameList as VARCHAR(MAX)
SET @FieldNameList = ''

CREATE TABLE #TEMP
(
  FieldName VARCHAR(10)
)

INSERT INTO #TEMP VALUES ('ABC')
INSERT INTO #TEMP VALUES ('DCF')
INSERT INTO #TEMP VALUES ('GVT')

SELECT
	@FieldNameList = @FieldNameList + FieldName +','
FROM
	#TEMP

SET @FieldNameList = LEFT( @FieldNameList, LEN(@FieldNameList) -1 )

SELECT LTRIM(RTRIM(@FieldNameList)) AS FieldName

DROP TABLE #TEMP

Please let us know your feedback.

Thanks

Kumar

---

KG, MCTS

Answer 2

I want to do it at the report level, is it possibe?

Answer 3

Hi Sam,

On what basis you need to concatenate the values, It will be more helpful If you can show your full Resultset.

Apart from that, If the Report is a parametrized one then based on the selection of Param1 value, you can concatenate the values of the Param2 using the 'Join' function.
=Join(Parameters!Param2.Value,",")

I feel this is the easiest way to get the desired Report output.

Please let us know

Thanks

---

Please click the 'Mark as Answer' button if my Reply helped you to solve your problem! Thanks M.Mahendra

Answer 4

Hi Sam1980

 

Could you tell me what's the version of your Reporting Services? You can use custom code to achieve your purpose, follow these steps:

1. Add custom code, such as: 

 

  Dim Temp As System.Collections.Hashtable

  Function AddValue(ByVal value As String, ByVal ParamArray key As String()) As String
    If value Is Nothing Then
      value = String.Empty
    End If
    If (key Is Nothing) Then
      key = New String() {String.Empty}
    End If
    If (Temp Is Nothing) Then
      Temp = New System.Collections.Hashtable
    End If
    If Temp.ContainsKey(key) Then
      Temp(key) = CStr(Temp(key)) + "," + value
    Else
      Temp.Add(key, value)
    End If
    Return Nothing
  End Function

  Function SumString() As String
    Dim Result As String = Nothing
    For Each element As System.Collections.DictionaryEntry In Temp
      If Result Is Nothing Then
        Result = CStr(element.Value)
      Else
        Result = Result + "," + CStr(element.Value)
      End If
    Next
    Temp = New System.Collections.Hashtable
    Return Result
  End Function

  

2. In the expression of detail cell, input the expression:

=code.addvalue(Fields!Detail.Value,Fields!ROWGROUP.Value)

 

3. Add a parent group with group footer, in the expression of group footer cell, input the expression:

=Code.SumString()

 

4. Set detail row height to 0in to hide the row, if you change the height in Design view, the minimum height is 0.03125in, so you'd better set the height in Code view. Then you will see the string you want in group footer cell.

 

You can download this report file as an example, you can modify the data source to SQL Server 2005/2008/R2 instance to preview the report:

http://cid-854fa6d2b7d88cc7.office.live.com/self.aspx/Work/ColumnWrappingTable.rdl

 

There is a similar thread with a different solution, you can refer to:

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/1d0206da-f1c6-4d36-b9c5-530a855861d7 

 

Thanks,

Albert Ye