locked
Column Wrapping RRS feed

  • 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

     

    Thursday, January 6, 2011 7:35 PM

Answers

  • 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

    • Marked as answer by Challen Fu Monday, January 17, 2011 11:56 AM
    Wednesday, January 12, 2011 9:32 AM

All replies

  • 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
    Thursday, January 6, 2011 8:02 PM
  • I want to do it at the report level, is it possibe?
    Thursday, January 6, 2011 8:08 PM
  • 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
    Friday, January 7, 2011 12:36 AM
  • 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

    • Marked as answer by Challen Fu Monday, January 17, 2011 11:56 AM
    Wednesday, January 12, 2011 9:32 AM