From a recent email:
I am creating a Crystal report where I want a count of invoices that have backorders. I have tried creating a formula field that returns a 1 or 0 if the sum of quantity backordered for an order is greater than 0. This works fine but I cannot sum this field for the report to give me a total count. Can you think of a way to do this?
Answer:
The answer is to use either a ‘count’ aggregation, or a ‘running total’, depending how the report is set up.
The easiest is a ‘count’, you just right click on a field and choose ‘insert summary’. Then under ‘Calculate this summary’, change ‘sum’ to ‘count’. Carefully choose the ‘summary location’ If you’re in Crystal 11, it’s much easier to check ‘add to all group levels’ and then delete the ones that you don’t need.
Depending on how you have the report set up, the 'count' may not work (if you are displaying invoice detail, you don't want the count of all those rows). in that case, you'll have to use the 'running total' functionality. If you have to do that, let me know, I'll walk you through it.
I don't see a way to make the 'running total' option work if you are using tables brought into Crystal, but if you base the Crystal report on a stored procedure, you can do it that way. You need an ORDER level field that indicates if a LINE ITEM in that order has a backorder - like this:
select h.sopnumbe,
h.soptype,
l.itemnmbr,
l.quantity,
l.qtytbaor,
case when bo.sopnumbe is null then 0 else 1 end as hasBackOrder
from sop10100 h with (nolock)
join sop10200 l with (nolock) on l.sopnumbe = h.sopnumbe and l.soptype = h.soptype
left join (select sopnumbe, soptype
from sop10200 with (nolock)
where qtytbaor <> 0
group by sopnumbe, soptype
) as bo on bo.sopnumbe = h.sopnumbe and bo.soptype = h.soptype
where
h.voidstts = 0
and h.soptype = 2