Force at least one parameter (2K5)

I am trying to build a report that has about 6 different parameters. All of the parameters have been set to allow null, however, I would like the user to put in at least one "search" criteria in order to run the report.

How would I be able to do that?

In addition, when I am viewing the report, it automatically runs and does not wait until I click "View Report". How can I set it not to execute when the report is viewed, but when the user clicks the button?

ThanksHow about setting one of the parameters to not allow blank but setting the default to blank? Might solve both problems in one go, at least forcing the user to think about what they enter... Haven't tested it though |||

The program will not allow you to set a blank default value with the "Allow Blank Value" checked.

Thanks for trying ;)

|||

DotNetSavvy,

Unfortunate if all of your parameter have null values and nulls are allow, then your report will automatically run. I personally set default to all my parameters but one.

On the forcing a parameter selection, you could raise an return error from SQL if all parameters are null.

hammer

|||

Solution:

Create embedded code function that takes as parameters your report parameters values (like this MyFunction(p1value, p2value, p3value,...) ) Inside do something like this: iff(IsNothing(p1value)=true,0,1)+iff(IsNothing(p2value)=true,0,1)+... and if the result is < 1 then throw error/message box with some text (like: "Please assign at least one parameter value") and return null else return 1 or other not null value.

Add internal parameter that cannot be null to your report and in the "expression" of the default value use your code function: =Code.MyFunction(p1.value, p2.value, ...).

Here is an example of using function and parameter to validate report execution http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=829175&SiteID=1

Should work.

Maciej