Sample Report: Counting Users In Each Role

The sample report in this short tutorial lists the number of users in each role of the site. It is based on using a new instance of Reports and accepting all defaults for other fields.

1. Go to a Reports module.
2. Select Manage > Settings from the module actions menu.
3. Go to the Data Source Settings section of the Report Settings tab.
4. In the Title text box, enter Users In Roles.
5. In the Description text box, enter The number of users in each role in the site.
6. At Active Data Source, select DotNetNuke Data Source.
7. In the Query text box, enter the query below:
   SELECT R.RoleName, COUNT(UR.UserID) AS 'UsersInRole'
   FROM {oQ}UserRoles AS UR
   INNER JOIN {oQ}Roles AS R ON UR.RoleID = R.RoleID
   WHERE PortalID = @PortalID
   GROUP BY R.RoleName
   
8. Go to the Display and Caching Settings section and mark the Show Info Pane and Auto Run Report check boxes.
9. Click the Update button.

Sample Report: Counting Users In Each Role