Advanced Reporting in Sugar on Change Logs

by Roxana Dicu on July 10, 2018

In part 1 of this blog, we discussed the Sugar Studio feature called  "Field Auditing" and how it enables users to view the history of changes via each record's audit log more easily.

In this second section of this blog post, we will walk you through Reporting on Record Change Logs via Advanced Reports and will explain how to create an advanced report that tracks changes made to audited fields.

Use Case

The following sections cover example use cases and the SQL select statements that should be used when creating the custom query record.

Tracking Account Changes in the Last Seven Days

To create an advanced report to track the history of changes made to records in the Accounts module over the last seven days, please use the following SQL select statement:

SELECT IFNULL(accounts.id,'') account_id
,IFNULL(accounts.name,'') accounts_name
,IFNULL(accounts_audit.date_created,'') `date_created`
,IFNULL(accounts_audit.created_by,'') `created_by`
,IFNULL(accounts_audit.field_name,'') `field_name`
,IFNULL(accounts_audit.before_value_string,'') `before`
,IFNULL(accounts_audit.after_value_string,'') `after`
 
FROM accounts
LEFT JOIN accounts_audit accounts_audit ON accounts.id = accounts_audit.parent_id
 
WHERE (((accounts_audit.date_created >= DATE_SUB(CURDATE(), INTERVAL 7 DAY))))
AND accounts.deleted=0;

Tracking Opportunities' Durations in Sales Stages

To create an advanced report to track how many days opportunities have been in each sales stage, please use the following SQL select statement:

SELECT
   o.name "Opportunity Name",
   FORMAT(o.amount, 2) "Amount",
   rla.before_value_string "Sales Stage",
   rla.date_created "Date Changed",
   DATEDIFF(
      rla.date_created,
      (SELECT date_created FROM revenue_line_items_audit WHERE field_name = 'sales_stage' AND parent_id = 
rli.id AND date_created < rla.date_created ORDER BY date_created DESC LIMIT 1)
   ) "Duration (Days)"
FROM
   revenue_line_items rli
   INNER JOIN revenue_line_items_audit rla ON rla.parent_id = rli.id
   INNER JOIN opportunities o ON o.id = rli.opportunity_id
WHERE
   rli.deleted = 0
   AND o.deleted = 0
   AND rla.field_name = 'sales_stage'
   AND rla.date_created BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
ORDER BY rli.id, rla.date_created]

Steps to Complete

Click the triangle in the Reports module tab and select "Manage Advanced Reports" to access the Advanced Reports module and expose its navigation tab, which is referenced in some of the steps below. The following sections cover how to create each of these components (the custom query, data format, and advanced report records) then relate the advanced report record to the data format record to generate the final report.   

Creating the Custom Query Record

Use the following steps to create and define a custom query that will retrieve the history of changes:

1. Click the triangle in the Advanced Reports module tab and select "Create Custom Query."

Create Custom Query in Sugar 8

2. Enter appropriate values for the fields (e.g., Query Name, Description, etc.) in the edit view layout.

3. Enter the SQL statement specific to your desired use case into the Custom Query field. The populated Custom Query field would look similar to this:

Saving SQL Custom Query

Creating the Data Format Record

Use the following steps to define the report formatting details and relate the custom query record created above:

1. Click the triangle in the Advanced Reports module tab and select "Create Data Format."

Create Advanced Report in Sugar 8

2. Enter appropriate values for the fields (e.g., Data Format Name, etc.) in the edit view layout and select the custom query record (e.g., Audit on Accounts Query - Last 7 days) created in the Creating the Custom Query Record section above.

Note: Select the Show Header checkbox if you want the column names to display above the data rows in the report.

3. Click "Save."

Save Query Record

Creating the Advanced Report Record

Use the following steps to create the advanced report record:

Click the triangle in the Advanced Reports module tab and select "Create Advanced Report."

Create Advanced Reports in Sugar 8

2. Enter appropriate values for the fields (e.g., Report Name, etc.) in the edit view layout.

3. Click "Save."

Save Report

Relating the Advanced Report and Data Format Records

Once you have created the custom query, data format, and advanced report records per the instructions above, use the following steps to relate the advanced report to the data format record:

1. Click the Advanced Reports module tab to access the Advanced Reports list view.

2. Locate the advanced report record (e.g., Audit on Accounts - Last 7 days) created in the advanced report section above and click the record's name to open it in detail view.

Search Advanced Report in Sugar

3. Below the advanced report information, click the Select button in the Data Format subpanel.

4. Locate the data format record (e.g., Account Audit - Last 7 days) created in the data format section above and click the record's name to link it to the advanced report.

Link data to Advanced Reports

Running the Advanced Report

After relating the appropriate data format record to the advanced report, you can generate the final report. Simply click the Run Report button at the top of the detail view of your advanced report.

Run Advanced Report

Viewing the Audit Log on a Record in Sugar

The final report will return results based on the custom query and defined data format. It will look similar to the following examples depending on the use case and SQL statement you used:

Exporting the Advanced Report

You can export the advanced report's data format table to a CSV (comma-separated values) file by clicking "Export" above the results table.

This concludes our walkthrough of Field Auditing in Sugar. For more information don’t hesitate to contact us.

Contact Us

Find similar articles in these categories:

AUDIENCES: Administrators End Users

Roxana Dicu
Associate Marketing Manager at UpCurve Cloud
More From This Author »