Formula to find the maximum of a value

[matpj]

Hi folks,
I am using CR10.
I have several tables linked together in a report that gives me the total actuals booked against a project.
One of the tables is called BudgetHeader, which can multiple lines (a new line for each budget revision, including an inceremented 'projectrevision' number.
If a project has 2 revisions, it will contain two different budget amounts. and a projectrevision of 1 and a projectrevision of 2.

Currently it will drag both of these records into the results, which causes it to double up the actuals (as it is linked to the WIP table)

is there a way I can tell Crystal to only retrieve the project records in budgetheader that have the maximum projectrevision number?




Matt
London (UK)

 

[dgillz]

Assumming you are grouping by Project, there is no formula needed. Just right click on the field in question, select insert, summary, and make it a maximum() operation.

 

[lbass]

If you have the option of using a SQL expression, create the following
{%max}:

(select max(AKA.`projectrevision`) from BudgetHeader AKA where
AKA.`ProjectID` = BudgetHeader.`ProjectID`)

Substitute your exact table name for "BudgetHeader" and exact field names for "projectrevision" and "ProjectID". Leave "AKA" as is, since it is an alias table.name. Then go to report->edit selection formula->record and enter:

{BudgetHeader.projectrevision} = {%max}

The advantage of this method is that you will be able to use inserted summaries on fields from your other table, since there will be only one result from the BudgetHeader table.

If you can't do that, another approach is to go to report->edit selection formula->GROUP and enter:

{BudgetHeader.projectrevision} = maximum({BudgetHeader.projectrevision},{BudgetHeader.projectID}

...assuming you have a group on ProjectID. However, you would have to use running totals on your data, since the non-group selected data would still contribute to inserted summaries.

-LB