SSRS Show table list in sequence

Question

I have a list of data, with sequence id 1, 2, 3, ..., 9

I want to insert them into SSRS report in the form of 3x3 table, arranged by sequence id

1   2   3
4   5   6
7   8   9

I should use list, or table, or matrix? Any solution for this arrangement? Thanks

Answer 1

I would typically use a matrix for this. You can calculate the row and column numbers for each row of data in your table and then use that for your row and column groups in the matrix.

Here's an example...

Note: You might not have or want the 'label' field so just swap this out for whichever field you want to show when we get to the report design.

Here's some sample data with the Row and Col calculated

DECLARE @t TABLE (Seq int, Label varchar(10))

INSERT INTO @t VALUES 
(1, 'AAAAA'), (5, 'BBBBB'), (10, 'CCCCC'), 
(20, 'DDDDD'), (50, 'EEEEE'), (100, 'FFFFF'), 
(101, 'GGGGG'), (102, 'HHHHH'), (210, 'IIIII')

SELECT 
    * 
    , ColN = (SeqOrder-1) %3
    , RowN  = (SeqOrder-1) / 3
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Seq) AS SeqOrder FROM @t) t

The inner query just assigns the SeqOrder as 1 thru 9. We then use this SeqOrder value to determine the row and column

This gives us the following dataset

Then we just add a matrix to our report

Next, drag the ColN field to the "Columns" placeholder, the RowN field to the "Rows" placeholder and the Label field (or whatever field you want to display) to the "Data" placeholder.

Run the report and you get this

Optionally, you can remove the first row and column (but NOT the associated group) and just leave the data cell.

Now when we run it, we get this.