March 30, 2010 at 8:55 am
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
March 30, 2010 at 9:14 am
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
March 30, 2010 at 9:23 am
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.
March 30, 2010 at 9:25 am
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
March 30, 2010 at 9:51 am
Hmm...going to have to read up on my Dynamic SQL. 🙂 Thanks!
March 31, 2010 at 6:19 am
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