TABLE FILE MSCRM_ACTIVITYPOINTER
SUM
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
COMPUTE CNT_ACTIVITY/D6 = 1;

BY PARTYID

WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;

ON TABLE HOLD AS H001M1
END
-RUN

TABLE FILE H001M1
SUM
CNT_ACTIVITY
SUM
PARTYIDNAME
ACTIVITYTYPECODE

BY PARTYID

ON TABLE HOLD AS H001M1
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

# 1

TABLE FILE ...
SUM
COMPUTE CNT_ACTIVITY/D6 = 1;
SUM
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE

BY PARTYID

WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;

ON TABLE HOLD AS H001M1
END

Option 2:

TABLE FILE ...
SUM
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE

BY PARTYID

WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;

ON TABLE HOLD AS H001M1
END

-RUN
-SET &TOTLINES = &LINES;

DEFINE FILE H001M1
COMPUTE CNT_ACTIVITY/D6 = &TOTLINES;
END

TABLE FILE H001M1
PRINT
*
CNT_ACTIVITIY
ON TABLE HOLD AS ...
END

Scott

TABLE FILE ...
SUM
CNT.ACTIVITYTYPECODE
SUM
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE

BY PARTYID

WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;

ON TABLE HOLD AS H001M1
END

GamP

---

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

  
-* File franciscount.fex
DEFINE FILE CAR
ONE/I5 WITH CAR = 1;
END
TABLE FILE CAR
SUM ONE
SUM SALES 
BY COUNTRY BY CAR
END

  
TABLE FILE CAR
SUM SALES
COMPUTE MCNT/I5=1 + MCNT;
BY SEATS
ON TABLE HOLD
END
TABLE FILE HOLD
SUM MAX.MCNT
SUM SALES
BY SEATS
END

TABLE FILE CAR
SUM CNT.DST.CAR WITHIN TABLE
 CNT.MODEL
 SALES
BY CAR
END

TABLE FILE MSCRM_ACTIVITYPOINTER
SUM
CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_ACTIVITY
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE

BY PARTYID

WHERE SCHEDULEDSTART FROM DT(2007-11-01 00:00:00.000) TO DT(2008-01-31 23:59:59.999);
WHERE CF_COMPANYIDNAME LIKE '%BMO Nesbitt Burns%';
WHERE ACTIVITYTYPECODE EQ 4201
WHERE PARTYOBJECTTYPECODE EQ 2;
WHERE PARTICIPATIONTYPEMASK EQ 5;

ON TABLE HOLD AS H001M1
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

quote:

TABLE FILE MSCRM_ACTIVITYPOINTER
SUM
CNT.DST.ACTIVITYTYPECODE WITHIN TABLE AS CNT_ACTIVITY
MIN.PARTYIDNAME
MIN.ACTIVITYTYPECODE
BY PARTYID
. . .
WHERE ACTIVITYTYPECODE EQ 4201
. . .
END

quote:

Manipulating Display Field Values in a Sort Group

You can use the WITHIN phrase to manipulate a display field’s values as they are aggregated
within a sort group. This technique can be used with a prefix operator to perform
calculations on a specific aggregate field rather than a report column. In contrast, the SUM
and COUNT commands aggregate an entire column.
You can use up to 64 fields in a display command when using the WITHIN phrase.The
WITHIN phrase requires a BY phrase and/or an ACROSS phrase. A maximum of two WITHIN
phrases can be used per display command. If one WITHIN phrase is used, it must act on a BY
phrase. If two WITHIN phrases are used, the first must act on a BY phrase and the second on
an ACROSS phrase.
You can also use WITHIN TABLE, which allows you to return the original value within a
request command. The WITHIN TABLE command can also be used when an ACROSS phrase
is needed without a BY phrase. Otherwise, a single WITHIN phrase requires a BY phrase.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

TABLE FILE CAR
"sum within table:"
SUM CNT.DST.SEATS WITHIN TABLE
SALES 
BY CAR
END

TABLE FILE CAR
"count within table:"
COUNT DST.SEATS WITHIN TABLE
SUM.SALES 
BY CAR
END

TABLE FILE CAR
"count within car"
COUNT DST.SEATS WITHIN CAR
SUM.SALES 
BY CAR
ON TABLE SUMMARIZE
END

  
TABLE FILE CAR
SUM CNT.DST.CAR WITHIN TABLE
SALES
BY CAR
BY MPG IN-GROUPS-OF 10
END

  
                             COUNT
                             DISTINCT
  CAR                   MPG  CAR        SALES  
  ---                   ---  --------   -----                                   
  ALFA ROMEO             20        12   30200  
  AUDI                   20        12    7800  
  BMW                    10        12   62540  
                         20        12   17850  
  DATSUN                  0        12   43000  
  JAGUAR                  0        12   12000  
                         10        12       0  
  JENSEN                 10        12       0  
  MASERATI                0        12       0  
  PEUGEOT                20        12       0  
  TOYOTA                 20        12   35030  
  TRIUMPH                20        12       0