Posted by: Terry Nederveld | February 15, 2010

How to do a Column Layout in SQL Reporting Services.

For this post I am using the following:

  1. Visual Studio 2008
  2. SQL 2008 Reporting Services
  3. The Query for the reports dataset is as follows:

DECLARE @tmp TABLE(
Display VARCHAR(25)
)


INSERT INTO @tmp (Display) VALUES ('Test 1')
INSERT INTO @tmp (Display) VALUES ('Test 2')
INSERT INTO @tmp (Display) VALUES ('Test 3')
INSERT INTO @tmp (Display) VALUES ('Test 4')
INSERT INTO @tmp (Display) VALUES ('Test 5')
INSERT INTO @tmp (Display) VALUES ('Test 6')
INSERT INTO @tmp (Display) VALUES ('Test 7')
INSERT INTO @tmp (Display) VALUES ('Test 8')
INSERT INTO @tmp (Display) VALUES ('Test 9')
INSERT INTO @tmp (Display) VALUES ('Test 10')
INSERT INTO @tmp (Display) VALUES ('Test 11')
INSERT INTO @tmp (Display) VALUES ('Test 12')

SELECT * FROM @tmp

This post will guide you through how to get a columnar layout with SSRS like the images below.

 

Accomplishing this is actually an easy process once you can figure out the solution. I hope this post will help you spend less time than I had to. After you have your Dataset created on your report. All you need to do is drop a Matrix control onto the report. The magic happens in the actual row and column group. Let’s start with the Row group. You will need to right click on the row and open up the Row’s group properties. In the Group Expressions you will need to add a “Group on” if one doesn’t already exist. You are going to add the following expression to that group on.

=Ceiling(RowNumber(Nothing)/2)

The following are the descriptions that Microsoft wrote for common functions we used above.

Ceiling: Returns the smallest integer greater than or equal to the specified double-precision floating-point number.
RowNumber: Returns a running count of all rows in the specified scope.

What the code above does is it takes Dataset and basically looks at each of the rows and gives it a number. If you just did RowNumber(Nothing) it would count up 1,2,3,4, etc. for each row. The number you divide by is the amount of columns you want in the layout. For this example we will start with 2 and later I will show you the group expressions to accomplish the three column. Now this alone will not give you the results you desire. You now have to look at the Group Properties for the column group. This time you will right click on the column and go into the Group Properties for this. Just like the row you will need to add a “Group on” expression if one doesn’t exist and inside it you will need to add the following expression.

=(RowNumber(Nothing) Mod 2)

The description given by Microsoft for Mod is: Divides two numbers and returns only the remainder. Most developers have used the Mod function in the past when they wanted to alternate the color of the rows in a table they were creating with code. It is a little different then changing the color of the row this time it is telling which column the data should be in. This is how we get the layouts show in the images above. Very simple and it works.

Now I said that I would show you how to get the three column layout. Well, it is really easy and you should have already figured it out. Just go into the Group On expressions and change the two to a three.

=Ceiling(RowNumber(Nothing)/3)
=(RowNumber(Nothing) Mod 3)

Advertisements

Responses

  1. Awesome! Exactly what I was looking for. I was using this method to present a dynamic number of gauges, but I wanted to limit them to 3 a row.

  2. Thank you! I fought with getting my rows to stack properly for hours. The Mod function was the trick that made a difference.

    Chuck

  3. I have been looking for a solution like this for the last two months. Thanks so much.

  4. Thanks Terry. This was very helpful. H

  5. I cannot get this to work. Can someone help me!

  6. I’m very happy to find this great site. I need to to thank you for your
    time for this particularly wonderful read!! I definitelyy really liked every
    art of it and i also have you book-marked to look at new
    things on your web site.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: