-Dell

 

Field to summarize is {ACCOUNT_LEDGER.ACCOUNT}.

Type of summary is Distinct Count.

Under Evaluate, select Use Formula.  Your formula would probably be "{@TurnOverLastmonth}=0"

Reset on change of field using the field that is your first group.

 

This way you can take out the group on {@TurnOverLastmonth} and your data should appear like you want it to.

 

-Dell

 

Create a formula:

if  ({Account_Ledger.Account} <> previous({Account_Ledger.Account}) and ({@TurnedOverLastmonth} = 0) then 1 else 0

 

Then do a sum on this formula.

 

The formula will only work if the account numbers are in order.  It will look to see if it's a duplicate account number and, if it isn't, then look to see if it had 0 turnover.  If both conditions are met, then you have a 1.  By summing the formula you should get the distinct count you're looking for.

 

-Dell

Also, don't use a running total.  Just put a summary on the report by clicking on the Summary button (has a "sigma" on it that looks like a big "E") or going to the Insert menu and selecting Summary.  You then select your formula and "Sum".

 

Because of the "previous" you may actually have to break down the formula into two formulas - one that uses the other.  It would look something like this:

 

{@IsNotDuplicate}

{ACCOUNT_LEDGER.ACCOUNT}<>previous({ACCOUNT_LEDGER.ACCOUNT})

 

{@TestFormula}

if {@IsNotDuplicate} and ({@TurnOverLastMonth}=0) then 1 else 0

 

Then create your sum on {@TestFormula}.

 

I'm not entirely sure this will work, but I think it will.  If it doesn't, I have one more option...

-Dell

Yes, working with the previous and next functions can cause problems when you need them for summaries.

 

The other option I have is to use a subreport that only contains the count that you're looking for.  You would include only the Account Ledger table and whatever table you need to link that to the to the Rep code. 

 

- In the subreport, suppress everything except one report footer section.

- Add your  {@TurnOverLastMonth} formula (hint:  if you copy the formula object from where it's located ON the main report, paste it into the subreport, the formula will be added to the formula list for the subreport.  You can then delete the object from the subreport since you're not going to be displaying it on the subreport, but it will still be available in the list.) 

- Add a select condition on {@TurnOverLastMonth}= 0. 

- Add a distinct count of the account number and put it in the non-suppressed report footer.

 

It's not the most elegant solution, but this should get you what you need.

 

-Dell