Post reply

Multi Value Parameter

  • June 3, 2010 at 6:03 pm

    #387079

    I have the following query as an expression in the dataset properties.

    ="SELECT A.AssetID, A.ComputerName, A.AssetNumber, A.SerialNumber, VIT.VendorItemTypeID, VIT.ItemTypeDescription AS AssetType, V.VendorName AS Vendor, VI.VendorItemDescription AS VendorItem, E.FirstName, E.LastName, D.DepartmentName, S.SiteName, FN.FloorNumber, Addr.StreetNo, Addr.StreetName, Addr.City, Addr.Postcode FROM Asset AS A INNER JOIN VendorItem AS VI ON A.VendorItemID = VI.VendorItemID INNER JOIN Vendor AS V ON VI.VendorID = V.VendorID INNER JOIN VendorItemType AS VIT ON VI.VendorItemTypeID = VIT.VendorItemTypeID LEFT OUTER JOIN Site AS S ON A.SiteID = S.SiteID LEFT OUTER JOIN Address AS Addr ON S.AddressID = Addr.AddressID LEFT OUTER JOIN FloorNumber AS FN ON A.FloorID = FN.FloorID LEFT OUTER JOIN Employee AS E ON A.EmployeeID = E.EmployeeID LEFT OUTER JOIN Department AS D ON E.DepartmentID = D.DepartmentID WHERE (VIT.VendorItemTypeID IN ('"+join(Parameters!VendorItemTypeID.Value,"','")+"')"

    But i get an error that says INCORRECT SYNTAX NEAR THE ')'

    It's referring to the IN CLAUSE.

    How can this be fixed?

    Alternatively, somone please show me how to implement a multi value parameter in SSRS 2008.

    I should point out that the Parameter VendorItemTypeID is of DATATYPE INTEGER

    Kindest Regards,

  • June 3, 2010 at 7:00 pm

    #1176265

    TriggerMe (6/3/2010)

    I have the following query as an expression in the dataset properties.

    ="SELECT A.AssetID, A.ComputerName, A.AssetNumber, A.SerialNumber, VIT.VendorItemTypeID, VIT.ItemTypeDescription AS AssetType, V.VendorName AS Vendor, VI.VendorItemDescription AS VendorItem, E.FirstName, E.LastName, D.DepartmentName, S.SiteName, FN.FloorNumber, Addr.StreetNo, Addr.StreetName, Addr.City, Addr.Postcode FROM Asset AS A INNER JOIN VendorItem AS VI ON A.VendorItemID = VI.VendorItemID INNER JOIN Vendor AS V ON VI.VendorID = V.VendorID INNER JOIN VendorItemType AS VIT ON VI.VendorItemTypeID = VIT.VendorItemTypeID LEFT OUTER JOIN Site AS S ON A.SiteID = S.SiteID LEFT OUTER JOIN Address AS Addr ON S.AddressID = Addr.AddressID LEFT OUTER JOIN FloorNumber AS FN ON A.FloorID = FN.FloorID LEFT OUTER JOIN Employee AS E ON A.EmployeeID = E.EmployeeID LEFT OUTER JOIN Department AS D ON E.DepartmentID = D.DepartmentID WHERE (VIT.VendorItemTypeID IN ('"+join(Parameters!VendorItemTypeID.Value,"','")+"')"

    But i get an error that says INCORRECT SYNTAX NEAR THE ')'

    It's referring to the IN CLAUSE.

    How can this be fixed?

    Alternatively, somone please show me how to implement a multi value parameter in SSRS 2008.

    I should point out that the Parameter VendorItemTypeID is of DATATYPE INTEGER

    If it's an integer, you don't need to delimit the values with commas AND single-quotes; just separate them with the commas.

    This also applies to the single-quotes with the parenthesis immediately after the IN, and at the end of the line.

    I think it should be:

    IN (" + join(Parameters!VendorItemTypeID.Value, ",") + ")"

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 3, 2010 at 7:09 pm

    #1176268

    WayneS,

    Thanks for the reply.

    I did what you said and I get the exact same error "Incorrect syntax near ')'

    Here is the updated query with the IN CLASUE as you recommended.

    ="SELECT A.AssetID, A.ComputerName, A.AssetNumber, A.SerialNumber, VIT.VendorItemTypeID, VIT.ItemTypeDescription AS AssetType, V.VendorName AS Vendor, VI.VendorItemDescription AS VendorItem, E.FirstName, E.LastName, D.DepartmentName, S.SiteName, FN.FloorNumber, Addr.StreetNo, Addr.StreetName, Addr.City, Addr.Postcode FROM Asset AS A INNER JOIN VendorItem AS VI ON A.VendorItemID = VI.VendorItemID INNER JOIN Vendor AS V ON VI.VendorID = V.VendorID INNER JOIN VendorItemType AS VIT ON VI.VendorItemTypeID = VIT.VendorItemTypeID LEFT OUTER JOIN Site AS S ON A.SiteID = S.SiteID LEFT OUTER JOIN Address AS Addr ON S.AddressID = Addr.AddressID LEFT OUTER JOIN FloorNumber AS FN ON A.FloorID = FN.FloorID LEFT OUTER JOIN Employee AS E ON A.EmployeeID = E.EmployeeID LEFT OUTER JOIN Department AS D ON E.DepartmentID = D.DepartmentID WHERE (VIT.VendorItemTypeID IN (" + join(Parameters!VendorItemTypeID.Value, ",") + ")"

    Kindest Regards,

  • June 3, 2010 at 7:22 pm

    #1176270

    You have a parenthesis mismatch... make the final ")" be "))".

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 3, 2010 at 7:29 pm

    #1176272

    WayneS,

    It works. Your modified IN CLAUSE is what the problem was. Thank you.

    Just one question though, in this case the parameter is of INTEGER DATATPE and as you pointed out the IN CLAUSE is to be,

    IN (" + join(Parameters!VendorItemTypeID.Value, ",") + "))"

    So, if the parameter was to be a string, what would the IN CLAUSE be? Is it what I originally had?

    Kindest Regards,

  • June 3, 2010 at 7:49 pm

    #1176276

    TriggerMe (6/3/2010)

    WayneS,

    It works. Your modified IN CLAUSE is what the problem was. Thank you.

    Actually, your original code only had one closing parenthesis also.

    Just one question though, in this case the parameter is of INTEGER DATATPE and as you pointed out the IN CLAUSE is to be,

    IN (" + join(Parameters!VendorItemTypeID.Value, ",") + "))"

    So, if the parameter was to be a string, what would the IN CLAUSE be? Is it what I originally had?

    Yes - and for dates also (well, with the two closing parenthesis!)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 3, 2010 at 7:59 pm

    #1176279

    Well done WayneS.

    Thanks for your help.

    Kindest Regards,

  • June 3, 2010 at 8:18 pm

    #1176283

    Glad I could help.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 8 posts - 1 through 7 (of 7 total)

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