count/sum non-duplicate records

[lsantos]

I have 2 files, requests and actions and a request can have many actions. In my report I want to list and count only one action per request.

I tried to condition the detail section which works but still counts the other duplicate request actions.

Also tried to create a {@duplicate record} formula to check when duplicate record found:

if previous ({aualdocs.fmt_acc}) = {aualdocs.fmt_acc} then true else false

I then have a flag field {@over_365days} that I set (and sum) based on following conditions:

if ({aualdocs.rec_dte}) >= {@date 365 from} // date range
and ({aualdocs.rec_dte}) <= {@date 365 to} // date range
and isNull ({aualdocs.det_cde}) // record status

and not({@duplicate record})
then 1
else 0

... but then the sum of {@over_365days} gives me an error:

"A summary has been specified no a non-recurring field. Details: @over_365days"

I need to sum all non duplicates only...

Any suggestions will be appreciated.

Luis

 

[IanWaterman]

Why don't you just count the requests, use a running total set to distinct count of the requests.

Ian

 

[synapsevampire]

Given that you must have some requests that don't have actions, which is why you wouldn't have used Ian's suggestion, you can create a group on the request, and use the 3 formula method:

In the report header:
whileprintingrecords;
numbervar MyCount:=0;

Group header formula:
whileprintingrecords;
numbervar MyCount;
if not(isnull({table.action})) then
MyCount:=MyCount+1

Report footer (display purposes)
whileprintingrecords;
Numbervar MyCount

-k

 

[IanWaterman]

Sorry Synapsevampire, but surely if the requests are in one table and the actions are in another table. If you have an equal join , then you will only see the requests that have an action. Thus you can count the Requests.

If you have a left out join between the tables then your method will be best.

Ian