I have a parameter in a report (Report Builder 3.0) called REPORT_FILTER. This parameter has 4 available values. The labels are (1) Home Group, (2) Home Branch, (3) Other Group, and (4) Other Branch. The corresponding values are 1, 2, 3, and 4.
The SQL used to run this report adds "1" to the "Filter1" column for the Home Group and "2" to the "Filter1" column for the Other Group.
The SQL used to run this report adds "1" to the "Filter2" column for the Home Branch and "2" to the "Filter2" column for the Other Branch.
When the SQL runs, I get 1 or 2 in the "Filter 1" and "Filter 2" columns. This data is just what I expect.
What I would like to do is add a filter to the report so the user only gets the records they want. What I'm struggling with is that the filter(s) will have to use 2 columns and I'm not sure how to do that.
For example, if the user selects, Home Group (value = 1), the report should only return those records that have a 1 in the Filter1 column
If the user selects, Other Branch (value = 4), the report should only return those records that have a 2 in the Filter2 column
This report already takes a while to run several minutes, so (I think) I would prefer to filter the results in Report Builder instead of with the SQL. If I could do it with SQL, that would be acceptable. I've tried to write SQL to do this but haven't been able to succeed as well.
Any suggestions would be appreciated. Thanks for the help.