How can I count an application code but ignore duplicate reco records? 1

[JasonMcConnell]

Hi Guys,

I need some help (Crystal 7.5 on an oracle database)

I have created a report that lists the total number of applications received by an agent.

When I first did this I realised it was displaying and counting duplicate applications, so I inserted another group (application Number) and dragged all of the fields into this group and also put a (distinct count total) in the group footer. (this resolved the duplicate applications being displayed and counted issue)

I then needed to know the total number of applications received by agent that have a {certain code} so I created a formula that said - if {certain code} = 123 then 1 else 0.

I then created a sum total formula and placed it in the same group footer. - but again soon realised that it was counting duplicate records for apllication that have a certain code?

Can anybody please tell me how I can count the total number of applications that have a {certain code} but ignore the duplicate application records??

Hope this make sense

Thanks for all your help

Jason

 

[synapsevampire]

Try to post specifics " I created a formula that said - if {certain code} = 123 then 1 else 0.

I then created a sum total formula and placed it in the same group footer"

Where was the original formula placed, and what was in this sum total formula?

Try the following in the group header:

whileprintingrecords;
numbervar MyTotal;
if {certain code} = 123 then
MyTotal:=MyTotal+1

Then in the report footer display it using:

whileprintingrecords;
numbervar MyTotal;
"The distinct group total for 123 is " + totext(MyTotal)

-k

 

[JasonMcConnell]

Thanks Snapsevampire,

The ceratin code (Major Stat Return Code) that I have placed in the group footer (Supressed) is

if {PLANNING_APPLICATION.STATUTORY_RETURN_CODE} = "MADW" then 1 else
if {PLANNING_APPLICATION.STATUTORY_RETURN_CODE} = "MAJIND" then 1 else
if {PLANNING_APPLICATION.STATUTORY_RETURN_CODE} = "MAJOFF" then 1 else
if {PLANNING_APPLICATION.STATUTORY_RETURN_CODE} = "MAJRET" then 1 else
if {PLANNING_APPLICATION.STATUTORY_RETURN_CODE} = "MAOT" then 1 else 0

The sum total formula was generated by crystal , I clicked on insert summary and selected SUM field {@Major Stat Return Code}

What I have done now is slightly ammend your code and placed the following in the Group header

whileprintingrecords;
numbervar MyTotal;
if {@Major Stat Return Code} = 1 then
MyTotal:=MyTotal+1

I have placed the following in the group footer

whileprintingrecords;
numbervar MyTotal;
+ (MyTotal)


Can you advise if I have done something wrong?

It seems to work sometimes (see below Copy of Results)

BRYS
2006/0257/NEW BRYS
2006/0299/NEW BRYS
2006/0584/NEW BRYS
2006/0589/NEW BRYS
2006/0594/NEW BRYS
2006/0615/NEW BRYS
2006/0662/NEW BRYS
2006/0706/NEW BRYS MAJRET
2006/0873/NEW BRYS
2006/0887/NEW BRYS
2006/0892/NEW BRYS HOUS
2006/0911/NEW BRYS
2006/1055/NEW BRYS
2006/1115/NEW BRYS
2006/1279/NEW BRYS
2006/1320/NEW BRYS
2006/1369/NEW BRYS
2006/1448/NEW BRYS MAOT
2006/1500/NEW BRYS
2006/1624/NEW BRYS
2006/1947/NEW BRYS
2006/1984/NEW BRYS
2006/1988/NEW BRYS
2006/2009/NEW BRYS MINOFF
2006/2026/NEW BRYS
2006/2094/NEW BRYS
Total: 26 Total Major: 2

But not all the time? (See below copy of results)

ENGJ
2006/1221/P ENGJ HOUS
2006/1229/P ENGJ MIDW
2006/1237/P ENGJ OTHER
2006/1238/L ENGJ ALB
2006/1266/P ENGJ MIOT
2006/1268/P ENGJ MIOT
2006/1271/P ENGJ MIOT
2006/1276/P ENGJ ALB
2006/1314/P ENGJ MIDW
2006/1317/P ENGJ MINOFF
2006/1341/P ENGJ CU
2006/1353/P ENGJ HOUS
2006/1476/P ENGJ MIOT
2006/1483/P ENGJ HOUS
2006/1502/P ENGJ HOUS
2006/1509/P ENGJ CU
2006/1514/P ENGJ MIDW
2006/1516/p ENGJ MIDW
2006/1541/P ENGJ HOUS
2006/1542/L ENGJ ALB
2006/1568/P ENGJ OTHER
2006/1570/P ENGJ OTHER
2006/1583/P ENGJ HOUS
2006/1586/P ENGJ MINRET
2006/2061/P ENGJ MIOT
2006/2068/P ENGJ MIOT
2006/2069/p ENGJ MIOT
2006/2074/p ENGJ
2006/2081/NEW ENGJ MIOT

Total: 33 Total Major: 2

As you can see from the last example it says there are 2 Major when the is clearly none?

Can you please advise what I have done wrong?

 

[lbass]

If you are evaluating this at an outer group level, you need a reset in the group header of that outer group, like this:

whileprintingrecords;
numbervar MyTotal := 0;

-LB

 

[JasonMcConnell]

Hi Ibass,

Thanks for that - works a treat.

Can you tell me how I can sum the footer code so I can get the group sub total and also how I can get a grant total? I have tried using the summary and grand total function but the field is not displayed on the drop down list.

Thanks for all your help

J

 

[lbass]

The way this is set up, MyTotal will BE the group subtotal. If you have another outer group and so want another higher order group subtotal, you need to set up a another variable. Same for the grand total. Let's assume you have three groups. You would need to add another reset formula:

//{@resetgrp1} to be placed in the higher order group header (grp#1):
whileprintingrecords;
numbervar outersubtot := 0;

Then change the accumulation formula to (placed in Group#3 header or footer):

whileprintingrecords;
numbervar MyTotal;
numbervar outersubtot;
numbervar grtot;

if {@Major Stat Return Code} = 1 then (
MyTotal:=MyTotal+1;
outersubtot := outersubtot + 1;
grtot := grtot + 1
);

Then add two new display formulas:

//{@displouter} to be placed in the outer group footer (Group Footer #1):
whileprintingrecords;
numbervar outersubtot;

//{@displgrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

You would still have your original reset formula for MyTotal and the display formula for that at the group #2 level.

-LB