How to stop execution the report if there is no parameter values inSSRS 2012

  • 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

  • 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.

  • asita - Tuesday, April 10, 2018 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

    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)

  • asita - Tuesday, April 10, 2018 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

    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