Identifying User Roles via Advanced Reports

Overview

Advanced reports allow users to create custom reports using SQL queries to directly query the Sugar® database for more flexible and extensible reporting. This article covers how to create an advanced report that displays your instance's user accounts and their assigned roles.

For more information on creating advanced reports, please refer to the Advanced Reports documentation.

Use Case

This example report will produce a list of all users in your Sugar instance along with their assigned roles. This type of report cannot be created using the Reports module, so we will use advanced reports.

Prerequisites

• You must be an administrator user and be familiar with SQL and Sugar's database structure in order to write the necessary custom queries.
• Your Sugar instance must be running on a MySQL database for the query provided in the Steps to Complete to work. All instances hosted in SugarCloud use MySQL. For an alternative query for use in Microsoft SQL Server databases, see the Microsoft SQL Server Alternative section.
• You should already have several users with different roles configured in your instance before creating the report. For more information on roles, please refer to the Role Management documentation.

Steps to Complete

Advanced reports are a combination of three related modules: Custom Queries, Data Formats, and Advanced Reports. In order to create an advanced report, you must create a record within each of these three modules and then relate those records. The following steps cover how to create each of these components (custom query, data format, and advanced report records) and relate the advanced report record to the data format record in order to generate the final report.  

1. Click "Manage Advanced Reports" in the Reports module tab menu to access the Advanced Reports module and expose its navigation tab.
   
2. Open the Advanced Reports module tab menu and select "Create Custom Query".
   
3. Enter appropriate values for the fields (e.g. Query Name) in the edit view layout.
4. Enter the following SQL select statement into the Custom Query field:
   

   SELECT users.user_name "User Name"
   ,CONCAT(IFNULL(users.first_name,''),' '
   ,IFNULL(users.last_name,'')) 'Full Name'
   ,acl_roles.name 'Role' 
   ,users.title 'Title'
   ,users.department 'Department' 
   
   FROM acl_roles_users 
   INNER JOIN users ON (acl_roles_users.user_id=users.id) 
   JOIN acl_roles ON (acl_roles.id=acl_roles_users.role_id) 
   
   WHERE acl_roles.deleted=0 AND users.deleted=0 AND acl_roles_users.deleted=0 
   ORDER BY users.user_name

   Note: See the Microsoft SQL Server Alternative section for the query to use with Microsoft SQL Server.

5. Click "Save".
   
   • Note: Once you click "Save", Sugar will determine whether the SQL statement provided in the Custom Query field is valid. If it is not, Sugar will display an error message, and you will need to correct the SQL statement before saving.
   
6. Access the Advanced Reports module tab menu again and select "Create Data Format".
   
7. Enter appropriate values for the fields (e.g. Data Format Name) in the edit view layout and select the custom query record (e.g. User List with Roles) created in Step 5. Click "Save".
   
   • Note: Mark the Show Header checkbox if you want the column names to display above the data rows in the report. Also, mark the "Exportable (CSV File Only)" box to enable the Export option for the report.
   
8. We now have all the components required to build the advanced report. Click the triangle in the Advanced Reports module tab and select "Create Advanced Report".
9. Enter appropriate values for the fields (e.g. Report Name) in the edit view layout. Click "Save". You will return back to the Advanced Reports list view.
   
10. We will now relate the advanced report to the data format record. Locate the advanced report record (e.g. User List with Roles) created in Step 9 and click the record's name to open it in detail view.
11. Below the advanced report information, click the Select button in the Data Format subpanel. Locate the data format record (e.g. User List with Roles Format) created in Step 7 and click the record's name to link it to the advanced report.
    
12. After relating the appropriate data format record to the advanced report, you can run the report by clicking the Run Report button at the top of the detail view of your advanced report.
    

Microsoft SQL Server Alternative

The query provided in the Steps to Complete is written for MySQL, which is the database used by all SugarCloud instances. Below is an alternative query for use in Microsoft SQL Server:

  SELECT u.user_name 'User Name'
,CONCAT(IsNULL(u.first_name,''),' ',IsNULL(u.last_name,'')) 'Full Name'
,u.title 'Title'
,u.department 'Department'
,r.name 'Role'

FROM users u
JOIN acl_roles_users ur on ur.user_id = u.ID
JOIN acl_roles r on r.id = ur.role_id

WHERE r.deleted=0
AND ur.deleted = 0
AND u.deleted=0
ORDER BY u.user_name

Application

The final report will return results based on the custom query and defined data format. You will now be able to view the list of users and their assigned roles in Sugar, which will look similar to this example report:
 

Users with no assigned roles will be omitted from the report. Users with multiple assigned roles will be displayed in as many rows as they have roles in the report, as seen in the example with user 'chris', who has been assigned to two roles. Return to the finished report at any time to view the current list of users and their assigned roles. Simply open the advanced report (e.g. User List with Roles) and click the Run Report button at the top of the page to generate the report's results:

You can export the advanced report's data format table to a .CSV (comma-separated values) file by clicking "Export" above the results table. For more information on exporting data formats, please refer to the Advanced Reports documentation. If the Export link is not available to select, you will need to edit the data format record and enable the "Exportable (CSV File Only)" option.