I have the following formula that pulls information from a sub report onto my main page (a summary page) .....this works fine except within these sub codes I have casenumbers, and some casenumbers appear in more than one SubCode. As a result I am trying to figure out how to change this formula to do this count but only to count these SubCodes if the casenumber is unique/distinct within these subcodes......


numberVar a1;
if
{PendingActions.SubCode}in [120,130,140,145,150,155,160,165,170,175,180,190,19 5,207,215]
then a1 := 1

It's been quite a while since I've had Crystal Reports, so I'm not 100% sure here, but your IN needs to use parens and not square brackets:

In(120,130,140,145,150,155,160,165,170,175,180,190 ,19 5,207,215)

(like I said, not sure that will be the only thing needed but it is one I can tell you for certain)

The formula I have works, it provides me with the answer '65', however I know that I want it to say 45, as it currentl counts all {PendingActions.SubCode}in [120,130,140,145,150,155,160,165,170,175,180,190,19 5,207,215] = 65

However I would like it to could all:-

DISTINCT {case.casenumbers} that are in ({PendingActions.SubCode} in [120,130,140,145,150,155,160,165,170,175,180,190,19 5,207,215])
Hope this makes more sense....

{} are for field names from your tables, not for the data, which is the []

Resolved it...


global cases(1000) as number
global lastCase as number
if {PendingActions.SubCode} = 120 OR {PendingActions.SubCode} = 130 OR {PendingActions.SubCode} = 140 OR {PendingActions.SubCode} = 145 OR {PendingActions.SubCode} = 150 OR {PendingActions.SubCode} = 155 OR {PendingActions.SubCode} = 160 OR {PendingActions.SubCode} = 165 OR {PendingActions.SubCode} = 170 OR {PendingActions.SubCode} = 175 OR {PendingActions.SubCode} = 180 OR {PendingActions.SubCode} = 190 OR {PendingActions.SubCode} = 195 OR {PendingActions.SubCode} = 207 OR {PendingActions.SubCode} = 215 then
if {PendingActions.CaseNo} in cases then
formula = 0
else
lastCase = lastCase + 1
cases(lastCase) = {PendingActions.CaseNo}
formula = 1
end if
else
formula = 0
end if