asked on

Find maximum value and print only associated record

I am trying to create a report where the latest contract amendment is found and displayed as a letter along with its associated work order. The problem is that I get multiple rows of the same data and the nuimber of rows appears to be assoicated with the number of amendments, but not always.

Below is the general groups of data:

Agreement Work Order Amendment
E00001 1 none
E00001 2 none
E00002 1 none
E00002 1 1
E00002 2 none
E00002 2 1
E00002 2 2
E00002 2 3
E00003 1 none

I have a formula that converts the amendment number of a letter (i.e., 1=A, 2=B, 3=C, etc)

I want the report to display:

E00001
WO# 1
WO# 2

E00002
WO# 1A
WO# 2C

E00003
WO# 1

What can I do to make this happen?

Thank you experts in advance.

James0628

Group your report on Agreement and "Work Order", sort by Amendment, suppress the details, and output your WO# line in the "Work Order" group footer. Then you will only see the last (highest) value for Amendment for each "Work Order".

James

richardlh

ASKER

Works perfect ... only now some of the adjacent rows are shaded where I had alternating rows shaded instead of using lines to separate rows. I moved the formula

If RecordNumber = 1 Then
crNoColor
Else If Remainder(RecordNumber,2)=0 Then
crSilver
Else
crNoColor

from the "detail" section to the "group footer" section. What do I need to change?

Mike McCracken

You need to count the records in the group footer or try using GroupNumber instead of RecordNumber

To count the groups
Add a formula to the report header
WhilePrintingRecords;
Global NumberVar MyRecordCount := 0;
''

In the group header
WhilePrintingRecords;
Global NumberVar MyRecordCount;
MyRecordCount := MyRecordCount + 1;
''

In the group footer color formula
Global NumberVar MyRecordCount;
If MyRecordCount= 1 Then
crNoColor
Else If Remainder(MyRecordCount,2)=0 Then
crSilver
Else
crNoColor

or simpler
Global NumberVar MyRecordCount;
If Remainder(MyRecordCount,2)=0 Then
crSilver
Else
crNoColor

mlmcc

richardlh

ASKER

mimcc,

I put the formulas in the report header, gorup header for for work orders and group footer for work orders and no change. Here is an overview

Report Header: - formula above
Group Header #1 - Agreement Number
Group Header #2 - Work Order Number and formula above
Details - Supressed
Group Footer #2 - Fields to be displayed and formula above

Did I place the formulas in the wrong header/footer?

Thanks, RichardLH

Mike McCracken

Looks like the right places to me.

mlmcc

richardlh

ASKER

Should all formulas be in the report at the same time or one formula in a selected place? They all set Global NumberVar "MyRecordCount" and appear to cancel one another. Am I missing something, cause I still have some adjacent rows with the same shading and not alternating shading.

Mike McCracken

WHich sections show data?

How do you need it alternated?

mlmcc

richardlh

ASKER

The following sections show data:
RH
PH
GH1 - Agreement Number
GF2 - Work Order Number and Amendement Number and other associated data fields
I want to have alternate GF2 sections shaded

Mike McCracken

Put the counting formula in GH2

mlmcc

James0628

As you probably know, the reason that your original formula didn't work in the group footer is because you're checking the record number and there could be varying numbers of records between the group footers.

If you just want to "shade" alternate lines, I'd forget the counting and just turn a switch on and off.

Put a formula like the following in the report or page header to initialize your switch. You probably want it in the page header to get the same on/off shading pattern on each page.

// init_shade
Global BooleanVar shade;

shade := False;

// Only output an empty string
""

The "" at the end is so that the formula doesn't actually produce any output, so you don't have to worry about suppressing it or anything.

Then your shading formula in the group footer would be:

Global BooleanVar shade;

// Reverse the shade switch
shade := not shade;

// I check for "not shade" below because I just reversed shade
If not shade Then
crSilver
Else
crNoColor

That should work.

James

richardlh

ASKER

Sorry I haven't responded for a while, I am out of town and not able to access Crystal Reports. I will be back the week of August 4 and will work to close this question.

Thanks.
Richard

richardlh

ASKER

James,

I like you idea of not counting and just alternating the shading.

I followed you example above and put the page header formula and group footer (where the data is) formula in and no shading shows up. Am I missing something? No erros detected in the formulas.

Thanks, Richard

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

richardlh

ASKER

Thanks James,

It was the placement and logic that resolved the issue.

James0628

You're welcome. Glad I could help.

James