I am trying to filter reports to only display organizations that share a characteristic with the organization that the user works for (in this case it's a population range). While I can filter to display only the user's organization, I can't get it to display the set of organizations I want.

I'd like it to filter automatically rather than require the user to select a filter option.

Thanks,
Marie

This message has been edited. Last edited by: Marie Eberlein,

Are these characteristics stored in a column? You can use DBA in the master file using RESTRICT to make your reports and charts to always send a specific WHERE test against this column. DBA will identify who's logged in and issue a WHERE test based on whatever data you want them to see.

Something like this:

USER=rm_central, ACCESS=R, RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=REGION EQ 'Central';, $

In this example, if rm_central logs into WebFOCUS, every request will have a WHERE REGION EQ 'Central'; appended to it.

Look up DBA and RESTRICT in the doc.

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

It looks like this is applying a restriction to a single user--does it work to apply it to all users?

Not this, because this did not work, but something like this:
USER=&&USERID, ACCESS=R, RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=TABLE.CHARACTERISTIC;,$

Not sure how I would include an EQ statement in the value because I want it to pull the value of the characteristic from the table for the particular user, then apply the filter based on that value.

Also may be putting the code in the wrong place or be missing something else, because I haven't been able to get it to work when defining a where statement for a particular user either. This was the most related documentation I found, but maybe I'm missing something better.

You have to read up on DBA in the doc. There are specific syntax you need to add to the master file for the behavior you want. You can use App Studio if you have admin access to the master files and use the GUI to paint these restrictions. I'd start with something simple and then do the more ambitious stuff you are describing.

Marie, please update your signature to add the IB version you are using it will help us pointing you to the right direction (version)

Search for DBA :
IBI 8.2.0.1

A couple notes...

You can't parameterize &&USERID. It doesn't work that way... You can however "SET PASS = X". Unfortunately the way DBA restrictions is setup is very antiquated and if you ask me, behaves like a port from the Mainframe FOCUS days. I would think of the "USER" value in the DBA file more as a group then a user... You can then use the set pass command to assign that user to the "Group" you created in the DBA file. Also there is the SET PERMPASS command. The PERMPASS command though will make that PASS permenant for the Session and you will not be able to change it. Where the PASS command can be changed from GROUP A to GROUP B if needed. As Babak and Martin mention, you will want to familiarize yourself with the documentation on it.

Also, another thing to note is that in Version 8.1.5+ the NAME attribute has been adjusted so that if NAME=SYSTEM, the restriction will ALWAYS be invoked in the JOIN request. If you want the behavior to be so that the restriction ONLY occurs when the table is needed to satisfy the query, then you need to use NAME=[segment name]. Before V8.1.5 NAME=SYSTEM did not enforce that the join occured.

Marie

I think you could benefit from using the MFD_PROFILE that does the lookup based on the user. You can use &FOCSECUSER in the MFD_PROFILE procedure in the WHERE condition for the user. Also I have been successful in using USER=&FOCSECUSER in the DBA portion. The SET PASS = &FOCSECUSER needs to be in the mfd_profile fex. Below is a sample of the CAR master and the MFD_PROFILE.


FILENAME=CAR, SUFFIX=FOC , MFD_PROFILE=ibisamp/site_profile, $
VARIABLE NAME=&&CAR_WHERE, $
VARIABLE NAME=&FOCSECUSER, $
SEGMENT=ORIGIN, SEGTYPE=S1, $
FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, FIELDTYPE=I, $
SEGMENT=COMP, SEGTYPE=S1, PARENT=ORIGIN, $
FIELDNAME=CAR, ALIAS=CARS, USAGE=A16, $
SEGMENT=CARREC, SEGTYPE=S1, PARENT=COMP, $
FIELDNAME=MODEL, ALIAS=MODEL, USAGE=A24, $
SEGMENT=BODY, SEGTYPE=S1, PARENT=CARREC, $
FIELDNAME=BODYTYPE, ALIAS=TYPE, USAGE=A12, $
FIELDNAME=SEATS, ALIAS=SEAT, USAGE=I3, $
FIELDNAME=DEALER_COST, ALIAS=DCOST, USAGE=D7, $
FIELDNAME=RETAIL_COST, ALIAS=RCOST, USAGE=D7, $
FIELDNAME=SALES, ALIAS=UNITS, USAGE=I6, $
SEGMENT=SPECS, SEGTYPE=U, PARENT=BODY, $
FIELDNAME=LENGTH, ALIAS=LEN, USAGE=D5, $
FIELDNAME=WIDTH, ALIAS=WIDTH, USAGE=D5, $
FIELDNAME=HEIGHT, ALIAS=HEIGHT, USAGE=D5, $
FIELDNAME=WEIGHT, ALIAS=WEIGHT, USAGE=D6, $
FIELDNAME=WHEELBASE, ALIAS=BASE, USAGE=D6.1, $
FIELDNAME=FUEL_CAP, ALIAS=FUEL, USAGE=D6.1, $
FIELDNAME=BHP, ALIAS=POWER, USAGE=D6, $
FIELDNAME=RPM, ALIAS=RPM, USAGE=I5, $
FIELDNAME=MPG, ALIAS=MILES, USAGE=D6, $
FIELDNAME=ACCEL, ALIAS=SECONDS, USAGE=D6, $
SEGMENT=WARANT, SEGTYPE=S1, PARENT=COMP, $
FIELDNAME=WARRANTY, ALIAS=WARR, USAGE=A40, $
SEGMENT=EQUIP, SEGTYPE=S1, PARENT=COMP, $
FIELDNAME=STANDARD, ALIAS=EQUIP, USAGE=A40, $
END
DBA=admin, $
USER=&FOCSECUSER, ACCESS=R, RESTRICT=VALUE_WHERE, NAME=SYSTEM, VALUE=&&CAR_WHERE ;, $


MFD_PROFILE code


-DEFAULTH &COUNTRY_WHERE = 'All';
-IF &FOCSECUSER EQ 'admin' GOTO SKIPLOOKUP;
-*-------------------------------------------------
-* Dynamically Create Security Filters Set
-*-------------------------------------------------
TABLE FILE USER_XREF
PRINT COUNTRY
WHERE USER_ID EQ '&FOCSECUSER'
ON TABLE SAVE AS WHERE_COUNTRY
END
-*
-IF &LINES EQ 0 THEN GOTO SETBOGUSWHERE;
-*-------------------------------------------------
-* Parse Record Set into Where Condition Parameters
-*-------------------------------------------------
-RUN
-SET &COUNTER = 0;
-READLOOP1
-READ WHERE_COUNTRY NOCLOSE &COUNTRY.A9.
-IF &COUNTER GT 0 THEN GOTO MOREFILES1 ;
-SET &COUNTRY = TRUNCATE(&COUNTRY);
-SET &COUNTRY_WHERE = '''&COUNTRY.EVAL''';
-GOTO SETCNTR1
-MOREFILES1
-SET &COUNTRY = TRUNCATE(&COUNTRY);
-SET &COUNTRY_WHERE = &COUNTRY_WHERE | ' OR ' | '''&COUNTRY.EVAL''';
-SETCNTR1
-SET &COUNTER = &COUNTER + 1;
-IF &COUNTER GE &LINES GOTO ENDLOOP1 ;
-GOTO READLOOP1
-ENDLOOP1
-SET &&CAR_WHERE = 'COUNTRY EQ ' | &COUNTRY_WHERE;
-RUN
-GOTO SKIPLOOKUP
-SETBOGUSWHERE
-SET &&CAR_WHERE = 'COUNTRY EQ ' | '''NOCOUNTRY''' ;
-SKIPLOOKUP
SET PASS = &FOCSECUSER


Hope this helps.

Thanks all, I will mark this as closed for the moment and check out the documentation, then see if I have any more specific questions after that.