JOIN
UOA_USERS.UOA_USERS.ID IN UOA_USERS TO MULTIPLE
UOA_GROUPSUSERS.UOA_GROUPSUSERS.USERID IN UOA_GROUPSUSERS TAG J0 AS J0
END
JOIN
J0.UOA_GROUPSUSERS.GROUPID IN UOA_USERS TO MULTIPLE UOA_GROUPS.UOA_GROUPS.ID
IN UOA_GROUPS TAG J1 AS J1
END

TABLE FILE UOA_USERS
PRINT
NAME
EMAIL
ID
J0.GROUPID
J1.DESCRIPTION
J1.NAME
WHERE NAME = 'username';
END 

JOIN ID      IN UOA_USERS TO MULTIPLE USERID  IN UOA_GROUPSUSERS AS J1
JOIN ID      IN UOA_USERS TO MULTIPLE USERID  IN UOA_USERSROLES  AS J2
JOIN PSETID  IN UOA_USERS TO MULTIPLE ID      IN UOA_PSETS       TAG J3 AS J3
JOIN GROUPID IN UOA_USERS TO MULTIPLE ID      IN UOA_GROUPS      AS J4
JOIN GROUPID IN UOA_USERS TO MULTIPLE GROUPID IN UOA_GROUPSRULES AS J5
JOIN UOA_GROUPSRULES.PSETID  IN UOA_USERS TO MULTIPLE ID IN UOA_PSETS TAG J6 AS J6

TABLE FILE UOA_USERS

WHERE NAME EQ 'fmariani';

PRINT
COMPUTE T0/A20 = 'UOA_USERS';
UOA_USERS.ID
UOA_USERS.DESCRIPTION
UOA_USERS.NAME

COMPUTE T3/A20 = 'UOA_PSETS';
J3.UOA_PSETS.DESCRIPTION
J3.UOA_PSETS.NAME

COMPUTE T4/A20 = 'UOA_GROUPS';
UOA_GROUPS.DESCRIPTION
UOA_GROUPS.EXTGROUPNAME
UOA_GROUPS.NAME

COMPUTE T6/A20 = 'UOA_PSETS';
J6.UOA_PSETS.DESCRIPTION
J6.UOA_PSETS.NAME

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

 FOC2506 - INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING REASON:
 FOC2516 - MULTIPLICATIVE EFFECT WOULD ENSUE
 FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED

SELECT
 T1."ID",
 T1."DESCRIPTION",
 T1."NAME",
 T2."ID",
 T2."PSETID",
 T4."ID",
 T4."DESCRIPTION",
 T4."NAME",
 T5."ID",
 T5."GROUPID",
 T6."ID",
 T6."PSETID",
 T7."ID",
 T7."DESCRIPTION",
 T7."NAME",
 T8."ID",
 T8."DESCRIPTION",
 T8."EXTGROUPNAME",
 T8."NAME"
 FROM
 WF_REPOS.UOA_USERS T1,
 WF_REPOS.UOA_USERSROLES T2,
 WF_REPOS.UOA_PSETS T4,
 WF_REPOS.UOA_GROUPSUSERS T5,
 WF_REPOS.UOA_GROUPSRULES T6,
 WF_REPOS.UOA_PSETS T7,
 WF_REPOS.UOA_GROUPS T8
 WHERE
 (T2."USERID" = T1."ID") AND
 (T4."ID" = T2."PSETID") AND
 (T5."USERID" = T1."ID") AND
 (T6."GROUPID" = T5."GROUPID") AND
 (T7."ID" = T6."PSETID") AND
 (T8."ID" = T5."GROUPID") AND
 (T1."NAME" = 'fmariani')
 ORDER BY
 T1."ID",T2."ID",T4."ID",T5."ID",T6."ID",T7."ID",T8."ID";

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:

&FOCSECUSER&FOCSECGROUP&FOCSECGROUPS

# --- Documentation: http://techsupport.information...cAdmin/wf8104sec.pdf
# --- WebFOCUS Security and Administration > Manipulating WebFOCUS Variables > Managed Reporting Internal Variables

# Managed Reporting Internal Variables

# The folder where the processed report is stored
<SET> IBIMR_folder (pass)

# The complete path for a procedure, including file name and extension
<SET> IBIMR_fullpath (pass)

# The domain HREF where the report being processed is stored
<SET> IBIMR_domain (pass)

# The description given to the report by the developer
<SET> MR_FULL_FEXNAME (pass)

# The file name (also known as the IBFS name) assigned to the report when it was created
<SET> MR_ITEM_HANDLE (pass)

# The groups in which the user is a part of.
<SET> IBIMR_memberof(pass)

# The user name of who is logged in.
<SET> IBIMR_user(pass)

SET ASNAMES = MIXED

-DEFAULT &Username = _FOC_NULL:
-DEFAULT &Fullname = _FOC_NULL:

TABLE FILE gavin_test/uoa_users
BY UOA_USERS.UOA_USERS.NAME AS 'Username'
BY UOA_USERS.UOA_USERS.DESCRIPTION AS 'Fullname'
WHERE UOA_USERS.UOA_USERS.NAME EQ '&IBIMR_user'
ON TABLE HOLD AS USERHOLD FORMAT ALPHA
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

-READFILE USERHOLD

-DEFAULT &IBIMR_memberof = _FOC_NULL;
-DEFAULT &IBIMR_folder = _FOC_NULL;
-DEFAULT &IBIMR_fullpath = _FOC_NULL;
-DEFAULT &IBIMR_domain = _FOC_NULL;
-DEFAULT &MR_FULL_FEXNAME = _FOC_NULL;
-DEFAULT &MR_ITEM_HANDLE = _FOC_NULL;

-*These variables were setup via Admin Console->Config->Custom Settings
-TYPE IBIMR_memberof = &IBIMR_memberof
-TYPE IBIMR_folder = &IBIMR_folder
-TYPE IBIMR_fullpath = &IBIMR_fullpath
-TYPE IBIMR_domain = &IBIMR_domain
-TYPE MR_FULL_FEXNAME = &MR_FULL_FEXNAME
-TYPE MR_ITEM_HANDLE = &MR_ITEM_HANDLE
-TYPE FOCSECGROUPS = &FOCSECGROUPS
-TYPE FOCSECGROUP = &FOCSECGROUP
-TYPE FOCSECUSER = &FOCSECUSER
-*EO These variables were setup via Admin Console->Config->Custom Settings

-SET &USER = GETUSER('A20');
-TYPE GETUSER() = &USER

-SET &LIST = GRPLIST(300, 'A300');
-TYPE GRPLIST() = &LIST

-TYPE FOCMODE = &FOCMODE
-TYPE IBIMR_user = &IBIMR_user

-SET &Username = TRUNCATE(&Username);
-TYPE Username = &Username
-SET &Fullname = TRUNCATE(&Fullname);
-TYPE Fullname = &Fullname

 0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1
 IBIMR_memberof = Administrators;EVERYONE;
 IBIMR_folder = Public
 IBIMR_fullpath = IBFS:/WFC/Repository/Public/GetUsersandGroupInformation.fex
 IBIMR_domain = Public/
 MR_FULL_FEXNAME = Get Users and Group Information
 MR_ITEM_HANDLE = GetUsersandGroupInformation
 FOCSECGROUPS = Administrators;EVERYONE
 FOCSECGROUP = Administrators
 FOCSECUSER = PTH\admin
 GETUSER() = SYSTEM
 GRPLIST() = Administrators;EVERYONE
 FOCMODE = WINNT
 IBIMR_user = admin
 Username = admin
 Fullname = Administrator

- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server