Report Showing All Active Groups

Question

Originally posted by Unknown

How do I create a report that shows all active groups including the Created Date and Last Modified Date?

Answer 1

Originally posted by Unknown

To create a report of all active groups you'll need to create a page and add a dynamic data block.

To the sql box you'll add the following query:

SELECT T.GroupName, T.CreatedDateTime, T.ModifiedDateTime, T.ActiveGroupMembersCount, T.GroupTypeName, T.GroupId
FROM
(
SELECT COUNT(GM.[Id]) AS ActiveGroupMembersCount
        , G.[Name] AS GroupName
        , G.[CreatedDateTime]
        , G.ModifiedDateTime
        , G.GroupTypeId AS TypeId
        , GT.[Name] AS GroupTypeName
        , G.[Id] As GroupId
FROM [GroupMember] GM
INNER JOIN [Group] G ON G.[Id] = GM.[GroupId]
INNER JOIN [GroupType] GT ON GT.Id = G.GroupTypeId 
WHERE G.GroupTypeId NOT IN (1, 10, 11, 12) AND G.CreatedDateTime IS NOT NULL AND G.IsActive = 1
GROUP BY G.[Name], G.CreatedDateTime, G.GroupTypeId, G.ModifiedDateTime, GT.[Name], G.[Id]

) T
WHERE T.ActiveGroupMembersCount > 0

The query will provide the following information in the report:

• Group Name

• Created Date Time

• Modified Date Time

• Active Group Members Count

• Group Type Name

• Group Id

The query above does not include:

• Any groups that are a Security Role, Family, Peer Network, or Known Relationship

If you'd like to link the groups in the report, you'll need to add the following information to the 'Selection URL' field in the block: /people/groups?GroupId={GroupId}