SAP Knowledge Base Article - Public

1204704 - The result of a formula is a multiple of the expected value.

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

Product

SAP Crystal Reports 10.0 ; SAP Crystal Reports 9.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2