CODEWITHSUNDEEP
visual-studioreporting-services
Chris
Chris

Reputation: 33

Showing a Specific Row From a Row Group - Visual Studio SSRS

I have an SSRS report in Visual Studio that displays students enrolled in a course. I am using a parameter to allow the user to include or exclude cancelled enrollments (there is a field in the source data that indicates whether an enrollment is cancelled or not).

When the user chooses to include cancelled enrollments, I am having trouble in the case of students who cancelled and then re-enrolled. This student would appear in 2 rows of data, but I would like only their most recent activity to appear (ie. if they enrolled, cancelled, then re-enrolled, I only want their latest enrollment to appear).

I currently have a Row Group expression set up to group by the student's unique identifier. However, I do not know how to control exactly which row determines the values for the other fields, when they have more than one row being grouped together.

My row grouping expression is as follows (where cf_code is the specific offering the student is enrolling in, and student_number is the student's unique identifier):

=Fields!cf_code.Value & Fields!student_number.Value

How can I indicate that when grouping cancelled and enrolled rows together for the same student, the only data that should be shown in the other fields in my matrix are the ones relating to the enrollment (the most recent activity)?

Thanks!

Upvotes: 1

Views: 439

Answers (1)

McGlothlin
McGlothlin

Reputation: 2099

There are a couple ways you could potentially handle this:

  1. If you only ever want to see the latest enrollment, you can eliminate these rows on the database side by doing a MAX(), partitioning on the student's unique identifier and ordering by the date. This may require passing your exclude parameter to the database so you don't exclude rows when you need them.

  2. Using SSRS: in the student group you could set the row visibility expression to something like:

    IIf(Count(enrollment_date) > 1, Max(enrollment_date) <> enrollment_date, False)

This will check to make sure each row is the latest, and only display the row if it contains the latest enrollment date for that particular student.

Update: here's how I might group that. Start with a parent group on student_number whose child is the detail row group.

student_number
    [detail rows]

Then, on the detail row in the tablix (but not the student level), set the row visibility as described above. This separates the student from the enrollment and allows you to hide detail rows without the student disappearing.

Upvotes: 1

Related Questions