Advanced Reporting in Sugar on Change Logs
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.
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.”
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:
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.”
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.”
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.”
2. Enter appropriate values for the fields (e.g., Report Name, etc.) in the edit view layout.
3. Click “Save.”
Relating the Advanced Report and Data Format Records
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.
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.
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.
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:
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.