Search code examples
mysqlreporting-servicesodbc

MySQL + SSRS | Stored Procedure only returns one single row

I'm working on several reports for SSRS written in MySQL via ODBC Adapter. For some reason, Stored Procedures only return a single row of data instead of an expected set of data.

Below is the same stored procedure when ran on an SQL Editor:

enter image description here

And below is the stored procedure's execution result when SSRS tries to run it (both on Query Designer and Report Viewer):

enter image description here

I have also set parameters properly as far as i can tell:

enter image description here enter image description here

Solution

  • so i wasn't able to find an exact answer as to why this happens on SSRS with MySQL via ODBC. What i was able to find was a workaround:

    by executing the command as an Expression rather than as a raw query via the Query Editor:

    enter image description here enter image description here

    Now the only caveat for this is that the DataSet Fields wouldn't be automatically generated, and that you have to plot them all manually. A good workaround for this is to first run a blank/null query with only the column names (i.e.: SELECT NULL 'column_name_1', NULL 'column_name_2') then later change the query source to Expression. The good thing about using expression is that you only need minor knowledge about how it works and it reduces the confusion with ODBC '?' Parameters.

    Cheers!