Filter a Matrix

  • Is it possible to filter a matrix so that if a value is over a certain amount the whole row will not show?

    For example
    VZW West Build Plan

       Yearly Sales  
    Salesman 20122013201420152016
    11120512623
    2123322334
    33443233412

    We need to exclude all records from the report where a salesman has a yearly sale of >=50. As the years are dynamic we would like to handle this in the RDL rather than in the SQL

  • SQL is the considerably better spot for it.  If you have a GROUP BY in your query, you would just add a HAVING clause that specifies that all the SUMs are > 50.  Even if your query is dynamic SQL, you would already have to have the field list to do so, thus adding another snippet that takes each field and adds it to the HAVING clause isn't all that difficult.   As you may have already discovered, trying to filter that matrix isn't going to work so well.

  • Another idea occurs to me though... have you tried using an expression to control ROW visibility?   Just be aware that it won't exclude the values from any totals that might be there for a given column, so that might not be a particularly good method, and it would largely prevent you from easily creating totals later, if a change in need comes along.

  • The visibility won't work because it will display when dumped to excel. Was hoping there was a filter trick on the group in SSRS but will manipulate the SQL to exclude. Thanks for the assistance sgmunson! Appreciate it.

  • marty.seed - Friday, September 29, 2017 1:10 PM

    The visibility won't work because it will display when dumped to excel. Was hoping there was a filter trick on the group in SSRS but will manipulate the SQL to exclude. Thanks for the assistance sgmunson! Appreciate it.

    Glad I could help.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply