Report Services - SQL WHERE IN CONDTION?

Under the parameters tab of your dataset properties, setup the parameter as Name = @CategoryString, Value = Parameters!CategoryString.Value.  Then in your query, change your WHERE statement to this:

WHERE     (Year = @Year) AND (MonthNumberOfYear = @Month) AND (Category IN (@CategoryString))

HTH,

Greg

I tried that same code, but it did not work for me.  Let say @CategoryString is Clothing or Tools it works fine, but if @CategoryString is ToolsClothing nothing is returned.  Any thoughts why

When you fill in that variable from your ASP.NET page can you add ' OR ' between each of the individual variables?  I'll think about it more over the weekend and let you know if I think of anything new on Monday.  

Greg

Still nothing.

 I have a non-query report parameter defined as follows:

Name: CategoryString
Prompt: CategoryString
Data type: String
Allow blank values checked
Non-queried label: Blank
Non-queried value:  Parameters!CategoryString.Value
Default values: 'Clothing'

Here is my sql:

SELECT     *
FROM         vProductProfitability
WHERE     (Year = @Year) AND (MonthNumberOfYear = @Month) AND (Category IN (@CategoryString))

When I run the query and enter:

@Year: 2003
@Month: 1
@CategoryString: Bike

The sql returns all the January, 2003 records with a category of Bike.  However when I enter:

@Year: 2003
@Month: 1
@CategoryString: BikeClothing

No rows are returned.   I'm trying to return both the Bike and Clothing category records.  Any ideas on what I might be doing wrong.  I appreciate the help so far.  This is probably something simple I'm over looking.
 
Paul

I tried setting @CategoryString to Bike,Clothing and 'Bike','Clothing' in parameter field prompt when running the sql from the data tab.  No results were returned.  However this SQL does return the desired results.  What am I missing?

 SELECT     *
FROM         vProductProfitability
WHERE     (Year = @Year) AND (MonthNumberOfYear = @Month) AND (Category IN ('Bike', 'Clothing'))


Here is the code from my program:

  Dim URL As String
            URL = "http://localhost/ReportServer?/Custom Reporting/Product Profitability"
            URL += "&rs:Format=HTML4.0&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
            URL += "&rc:HTMLFragment=true"
            URL += "&Year=" + YearListBox.SelectedValue
            URL += "&Month=" + MonthListBox.SelectedValue
            URL += "&Category=" + GetParameterMultiSelect(CategoryListBox, "CategoryString").Value

            'Pass the URL as a Web request
            Dim request As HttpWebRequest = WebRequest.Create(URL)
            request.Credentials = System.Net.CredentialCache.DefaultCredentials
            Try
                'Write the response into a byte array
                Dim response As HttpWebResponse = request.GetResponse()  "THIS IS WERE I'M FAILING
                Dim ReceiveStream As Stream = response.GetResponseStream()
                Dim read(512) As Byte
                Dim bytes As Integer = ReceiveStream.Read(read, 0, 512)

                'Clear the text of the Label control and then write the bytes
                'from the byte array into the control's text property after encoding as UTF-8
                Label1.Text = ""

                While (bytes > 0)
                    Label1.Text += encode.GetString(read, 0, bytes)
                    bytes = ReceiveStream.Read(read, 0, 512)
                End While
            Catch ex As Exception
                Label1.Text = "Error retrieving page"
            End Try

This is what the URL value looks like when execution fails:
"http://localhost/ReportServer?/Custom Reporting/Product                        Profitability&rs:Format=HTML4.0&rs:Command=render&rc:
Parameters=false&rc:Toolbar=false&rc:HTMLFragment=true&Year=2003&Month=1&Category='Bike','Clothing'"

This is the error message:

The remote server returned an error: (500) Internal Server Error.

I still think this is something stupid I'm doing, but for whatever reason I cannot seem to put my finger on it.