How to sum a calculated field in report having several groups (1 Viewer)

wasim_sono

Registered User.
Local time
Today, 16:09
Joined
May 29, 2008
Messages
33
Dear all

I have a report with three groups namely Sub_Zone, Billing_month, and Empno.

I used a calculated field in detail section namely "Incentive".

I used a calculated field namely "txtRunInc" in Empno group to sum the "Incentive" field which is working properly.

I also used a calculated field namely "TnInc" in footer section of SubZone group to sum the calculated field namely "Text119" used in footer section of "EmpNo" group, but its not working. Please help me to solve the problem. :banghead:

I enclosed sample database for ready reference.

Thanks a lot.
 

Attachments

  • Report.zip
    600.3 KB · Views: 92

June7

AWF VIP
Local time
Today, 04:09
Joined
Mar 9, 2014
Messages
5,423
I do not see any Sum calcs in Empno group.

Aggregate functions must reference field not control.

Not linking Inc_Rates and IncTransactionB on Inc_Rates primary key field. If you don't save ID as foreign key then should not be designated as primary key.
 
Last edited:

wasim_sono

Registered User.
Local time
Today, 16:09
Joined
May 29, 2008
Messages
33
Dear June7

I'm sorry that I 'm using field instead of control.

There is a control namely "Text119" in footer section of empno group which shows the total for "Incentive" control.

I also used a calculated control namely "TnInc" in footer section of SubZone group to sum the calculated control namely "Text119" used in footer section of "EmpNo" group, but its not working.
 

June7

AWF VIP
Local time
Today, 04:09
Joined
Mar 9, 2014
Messages
5,423
I see you are using RunningSum instead of Sum(). This is seeing only the total of last employee group in each subzone group and summing those. Don't think I've ever seen a report like this. Perhaps do Sum() calcs of the raw data fields in subzone footer and replicate the =IIf([Total_Meter_Reads]-[Min_Meters]<0,0,[Total_Meter_Reads]-[Min_Meters]) calc in the subzone footer with its Sum() controls.

Alternatively, maybe do another query that uses QIncentiveBSummary as its source and do some more calcs in there (like the expression in red above) so those can be fields available to the report.

Haven't even looked at the Report footer calcs so see if same issue there.

You use LIKE operator in filter criteria but don't use wildcard, might as well use = sign. Should probably be:

HAVING (((Departments.Zone) Like [forms].[Incentivereports].[fzone] & "*") AND ((IncTransactionB.BillingMonth) Like [forms].[Incentivereports].[fibm] & "*"));

The report crashes in Report View when I scroll to the end. Very odd.
 
Last edited:

Users who are viewing this thread

Top Bottom