SSRS MySQL Multi-Value Parameters

0
votes

I am trying to use multi-value parameters in SSRS with MySQL but receiving strange results when selecting multiple values for a parameter.

Parameter Properties:

  • Name: parameter1
  • Data Type: Integer (I have tried "Text" but made no difference to the output)
  • Selected "Allow multiple values"
  • Available Values: (Label: 1, Value: 1) and (Label: 2, Value: 2)

Dataset Properties

  • Query type: text
  • Query: select column1 from table1 where id IN (?)
  • Parameter Name: ?
  • Parameter Value: =Join(Parameters!parameter1.Value, ",")

Result:

  • When selecting a single value: select column1 from table1 where id IN ('1')
  • When selecting multiple values: select column1 from table1 where id IN ('1,3')

Running on:

  • Windows 10 Pro (1909 - 18363.1316)
  • MySQL Community Server (8.0.22)
  • MySQL ODBC Connector (8.0.22) - Configured using Unicode Driver
  • Visual Studio 2019 (16.8.3)
  • Reporting Services Project (2.6.7)

Observation:

  • Parameter is integer, why is the value sent like a string?
  • Multi-value generated '1,3' when it should be '1','3' or 1,3

Any suggestions on how to resolve the strange multi-value behaviour?

1
mysqlreporting-services

1 Answers

0
votes

Keep your parameter properties the way it is now.

Try to use this in your Dataset Properties: Query > select column1 from table1 where id IN (@parameter1) and remove the Parameter Value expression.