RDLC Report- Error Rownumber() can't be used in filter

Question

I have two Tablix(tables) and one dataset. They are bounding with this dataset

   Serial No.  |      Name    
 ______________|________________
    SN0003     |      Beckham
    SN0005     |      Henry
    SN0012     |      Rooney
    SN0119     |      Ji Sung
    SN0200     |      Gerrard

In my report, I need to split two tablix It will display like below...

(It's "Left to Right" before "Up to Buttom")

   Serial No.  |      Name        ||    Serial No.  |    Name
 ______________|________________  || _______________|______________
    SN0003     |      Beckham     ||     SN0005     |    Henry        
    SN0012     |      Rooney      ||     SN0119     |    Ji Sung
    SN0200     |      Gerrard     ||                |

Left table is Tablix1 and Right table is Tablix2

I think use this FilterExpression for each tablix.

      = RowNumber(Nothing) mod 2 = 0 

for Tablix1(Left table)

      = RowNumber(Nothing) mod 2 = 1

for Tablix2(Right table)

But it's error this Message...

     "A FilterExpression for tablix ‘Tablix1’ uses the RowNumber function.
      RowNumber cannot be used in filters."

It's can't use Rownumber in Filter. What should I do? Is it possible if I don't edit dataset.

Answer 1

Is it possible for you to edit the dataset that you are reciving in the first place? You can add a ROW_NUMBER field to this by using

SELECT
  ...,
  ROW_NUMBER() OVER(ORDER BY SerialNo ASC) AS RowNum,
  ...

Then your returned dataset would look like

   Serial No.  |      Name      |  RowNum
 ______________|________________|__________
    SN0003     |      Beckham   |   1
    SN0005     |      Henry     |   2
    SN0012     |      Rooney    |   3
    SN0119     |      Ji Sung   |   4
    SN0200     |      Gerrard   |   5

And then you can do your filter on RowNum mod 2 = 0

Edit

Alternatively if you can't change the underlying dataset you can be clever using RunningValue and CountDistinct.

CountDistinct will give you the number of rows that match in the dataset, for each row (so, usually 1 assuming Serial Numbers are unique)

RunningValue will give you an increasing total of values across each row in a table... and with this you can add up all those `CountDistincts'.

Set the Row Visibility of your table to be something like

For Tablix1

=(RunningValue(CountDistinct(Fields!SerialNo.Value), Sum, "DataSetName") mod 2) = 0

For Tablix2

=(RunningValue(CountDistinct(Fields!SerialNo.Value), Sum, "DataSetName") mod 2) = 1