SSRS 2005: Change column display from item column value/one row to something else?

Question

I have a table/dataset like:

1/1/2009 | Training 1 | Mike
1/1/2009 | Training 1 | Bill
1/1/2009 | Training 1 | Steve

I would like to display as

1/1/2009 | Training 1 
Mike, Bill, Steve

The idea is that the resulting printed page space is not wasted by printing one column on most of the page down but shortening the space to either a horizontal list or a columned result inside that field. What is the best way?

I would like to handle this is report designer as opposed to mucking with SQL results. I see several ways that gets close but not exact. I'm on SSRS 2005.

Answer 1

Without using SQL to generate the CSV, then try a matrix control to change the rows into columns and groups on the date/training... but you won't have commas in between.

Answer 2

(I'm arbitrarily labeling your three columns Date, Session, and Participant, and your table MyTable)

I don't think you're going to be able to do this solely in the report designer. The best way I see to do it is to write a scalar-valued function which takes a datetime and a varchar (or two varchars) as two inputs and produces a varchar output. The function would look something like this:

Declare @Names varchar(MAX), @Participant varchar(MAX)
Set @Names = ''
Declare NameCursor Insensitive cursor For 
    SELECT Participant FROM MyTable WHERE Date = @Date AND Session = @Session
Open NameCursor
Fetch Next From NameCursor Into @Participant
While @@FETCH_STATUS=0
Begin
    Select @Names = @Names + ', ' + @Participant
    Fetch Next From NameCursor Into @Participant
End
Close NameCursor
Deallocate NameCursor
Return SUBSTRING(@Names, 3, LEN(@Names))

Then, when you select from MyTable, do it with

SELECT Date, Session, dbo.MyFunction(Date, Session) As Participants GROUP BY Date, Session

Answer 3

I wrote the following query to build a basic data set using a common table expression and then a grouping/concatenation operation to get the results I think you're looking for:

    WITH temp AS (
    SELECT CAST('1/1/2009' AS DATETIME) AS Date, 'Training 1' AS Session, 'Mike' AS Participant
    UNION ALL
    SELECT CAST('1/1/2009' AS DATETIME) AS Date, 'Training 1' AS Session, 'Bill' AS Participant
    UNION ALL
    SELECT CAST('1/1/2009' AS DATETIME) AS Date, 'Training 1' AS Session, 'Steve' AS Participant
    UNION ALL
    SELECT CAST('1/2/2009' AS DATETIME) AS Date, 'Training 2' AS Session, 'Steve' AS Participant
    UNION ALL
    SELECT CAST('1/2/2009' AS DATETIME) AS Date, 'Training 2' AS Session, 'Bill' AS Participant
    UNION ALL
    SELECT CAST('1/3/2009' AS DATETIME) AS Date, 'Training 3' AS Session, 'Mike' AS Participant
)
SELECT DISTINCT
    Date,
    Session,
    (
     SELECT STUFF(
      (
       SELECT
        ',' + CAST(Participant AS NVARCHAR(50)) + '' AS [text()]
       FROM
        temp b
       WHERE
        b.Date = a.Date AND
        b.Session = a.Session
       FOR  
        XML PATH('')
      ),
      1,
      1,
      ''
     )
    ) AS Participants
FROM
    temp a

This outputs the following:

Date    Session Participants
2009-01-01 00:00:00.000 Training 1 Mike,Bill,Steve
2009-01-02 00:00:00.000 Training 2 Steve,Bill
2009-01-03 00:00:00.000 Training 3 Mike

You can format these results however you want in SSRS. No promises that this will run fast on a very large dataset due to the DISTINCT clause, but any grouping operation you do on a large dataset would be slow anyway. Using the FOR XML clause like this absolutely rules when it comes to concatenation.

Hope this helps.

Answer 4

The usual way to do aggregate concatenation in SSRS is with custom code. See here for an example:

http://blogs.msdn.com/suryaj/archive/2007/08/11/string-aggregation.aspx

Here's the custom code in basic form:

Private CurrGroupBy As String = String.Empty
Private ConcatVal As String = String.Empty
Public Function AggConcat(GroupBy as String, ElementVal as String) as String
    If CurrGroupBy = GroupBy Then
        ConcatVal = ConcatVal & ", " & ElementVal 
    Else
        CurrGroupBy = GroupBy 
        ConcatVal = ElementVal 
    End If
    Return ConcatVal 
End Function

Followed by this at the grouping level you want to display:

=RunningValue(
     Code.AggConcat(
         Fields!YourFieldToGroupBy.Value
       , Fields!YourFieldToConcat.Value
       )
   , Last
   , "YourGroupName" 
   )

Answer 5

I wound up using a SQL stuff/xml statement in the select portion of the t-sql with a join back to the overall select's data. Something like this: http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx