Crosstab Totals

[Leighton21]

Hi All,

Is there a way to change the formula in the Totals Column of a crosstab report. Instead of a total for each row I woul like to show the average.

Rgards

 

[IanWaterman]

Which version do you have, in 8.5 and above there is a button on the cross tab expert - Change Summary. Not sure about previous versions.

Form there you can choose sum, count average and a whole lot more.

Ian

 

[lbass]

You could suppress the row totals by checking this in the customize style tab and then insert a second crosstab that has the same row field, but no column field, where you insert an average as your summary.

-LB

 

[Leighton21]

Thanks Ian and lbass,

lbass I tried your suggestion however the summarized field was blank without the column entr, am I doing something wrong

Regards

 

[lbass]

Please explain what your row, ,column, and summary fields are in the main crosstab and where your crosstab is placed. If you have it in a group, please state what the group field is.

-LB

 

[Leighton21]

Hi Lbass,

you actually helped me before on this, to quote from the previous post.

Use {table.product} as the row, and {table.date} as the column field. In the crosstab expert, highlight {table.date} after selecting it as your column field, and then click on "Group Options" and choose "print on change of month". Then add {table.SLSquantity} as your summary field, with sum as your summary.

So it looks like this

PeriodDate
ProductCode | SumofSlsQty

Regards

 

[lbass]

So I guess the main crosstab is in the report header or footer. Insert a new crosstab, using {table.productcode} as the row and average of {table.slsquantity} as the summary. The only way I think you would get a blank column is if all summarized fields are null. Also make sure you did not accidentally suppress the total column field in the second crosstab by right clicking on it->format field->common and uncheck "Suppress".

-LB

 

[Leighton21]

Thanks Lbass,

Worked a treat, by the way is there a way to link the two crosstabs (for printing) so that the second will not overlap the first. I.e the number of columns in the first crosstab can grow and when it does it overlaps the new crosstab.

PS Sorry to keep bothering you however can you use the summary field in the crosstabs to perform other calculations i.e. say if I had a sum (summarized field) can divide that say by a number?
Regards

 

[lbass]

To your first question--I don't think so, but you might consider making the average your first column on the left, since you know that won't change size.

To your second question--not that I know of.

-LB

 

[IanWaterman]

Leighton

Its a good job LBass read the question properly as I obviously missed the point.

However, you should be able to stop the main cross tab overlapping the second one by splitting the section in which the crosstabs appear. The section will grow with the main cross tab and then run the second cross tab in the new section. (Only works if one is set above the other, not if they are alongside)

eg if your cross tab is in a group footer, just right in the section in the grey area to left, in the menu you will see insert section below. This will split section for you.

Ian