Symptom
The tables used in the report have at least 1 relationship such as one-to-many at the database level.
A report is created off, for instance, 3 tables - A, B and C " which have the following links at the database level:
- table A is linked with 1 to many relationship to table B;
- table A is linked with 1 to 1 relationship to table C;
The
result of the formula used in Crystal Reports returns a value which
is the expected value multiplied by an entire number. If, for
example, the filed of the table A matches 5 records in the tables B
then the entire number should be equal to 5, so the formula is
multiplied by 5.
Cause
The one to many and one to one relationships do not allow a single result set without duplication.
Resolution
Using
3 formulas the right result will be returned.
For instance, a report contains three tables, A, B and C where table A is the Customer table, B is the Sales table and C is the LastYearTotSale table. The report is, also, grouped by {Customer.RegionID} and {Customer.CustomerID} and in the Details section is showing data from Customer and Sales tables.
In the Group Footer 1, which is based on the Region ID, a formula displays amount of Sales for each customer, such as sum({LastYearTotSale.amount},{Customer.RegionID})
display
the If Details section is showing data from Customer and
Sales tables, then the following formula
You may create the following formulas and place them in the relevant section as specified below:
1. "Reset" formula
// this formula has to be placed into either Report header or Group Header section related to the Region
WhilePrintingRecords;
Global currencyVar mRT := 0;
2. "Evaluate" formula:
// this formula should be placed into the
Group Header for the Customer ID
WhilePrintingRecords;
Global currencyVar mRT := mRT + {LastYearTotSale.amount};
3. "Display" formula:
// this formula displays the sum result, mRT, and it has to be put into the Group Footer related to the Region
WhilePrintingRecords;
Global currencyVar mRT;
Keywords
incorrect summary grandtotal 1 to many 1 to 1 relation result set single resultset duplication manual running total The result of a formula is a multiple of the expected value. , 7811991 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To