Crosstab Running Off The Page

If you've started down the path of using the powerful, yet annoyingly limited power of the crosstab objects and are using subreports then you have no doubt ran head first into data getting orphaned off onto imaginary pages to the right (unless you are returning small amounts of data).

A bit like this:

The easiest way I found to defeat this particular beasty is to get the sql to work out columns and sections that the data belongs to using the ROWCOUNT() OVER function (new in SQL Server 2005). Once you've nailed this you can group by section numbers and voila - no more overrunning.

An example I here you cry!  

Let us imagine a database with sample testing information.  We select one at random, let's call this Dougal.  Dougal has 248 records.

select 
s.original_sample
, s.status
, s.sample_number
from 
sample s
where 
s.project = 'DOUGAL'
order by
s.original_sample
, s.status

This gives us:

Now let us work out the row count for each record.

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
from
sample s
inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
from
sample s1
where s1.project = 'DOUGAL' group by s1.original_sample ) as A on s.original_sample = A.original_sample
where s.project = 'DOUGAL'
order by s.original_sample , s.status

Which now produces :

Now we can work out which column each of the items will go in for each section (not really needed here - but may come in handy elsewhere).

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
, a.myColCount
from
sample s
inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) - 10 *((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as myColCount
from
sample s1
where
s1.project = 'DOUGAL'
group by
s1.original_sample
) as A on s.original_sample = A.original_sample
where
s.project = 'DOUGAL'
order by
s.original_sample
, s.status

Giving:

And finally we now derive the section numbers

select
s.original_sample
, s.status
, s.sample_number
, a.myRowCount
, a.myColCount
, a.mySecCount
from
sample s inner join (
select distinct
s1.original_sample
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) as myRowCount
, ROW_NUMBER() OVER (ORDER BY s1.original_sample) - 10 *((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as myColCount
, ((ROW_NUMBER() OVER (ORDER BY s1.original_sample)-1)/10) as mySecCount
from
sample s1
where
s1.project = 'DOUGAL'
group by s1.original_sample ) as A on s.original_sample = A.original_sample
where s.project = 'DOUGAL'
order by s.original_sample , s.status

Giving us:

The rest is plain sailing from here on in.  Open up the Group Expert in the Reports menu and then set up 'SecCount' (or whatever you have called your sections) as a group.

Now add your crosstab to the footer of this group and with a minimal amount of tweaking you should have something like:

A nicely wrapping crosstab set to 10 columns.  Of course changing the figure changes the number of columns displayed, so you have flexibility over font, size and layouts.


Hope this helps someone!