January 5, 2009 at 9:28 am
I have a query wich returns the stock inventory but I'd like to return products in stock or not based on the clausule WHERE quantity >= 1 OR quantity = 0 using a report parameter. The dropdown would be: "In stock" "not in stock" and then, the report shows me only those products. Is it possible ? Do I need chaging in my query ? Is it possible to use filters in a table ?
thanks in advice.
January 5, 2009 at 10:55 am
There are a couple of ways you could look at this... Really the best answer is It Depends and you'll probably have to experiment to find the best solution for your environment.
If you use a stored procedure, you could do it in the query itself. You could create a parameter which passes a boolean value to the stored procedure you are calling to populate your report and have the stored procedure handle alternating between >= 1 or = 0 depending on the value it receives. This is the way I would recommend you do it particularly if you have a large list of items as the query will not need to return as much data and will only need to return what you need.
Alternatively, you could add a column to your query with a CASE statement specifying if it's in stock or out of stock then filter on that column.
-Luke.
January 5, 2009 at 6:05 pm
There are serval ways to achieve that.
1. if you want to use proc to handle that. create param called "In Stock" and "Outof Stock"
and use if else condition
IF param = InStock
Do the query with where condition stock>0
IF param = OutofStock
Do the query with where condition stock=0
2. In SSRS define the param manully, and use the transfer the param to your sql script
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply