SUM CNT

[pvuppa1]

Hi,
Can anyone help me using sum and count to store the value for number or records and using it to print. I can print by saying COUNT FIELDNAME, but I want to use multiple queries and i need to print those multiple query record counts using the same mas file.
any help would be appreciated.
Thanks
-pawan

 

[kiddpete]

I'm not quite sure what you are asking or trying to do, but you can use CNT with SUM as follows:

TABLE FILE yourfile
SUM CNT.fieldname
BY anotherfield
etc.
END

You can also say SUM CNT.fld1 fld1 fld2 CNT.fld3 etc.

HTH

 

[pvuppa1]

Actually, I have 5 different reports (i.e; 5 different FEX files/procedures using same MAS file)which return me 5 different recordcounts using SUM OR COUNT for some where condition used by parameter prompt (like start and end dates) and I want to display all those record counts into one single report. Any ideas? Im totally new but any help would be great.
report1
TABLE FILE SAMEFILE
COUNT ID AS REPCOUNT1
HEADING REP1
WHERE MYFIELD1=&PARAM1 AND MYFIELD2=&PARAM2
END
TABLE FILE SAMEFILE
COUNT id AS REPCOUNT2
HEADING REP2
WHERE MYFIELD3=&PARAM1 AND MYFIELD4=&PARAM2
END
TABLE FILE SAMEFILE
COUNT ID AS REPCOUNT3
HEADING REP3
WHERE MYFIELD5=&PARAM1 AND MYFIELD6=&PARAM2
END
.
.
/so, please give me any ideas on this. I want to display REPCOUNT1,REPCOUNT2,REPCOUNT3 all together in one single report (PDF,HTML or whatever)..Right now, i can get individual REPCOUNTS. All i want is to combine or display in one single file by running one single FEX file.

Thanks for your time and help
-P

 

[kiddpete]

Sure, that looks pretty simple. Something like:

DEFINE FILE SAMEFILE
KOUNT1/I1=IF MYFIELD1=&PARAM1 AND MYFIELD2=&PARAM2 THEN 1
ELSE 0;
KOUNT2/I1=IF MYFIELD3=&PARAM1 AND MYFIELD4=&PARAM2 THEN 1
ELSE 0;
etc.
END
TABLE FILE SAMEFILE
SUM KOUNT1 KOUNT2
END

Assuming MYFIELD1-4 are numeric. If they are alpha, you would need:

MYFIELD1='&PARAM1' AND MYFIELD2='&PARAM2'

HTH

 

[pvuppa1]

tHANKS again, here's the actual code
DEFINE FILE SAMEFILE
KOUNT1/I1=IF
(DATE1 GE '&BeginDate.BeginDate.') AND
(DATE1 LE '&EndDate.EndDate.') THEN 1
ELSE 0;
KOUNT2/I1=IF DATE2 GE '&&BeginDate.Enter Begin Date.'
AND DATE2 LE '&&EndDate.Enter End Date.' THEN 1
ELSE 0;
END
TABLE FILE SAMEFILE
SUM KOUNT1 KOUNT2
END

The output i got was

KOUNT1 KOUNT2
* *

INSTEAD THE VALUES ARE SOME NUMBERS 48 AND 318 WHICH I KNOW SINCE I RAN THE REPORT INDIVIDUALLY.
PLEASE HELP, I REALLY APPRECIATE IT

 

[kiddpete]

You changed the code you were showing me! It would help if you explain it the first time. I would expect Focus to be totally confused by:

(DATE1 GE '&BeginDate.BeginDate.') AND

Where did you get this? What did it do? Did it prompt you for &BeginDate with the message of BeginDate?

If you want it to prompt for &BeginDate try:

-PROMPT &BeginDate.BeginDate.

 

[kiddpete]

Also, you must give KOUNT enough size. That was my fault. Something like:

KOUNT1/I9=etc.

the * says I1 wasn't big enough.