Multiple columns with Report Builder 3.0

[RRinTetons]

SQL Server 2008 R2, Report Builder 3.0

I want to create a report that shows two columns of data in multiple sets of columns across the page. I don't see a way to make a nice neat table to illustrate, so I'll use dots to line things up,

Jones, Tom.............HR
Miller, Alice..........Accounting
Ray, Richard...........IT
Jenkins, Fred..........Operations

becomes

Jones, Tom.............HR...............Miller, Alice..........Accounting
Ray, Richard...........IT...............Jenkins, Fred..........Operations

I see a number of articles that say it can be done, but they all seem to be describing steps in Visual Studio. Can this be accomplished using Report Builder?





-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[SteveAtVarlink]

Hi Richard

I have a not-so glamorous solution, but it seems to do what you want.

Add two rectangles where you want your columns positioned on the page.

Insert a list into each rectangle.

Add the columns required to each list, i.e. name, department.

Select the first list/tablix, and select Tablix Properties, then take a note of the Tablix name, and close dialogue.

Then right-click on the left edge of the row and select "Row Visibility", select "Show or hide based on an expression", then click fx to go the Expression screen.

Paste or type in the following

= IIf(RowNumber("Tablix1") Mod 2 = 0, True,False)

.. and click OK.

Do the same for the second rectangle/list with the follwing

= IIf(RowNumber("Tablix2") Mod 2 = 0, False,True)

(note the change of tablix name and reversal of the true/false options)

And hopefully voila!

So what we are doing is using the rownumbers of the dataset within each Tablix, testing if the rownumber is even, and returning a True or False to the row visibility to show or hide the row.

I'm not sure if the lists need to be in rectangles, but I read somewhere that it is a "cleaner" way to layout in the report.

I hope this helps, let me know how you get on.

Cheers, Steve