I need to calculate the total quantity of the field {Quantity} based on different names stored in a field called {Substitute}.

 

I have tried:

 

Sum ({Quantity},(where {Substitute}="Tube")

 

This does not work.  I get this error message:

 

"Running Total\Summary Field could not be created"

 

Could someone please send me the correct syntax to use for this formula?

 

Thank you,

Richard Scott

and make a Sum based on this group.

 

typically --> sum({qty},[groupname])

 

 

 

hope it help.

 

 

I have continued to tinker with the formula and the following formula comes close but it sums the total for all items:

 

If {ProductNumber.ProductNumber}='100049'

   then sum({LineItem.Quantity}) else 0

The problem with the above formula is that it SUMS all the Quantity for all items but I only want it to SUM the Quantity if the roductNumber=100049.

 

I think the formula should be like (doesn't work):

 

Sum({LineItem.Quantity}) where {ProductNumber.ProductNumber}='100049'

 

But I get the error message " The remaining text does not appear to be part of the formula" right before "where".

 

Does anyone know the syntax for a conditional SUM where it only SUMs the items the condition is based on?

 

Thanks,

Richard Scott

 

 

IMO the easiest solution is a series of Running Totals.

Right click on RTs and select New

Name=product100049 (or whatever)

field to summarize= {LineItem.Quantity}

Summary Type=SUM

Evaluate = Use a formula

Reset=Never

Place in Report Footer

 

You can make as many as you need for each product type and change the evaluate formula to match

 

That works!  Thank you so much for the direction.   I was stuck and had the pressure of needing this report complete by Thursday morning.

 

I appreciate everyone's input in helping me solve this issue.

 

Thank you,

Richard Scott