Cross-Tab grouping 1

[gav12345]

Hello,

I'm working in a CR10 report with a cross-tab object. The cross-tab rows specify firstly an animal group number and secondly an animal number. With my current data, there are 9 animal groups with 6 animals within each group (this is veterinary medicine in case you were wondering), and the animal numbers within each group aren't sequential. - The cross-tab rows look a bit like this:

An. Group An. No COLUMN DATA
1 13
9
6
24
31
32

2 12
11
26
17
19
3

3 10
16
7
2
4
20

What I am trying to do is keep the groups together, when the cross-tab moves onto the next page (at present the report will just cut group 3, for instance, in half). The problem is, its not possible to predict how many animal groups there will be, nor how many animals per group.

I've tried creating a group based on a formula and putting the crosstab into that group, but the problem is that whenever I use any function in the formula (for instance to count the total number of animals on the page), I get the error message 'Group specified on a non-recurring field'. - Remember I can't 'count' the animal numbers on the page because there aren't sequential.

Does anyone have any ideas on this? I'm not sure if I've been at all clear so please let me know if more info is needed.

Many thanks in advance for any suggestions.

Gavin

 

[lbass]

One approach would be to place the crosstab (maintaining the crosstab group structure as is) in a group inserted on {table.animalgroup}. Then create three formulas:

//{@reset}:
whileprintingrecords;
numbervar grpcnt := 0;
numbervar linecnt := 0;

//{@grpcnt}:
whileprintingrecords;
numbervar grpcnt := grpcnt + 1;

//{@linecnt}:
whileprintingrecords;
numbervar linecnt := linecnt + distinctcount({table.animalno},{table.animalgroup}) + 2//the "2" allows for the
//column label row and the column total row within each group

Place the both {@grpcnt} and {@linecnt} in the group header section. I'm assuming that the detail and the group footer section are suppressed.

Next, manually count the number of crosstab rows (including title and total rows that represents the maximum that can be displayed per page. Let's say the result is 32. Go to the section expert->group header->new page before->x+2 and enter:

{@linecnt} > 32

This will force a new page if adding the next group to the page would take the total lines over 32.

I included the {@grpcnt} formula in case you only want the column labels to appear once per page. You can click on one of the column labels->format field->suppress and enter:

{@grpcnt} <> 1

The problem with this is there are grid lines around the column labels. You can eliminate them entirely from the column label fields by going to the customize style tab of the crosstab->format grid lines-> select the label grid lines->uncheck "Draw lines". But then you will be left with empty space at the top of each group, so you might want to leave the recurring labels in after all.

If you want column totals for the entire crosstab, you will need to add a second crosstab to the report footer that uses the column field and the summary field, but no row field.

-LB

 

[gav12345]

Thanks lbass, that did the trick (and taught me a few things about cross-tabs as well). Great to come back to a solution after a couple of days off work, thanks again.

 

[gav12345]

lbass, hopefully you'll check this thread again. In your reply you mentioned the empty space / empty grid lines at the top of each group, where the column labels would be (2nd paragraph from the end). Didn't think this would be important but unfortunately it is. Do you know a way to get round this?

Thanks again, Gavin

 

[lbass]

I can't think of a fix for that. You could, of course, do a manual crosstab which would give you the control you need.

-LB

 

[gav12345]

lbass,

Ah hah! - The mystical manual crosstabs! I've heard a lot about these but have no idea how to implement them - could you point me in the direction of any documentation? Cheers, Gavin

 

[lbass]

There are a couple of FAQs on this, but basically you would insert a group on {table.AnimalGroup} and on {table.AnimalNo}. Then you would create a series of formulas, one for each column. You haven't identified what your column field is, but the formulas would take the form of:

//{@col1}:

if {table.col} = "Rabies" then 1 //or {table.amt} depending upon
//whether you are counting or summing a quantity

//{@col2}:
if {table.col} = "Heartworm" then 1//or {table.amt}//etc.

Then you would insert summaries (sums, not counts) on each formula at the group level and suppress the detail section.

You could try controlling your page breaks by checking "Keep group together" when you set up your groups. If that doesn't meet your needs, there is a way to count lines on a page and then set the page breaks to occur after a certain number of lines, with a formula to prevent a page break right after a group header.

-LB

 

[gav12345]

Thanks again lbass, I'll give that a shot. Gavin