CODEWITHSUNDEEP
reporting-services
Mohamed Aly Omer
Mohamed Aly Omer

Reputation: 153

SSRS - How to continue data to next column?

I need to report "Employee Name" And "Age", but I do not need to continue the names to the next page; I need to continue it to the next column.

It should look like this : 

_________________________________________________
| Employee Name | Age | Employee Name | Age     |
|_______________|_____|_______________|_________|

Can anyone send an report that manages this? I am using Microsoft report service.

Upvotes: 15

Views: 33788

Answers (2)

Chris Latta
Chris Latta

Reputation: 20560

You want a multi-column report by the sound of it.

On the Layout tab, right-click somewhere where there aren't any report objects and choose Properties. Click on the Layout tab and set the number of columns to how many columns you want on the page - in your example it would be 2. This will give you a column to lay out report objects on the left and a greyed-out column on the right that will be where your column on the left will repeat.

However, there are a couple of tricks here:

  • Your columns must fit on the page or you won't get any columns. For example, if your Layout settings are at their default of 1cm spacing and 2.5cm left and right margins, then your column must be less than 7.5cm on an A4 (21cm wide) page. Otherwise you will only get one column as two columns don't fit on the page.
  • The report renderer in your designer doesn't support multiple columns so you will only see one column in the designer. The HTML renderer also doesn't support multiple columns and you will only see one column for the report when deployed to the reporting services server and displayed as a web page. You need to output your report to a renderer that supports multiple columns, such as PDF or a printer, in order to actually see columns in a multi-column report.

Update - faking multi-column report using tables

Failing that, to get it to display the way you want independent of renderer is to use a hack with two tables, like you tried, but the trick is to hide the rows in an alternating manner.

To implement this hack, create two tables side by side that both point to your data set. On the Detail row of the first table, for the Visibility-Hidden property use the following formula:

=iif((RowNumber(Nothing) Mod 2) = 0, True, False)

On the Detail row of the second table, for the Visibility-Hidden property use the opposite formula:

=iif((RowNumber(Nothing) Mod 2) = 1, True, False)

This means the first table will print every odd row and the second table will print every even row, effectively giving you multiple column output.

For a generic solution for visibility of the tables when wanting more than two columns, use the formula:

=iif((RowNumber(Nothing) Mod TOTALCOLUMNS) = THISCOLUMN, True, False)

where: TOTALCOLUMNS is the total number of columns THISCOLUMN is the column number this table represents, zero-based (i.e. first column is 0, second is 1, etc)

For example, the table for the third column in a five column output has the Visibility-Hidden property set to:

=iif((RowNumber(Nothing) Mod 5) = 2, True, False)

Note that this is slightly different output than true multiple column as it reads left-to-right rather than down the page before wrapping to the right hand column as true multi-column does. However, it has the advantage of rendering properly in every renderer.

You could adapt this method to do true multi-column by working out how many rows you can fit on the page from the InteractiveSize-Height property and displaying that many columns in the left table then the rest in the right table and so on throughout the report but this might be fragile depending on renderer and changing page layout settings like margins. The method above is simple and effective.

Upvotes: 36

Esteban Lezama
Esteban Lezama

Reputation: 41

I am trying to do EmployeeName and EmployeeTitle. I tried to implement Chris' solution above. I would get the expected values in each table but they would be duplicated.

I did a little bit more research and I ended up with the following for row visibility.

For Even rows I used:

=iif(RunningValue(Fields!jobtitle.Value, CountDistinct, "EmployeesDataSet") Mod 2 = 0, True, False)

For Even odd rowsI used:

=iif(RunningValue(Fields!jobtitle.Value, CountDistinct, "EmployeesDataSet") Mod 2 = 1, True, False)

The rowgroup was on jobtitle.

Source: RowNumber for group in SSRS 2005

Upvotes: 0

Related Questions