I need to get a list of reps who have 10 or more clients with matching transactions. The first step I was going to take is to create a "list" of clients and accounts by the fields that I need to match on by Rep, however I don't see how to LIST in DevStudio. Here's my code:
TABLE FILE AL_ACCOUNTTRANS
PRINT ---- I tried to replace PRINT with LIST
SSN_TIN
ACCT_NUMBER
BY
ADVISOR_TAX_ID
BY
TRANS_DATE
BY
SEC_TYPE
BY
BuySell
BY
NORM_TRANS_CODE
BY
FUND_NAME
BY
DESCRIPTION
WHERE TRANS_DATE GE WEEKDATE;
IF INT_TYPE EQ (INTTYPES)
IF NORM_TRANS_CODE EQ (NORMTRANS)
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON

The error I get when I try to run the code with LIST is: Error Parsing Report Request

Thanks for any help....

If I understand correctly, you want to list reps (advisor_tax_id) and their clients (acct_number, ssn_tin) where the rep has at least 10 distinct client accounts that had certain types of transactions in a given period of time (as per your IF and WHERE phrases).

You can do that in two steps:

-* 1. get one record per distinct combination of
-* advisor and client
TABLE FILE AL_ACCOUNTTRANS
WRITE CNT.TRANS_DATE
BY ADVISOR_TAX_ID
BY ACCT_NUMBER
BY SSN_TIN
WHERE TRANS_DATE GE WEEKDATE;
IF INT_TYPE EQ (INTTYPES)
IF NORM_TRANS_CODE EQ (NORMTRANS)
ON TABLE HOLD
END

-* 2. produce listing of advisors and their
-* clients, where client count is 10 or more
TABLE FILE HOLD
WRITE CNT.ACCT_NUMBER NOPRINT
BY ADVISOR_TAX_ID
LIST ACCT_NUMBER
SSN_TIN
BY ADVISOR_TAX_ID
IF TOTAL CNT.ACCT_NUMBER GE 10
END
You can use Dev Studio to style the second part as you please. You may want add Joins in step 2, to include other attributes for the reps and clients in the listing.

If you want to list the transactions by rep by client, hold the list of reps from step 2, and use that to screen on rep (advisor code) in the code you originally posted as step 3.

This message has been edited. Last edited by: <Mabel>,

Thanks. I think this is getting me closer. The client can only be counted once if he has more than one of the same type of transaction on a given day. The "match" on transcation is determined by TRANS_DATE, BUYSELL, NORM_TRANS_CODE, FUND_NAME AND DESCRIPTION. A client is determined by his SSN_TIN and ACCT_NUMBER combination.

This should do it:

-* get set of "distinct" transaction rows per rep
TABLE FILE AL_ACCOUNTTRANS
BY ADVISOR_TAX_ID
BY SSN_TIN
BY ACCT_NUMBER
BY TRANS_DATE
BY SEC_TYPE
BY BUYSELL
BY NORM_TRANS_CODE
BY FUND_NAME
BY DESCRIPTION
WHERE TRANS_DATE GE WEEKDATE;
IF INT_TYPE EQ (INTTYPES)
IF NORM_TRANS_CODE EQ (NORMTRANS)
ON TABLE HOLD AS HOLD1
END

-* get list of reps with 10+ rows
TABLE FILE HOLD1
COUNT ENTRIES NOPRINT
BY ADVISOR_TAX_ID
IF TOTAL COUNT GE 10
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
ON TABLE SET HOLDLIST PRINTONLY
END
?FF HOLD2
...followed by your report, with

IF ADVISOR_TAX_ID EQ (HOLD2)

added to its selection criteria.

(The ?FF displays the fields and their formats for the hold file; HOLD2 should only have a single field, ADVISOR_TAX_ID.)

This message has been edited. Last edited by: <Mabel>,