3

I am selecting a TINYINT column from MySQL database, it will display properly in MySQL, but when I print the value in the report using SSRS, it will display the value as True or False instead of its original value in number. According to this answer, it evaluates the TINYINT which has value not equal to 0 to true, false otherwise, but in this case, I don't want it to be like this.

Since it is unable to convert or cast TINYINT to INT in MySQL (reference), is there any simple workaround other than changing the column datatype to INT?

UPDATE The column will not have 0 and 1 as value only, it might be any non-zero value which supports by TINYINT.

Value displayed in MySQL GUI Value displayed in MySQL GUI

Value displayed in SSRS enter image description here

Newbie
  • 1,584
  • 9
  • 33
  • 72
  • Go to textbox properties and select Number. What is selected there? – AB_87 Jul 17 '18 at 05:51
  • It seems the problem is with mysql giving SSRS a bit datatype instead of tinyint. SSRS treats tinyint like any other numeric data type. Only bit datatypes are presented as True/False values and unfortunately conversion doesn't work in this case – niktrs Jul 17 '18 at 05:54
  • Can you not cast the tinyint to int in the SSRS dataset sql? Or create a mysql view with the value cast to int and use that instead of reading the table direct? – MandyShaw Jul 17 '18 at 06:10
  • @AB_87 Tried both `Default` and `Number`, none of these working. – Newbie Jul 17 '18 at 06:17
  • @niktrs Your assumption make sense, but do you have any idea to deal with it? – Newbie Jul 17 '18 at 06:18
  • @MandyShaw the `Cint()` function will not work in this case, it will returns `-1`. Again, MySQL cannot use CAST for `TINYINT` – Newbie Jul 17 '18 at 06:20
  • The only option if possible is to fix it at the database level to make SSRS get a tinyint value data type instead of bit – niktrs Jul 17 '18 at 06:46
  • 1
    @Newbie can you cast tinyint to a character value in mysql? (Seems to me you are going to have problems with this column in other places than just SSRS, by the way.) – MandyShaw Jul 17 '18 at 08:12
  • 1
    Maybe what MandyShaw suggesting could be a good workaround. Convert to string then use Cint in SSRS to convert it to integer – niktrs Jul 17 '18 at 08:21
  • It works! Thanks for the idea. It is true that MySQL cannot cast the value to INT, but it actually can cast to `SIGNED` or `UNSIGNED`. So my case can be simply solved by `CONVERT(column, UNSIGNED)` since the column will only have non-zero value – Newbie Jul 17 '18 at 08:52

0 Answers0