How to build report of every user in each group

Hello all. I am running MSSQL as my database for CS 10.0.0 UP2 SP 11. I am trying to build a report that lists each group, with all of the users in that group for every group in Content Server. My query is as follows:

SELECT c.name as "Group", a.firstName, a.lastName
FROM kuaf a, dtree b, kuaf c
WHERE a.id=b.dataid and a.firstName!='?' and a.deleted='0' and a.groupid=c.id;

My problem is that this report and query are only showing that every user is in ONE group only. However there are cases in which one user account is in several different groups, for various reasons. I would like to be able to build a report that both shows how many different groups a user is in and also show a group by group breakdown with what users are in each group. Thanks everyone!

Find more posts tagged with

Comments

Appu_Nair1

eLink : How to build report of every user in each groupFor 'A' group it's kuaf.id will be present in Kuafchildren throw some niceties and you have your query search for Kuafchildren in this forum to get some useful ones

Sent with Good (www.good.com)

-----Original Message-----
From: eLink Entry: Content Server LiveReports Forum [livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, January 20, 2014 07:57 AM Central Standard Time
To: eLink Recipient
Subject: [EXTERNAL]How to build report of every user in each group

How to build report of every user in each group

Posted by im.cd@accenture.com (Accenture, Accenture) On 01-20-2014 08:49

SELECT c.name as "Group", a.firstName, a.lastName
FROM kuaf a, dtree b, kuaf c
WHERE a.id=b.dataid and a.firstName!='?' and a.deleted='0' and a.groupid=c.id;

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

Colin_Schmidt

Using KUAFCHILDREN in a direct way will get you direct membership. When you click on Groups for a User, it lists all memberships, direct and inherited....and this can be confusing when you're trying to mimic or duplicate another users memberships.

There is probably an MSsql operation or function that will let you traipse through this.

Basically to see the direct membership: KUAF.ID(Type=1) = KUAFCHILDREN.CHILDID, then you take KUAFCHILDREN.ID and pull out the names of the Groups from the KUAF (Type = 0).

Colin J.

On Mon, Jan 20, 2014 at 9:19 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE [EXTERNAL]How to build report of every user in each group Posted byappu.n.nair@conocophillips.com (Nair, Appu)On 01-20-2014 09:16

For 'A' group it's kuaf.id will be present in Kuafchildren throw some niceties and you have your query search for Kuafchildren in this forum to get some useful ones

Sent with Good (www.good.com)

-----Original Message-----
From: eLink Entry: Content Server LiveReports Forum [livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, January 20, 2014 07:57 AM Central Standard Time
To: eLink Recipient
Subject: [EXTERNAL]How to build report of every user in each group

How to build report of every user in each group Posted by im.cd@accenture.com (Accenture, Accenture) On 01-20-2014 08:49

Hello all. I am running MSSQL as my database for CS 10.0.0 UP2 SP 11. I am trying to build a report that lists each group, with all of the users in that group for every group in Content Server. My query is as follows:

SELECT c.name as "Group", a.firstName, a.lastName
FROM kuaf a, dtree b, kuaf c
WHERE a.id=b.dataid and a.firstName!='?' and a.deleted='0' and a.groupid=c.id;
My problem is that this report and query are only showing that every user is in ONE group only. However there are cases in which one user account is in several different groups, for various reasons. I would like to be able to build a report that both shows how many different groups a user is in and also show a group by group breakdown with what users are in each group. Thanks everyone!

[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center
[To post a comment, use the normal reply function]
Topic: How to build report of every user in each group
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.

Accenture_Accenture

Thank you both! I found something on the forums after the mention of KUAFChildren and am now using the following:

SELECT a.Name "Groups", c.Name "Users", c.firstName "First Name", c.lastName "Last Name"
From KUAF a, KUAFChildren b, KUAF c
WHERE a.ID=b.ID
AND b.ChildID=c.ID
AND a.Type=1
And c.Type=0
ORDER BY c.Name

Quoted Colin Schmidt on 2014-01-20 11:52:

There is probably an MSsql operation or function that will let you traipse through this.

Basically to see the direct membership: KUAF.ID(Type=1) = KUAFCHILDREN.CHILDID, then you take KUAFCHILDREN.ID and pull out the names of the Groups from the KUAF (Type = 0).

Colin J.

On Mon, Jan 20, 2014 at 9:19 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE [EXTERNAL]How to build report of every user in each group Posted by appu.n.nair@conocophillips.com (Nair, Appu) On 01-20-2014 09:16

For 'A' group it's kuaf.id will be present in Kuafchildren throw some niceties and you have your query search for Kuafchildren in this forum to get some useful ones

Sent with Good (www.good.com)

-----Original Message-----
From: eLink Entry: Content Server LiveReports Forum [livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, January 20, 2014 07:57 AM Central Standard Time
To: eLink Recipient
Subject: [EXTERNAL]How to build report of every user in each group

How to build report of every user in each group Posted by im.cd@accenture.com (Accenture, Accenture) On 01-20-2014 08:49

Hello all. I am running MSSQL as my database for CS 10.0.0 UP2 SP 11. I am trying to build a report that lists each group, with all of the users in that group for every group in Content Server. My query is as follows:

SELECT c.name as "Group", a.firstName, a.lastName
FROM kuaf a, dtree b, kuaf c
WHERE a.id=b.dataid and a.firstName!='?' and a.deleted='0' and a.groupid=c.id;

My problem is that this report and query are only showing that every user is in ONE group only. However there are cases in which one user account is in several different groups, for various reasons. I would like to be able to build a report that both shows how many different groups a user is in and also show a group by group breakdown with what users are in each group. Thanks everyone!

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

[To post a comment, use the normal reply function]
Topic: How to build report of every user in each group
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.

Colin_Schmidt

There's also a way to use a SubReport. Report 1 lists all the groups and the counts with a link to the right. Click on the link and you get all the members (Users and Groups).

The Attachment contains those two LRs, but they're for Oracle. It makes for a shorter list if it's for inquiry.

Colin J.

On Mon, Jan 20, 2014 at 11:56 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re RE [EXTERNAL]How to build report of every user in each group Posted byim.cd@accenture.com (Accenture, Accenture)On 01-20-2014 11:52

Thank you both! I found something on the forums after the mention of KUAFChildren and am now using the following: SELECT a.Name "Groups", c.Name "Users", c.firstName "First Name", c.lastName "Last Name"
From KUAF a, KUAFChildren b, KUAF c
WHERE a.ID=b.ID
AND b.ChildID=c.ID
AND a.Type=1
And c.Type=0
ORDER BY c.Name
Quoted Colin Schmidt on 2014-01-20 11:52:

Using KUAFCHILDREN in a direct way will get you direct membership. When you click on Groups for a User, it lists all memberships, direct and inherited....and this can be confusing when you're trying to mimic or duplicate another users memberships.

There is probably an MSsql operation or function that will let you traipse through this.

Basically to see the direct membership: KUAF.ID(Type=1) = KUAFCHILDREN.CHILDID, then you take KUAFCHILDREN.ID and pull out the names of the Groups from the KUAF (Type = 0).

Colin J.

On Mon, Jan 20, 2014 at 9:19 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE [EXTERNAL]How to build report of every user in each group Posted by appu.n.nair@conocophillips.com (Nair, Appu) On 01-20-2014 09:16

For 'A' group it's kuaf.id will be present in Kuafchildren throw some niceties and you have your query search for Kuafchildren in this forum to get some useful ones

Sent with Good (www.good.com)

-----Original Message-----
From: eLink Entry: Content Server LiveReports Forum [livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, January 20, 2014 07:57 AM Central Standard Time
To: eLink Recipient
Subject: [EXTERNAL]How to build report of every user in each group

How to build report of every user in each group Posted by im.cd@accenture.com (Accenture, Accenture) On 01-20-2014 08:49

Hello all. I am running MSSQL as my database for CS 10.0.0 UP2 SP 11. I am trying to build a report that lists each group, with all of the users in that group for every group in Content Server. My query is as follows:

SELECT c.name as "Group", a.firstName, a.lastName
FROM kuaf a, dtree b, kuaf c
WHERE a.id=b.dataid and a.firstName!='?' and a.deleted='0' and a.groupid=c.id;

My problem is that this report and query are only showing that every user is in ONE group only. However there are cases in which one user account is in several different groups, for various reasons. I would like to be able to build a report that both shows how many different groups a user is in and also show a group by group breakdown with what users are in each group. Thanks everyone!

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

[To post a comment, use the normal reply function]
Topic: How to build report of every user in each group
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.

[To post a comment, use the normal reply function]
Topic:	How to build report of every user in each group
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.

UsersGroups_LiveReport.rpt