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

1
votes

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.

2
mysqlreporting-servicesssrs-2012
why dont you just fill vareables with the parameter and use the vareables in your query instead of duplicating parameters? - Bacon
@Bacon im sorry, but the reason for that is i haven't thought of it. thanks for your recommendation. will try it now :) - Nii

2 Answers

2
votes

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.

1
votes

Use ? as variable in your script, then remember specific order of '?' then using specific order/arrangement of '?' parameters, setup them in the parameter tab after you add the MySQL script.

Ex. Script.

Select * from table1 where column1 = ? and column2 = ?

When you paste this on the dataset, each '?' will be mapped in the parameter tab. ? Parameter1 ? Parameter2

Change this to your own parameters then you're good to go.