April 10, 2018 at 6:43 am
I have SSRS report it has Two parameters string parameters (text boxes) 1.SearchByFirstName, 2.SearchByLastName
how to validate these parameters, i mean when user clicks on view report button it needs to check either of values were filled in otherwise it should not execute report at all.
I have little idea to code but not sure how to stop report execution when there is no values in parameters.
Please advise
Thank you in Advance
Asita
April 10, 2018 at 1:44 pm
This is a VERY clunky way of doing it, but in the past I've put something like this at the begin of my query:IF @paramA IS NOT NULL OR @paramB IS NOT NULL
BEGIN
Then put END at the end of the query. This results in the query coming up completely blank if the parameters are empty, which means the report generates an error. That's different from not executing - it executes but fails (that's part of what makes this so clunky).
I would be very interested in any better ideas other folks have.
April 20, 2018 at 11:51 am
asita - Tuesday, April 10, 2018 6:43 AMI have SSRS report it has Two parameters string parameters (text boxes) 1.SearchByFirstName, 2.SearchByLastName
how to validate these parameters, i mean when user clicks on view report button it needs to check either of values were filled in otherwise it should not execute report at all.
I have little idea to code but not sure how to stop report execution when there is no values in parameters.
Please advise
Thank you in Advance
Asita
The best way to solve that problem is to encapsulate your query in a Stored Procedure, and ensure the WHERE clause includes COALESCE(@LastName, @FirstName) IS NOT NULL as it's first condition. That way, if you execute the stored procedure with no paramaters, you don't get any data and the only "expense" is to derive the execution plan. The report will have 0 rows, so the expense is minimal. I'm not sure if you can create a group for the parameters, and provide some kind of group property that will require one of the parameters to have a value. That;'s what I would search for...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 20, 2018 at 2:00 pm
asita - Tuesday, April 10, 2018 6:43 AMI have SSRS report it has Two parameters string parameters (text boxes) 1.SearchByFirstName, 2.SearchByLastName
how to validate these parameters, i mean when user clicks on view report button it needs to check either of values were filled in otherwise it should not execute report at all.
I have little idea to code but not sure how to stop report execution when there is no values in parameters.
Please advise
Thank you in Advance
Asita
This post has a good solution so that the report is only run if at least one of the two parameters is populated:
Ensuring 1 of 2 parameters is populated
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply