September 28, 2017 at 1:03 pm
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
| Yearly Sales | |||||
| Salesman | 2012 | 2013 | 2014 | 2015 | 2016 |
| 1 | 11 | 20 | 51 | 26 | 23 |
| 2 | 1 | 23 | 32 | 23 | 34 |
| 3 | 34 | 43 | 23 | 34 | 12 |
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
September 28, 2017 at 1:52 pm
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 28, 2017 at 1:55 pm
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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 2, 2017 at 9:16 am
marty.seed - Friday, September 29, 2017 1:10 PMThe 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply