MySQL cannot use ":=" in SSRS

Question

I want to assign an index to each row of the query result, I am following this method to achieve that.

This index number is not for displaying purpose, if it is only for displaying, using RowNumber() in SSRS is enough. Instead, I will be using that index number for filtering purpose. Please also note that SSRS cannot use RowNumber() in Filter Expression.

For example, I want the results with index < 10 to be displayed in tablix 1, index >= 10 will be displayed in tablix 2, something like that.

My query works fine in MySQL, until I run it with SSRS, it comes out the error. Looks like SSRS does not support the := operator in @curRow := @curRow + 1 AS row_number. If I remove the colon : from the operator, the error gone, but row number does not increment.

Why SSRS does not support the := operator? is there any other workaround?

Answer 1

I have found a workaround for this case from here

DISCLAIMER:

1. Does not work on older versions of mySQL It is based on using variables that only reset when time changes.
2. If the function is invoked in 2 separate places in the same microsecond(1 millionth of a second), the output will not be as expected. The variable will not reset, the output will not start counting from 1.
3. If you run the function more than once in the same query, it will increment every time it is run, so possibly multiple times per row.

The workaround is to create a MySQL function to generate the row number:

DROP FUNCTION IF EXISTS rownum;
DELIMITER $$
CREATE FUNCTION rownum()
  RETURNS int(11)
BEGIN
  set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1);
  set @ranklastrun=CURTIME(6);
  RETURN @prvrownum;
END $$
$$
DELIMITER ;