Horizontal Tables
Question:
Does Reporting Services support horizontal tables (fixed rows and dynamic columns)?
Answer:
There is no native "horizontal table" report item, but simple horizontal tables can be simulated using Matrix.
Step 1: Add a matrix to your report
Step 2: Add static rows to the matrix
Right-click in the Data cell and select "Add Row". Repeat for the number of fixed rows you want.
Step 3: Drag fields into the Data cells
You'll notice that the design tool automatically wraps your field reference in the "First" aggregate (e.g. =First(Fields!City.Value)). Since you're doing this in the context of a matrix, the design tool is ensuring that the expression is meaningful even in the context of a subtotal or if the matrix is showing aggregated data rather than detail data. Since you're going to show detail data without subtotals, you technically could remove the aggregate expression (but don't, since then you'll start getting warnings which you'll need to ignore).
Step 4: Add a column grouping
Right-click on the column header and select "Edit Group". Enter this for the group expression: =RowNumber(Nothing). This will cause the matrix to give you one column per row of data. Since horizontal tables can end up rather wide, you probably want your table wrap around to the next "line" after a specific number of columns.
Step 5: Put the table into a list
Add a list to your report and drag the table into it
Step 6: Group by a number of rows
Right-click on the list and select Properties. Then click on Edit Details Group.
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/3)
This will cause the list to group on every three rows. So you'll get a separate table for every three rows.
Step 7: Adjust the group expression in the matrix
Edit the column group expression in your matrix and change the RowNumber argument to be the list group name.
For example: =RowNumber("list1_Details_Group")
A full working sample of horizontal table is attached.