SSRS: How to expend report details horizontally and vertically simultaneously to use minimum pages

Question

I am working with AX to create an RDP based SSRS report and I need help with a certain design requirement in SSRS report.

I need to show student data in header detail style but with header group based on Class Id and detail group based on Student Id.

And the SSRS shows detail data in simple tabular style like this..

But I want the detail data to expend horizontally first in four columns and then goes into next row for another four values and so on.. So that report would use minimum pages possible to show all the data.

Below is snapshot of the sample output I want to achieve:

Kindly suggest if there is any solution to obtain this.

Thanks.

Answer 1

You can use a matrix to force grouping by rows of 4 cells that you can put your current table with Student and Marks in.

Your data would need a ROW_NUMBER added to it in order for the data to be sorted into groups of 4. You could just add another column to your data ROW_NUMBER()OVER(ORDER BY STUDENT_ID) AS ROW_NUM.

Add a matrix to the report and remove the row and column headers so there's only 1 cell remaining (I had to remove them and add the Group back in and remove it again) and set it to the same DataSetName as your current table.

Row Grouping:

=INT((Fields!ROW_NUM.Value - 1) / 4)

Column Grouping:

=Fields!ROW_NUM.Value MOD 4 

This will sort your data to have groups of 4 with a column grouping of 0 on the first row.

Then size the cell to fit your table with the Student ID and Marks and put your table in the cell.

Answer 2

I'm not aware of a feature in the SSRS report designer that would allow you to do this. However, you could create a table to be used as data source for the report that has the necessary structure. The table would have 4 student id and 4 marks fields. This should allow you to create the desired design.