Post reply

Dynamic Sql based on Parameter in SSRS

  • February 25, 2009 at 12:29 pm

    #218757

    hello all

    i am working on report where there are two parameter

    Report can be pulled with one value or both

    region is one parameter, employee name

    1.user should be able to pull all details in a particular region.

    2. user should be able to pull with both combination

    please let me know how to in corporate in the sql statement in the dataset

  • February 26, 2009 at 4:55 am

    #950510

    Hi

    You could try setting defaults on each of your report parameters. Set the default parameter value to '%' , your WHERE clause would then look something like this:

    WHERE region LIKE @report_parameter1 AND employee LIKE @report_parameter2

  • February 27, 2009 at 8:20 am

    #951201

    or pass a NULL for the employee parameter and do:

    WHERE region = @region and (employee = @employee or @employee is null)

    Nigel
    How to post questions

  • April 1, 2009 at 2:39 pm

    #970234

    Why not use something like this:

    ="Select * FROM blablabla WHERE" + IIF(@region = null, " (employee = " + @employee + ")", IIF(@employee = null, " region = " + @region , " region = " + @region + " and (employee = " + @employee + ")"))

Viewing 4 posts - 1 through 3 (of 3 total)

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