Search code examples
mysqlreporting-servicesssrs-2012

MySQL + SSRS (SQL Server Report Builder) Dataset/Query Parameters not working

I am currently using SQL Server Report Builder 2012 and is connected to my MySQL Database via an ODBC Connector and as far as base report goes, all is well.

However, i can't seem to make the Query/Dataset Parameters to work the way its supposed to be. I have multiple parameters to my query as you can see below (obviously table and column names are removed):

enter image description here

Now the problem is, if i leave the parameters as is (@OfMonth, @OfDay, @OfYear) - SSRS does not seem to bind the actual values passed from the Report Builder's Parameter Object which i am confident to day that i have associated properly. Not even on the preview/query designer.

However, if i change all @XXXX parameters to simple ? placeholders, it magically works. This poses as issue specially with queries that have multiple parameters.

This is the Report Builder's screenshot of my Work in Progress: enter image description here

i have no issues defining the 3 Parameter object under the Parameter Node. However, if i try to bind them under Dataset Properties with specific @XXXXX placeholders, it doesn't work, and the report fails to generate data. But if i replace all @XXXXX with ? (all of them are just ?, therefore duplicates), the parameter gets passed and the report loads.

Solution

  • For ODBC connections, you do need to use a ? instead of named variables.

    dba.stackexchange | Pass Parameter - SSRS to MySQL

    The Parameter Name field on the Dataset Properties should auto-fill with Parameter1, Parameter2,... to match your query but doesn't always seem to work. You can try adding them manually. Since it worked without the name for you, I assume the name doesn't actually matter.

    When I would have a parameter used multiple times, I would declare a new one in the query and reuse the new one as @Bacon mentioned: 

    DECLARE @OfMonth INT

SET @OfMonth = ?

    This way you only have to match them once at the beginning of your query.