Using ">", "<", "<>" in a Parameter

  • Hello...rather new to using SQL and Reporting Services. I have a question about setting up and using report parameters.

    I have a report where I want the user to be able to filter for a certain order quantity. I have a parameter @Qty set up as an Integer parameter. However, I would like the user to be able to on the fly search for say quantities > 100, or quantities < 200. Is there a way in that parameter box to be able to key in something to the effect of ">100" or "<200" and get that to work with my SQL WHERE clause?

    Thanks a bunch!

    Tony

  • Add a drop-down to the report parameters with the various comparison operators in the drop-down. Then use that parameter in the Where clause.

    Where @Operator = '>' and MyColumn > @Value

    or @Operator = '<' and MyColumn < @Value

    or @Operator = '<=' and MyColumn <= @Value

    and so on, through the operators that you want to allow.

    Will that do what you need?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yeah, that would work and I did think about that...but I guess I was hoping for a way to sort of dynamically filter on the fly for that. I definitely can set up a dropdown parm with like ">", "=", etc, then use that with my parm to filter as needed, but I really would like to just be able to enter ">100" or what not if possible.

  • You can do that. Would be dynamic SQL, and you'd have to take all usual efforts to prevent injection attacks.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hmm...going to have to read up on my Dynamic SQL. 🙂 Thanks!

  • You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

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