SSRS parameters - multi-choice selection

Question

SSRS parameters - multi-choice selection

DerbyNeal

Hall of Fame

Points: 3695
More actions
August 3, 2010 at 9:52 am

#224968

Hi,
I am very new to SSRS so please be gentle with me.
I have a SSRS 2005 report that contains several user prompts (search criteria):
National Account – Text Box
Invoice Account – Text Box
Add Delivery Account – Dropdown selection (only ‘Y’ & ‘N’ values)
Delivery Account – Dropdown selection
The process needs to be semi-cascading, depending on which selection is made.
The user must either populate a value in the National Account or Invoice Account field. If the user has entered a value in the Invoice Account field, the user needs to select ‘Y’ or ‘N’ from the Add Delivery Account dropdown list. If ‘Y’ is selected then the user must select from the associated Delivery Account values (linked on Invoice Account field selection).
Is there a method to prevent users from entering values in both National/Invoice fields (apart from setting the ‘Allow Null value’ in both report parameters)?
Any ideas please?
Thanks in advance,

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

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

ramdas.narayanan Ten Centuries Points: 1154 More actions · Answer 1

Hi,

With respect to parameters you can a specify a list of values that can be entered by using the options in parameter definition window. I don't think you can perform data validation like how one would do with data enetered textboxes in a form.

mvee Hall of Fame Points: 3634 More actions · Answer 2

As far as we have found, there is no way to do custom parameter validation within SSRS. If you find an way, please share.

Thanks, Megan

weitzera Hall of Fame Points: 3442 More actions · Answer 3

Unfortunately, there is no way to completely do what you're asking. You have three options:

You can define the list of available values in the second parameter to include only null if the first parameter is filled in. this will appear kindof kludgy to the users, as they'll have a dropdown with no values in it. If they change their mind, they'll have to unselect the first parameter. Also, if they select the second parameter first, you will not be able to filter the values from the first parameter.

You can check for invalid parameters in sql, and communicate invalid parameters back to the report using raiserror, and then present the message to the user in a somewhat useful fashion.

You can build a front-end to the report that gathers and validates the parameters however you want. Obviously this adds a lot of extra effort.

Let me know if you need help with any of these options.

Explanation of the limitation:

SSRS does not do smart dependency checking for parameters. The rule that ssrs uses is that if a parameter uses code or executes sql for its default or available values, it is assumed to be dependent on all parameters that come before it in the list. Conversely, parameters cannot have dependencies on parameters later in the list.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

DerbyNeal Hall of Fame Points: 3695 More actions · Answer 4

Thanks guys.

I appreciate your efforts and responses

jmonk 57451 SSC Enthusiast Points: 131 More actions · Answer 5

One possibility is to ask the user to define the type of acount being entered (using another parm) and only have one box to enter the account in. Another possibility is to have the invoice parm be dependent upon the national parm, i.e. if a natl. parm is entered. Automatically populate the invoice parm with a null value and present a message to the user that a National account has already been entered. These are both options I've used in the past. Another option is dynamic sql but I'd leave that as a last resort. Hope that helps.