Pass a parameter from SSRS to MySQL

Question

Here is the query for the dataset:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = @FieldManager

FieldManager is the parameter name. It's not taking the value of the parameter.

Please help me with the syntax to pass a parameter to a MySQL query.

Answer 1

If you are using a MySQL ODBC connector in SSRS you can't use named parameters but you should use ? instead.

So for your query it should look like:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = ?

That way you should get a parameter named Parameter1. If you have more than one question mark your parameters will be named Parameter1, Parameter2 etc in the same order as they appear in the query.

This isn't specific to MySQL but to any ODBC connection. See Binding Parameters ODBC for reference.