Subreport Totals on Main Report

[Czeszyn]

Hello

I am trying to get my totals from my subreports, and dividing the number by 2 or 4, and put the new number on the main report.

for Operator/Trainer Productivity, I used:
=([rptEmployEvaluationOperator subreport].[Report]![OPTotal]+[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal])/2

for Auditor/Trainer Productivity, I used:
=([rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor2 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor3 subreport].[Report]![OPTotal]+[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal])/4

These both work if there are values in all totals subreports. When one of them might not have a total, I get an error message.

I tried to use :

=IIf([Orders].[Report].[HasData], [Orders].[Report].[txtOrderValue], 0)
=IIf([Orders].[Report].[HasData], Nz([Orders].[Report].[txtOrderValue], 0), 0)

=iif([rptEmployEvaluationOperator subreport].[Report].[HasData],([rptEmployEvaluationOperator subreport].[Report].[OPTotal]+=iif([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report].[OPTotal])/2

and the same idea for the second one. I tried both methods and did not work. I am not sure what I am missing.

Thank you for your help in advance.
Tony:banghead:

 

[dmattarn]

from what i can see you are missing the 0 portion of the argument. try this:
(IIF([rptEmployEvaluationOperator subreport].[Report].[HasData],
[rptEmployEvaluationOperator subreport].[Report]![OPTotal],
0)
+
IIF(Next report,field you want, 0))/2

 

[Czeszyn]

Thank you, that worked for the first one. The second one, do not know if I am missing something.

=(IFF([rptEmployEvaluationAuditor1 subreport].[Report].[HasData],[rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal],0)+IFF([EmployEvaluationAuditor2 subreport].[Report].[HasData],[EmployEvaluationAuditor2 subreport].[Report]![OPTotal],0)+IFF([EmployEvaluationAuditor3 subreport].[Report].[HasData],[EmployEvaluationAuditor3 subreport].[Report]![OPTotal],0)+IFF([EmployEvaluationAuditorTrainer subreport].[Report].[HasData],[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal],0))/4

This looks correct.
Tony

 

[dmattarn]

I think for the second one you used IFF rather than IIF

 

[Czeszyn]

Okay, so far so good. On the first one I noticed that it is not doing the math correct.

=IIf([rptEmployEvaluationOperator subreport].[Report].[HasData],[rptEmployEvaluationOperator subreport].[Report]![OPTotal],0)+IIf([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal],0)/2

example, 53% + 79% = 132 / 2 = 66%. For some reason the above is giving me 93%? Not sure why. The answer should be 66%.

Tony

 

[Czeszyn]

Hey, figured what I did wrong, forgot the ( ). It works now. Hey thank you very much. I do not know what I would do with this Forum, you are all so great and smart.

Tony

 

[dmattarn]

Glad I was able to help and that you figured it out. I'm relatively new to access and felt bad only asking questions, so having an answer is cool

 

[Czeszyn]

Hey, you are smart to me. Great job.

Tony

 

[dmattarn]

Thanks! don't forget to mark this as solved by using the thread tools menu!

Derek

 

[Czeszyn]

Hey, almost forgot.

=(IIf([rptEmployEvaluationOperator subreport].[Report].[HasData],[rptEmployEvaluationOperator subreport].[Report]![OPTotal],0)+IIf([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal],0))/2

Remember how I said that I divide this one by 2 and the other one by 4. Question is, is there a way for say if there is only 1 data listed, that it would not be divided by 2. Or in the other one, say there is only 3 numbers in the data, not four. Is there away for it to know what number to divide it by on how many totals are available??? I know that this one is a tough one. Not even sure if its possible.

Tony

 

[dmattarn]

you might be able to do it by creating 2 hidden controls to initially hold the value 0, and writing a similar IIF statement that would go something along these lines IIF(Report.HasData, control +=1,) so that it adds one for each valid value. then divide by the control in the main set of IIF statements.

this is a shot in the dark but it could work