Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Create a Group Membership Report

Created: 15 Feb 2013 • Updated: 26 Aug 2013 | 2 comments

I have been trying to figure out a way to create a report in SD 7.5 report designer that will show me the list of groups that have membewrs assigned to them and also list those members for each group.   Output Example would be:

 

Support:

Joe Schmoe

John Doe

Support 2:

Jane Doe

Janet Schmoe

 

i do not have access to the backend of SQL for our implementation so I have no idea what the table or field structure (data dictionary) is for the SD schema.

 

The one script I did try was Select * From [Group] order by "group name"  however this did not provide the desired results.

 

Can anyone assist me with this?

 

Thank You

Comments 2 CommentsJump to latest comment

michael.george's picture

I just tested this on my 7.5 install, but I imagine it would work with 7.1 as well. I don't think any table names changed. Looks like you're using 7.5 though, so it should be fine. A quick note of caution. If you have a ton of users, this may be a really big report. You could easily add a where clause to leave out the 'All Users' group if that helps.

 

SELECT SDUser.[FirstName] + ' ' + SDUser.[LastName] AS Name
      ,GRP.[GroupName]
  FROM [dbo].[UserGroup] UG
  JOIN [dbo].[User] SDUser
    ON SDUser.[UserID] = UG.[UserID]
  JOIN [dbo].[Group] GRP
    ON UG.[GroupID] = GRP.[GroupID]
  Use that as a sql query and you'll end up with two columns in the report designer: name and groupname. You can then go into the report designer options tab and set the 'group by' option to the groupname column. You should end up with this, but with different group names, obviously.

 

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

LDAY's picture

Works like a charm, perfect, thank you so much.