CODEWITHSUNDEEP
reporting-servicesreportbuilder3.0
Dan Scally
Dan Scally

Reputation: 2042

How do I Group Columns in SSRS on Row Number within a Row Group

I am trying to create a report that outputs a single row per student with a column for each of their classes. The obvious way is via a Matrix, however I am struggling with the setting for the Column Group - I need the output to run like the below:

enter image description here

The data is a row per Student and Group, as below:

enter image description here

so what I want is basically a Matrix with the Column Grouping set to something like ="Group" & CStr(RowNumber("RowGroup"))

Unfortunately SSRS won't let me do that because the RowGroup does not contain the column group. So how can I otherwise achieve this? I guess I can do a vb hashtable in Custom Code, but that's pretty annoying and feels really clunky so I'd rather a better solution.

Upvotes: 0

Views: 2560

Answers (2)

Dan Scally
Dan Scally

Reputation: 2042

The answer turned out to be dirty hashtables using VB Custom Code. This plugged into the Custom Code:

Dim MyTable As New System.Collections.Hashtable()

Public Function GroupNumbers(ByVal LearnerID As Integer, ByVal GroupCode As String) As Integer

    If MyTable Is Nothing Then
        Dim MyTable As New System.Collections.Hashtable()
    End If

    If MyTable.ContainsKey(LearnerID) Then
        If MyTable(LearnerID).ContainsKey(GroupCode) Then
            Return MyTable(LearnerID)(GroupCode)
        Else
            MyTable(LearnerID).Add(GroupCode, MyTable(LearnerID).Count + 1)
            Return MyTable(LearnerID)(GroupCode)
        EndIf
    Else
        MyTable.Add(LearnerID, New System.Collections.Hashtable())
        MyTable(LearnerID).Add(GroupCode, 1)
        Return MyTable(LearnerID)(GroupCode)
    End If
End Function

This takes the Learner number and just stores an increasing number against each group for that Learner in a hashtable, then resets the count for the next Learner - it's pretty gross but it seems to work fine.

Just call the code with;

="Group" & CStr(Code.GroupNumbers(Fields!Learner_ID.Value, Fields!Group_Code.Value))

Upvotes: 2

Jonnus
Jonnus

Reputation: 3028

Unfortunately the only way to do this is to go back to your query and insert a new column to group the Groups based on the Student ID/Name

For example, add the new ROW_NUMBER row to your query as below

SELECT
 StudentID,
 StudentName,
 GroupID,
 Row_Number() OVER (PARTITION BY StudentID ORDER BY StudentID) AS GroupNum
FROM @MyData

When you import the DataSet it will now have the GroupNum column and you can set up your matrix as shown

enter image description here

To get this output

enter image description here

I’m fairly certain there is no way to assign this group number from within SSRS

Upvotes: 1

Related Questions