How To: Span Column Groups Across Page

[DanC]

Have you had to create a report for a large dataset that, while having many rows, only has a few columns? If yes, then you are familiar with the following look:

A narrow strip of data and a lot of whitespace. This does not make for the nicest looking report, nor is it the best use of space and paper. With a few tools and utilizing some basic math functions, we can create a report that looks more like this:

The records inthis report span across the page in multiple groups of columns. So, how is this done?

Here are the steps:

Step 1 - Add a Record ID tool after your data input

Step 2 - Add a Formula tool

Here we will create a PageID column by using the Ceil function and dividing the RecordID by the number of rows that will fit on thepage. In this example, the maximum number of rows that will fit given the font size, layout and margins used is 42.So, for as long as the PageID is less than or equal to 42, the PageID will remain 1. Then it will jump to 2 for the next 42 records, 3 for the next, and so on and so forth.

Ceil([RecordID]/42)

Step 3 - Add a Table tool

Now we willgroup the table by the PageID created in the previous step, giving us separatetables for each set of 42 records. You will need to make adjustments to the table and column widths so that the groups of columns fit across the page. In other words, your table width in inches multiplied by the number of groups of columns must be smaller than the page width.

Step 4 - Add another Formula tool

This is used in the next step to great a PageGroupID

Step 5 - Add a Multi-Row Formula tool

Here we are using the Mod function to add 1 to the PageGroupID each time the PageID is evenly divisible by 3 (has no remainder).

IF Mod([Row-1:PageID],3)=0 Then [Row-1:PageGroupID] +1 Else [Row-1:PageGroupID] EndIf

Step 6 - Add a Layout tool

In the Layout tool we will group by the PageGroupID in order to keep each group together on one page and we will configure each group horizontally. So, in essence, this is inserting each member of the group, starting from the left to the right, until all three members of the group are inserted.

Step 7 - Add the Render tool

In the report data section, make sure to choose "Insert Section Breaks Between Records" as the Separator.

Important Note

If your text is too long and wraps within a cell, this will throw off how many rows can fit on the page and likely ruining the format of the entire report. You can either adjust the width of the cell or use the Left formula function to trim the column to a length that you then verify will fit. You can even possibly use the Length functionfor each column and use the Row Rule in the Table tool to make the text size smaller depending upon the length of the text.

This workflow is attached for you to download. Also included is a separate stream that is similar to above, but I have utilized several Multi-Row Formula tools to remove repetitivetext, such as Region, County and Type. This can make the report easier to read.