Report total only calculating for the last group.

[Mj~]

I have a calculation field in a report with an IIF statement at the end of each group level, (grouping by date)

=IIf([STAGE_STAT_CD]="PROC",([CountOfREF_NUM]/[TOTAL]),"0.00%")

This is only working if the last record in the group meets the criteria (status = "PROC")

If any other status is listed as the last record the value is the default of 0.00%. I can't just sort the group ascending or descending because the status PROC isn't the first or last alphabetically.

Does anyone know how to make this work no matter where the status PROC is listed in the grouping?

[MMcCarthy]

I think you have a problem with your logic here. If this calculated field is in the details section it would work for each record. However if it is in the group footer it needs to preform calculation on all appropriate records and this would require some kind of aggregate function. You could try something like this.

 =Sum(IIf([STAGE_STAT_CD]="PROC",([CountOfREF_NUM]/[TOTAL]),0))
 

Mary

[Mj~]

Believe me, I am constantly questioning my logic. :o)

The function is in the group footer, and originally I started out with an aggregate function in front but kept getting syntax errors. The expression I originally typed was my first breakthrough. I will keep trying, but appreciate any feedback and ideas.

Thanks for your help!

Mj~

[MMcCarthy]

Believe me, I am constantly questioning my logic. :o)

The function is in the group footer, and originally I started out with an aggregate function in front but kept getting syntax errors. The expression I originally typed was my first breakthrough. I will keep trying, but appreciate any feedback and ideas.

Thanks for your help!

Mj~

If you're getting syntax errors post your code and we'll have a look.

Mary

[Mj~]

If you're getting syntax errors post your code and we'll have a look.

Mary

I think I figured it out, I needed a Domain Aggregate function. It works just fine now. Thanks for setting me in the right direction and for your willingness to help out!

This is what i ended up doing:

=DSum("[CountOfREF_NUM]","[qryPercentProcessed]","[STAGE_STAT_CD]='PROC'")

Then I just take that data to figure out the percentage I was looking for.

Thanks again!

Mj~