Custom Report - Account, Role and Role Permissions

Summary

This article explains how to generate a custom report that will show you all accounts, the associated role, owner, role specifics and the role permissions associated with the accounts.

If you are looking for information on adding roles, setting permissions or importing predefined role sets, see Roles – Adding Roles, Setting Role Permissions and Importing.

Creating the Permissions Report

Create the Query

1. Load Microsoft SQL Server Management Studio

2. Expand the Databases in the Object Explorer view

3. Right click on 'EngageIP' and select 'New Query'

4. The SQL Query pane should load, paste the SQL below into the SQL Query pane:

   SELECT dbo.[USER].ID AS UserID, dbo.[USER].Name AS [USER], dbo.Owner.Name AS Owner, dbo.Role.Name AS RoleName, dbo.Role.PasswordChangeDays, dbo.Role.PasswordMinimumLength, dbo.Role.PasswordMustContainNumbers, dbo.Role.ForcePasswordChangeOnFirstLogin, dbo.Role.FailedLoginLimit, dbo.Role.LockoutDuration, dbo.Role.PasswordHistoryLength, dbo.RolePermissions.Name AS PermissionName FROM
   dbo.[USER] INNER JOIN
   dbo.Role ON dbo.[USER].RoleID = dbo.Role.ID INNER JOIN
   dbo.RolePermissions ON dbo.Role.ID = dbo.RolePermissions.RoleID INNER JOIN
   dbo.Owner ON dbo.[USER].OwnerID = dbo.Owner.ID AND dbo.[USER].ActingOwnerID = dbo.Owner.IDClick Save then enter the name of the query (remember the name of the view created as it will be used below)

5. Click the Execute button to check that the query runs

6. Click Save then enter the name of the query (remember the name of the view created as it will be used below

EngageIP Setup

1. Click the Setup tab

2. Click Custom Reports

3. Click the Add button

4. Enter a Name (no special characters or spaces, under scores can be used in place of spaces) for the report. The value entered here will become a role permission enabling only accounts within that role to view the custom report. It is recommended that you name all your custom reports with a common prefix so that they will be easy to locate within the role permission list

5. Enter the following Connection String; replacing User ID and Password (XYZ) with the correct values:

   server=localhost;Trusted_Connection=FALSE;DATABASE=boss;uid=XYZ;pwd=XYZ

6. Enter the path and name of saved sql file in the Table field (be sure to include the full path and file extension, i.e. 'c:\xxxx\xxx\queryfilename.sql')

7. Click Save

8. Log out and then back in to EngageIP. This will reset the role permission set and allow you to grant access to the report

9. Click on the Setup tab

10. Click Roles

11. Click on the name of the role from in the list that you wish to grant view permissions on for this report

12. Scroll down to the Role Permissions section and locate the name of the report within the list

13. Check the List check box next to that permission

14. Scroll to the bottom of the permission set and click the Save button

15. Repeat steps 11-15 above until all applicable roles have been granted permission to view the report

16. Click the Reports tab

17. Scroll to the bottom of the screen where you will see a new section entitled; Custom Reports

18. Click on the Name of your Custom Report to view the output

    

Note: the image above does not display all the columns that are present in the table. The SQL can be adjusted as necessary to display the columns desired. Alternatively content presented on the report can also be controlled via the 'View' configuration.

Like all other lists within the application, custom reports can be filtered, printed, and/or exported using the appropriate list icons.