Can I create a variable in my report to compare values?

Question

Can I create a variable in my report to compare values?

Bob-7209 86

I have an SSRS report that shows student grades. The user can choose to group the results by major, degree, instructor, etc. I need it to filter records if they do not fit the grouping choice. The only issue I am having is when I group by the instructor.

The report currently looks like this. The instructor is shown above the black line. I need to filter out courses under the student that are not this instructor. The 2nd and 3rd students both have a course with Sabrina Locke, so they are shown under the same black line, but I need to remove their courses that are not for Sabrina Locke.

CURRENT

My goal is end up with this result. I highlighted where the values should match. The problem is that the instructor in both areas is being populated by the same field in the dataset.

It seems to me that I need a variable in the instructor cell above the line. I want to compare the courses for each student to that variable and only show the record when the two match.

Is this the best approach? Is this something you can help with?

FUTURE

For more context, the report will look like this if they group by the major. This is already correct. I only need it to change when grouping by the instructor.

MAJOR

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Yes. A group variable is the right fit for this scenario.

A group variable is calculated once per group instance and can be used in expressions from nested child groups. That matches the requirement when the outer group is Instructor and the child rows are the student’s courses.

A workable approach is:

Define the outer group by Instructor.
Create a group variable on that Instructor group to hold the instructor value shown above the black line.
On the child group or detail rows that list courses, add a group filter that compares the course Instructor field to that group variable.

In Report Builder / SSRS:

Open the report in Design view.
In the tablix, select the Instructor parent group.
Open Group Properties for that group.
Define a group variable for the instructor value.
- Group variables are intended for expressions scoped to the group and accessed from nested groups.
Then select the child row group or detail group that displays the courses.
Right-click it in the Grouping pane and select Group Properties.
Select Filters.
Add a filter:
- Expression: the dataset field for the course instructor
- Operator: =
- Value: the Instructor group variable
Select OK.

This keeps only the course rows whose instructor matches the current Instructor group instance.

This approach is consistent with how SSRS processes grouped data:

Filters can be applied at the data region group level.
Group variables are available to nested child groups.
Group expressions are evaluated after filters are applied to the data in the report.

If the report must behave differently only when the user selects group by Instructor, apply this filter only on the Instructor-based grouping path. If the same tablix is reused for other grouping choices such as Major, avoid applying the Instructor-match filter there, because it would remove valid rows for those other groupings.