Multi Value Parameter

Question

Multi Value Parameter

TriggerMe

SSChampion

Points: 11752
More actions
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,

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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

TriggerMe SSChampion Points: 11752 More actions · Answer 2

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,

WayneS SSC Guru Points: 95460 More actions · Answer 3

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

TriggerMe SSChampion Points: 11752 More actions · Answer 4

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,

WayneS SSC Guru Points: 95460 More actions · Answer 5

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

TriggerMe SSChampion Points: 11752 More actions · Answer 6

Well done WayneS.

Thanks for your help.

Kindest Regards,

WayneS SSC Guru Points: 95460 More actions · Answer 7

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