Grouping on a Maximum Formula 1

[TerrieS]

I am using Crystal XI with a SQL Database. I am working on an existing report created by someone else - if need be I can create a view...but it seems like this is a simple request that should be possible.

It is a fairly simple report that shows employees grouped by their "Generic Position Title". Each employee has their own unique title and they have an assigned 3-digit Job Position Code - this relates to their Salary Grade, but also links to another table with a "Generic Position Title". Unfortunately, multiple 3-digit Job Position Codes may use the same Generic Position Title and the VP of one Department may be in a higher Salary Grade than the Deputy VP of another dept.

My problem is that in ordering the report, I want to use the Maximum 3-digit Job Position Code associated with each Generic Position title to drive the final order of display.

The program won't allow me to group by the Maximum 3-digit Job Position Code per Generic title. I have tried a Running Total and a Formula.

The report should end up looking like this

Executive and CEO
Bill Wonder (999) Executive and President

Deputy Vice Presidents
J Lone (900) Deputy VP
X Lowe (870) Deputy VP of Lowly Depts


Senior Vice Presidents
Jan B Walker (880) Senior VP of Operations
Brian B. Miller (880) Senior VP of Accounting
Fran Stevens (769) Senior VP of HR

Vice Presidents
A B Smith (799) VP of Operations
B C Johnson (799) VP of Accounting
F J Walker (669) VP of HR

Executive Assistant
S Barney (550) Executive Administrator

Currently, the report is grouping sorting and displaying only by the Generic Position Title names such that Executive Assistant is displaying ahead of the Senior VPs and such. Even though their 3-digit position code is lower than some VPs, the Deputy VPs should always be above them in the display list...based on the maximum (900) value that may be assigned to that Generic Position Title.

I hope that I am being concise and providing enough information. Any help here would be appreciated.
Thanks!

 

[lbass]

Insert a maximum on the job position code at the Generic Position Group level. Then go to report->group sort->"All" and select this summary as the group sort field->descending.

-LB

 

[TerrieS]

LBASS - Thanks for your time in responding! Unfortunately, I did have to add the SQL view, so the report is working okay.

Just for follow-up and out of curiousity...I had already created a the Maximum on the job position code at the Generic Position Group Level a) as a Running Total Field and also b) as a Formula. Report>Group Sort is dimmed and not available for selection. In Report>Group Expert - neither the Running Total Field nor the Formula is available to select to group on. Since other "Formula" fields were available, I changed the Formula above to make it equal to a single field, it was then available for Group selection. Then, I went back and edited the Formula back to being the Maximum on the job position code at the Generic Position Group Level. When I run the report - an error message that a non-recurring field cannot be grouped on pop-ups and no records are returned.

Again, I am okay with the report now, but wanted to give you feedback. Thanks again!

T.

 

[lbass]

You must place the field in the detail section->right click->insert summary->maximum and then the group sort becomes activated. As you found it, formulas or running totals cannot be used for this.

-LB

 

[TerrieS]

Thank you LBASS - I went back and retried with this methodology and it worked perfect! T.