cross-tab summary question 1

[CRuser89]

Hello everyone,

I am currently using CR9 and have an existing cross-tab that has 4 rows (division, emply #, emply_name, emply_salary). The row is salary_year. There is a summary field on the bottom of the table that averages the salary. Is there a way for me to add a summary line after each division instead of averaging on all divisions? For example, I would like the following:

Div Emp# EmpName Salary Year
1999 2000 2001 2002
DIV1 12 Sally Jones 40,000 45,000 50,000 55,000
DIV1 10 Brooke Lee 35,000 40,000 45,000 50,000
Div Average 37,500 42,500 47,500 52,500
DIV2 11 James Le 10,000 15,000 15,000 25,000
DIV2 14 John Jones 50,000 55,000 60,000 70,000
Div Average 30,000 35,000 37,500 47,500

Currently is it like so:

Div Emp# EmpName Salary Year
1999 2000 2001 2002
DIV1 12 Sally Jones 40,000 45,000 50,000 55,000
DIV1 10 Brooke Lee 35,000 40,000 45,000 50,000
DIV2 11 James Le 10,000 15,000 15,000 25,000
DIV2 14 John Jones 50,000 55,000 60,000 70,000
Div Average 33,750 38,750 42,500 47,500

Thanks....

 

[lbass]

It looks like your column field is year, and that your summary is emply_salary. But do you really have three distinct row fields or did you concatenate the division, employee number and employee name? I wouldn't expect to see the division number repeated if it were a separate row field.

If it already is a separate row, go to the customize style tab, highlight the division field and uncheck "suppress subtotals". If it is a concatenation, remove it from the formula, and instead add it as your highest order group (drag it in the crosstab expert so that it is the topmost field).

-LB

 

[CRuser89]

Hi LB..thanks for your reply. I do have the division as a separate row and it is at the highest order group and the 'suppress subtotals' box isn't checked so I don't know why it isn't printing the grand total after each division...I'm beginning to think I did something wrong...

 

[lbass]

Is your division row based on a database field that results in Div1, Div2, etc.? Or is it a formula? If it is a formula, what is the content?

Also, please go into the crosstab expert and verify what your rows are, because a normal crosstab does not repeat the row for outer groups. In other words, we should only be seeing "Div" once per instance, not repeated as you are showing. This suggests there might be a suppressed outer group of some type.

-LB

 

[CRuser89]

HI LB - I'm sorry but after confirming some of the settings, it worked fine. My division is actually not repeating itself. I typed it up wrong in the example...thanks for your help!