I need to create a report based on the status of the user. If this user is internal person, then the output will be the whole list of order. If this user is a client (or outside user), only the client's order will be output. Do you have any suggestion?

Thanks

This message has been edited. Last edited by: WebFocusDiver,

Do you have a way to identify from where the user come from (internal/external) ?

You first need to know that then easy to assign a variable based on that such as :

-SET &CLTFILTR = IF &INTERNALUSR EQ 'Y' THEN FOC_NONE ELSE &CLIENTNO;

TABLE FILE ORDERS
...
WHERE CLIENTNO EQ &CLIENTNO;
END

I do have a separate master files that identify user status. Do I need to create another fex to get the &clientno and include this fex?

Thank you for your help

I would say no (you can add the code in the same fex) but without knowing all the situation, configuration and model difficult to say exactly what to do.

If the user status is in another file, to retrieve its status you probably already have (as in a parameter) the user/client number.
So use that client/user parameter to retrieve its status then apply something similar as I already suggested to test if it's an inner or outside client and set the filter.

1- use client/user parameter to retrieve its status.
2- assign filter parameter according to status.
3- perform your report using above filter.

Sample:

-* WILL RETURN ONLY TRIUMPH
-*-SET &CAR = 'TRIUMPH';
-* WILL RETURN EVERYTHING
-SET &CAR = 'DATSUN';

TABLE FILE CAR
PRINT BODYTYPE
WHERE CAR EQ '&CAR';
ON TABLE SAVE AS DTYP FORMAT ALPHA
END
-RUN
-READ DTYP &BODYTYPE.A12.
-RUN

-SET &FILTR = IF &BODYTYPE EQ 'HARDTOP' THEN &CAR ELSE FOC_NONE;

TABLE FILE CAR
PRINT CAR
      MODEL
      BODYTYPE
BY COUNTRY
WHERE CAR EQ '&FILTR';
END
-RUN

This message has been edited. Last edited by: MartinY,

Just a thought: How about a JOIN and a DEFINE?

JOIN (to your file that contains the 'user status')
DEFINE FILE CAR
FILTR = IF &BODYTYPE EQ 'HARDTOP' THEN &CAR ELSE FOC_NONE;
END
TABLE FILE CAR 
PRINT CAR MODEL BODYTYPE 
BY COUNTRY
WHERE CAR EQ FILTR
END

Thank you, Martin.

I tried your method and it works for Car.mas. However when I tried on my master data. For some reason, the &INSTNAME from -READ has extra characters such as '000003' before the actual value of this field. Here is my code before I can build the second half.

-SET &ECHO=ALL;
TABLE FILE INSTITUTIONLIST
PRINT
INSTNAME
WHERE LOGINID EQ '&FOCSECUSER';
ON TABLE NOTOTAL
ON TABLE HOLD AS InstList FORMAT ALPHA
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN
? HOLD InstList
-READ InstList &INSTNAME.A100.
-RUN
-SET &FILTER= IF &INSTNAME EQ 'All' THEN FOC_NONE ELSE &INSTNAME;

The return of -SET is like this

-SET &FILTER= IF 000003All EQ 'All' THEN FOC_NONE ELSE 000003All

Any thoughts?

Thanks

it's a question of FOCLIST which is added to FOCUS files.

Instead try:

TABLE FILE INSTITUTIONLIST
PRINT INSTNAME
BY LOGINID NOPRINT
WHERE LOGINID EQ '&FOCSECUSER';
ON TABLE HOLD AS InstList FORMAT BINARY
END
-RUN
? HOLD InstList
-READFILE InstList
-RUN
-SET &FILTER= IF &INSTNAME EQ 'All' THEN FOC_NONE ELSE &INSTNAME;

This message has been edited. Last edited by: MartinY,

I tried this but when I run it, INSTNAME become a drop down box.

-SET &ECHO=ALL;
TABLE FILE INSTITUTIONLIST
PRINT
INSTNAME
BY LOGINID NOPRINT
WHERE LOGINID EQ '&FOCSECUSER';
ON TABLE NOTOTAL
ON TABLE HOLD AS InstList FORMAT BINARY

END
-RUN
? HOLD InstList
-READFILE InstList
-RUN
-SET &FILTER= IF &INSTNAME EQ 'All' THEN FOC_NONE ELSE &INSTNAME;

I don't understand why it become a DropBox.
You may have something else somewhere.

Are you running from the Content or ReportingServer ?

Try this from Console:

-DEFAULT &CNTRY = 'FRANCE';

-SET &ECHO=ALL;
SET ASNAMES = ON
TABLE FILE CAR
PRINT MODEL AS 'INSTNAME'
BY COUNTRY NOPRINT
WHERE COUNTRY EQ '&CNTRY';
ON TABLE HOLD AS InstList FORMAT BINARY
END
-RUN
? HOLD InstList
-READFILE InstList 
-RUN
-SET &FILTER= IF &INSTNAME EQ 'All' THEN FOC_NONE ELSE &INSTNAME;
-TYPE &FILTER

1. The 00003 is because it's a VARCHAR, re-define it as Alpha
2. Need to initialize &INSTANCE to "", AND, any other &VARIABLE that needs to be read in, at least, that's what we're having to on 8.1.04...

I initialize the &variable and it works. Thank you.