Need to build report which shows user id and groups assigned to it

Prasanna_Darda

Hi,

I have created below live report but its giving me group id and all other details.

 Select * from KUAF where Type=0 and Deleted=0

It giving me all data but i wants to have repor which shows what are the groups assigned to users.

By above mentioned query, i am getting group id.

Can someone please help me to build the query which would be giving output as :-

By above qurer, i am able to get 3 requirements but somehow not able to get group name assigned by it

1) User id

2) first name

3)Last Name

4) Group assigned

 

Regards,

 

Prasanna Darda

Colin_Schmidt

select kc.childid "Child ID", k1.name "User", k1.firstname||' '||k1.lastname "Full", kc.id "Group ID", k2.name "Group name", case when k2.type = 0 then '['||to_char(k2.type)||'] User' when k2.type = 1 then '['||to_char(k2.type)||'] Group' when k2.type = 3 then '['||to_char(k2.type)||'] System' when k2.type = 4 then '['||to_char(k2.type)||'] Group Admin' when k2.type = 5 then '['||to_char(k2.type)||'] Domain' when k2.type = 6 then '['||to_char(k2.type)||'] X-Domain group' when k2.type = 101 then '['||to_char(k2.type)||'] Signing Group' when k2.type = 102 then '['||to_char(k2.type)||'] eSign group' when k2.type > 102 and dt.subtype = 202 then '['||to_char(k2.type)||'] Project: '||dt.name||' [Leader: '||(select kx.name from kuaf kx where dt.userid = kx.id)||']'when k2.type > 102 and dt.subtype in (3030002, 3030201, 3030202) then '['||to_char(k2.type)||'] Community: '||dt.name||' [Leader: '||(select ky.name from kuaf ky where dt.userid = ky.id)||']'else '['||to_char(k2.type)||'] Other' end Type from kuafchildren kc join kuaf k1 on kc.childid = k1.id left outer join kuaf k2 on kc.id = k2.id left outer join dtree dt on k2.type = dt.dataid where kc.childid = %1 Order by k2.type, k2.name

Colin J. Schmidt

Opentext Content Server Support Admin

colin.schmidt@mwv.com
+1 804.444 5012 (Direct)

 
MWV
501 South 5th Street
Richmond, VA 23219-0501
mwv.com

On Wed, Jul 17, 2013 at 10:37 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Need to build report which shows user id and groups assigned to it

Posted byprasanna.darda@accenture.com (Darda, Prasanna)On 07-17-2013 10:32

Hi, I have created below live report but its giving me group id and all other details.  Select * from KUAF where Type=0 and Deleted=0 It giving me all data but i wants to have repor which shows what are the groups assigned to users. By above mentioned query, i am getting group id. Can someone please help me to build the query which would be giving output as :- By above qurer, i am able to get 3 requirements but somehow not able to get group name assigned by it 1) User id 2) first name 3)Last Name 4) Group assigned   Regards,   Prasanna Darda

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.