15

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.

SarekOfVulcan
  • 1,348
  • 4
  • 16
  • 35
Mohamed Aly Omer
  • 153
  • 1
  • 1
  • 4
  • It's kinda hard to see what you want: what do you mean by "continued it to next column"? Also, it helps if you tell us what you've tried yourself so far and why it didn't work. – Jeroen Jun 04 '12 at 14:58

2 Answers2

36

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.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • 1
    Ia m using "SQL Server Business Intelligence Development Studio" it is A "SQL Report Service" i do not know where is "Layout tab" – Mohamed Aly Omer Jun 05 '12 at 10:35
  • The layout tab is the design surface for the report where you add reports objects (table, text boxes, etc) to the report bands – Chris Latta Jun 05 '12 at 23:41
  • maybe you didn't understand what i mean.. All i need that when i create this report the data showed in 12 page every page show have some rows every row have 1 emp i need to make every row have 2 emp not 1 emp to reduce pages to 6 not 12 so i create to tables for this table1 & table2 thay are the same but data should not be the same, the problem is the data repeated in 2 tables i don't need that i need table2 is the continued data in table1. lets say that page1 table1 last emp was number 20 i need table2 at the same page begin with emp number 21. i hope you understand and thx for your answers – Mohamed Aly Omer Jun 06 '12 at 14:46
  • oh i did what you say by adding another column and i got the result when i export it to PDF file, its okay but is there anyway that can show it in preview not when i export it? – Mohamed Aly Omer Jun 06 '12 at 16:18
  • Unfortunately not - the ability to display columns depends on the renderer and the report designer and the HTML renderer don't support column output. You have to print or PDF it to see the columns. There is a hack you can use however, which I'll add to my answer. – Chris Latta Jun 07 '12 at 01:34
  • 1
    hah I had to work late while to solve this particular problem. Saved me big time :) – Serve Laurijssen Feb 11 '14 at 19:02
  • hi i have tried this suggestion above but it does not work for me. I have a tablix inside a sub report that has two levels of sorting on the row group. when i export to pdf the second column is not used even though the widths are correct. is there a way to achieve this within a sub report? – Paul May 27 '15 at 14:55
  • I've never tried it on a sub-report but it shouldn't be any different. Try making the columns very thin to check the sub-report isn't adding a level of margin or padding that is causing the second column not to show. – Chris Latta May 27 '15 at 23:52
  • Maybe a bit late but, how can I make this work when there's an odd number of rows? Currently I have the left table with 21 rows and the right table with 20 rows. If I show the lines of these columns it will look weird because they are displayed as supposed to be one table? – Theun Arbeider Aug 18 '15 at 11:46
  • Which version of SSRS does this solution apply to? I am using SSRS 2008 R2, cannot find the layout tab in Properties – Newbie Aug 16 '18 at 01:47
  • @Newbie I don't have SSRS in front of me but I think you can set the columns in the Report Properties panel as well - click on a blank part of the design surface to have the Properties panel show the Report Properties and set the Columns property value. – Chris Latta Aug 16 '18 at 04:07
  • @Chris Latta I tried it and it works, printed in 3 columns. But when took that report and added it as sub report to another it renders in one column. Do you have any idea why it does not work when in sub report? do you have any suggestions? – Mr.No Jul 29 '19 at 06:17
  • @Mr.No Sorry, I've never tried it in a subreport, but another commenter was saying that column output didn't work in subreports either. Did you try the table-hack solution? That should work regardless. – Chris Latta Jul 29 '19 at 07:50
  • @Chris Latta if you mean this [link](https://picnicerror.net/development/sql-server/create-multi-column-lists-sql-server-reporting-services-ssrs-2011-10-03/) then yes. It got me what I wanted. – Mr.No Jul 30 '19 at 06:06
  • @Mr.No Well, I meant the solution I described in the answer above under the heading "faking multi-column report using tables" but that link looks like the same method. I'm glad you got it sorted. – Chris Latta Jul 30 '19 at 07:26
0

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

Community
  • 1
  • 1
Esteban Lezama
  • 41
  • 4