Skip to main content

Data Export: All users, their assigned menu sets and assigned roles

Data export to show all users, their assigned menu sets and assigned roles.

H
Written by Harry Ledger
Updated over 3 weeks ago

  1. Log in to the SelectHR Administrator Tool.

  2. Click Import/Export Data.

  3. Click Create an export definition.

  4. Click Select the data to export.

  5. Change the database to SELECTSYSTEM.

  6. Log in.

  7. Copy the below SQL:

    Select HR.[Person Details].[Informal Name], WEB.[Menu Sets].[Menu Set Name], Security.Roles.Name As ROLE, 'Individual' As [Menu Type], ' ' As [Group Name], Case When WEB.[Menu Sets].[Default Menu Item Number] Is Null Then ' ' Else 'Default' End As [Default Menu Set], HR.[Person Details].[Person Number], WEB.[User Menu Sets].[Read Only] From Security.Users Inner Join HR.[Person Details] On Security.Users.[Person Number] = HR.[Person Details].[Person Number] Inner Join WEB.[User Menu Sets] On Security.Users.[User Number] = WEB.[User Menu Sets].[User Number] Inner Join WEB.[Menu Sets] On WEB.[Menu Sets].[Menu Set Number] = WEB.[User Menu Sets].[Menu Set Number] Inner Join Security.Roles On Security.Roles.[Role Number] = WEB.[User Menu Sets].[Role Number] Union All Select HR.[Person Details].[Informal Name], [Menu Sets].[Menu Set Name], Security.Roles.Name As ROLE, 'Group' As [Menu Type], Security.Groups.[Group Name] As [Group Name], Case When WEB.[Menu Sets].[Default Menu Item Number] Is Null Then ' ' Else 'Default' End As [Default Menu Set], HR.[Person Details].[Person Number], Security.[Group Menu Sets].[Read Only] From Security.Users Inner Join HR.[Person Details] On Security.Users.[Person Number] = HR.[Person Details].[Person Number] Inner Join Security.[Group Memberships] On Security.Users.[User Number] = Security.[Group Memberships].[User Number] Inner Join Security.[Group Menu Sets] On Security.[Group Memberships].[Group Number] = Security.[Group Menu Sets].[Group Number] Inner Join WEB.[Menu Sets] On [Menu Sets].[Menu Set Number] = Security.[Group Menu Sets].[Menu Set Number] Inner Join Security.Roles On Security.Roles.[Role Number] = Security.[Group Menu Sets].[Role Number] Inner Join Security.Groups On Security.[Group Memberships].[Group Number] = Security.Groups.[Group Number]

  8. In the top left of the data export window, click Options, Paste SQL from clipboard.

  9. Click Data.

  10. Once the data export is created, a Primary Key must be selected. Click the Key Field icon and select the appropriate Key. The Key Field must be represented by a number (i.e. Person Number, Absence Number etc.). When the export is finished it can be saved for future use.

  11. Click OK to continue the export. The Data Export Wizard windows appears.

  12. In the Definition name field, enter a full name. This will be the name of the report saved in the Admin Tool, which can be edited in the future, if needed. There is more information available about how to edit an existing report.

  13. Click Browse to choose a location for the report exported to Excel.

  14. In the File name field, enter a name. This will be the name of the Excel document once exported on the local machine.

  15. Use the File Format drop-down and select Microsoft Excel 97-2003.

  16. Click Next. A preview of the data appears.

  17. In the Column section under the columns preview, click a column header to select a column and set export options.

  18. Click Next.

  19. Click the Open file after export check box. The document will then open once the export is completed.

  20. Click Finish. The Export will now appear in the External Data Transfers list and can be run again.

Related Articles
Data Export: Emergency contact information
Data Export: Employees and their assigned working patterns/schedules
Data Export: All users and their last login time and date
Data Export: All running absence workflows
Data Export: Auditing User Account logins, Menu Set & Group Assignments
Did this answer your question?