community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Span Column Groups Across Page

Moderator
Moderator
Created on

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:

 

ReportBefore.png

 

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:

 

ReportAfter.png

 

The records in this 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

Step1.png

 

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 the page. 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)

Step2.png

 

Step 3 - Add a Table tool

Now we will group the table by the PageID created in the previous step, giving us separate tables 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. 

Step3.png

 

Step 4 - Add another Formula tool

This is used in the next step to great a PageGroupID

Step4.png

 

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

Step5.png

 

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.

Step6.png

 

Step 7 - Add the Render tool

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

AllTools.png

 

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 function for 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 repetitive text, such as Region, County and Type. This can make the report easier to read.

Attachments