5 Replies Latest reply on Apr 7, 2010 4:33 AM by theparallaxview

    Tracking how many members in each group

      I'd like to be able to view the number of members in each of the groups in our community.  I know I can see some variation of this using the dashboard, but if I wanted to know the entire membership (active, non-active, etc.) of all my groups at any given time, would that be a custom report?

        • Re: Tracking how many members in each group

          We at Manheim are looking for the exact same report. This data is very essential for a community, i wish this could be made a standard one in the analytics module.

          • Re: Tracking how many members in each group

            While I don't have the exact report you are looking for, I have something close....

             

            This shows the number of unique people who were participating within a group:

            SELECT MONTHS, GROUPS, DISTINCT_USERS  FROM   (  SELECT jivedw_month.begin_ts as MONTHS, group_name as GROUPS, count (distinct user_id) as DISTINCT_USERS  FROM  (  SELECT A.month_id as MONTH_ID, 'RESPOND' as ACT, 'THREAD' as OBJ, C.name as GROUP_NAME, A.user_id as user_id   FROM jivedw_activity_agg_month A, jivedw_object B, jivedw_container C  WHERE   A.activity_type = 20  AND A.direct_object_type = 2  AND A.direct_object_type = B.object_type  AND A.direct_dw_object_id = B.dw_object_id  AND A.dw_container_id = C.dw_container_id  AND A.dw_container_id IS NOT NULL  AND C.container_type = 700  AND B.object_id in   (  Select message_ID  from jivedw_message    where parent_message_id > 0  )     UNION      SELECT A.month_id, 'CREATE' as ACT, 'THREAD' as OBJ, B.name as GROUP_NAME, A.user_id as user_id   FROM jivedw_activity_agg_month A, jivedw_container B   WHERE A.dw_container_id = B.dw_container_id  AND A.dw_container_id IS NOT NULL  AND B.container_type = 700  AND A.direct_object_type = 1  AND A.activity_type = 20      UNION    SELECT A.month_id, 'CREATE' as ACT, 'COMMENT DOCUMENT' as OBJ, B.name as GROUP_NAME, A.user_id as user_id   FROM jivedw_activity_agg_month A, jivedw_container B   WHERE A.dw_container_id = B.dw_container_id  AND A.dw_container_id IS NOT NULL  AND B.container_type = 700  AND A.direct_object_type = 105  AND A.activity_type = 20  AND A.indirect_object_type = 102      UNION    SELECT A.month_id, 'CREATE' as ACT, 'COMMENT VIDEO' as OBJ, B.name as GROUP_NAME, A.user_id as user_id   FROM jivedw_activity_agg_month A, jivedw_container B   WHERE A.dw_container_id = B.dw_container_id  AND A.dw_container_id IS NOT NULL  AND B.container_type = 700  AND A.direct_object_type = 105  AND A.activity_type = 20  AND A.indirect_object_type = 1100      UNION    SELECT Y.month_id as MONTH_ID, 'CREATE' as ACT, 'COMMENT BLOGPOST' as OBJ, Z.GROUP_NAME as GROUP_NAME, Y.USER_ID as user_id  FROM  (  Select A.month_id as MONTH_ID, C.blog_id as BLOG_ID, A.activity_count as ACTIVITY_COUNT, A.user_id as USER_ID  from jivedw_activity_agg_month A, jivedw_object B, jivedw_blogpost C  where A.indirect_object_type = 38  AND A.direct_object_type = 105  AND A.activity_type = 20  AND B.object_type = 38  AND A.indirect_dw_object_id = B.dw_object_id  AND B.object_id = C.blogpost_id  )Y,   (  SELECT B.name as GROUP_NAME, C.object_id as BLOG_ID  FROM jivedw_activity_agg_month A, jivedw_container B , jivedw_object C  WHERE A.dw_container_id = B.dw_container_id  AND a.direct_dw_object_id = C.dw_object_id  AND A.dw_container_id IS NOT NULL  AND B.container_type = 700  AND A.direct_object_type = 37  AND A.activity_type = 20  )Z  WHERE Y.BLOG_ID = Z.BLOG_ID      ) as JOINED, jivedw_month  WHERE JOINED.month_id = jivedw_month.month_id  GROUP BY begin_ts, group_name  ) as TRY  ORDER BY MONTHS, DISTINCT_USERS DESC