Displaying corresponding value for a maximum record 1

[morechocolate]

I think what I need is pretty simple, but I am not sure of the best way to do what I need. Before I begin, I am using Crystal 8.5.

Say I am displaying the following, Group 1 is on Type and Group 2 is on Name:

Rate Name Store Type
8.5 Hats Barney's Father
7.5 Hats Zales Father
8.9 Hats Hat Shop Father

5.6 Ties Frank's Father
5.7 Ties Pete's Tie Shop Father
8.1 Ties Zales Father

I want to display in the Type header (group header 1) the highest rate and the corresponding name for the highest rate. The way I have it the rate of 8.5 displays and Hats displays, which is correct, however, that is only because of the pass rule. If I were to take my current formula and move it to the group footer, I will still have the correct rate, but I would display Ties as the name which is incorrect.

Would I have to use an array to solve this and if so, I am not that great with arrays. Having help on this one is appreciated.

Thanks

mc - I really need it (chocolate) today!!!

 

[lbass]

Create a SQL expression like:

[{%maxrate}:]
(select max(A.`rate`) from table A where
A.`type` = table.`type`)

Substitute your exact field names for "rate" and "type" and your table name for "table". Leave "A" as is, since it is an alias table name. Note that record selection criteria would have to be built into the expression.

Then create a formula for your detail section:

if {table.rate} = {%maxrate} then
{table.rate} & " " & {table.name}

Right click on this and insert a maximum and drag it into the type group header.

-LB

 

[morechocolate]

Thanks LB, as always for your help.

I am coming across a situation where, for this example, the names have the name rate. Using what you mentioned above, I end up only displaying Ties Father, when there is also Hats Father. I cannot use minimum because that returns a blank. Am I now looking at an array?

Thanks

mc

 

[lbass]

Using your initial example, you should get "Hats Father" not "Ties Father". Isn't that what you wanted? The highest rate per Type group?

-LB

 

[jollyreaper]

You know, I think this might be the same problem I'm looking at right now.

I'm running queries against our accounting system. The way it's setup, monthly totals are stored within the system. A CAP number (Cost Accounting Period) is used to tell you what period the total is from. Periods are one month long and change at the end of every month.

If I need to know what the balances are for this month, I just need to find the total that corresponds to the highest CAP in the system. So a select max on the CAP should do it?

 

[morechocolate]

Thanks LB - I suppose I oversimplified what was needed, thus not including the fact that each can have the same rate, so I will try again, but this time using an example that is closer to the actual data.

(1) The groups

GR1 - Interest Type
GR2 - Loan Type (i.e. commercial or residential)
GR3 - Account Name
GR4 - Loan Number
Details - Loan Number, Interest Type, Account Name, Interest Rate, Property Name

(2) What is needed

For GR1 and GR2, I need to display the maximum and minimum interest rate with it's corresponding Account Name. The oringial answer you provided works great, if the rate for the account names is not the same. In my case, there are two account names that have the same loan number and therefore, they have the same rate. As such, I need to display both accounts names. In the end I should have something like the following if more than one account has the same rate.

Maximum 12 Brewster
Minimum 4.5 Brewster
Minimum 4.5 Kinsey

Thanks

mc

 

[lbass]

You could do collect my earlier formula in variables, but with multiple groups and multiple summaries, maybe it would be simpler just to insert subreports in the group footers for interest type and loan types. In the interest type subreport (linked by interest type) just group on interest type and then go to report->selection formula->GROUP and enter:

{table.rate} = maximum({table.rate},{table.interesttype}) or
{table.rate} = minimum({table.rate},{table.interesttype})

You would display only the rate and name field in the detail section of the subreport. Suppress all other sections. Only the rows that represent the maximum or minimum will display. If you want to label them then create a formula for the detail section:

if {table.rate} = maximum({table.rate},{table.interesttype}) then "maximum" else "minimum"

Create the same subreport for the loan type group footer, but link on interesttype and loantype. In the subreport, group only on loantype, and then enter the same formula as earlier, just changing the group condition to loantype.

-LB

 

[morechocolate]

Thanks again LB. I went with the subreport option. Works like a charm.

Your help and prompt responses are more than appreciated.

mc