peterzeke (3/13/2013)
A couple of thoughts come to mind. Top of my head, I don't think SSRS has an equivalent built-in feature you described in Crystal. So, for the moment, I'll offer a possible workaround.
1) It sounds like you want to give users the ability to provide more than one choice at a time -- i.e., multivalue parameter?
2) If you are able to provide a list of choices, and the list isn't too long, you could create a multivalued parameter that is populated with all of the choices a user might select (e.g. "5812 - EATING PLACES", "0000 - BORING PLACES", "0013 - UNLUCKY PLACES","0666 - EVIL PLACES",...). Set the value of the parameter to the 4 digit code, and the label of the parameter to the full description of each choice.
3) Offering a multivalued parameter means the user merely needs to click-to-choose rather than type in their selections.
4) Regarding the sql to process the parameter, two basic approaches to consider. One is to write the sql code in SSRS with a "WHERE SICCode in (@paramSICCodes) " . The other approach is to pass the parameter to a stored procedure, but you'll need to parse out the individual values contained within the parameter as one of the steps within the stored proc. Jeff Moden's DelimitedSplit8k function is great at this task (see
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url])
Again, this work around assumes you have some sort of reference table of SIC codes available, rather than just the fulltext descriptions.
--Pete[/quote
Hi Pete,
To answer your questions:
1) Yes, the users need a multi-value parameter. They should be able to report on as many SIC codes as they like.
2) I have set up this parameter to use a query to provide a list of choices, where the value is the 4 digit SIC code ("5812") and the label is the 4 digit code + description, i.e. "5812 - EATING PLACES"
3) Yes, I am aware of that. I have used multi-value parameters many times.
4) I guess I still don't understand what I need to do. I know I can parse the individual values, but how do I get them into a WHERE clause that works like the "startswith" in Crystal?? There are 1100 possible
SIC codes. The table that I am using to produce the parameter list of choices contains a standardized listing of SIC codes, but the users are able to type whatever they want as a description, so some accounts may
appear on the database as "5812 - EATING PLACES" or "5812-RESTAURANTS" or "5812 - PLACES to EAT" , but they should be able to select the standard "5812 - EATING PLACES" and the report should return all
accounts with an SIC that starts with "5812" which would give them all 3 of these example.
I appreciate any help I could get.
Barbara