Dealing with Virtual pages in a crosstab

Hi

We have all had to deal with the ANNOYING problem with virtual pages when doing a cross tab. Virtual pages are created when the number of columns in your crosstab becomes so many that it spills over horizontally to form Virtual pages. These pages have no page numbers and are not counted when doing page totals etc. Also, they can look messy.

A colleague has developed a technique to deal with this problem. I thought I would post it in case it is useful.

The first thing you do is to create a formula as you see below, which you are going to group on.

Whilereadingrecords;
StringVar old_Header;
NumberVar col_count;
Local NumberVar grp;
Local NumberVar rem;
[color green]For each record in the recordset, check to see if the data going into the column header is the same as for the previous record. If not increment the column count by one and set the old_header variable to equal the current header
The crosstab uses the formula ({@ColumnHeader} as the column header. You could use a field in a database. I have used a formula to allow me to concatenate several fields. [/color]
If ({@ColumnHeader} <> old_Header) then
(col_count := col_count + 1;
old_Header := {@ColumnHeader});
[color green] Divide the column count by the number of columns you can fit across a page (10 in my case) -ie YOU MUST SET IT TO WHATEVER IS APPLICABLE IN YOUR CASE. Then determine the remainder. If this is greater than 0, that means you have more than 10 columns so you must add 1 to your grp variable. Since Grp is global, it will increment to 2, 3, 4 etc. [/color]
grp := Int (col_count / 10);
rem := Remainder (col_count,10);
If (rem > 0.0) then
grp := grp + 1;
grp;
[Color green] Since you are grouping on this formula, you will find that the group is created every 10th column. [/color]

If you wish your column headings to be in a particular order, you must modify your SQL query (eg add an "order by option" to your report) so that the recordset is returned in the order you require.

Simply place your crosstab in the group based on the above formula and that should to the trick.

I hope that helps some people out! It certainly has helped me.