Too many columns creates unwanted extra width, how to achieve wrapping?

I have created a crosstab report that selects all the data correctly. There is a variable number of columns and rows. If all the data fits onto one page everything is fine. If there are more rows than will fit onto one page it is handled correctly. However if there are more columns than will fit onto the page they flow onto the virtual page and print on a separate sheet with no headers or footers, no page number, and of course the row headers are not printed.
How can I control the number of columns selected for the crosstab, and make the whole table repeat for the excess columns?
I am using Crystal Reports 10 and selecting my data from an Access database.

Many thanks,
Who is Participating?
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:

If you want your report to paginate horizontally, then check out the comments above.

To directly answer your question, "How can I control the number of columns selected for the crosstab, and make the whole table repeat for the excess columns?":

You have two options:

1)  Identify sets of columns values (10 values, for example).  Select data with these values only using the Records Selection Criteria.  Since you may have additional values, you would then need to create one or more subreports** , each of which has a specific set of values.  You could then format the container report and subreports, so that your overall presentation shows multiple vertical crosstabs, each of which has a finite number of columns.  This approach works, but may not look as professional as it should.  The reason is because one crosstab might only have 2 columns, but another might have 10.

2)  Create manual crosstabs.  This can be pretty complex and tedious, but it works well if you want a finite set of columns.  Since you want multiple vertical crosstabs, you'll also need to use subreports** for this solution.

To create a manual crosstab, you'll need to Group by your row field.  Next, create text column headers for as many columns as you want.  Then, you'll create a series of formulas, one for each column.  A sample formula might look something like this:

//@sample column formula
//assuming a numeric Qty field
  {table.field} = 'Column Value 1'

You'll place these formulas in the detail section of the report, in their respective columns (identified by the Headers you created).  You'll then right click on each formula and Insert Summary in the Group Footer for the Row Group.  Lastly, you'll hide the detail section.  Voila!  You have a manual crosstab.

**Since you want multiple vertical crosstabs, each of which with a finite set of columns, you'll need to use subreports.  Save the report you just created.  Then save the report again as a different name. Modify the newly saved report by changing the column values in the Record Selection Criteria (Option 1) or in the formulas and text headers (Option 2) to be equal to the next set of columns you need.  Save the new report.  Repeat for as many subreports as you need.  

Insert each new subreport into the Report Footer of the container report.  If it helps, create multiple Report Footer sections, one for each subreport.  This keeps things tidy.  If your report is parameterized, then you'll need to link the subreports, otherwise you won't need to.

check these out

Also read this :

Printing Cross-Tabs that span multiple pages
When you create a Cross-Tab that is wider or longer than the specified page size, the program automatically spans the printing across enough pages to accommodate the size of the Cross-Tab. In the Preview tab, a line will appear at each page break. For ease in reading, column headings will be repeated on subsequent pages. Row headings can also be repeated using the Keep Columns Together option.

To repeat row labels
Right-click the blank top-left area of the Cross-Tab and select Cross-Tab Expert from the shortcut menu.
The Cross-Tab Expert appears.

Click the Customize Style tab.
Select the Repeat Row Labels check box, if desired.
Crystal Reports lets you designate report objects that don't expand horizontally, such as text objects, field objects, OLE objects, charts, maps, lines, boxes, and so on, to be repeated on each additional horizontal page that a Cross-Tab creates. For more information, see Repeating report objects on horizontal pages.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

To repeat objects on horizontal pages
Right-click the report object you want to repeat.
On the shortcut menu, click Format Field, or the appropriate format option.
Note:    Formatting options are not available if the object you select is connected to the Crystal Repository. For information about modifying an object in the repository, see Modifying objects in the repository.

On the Common tab of the Format Editor, select the Repeat on Horizontal Pages check box.
For a line or a box, the Repeat on Horizontal Pages option appears on the Line or Box tab.

Click OK to save your changes.
Now, if a cross-tab or OLAP grid makes your report expand horizontally, the object you formatted is repeated on each horizontal page.

Hello BleedinFingers,

Given that you are using the first version of CR where almost all this is possible then what a shame ...

Right click on the object you want to repeat and 'format field'.
On the common tab, select 'repeat on horizontal pages'.

I don't know how you can work out how mant columns there will be; but if you want to creat a lopp then you will have to do it outside of CR in a VB (or similar) front-end.

BleedinFingersAuthor Commented:
Many thanks for all these wonderful responses.  I will get back to you all soon once I've had a chance to evaluate them.
BleedinFingersAuthor Commented:
Hi all,

Kurt, I think your response most directly addresses the question I posed, however ebolek's 2nd response was the most helpful.  Can you both help me a little further?

Where the crosstab has spanned additional pages horizontally and I print I get 4 pages.  The first page is labelled "pg 1 of 2" and contains the first rows of the table, the second page is not labelled and contains the spanned columns.  The third page is labelled "pg 2 of 2" and contains the later rows and a fourth page is not labelled and contains the last rows and spanned columns. (Hope that's not too convoluted.)

I think the whole report would work if I could get the spanned horizontal pages numbered (ie "pg 2 of 4").  I'm sorry that the context of this question is moving, this will be the only modification to the above question.

I have no help files or manuals, although the version I'm using is based upon version 10, I'm using a third party's implementation of it.

Thank you for your patience and help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.