How do I select the result value of a case expression
Hello,
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
etc.
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?
Thanks!
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
etc.
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?
Thanks!
joevi
Ideally you'd feed the parameter (@Agency) with a query (including an 'All' value). Use the query to feed a parameter listboxe in RS then create a dataset with some If..then logic (for the 'All' conditon). The rest is pretty much straight up TSQL.
ASKER
More specific please. Not sure what a parameter listbox is or how to set one up. Not sure what the if...then logic should be or how to set that up. And 'the rest is pretty much straight up TSQL' - the rest of what? What needs to be done in SQL?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, thanks!