This is for an SSRS report and I'm not sure if what I'm trying to accomplish should be done in the SQL Query itself or somewhere in my Reporting Services report.
I have two separate agencies: ABC and DEF. Each agency has a bunch of sub-agencies defined my state. So in my database I would have:
ABC - NY
ABC - TX
ABC - MA
DEF - NY
DEF - PA
Transactions are coded to the individual agencies as displayed above. But my end users want the option to select either ABC or DEF or both (Select All). On the selection parameter they do not want to see it broken out to the sub-agencies. I have tried accomplishing this by setting up a second dataset and using select distinct with a case expression, but it returned no data when I ran my report.
Does anyone know how to do what I'm trying to accomplish?