Report Builder 3.0 - adding filter(s) on 2 different columns

• mschaper

  Ten Centuries

  Points: 1120

  More actions

  June 13, 2016 at 1:18 pm

  #327451

  I have a parameter in my 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.