'?' Parameters not Filtering Data in SSRS (MySQL ODBC)

Question

I have a report in SSRS that displays no results when a '?' parameter is specified. If I replace the '?' parameter with a string, it returns the desired results. I'm using a ODBC connection with a MySQL server. I have another report in the same project with a working '?' parameter.

Non-working SQL:

SELECT 
*
FROM
(    
SELECT 

firstname
,lastname
   ,CASE
        WHEN LENGTH(mdl_user.idnumber) = '1' THEN CONCAT('00000', mdl_user.idnumber)
        WHEN LENGTH(mdl_user.idnumber) = '2' THEN CONCAT('0000', mdl_user.idnumber)
        WHEN LENGTH(mdl_user.idnumber) = '3' THEN CONCAT('000', mdl_user.idnumber)
        WHEN LENGTH(mdl_user.idnumber) = '4' THEN CONCAT('00', mdl_user.idnumber)
        WHEN LENGTH(mdl_user.idnumber) = '5' THEN CONCAT('0', mdl_user.idnumber)
        ELSE mdl_user.idnumber
    END AS KUserID
    ,from_unixtime(GH.timemodified,'%Y-%m-%d %H:%i:%s') AS DateTaken
    ,YEAR(from_unixtime(GH.timemodified,'%Y-%m-%d %H:%i:%s')) AS YearDateTaken
    ,itemname
    ,ROW_NUMBER() OVER(PARTITION BY itemname ORDER BY DateTaken asc) AS `rn`
    ,finalgrade
    ,CASE
        WHEN finalgrade >= 70 THEN 'Pass'
        WHEN finalgrade < 70 THEN 'Fail'
     END AS IsPassing
    ,gradepass

FROM moodle_prod.mdl_grade_grades_history GH
    INNER JOIN mdl_user ON GH.userid = mdl_user.id
    INNER JOIN mdl_grade_items GI ON GH.itemid = GI.id

WHERE 
    rawgrade is not null

 ) as subq
WHERE
subq.KUserID = ?
AND
subq.YearDateTaken = ?

ORDER BY itemname

Here's the working WHERE clause

WHERE 
subq.KUserID = '032649'
AND
subq.YearDateTaken= '2018'

Any thoughts?

Everything looks right. Is the parameter data type "text" for both? — StevenWhite
I’ll be able to look at it Monday, but first parameters (the ID) is text, second (year) is an Int. — Evermynd
The only thing I can think of is making sure the data types are enforced. For example, try subq.KUserID = CAST( ? AS char) to force it to be handled as a string in case it's truncating to a number at some point and losing the leading "0". — StevenWhite

Evermynd Evermynd · Accepted Answer · 2019-01-24T21:36:54

UPDATE: I've found a sub optimal workaround that gives me the results I'm looking for but at the cost of performance.

I removed the parameters from the query itself, and instead used the built-in filters in SSRS. This is quite a bit slower because the query returns all results and then the report filters them.

I thought I would include this as an answer because it may be helpful for anyone else who has this same issue, isn't bothered by a long query run time, and just wants a quick fix.

I'm still looking for an solution to the actual problem, rather than a workaround.

'?' Parameters not Filtering Data in SSRS (MySQL ODBC)

1 Answers