SSRS - How to build a simple multi-column report?

Question

I am using SQL Server 2008 and I want to show 1 single field from a table in multiple columns in the report. Just like if I were to print labels. How can I achieve this?

Example:

Instead of printing:

Names Report

Andrea
Austin
Barbara
Bob
Bruno
Cathy
Chis
...

I want to print in columns (say 3 fixed columns):

Names Report

Andrea ---- Bruno ---- Darren
Austin ---- Cathy ---- Francis
Barbara ---- Chis ---- Gabriel
Bob ---- David ---- Gerald
....... ---- ....... ---- .......

Edit: If I run the report and click on "Print Layout" button, I can see multiple columns. Can I set this mode as default?

Answer 1

For Horizontal layout of labels...

One choice is to use the columns property on the report or body elements. This doesn't always display correctly On reportviewer. I've noticed that even if it displays correctly on your IDE and when you export to PDF. In the report viewer it will display only one column. Also it snakes the labels top to bottom then left to right.

One choice is to use a matrix and group on every 3 rows (if you want 3 columns).

This one is a little complicated.

My solution of choice is to put 3 vertical lists on the page. put the same label in each list. Return the row number in your dataset. Then just filter each list on modulo 3

For example

Result set

RIndex Fname
1 abe
2 burt
3 fred
4 george

Filter expressions

list 1 -> =Fields!RIndex.Value mod 3 = =1
list 2 -> =Fields!RIndex.Value mod 3 = =2
list 3 -> =Fields!RIndex.Value mod 3 = =0

Result

Abe Burt Fred 
George 

Answer 2

The method I use is a bit similar as what Vern suggested but differs enough to make it worth mentioning here.

You can combine the ROW_NUMBER with the modulo (%) operator directly in the query to fabricate the column number in which the record should get displayed. Here's an example that generates one while taking a group into account:

declare @numberOfColumns int = 4;

select dpc.EnglishProductCategoryName, dp.ProductAlternateKey
    , (ROW_NUMBER() OVER (
        PARTITION BY dpc.EnglishProductCategoryName
        ORDER BY dp.ProductAlternateKey) + @numberOfColumns - 1) % @numberOfColumns + 1
    as DisplayColumn
from dbo.DimProduct dp
inner join dbo.DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
inner join dbo.DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey;

To get this displayed I'm using nested tables which are then filtered on DisplayColumn.

Have a read through following article for all the details: Creating Multiple-Column Reports