Report Builder - Where or Having Using Parameters

  • Maybe this has been answered before, but I can't seem to find what I need.
    I have a SQL statement that has a Having clause, as well as Where.
    I want to be able to say IF a parameter is x, then apply the Having line.  IF a parameter is Y, then apply the Where statement.
    Can this be done, and if so, how?
    Here is an example of what I have:

    SELECT Salesperson, SUM(SalesLines) FROM Table
    WHERE Salesperson IN (SELECT DISTINCT Name from SalesPersonTable)
    GROUP BY Salesperson, SalesLines
    HAVING SUM(SalesLines) > 100

    I want to say if Parameter1 evaluates to true, then apply the Where clause; if Parameter2 evaluates to true, apply the Having clause, or else don't.
    Thank you!

  • For starters, you probably don't need that DISTINCT in the subselect, nor should SalesLines be included in the GROUP BY.  This could easily be done with dynamic SQL.  A cruder version only using one parameter could be done like this, though I'm sure others will be able to provide far more elegant solutions:

    DECLARE @x BIT
    SET @x = 0

    IF @x = 1
    SELECT Salesperson, SUM(SalesLines)
    FROM Table
    WHERE Salesperson IN (SELECT Name from SalesPersonTable)
    GROUP BY Salesperson

    IF @x = 0
    SELECT Salesperson, SUM(SalesLines)
    FROM Table
    GROUP BY Salesperson
    HAVING SUM(SalesLines) > 100

  • can you use a case statement to evaluate two different values based on your parameter? assuming the sum > 0 like this?

    declare @param varchar(3) = 'Y'
    SELECT Salesperson, SUM(SalesLines)
    FROM [MyTable]
    WHERE Salesperson IN (SELECT Name from SalesPersonTable)
    GROUP BY Salesperson
    HAVING SUM(SalesLines) > CASE WHEN @param = 'X' THEN 100 ELSE 0 END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell - Wednesday, August 9, 2017 4:24 PM

    can you use a case statement to evaluate two different values based on your parameter? assuming the sum > 0 like this?

    declare @param varchar(3) = 'Y'
    SELECT Salesperson, SUM(SalesLines)
    FROM [MyTable]
    WHERE Salesperson IN (SELECT Name from SalesPersonTable)
    GROUP BY Salesperson
    HAVING SUM(SalesLines) > CASE WHEN @param = 'X' THEN 100 ELSE 0 END

    This is perfect!  Exactly what I needed, but I couldn't quite get my brain there.  
    Thank you so much.  I really appreciate it, as well as the other answer.

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

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