Reporting Services: Achieving Text Flow with Columns

In a previous post I described how to create a Table of Contents for various sections of your report, to make it easier to navigate.

One of the issues with the Index I created was that it was quite long. If your report has a lot of bookmarks, then your Table of Contents page is going to be pretty big. Well, it’s long. And thin.

What we really need to do is spread it out across the page a bit, so it can use some of the space over to the right hand side. Ideally, we want the table to support some form of Column Flow, so that the data in the rows seamlessly flowed into multiple columns, like a newspaper article.

Unfortunately, Reporting Services doesn’t do this out of the box. However, there’s a neat little trick which you can use to make it happen.

Just imagine for a moment the multi-column Table you’d like to see. It would have three separate columns side by side. And the headings of all your different Bookmarks would flow seamlessly in order, from left to right.

And that’s it. That’s how you can make it happen. Create three Tables, side by side, and fix the data each one has so that it looks right.

Keeping with my ideal of having the titles flow Left to Right, Top to Bottom for a moment (see at the end for an alternative solution), I’m going to continue using the data and tables from my previous post. Copy and Paste the original TOC twice, and lay them out next to each other:

If you Preview the report now you’ll see that we now have three identical Tables.

Our data is already sorted alphabetically by Title. What we want is for the first table to display the first, fourth, seventh row, the second table to display the second, fifth, eighth row, and the third table to display the third, sixth, ninth row.

We can do this quite easily using the Mod, or Modular method. By applying a filter to the row based on the Mod of the row number with respect to 3, we can limit results to the first, second and third tables with a Mod value of 1, 2 or 0 respectively.

First, alter the result SQL to include the row number (notice how we can’t cheat and use DISTINCT anymore!):

SELECT title,
ROW_NUMBER() OVER (ORDER BY title) AS row_num
FROM humanresources.employee
GROUP BY title
ORDER BY title

This gives us the row numbers to work with. Now, bring up the properties of the left-most table by right-clicking and selecting Properties. Under the Filter tab, add a new filter. The expression should take the form =Fields!row_num.Value Mod 3 and the rest of the values should look like this:

Now, repeat the process for the second table, changing the filter value to 2, and for the third table, changing the filter value to 0 (a Mod, or remainder, of zero will be every third row)

The final outcome is shown below: a nicely formatting set of three columns that looks like it flows:

Alternative Layout Configuration

Rather than the Left to Right, Top to Bottom approach described above, you may prefer to see a Top to Bottom, Left to Right flow where the items flow to the bottom of the first column before restarting at the top of the second. This can be done in a very similar way, but instead of using Row Number, we can use a SQL Server Function, NTILE to divide the result set into three:

SELECT title,
NTILE(3) OVER (ORDER BY title) AS row_split
FROM humanresources.employee
GROUP by title
ORDER BY title

Because we passed “3” to the NTILE() function it splits the results equally into three. You can then use Mod as described above to divide the results amoung the three tables.

Note: this would work perfectly well with 2, 4 or 40 tables. I only pick three as it lays out nicely across the viewable area of the screen – there’s no other reason it can’t be any other number.