SugarCRM comes equipped with reporting feature. We will look into the same in details in this article. You may already know that SugarCRM has a feature to generate lists within the respective modules. However, for an effective SugarCRM implementation, you may have more complex reporting needs.  To handle such requirements SugarCRM provides ‘Advanced Reports’.

CRM assessment reaching out to our consultant

These are the reports generated directly of the SQL statements on your SugarCRM database. It allows the administrators to create a custom report in a single SQL query. In this article, we are going to see how to create and run the advanced report in the SugarCRM, which will display records based upon the custom query and data format given to the advanced report.


Advanced Reports SugarCRM components

Advanced reports allow administrators to create custom reports using one or more SQL queries to your database. Although the Reports module may be used to create many kinds of reports. Advanced reports are a combination of three related aspects. They are listed and explained below.

ComponentExplanation
Custom QueriesThis contain the SQL statement to be used to get the information from SugarCRM database.
Data FormatsThey describe how to format / display of the output will be.
Advanced ReportsThese report records display or or more data formats.

Administrators can create records where Non-Admin users can view advanced reports.

Advanced Reports Module Tab:

  • Navigate to Reports > Manage Advanced Reports enables you to access the Advanced Reports.

Navigating to reports module in SugarCRM

  • If you click on the Advanced Reports tab it will display the list view.

Click on the drop-down to create in reports module

Creating Advanced Reports

Since a single advanced report consists of records from three related modules, the following instructions will show the creating a record within each module and relating those records to generate a report.

  • Click on the Advanced Reports module tab to open the actions menu and select “Create Advanced Report”.

Click on create an advanced report in SugarCRM

  • Enter values for the fields in the edit view and click on “Save”.

Click on the Create advanced report

Creating Custom Query Records

Advanced reports enable you to create reports using complex SQL queries. The SQL query  is there in a custom query record. Each custom query record can be related to multiple data format records which allows it to be reused in multiple reports.

Select Create create a custom query in the reports module

  • Click on the Advanced Reports module tab to create a custom query “Create Custom Query”.
  • Enter the below SQL select statement into the Custom Query field.
Copy to Clipboard

 

Entering SQL custom query

  • Click “Save”.

Creating Data Format Records:

Data format records describe how to format and display the results of the related custom query and it specifies reports  of formatting details such as the text font, text color, header, font size  and the background color for a particular SQL query contained in a custom query record.

  • Click on the Advanced Reports module tab to create a custom query “Create Data Format”

Select Create Data format from reports drop-down

  • Enter the values for the fields in the edit view. For the Query Name field, select the custom query record from the custom queries table.
  • Click on “Save”.

Entering required details in data format edit view in SugarCRM

How to Run the Advanced Report:

After creating all the records in the advanced report menu tab.

  • Click the Advanced Reports module tab to access the Advanced Reports list view.
  • Search for the advanced report record created in the steps above and click the record’s name to open it in detailview.
  • Below the advanced report information, click on the Select button and select the custom data formats. shown in the below screenshot

Running a report in SugarCRM

  • Finally, click on “Run Report” and it will display the records given in the query headers and you can export the separate table records into csv shown in the below screenshot.

Exporting report in SugarCRM

Advantages of Advanced Reports in SugarCRM

In the business implementation time and again a requirement for an advanced report comes up, where a complex report needs to be generated relating to multiple modules based upon just 1 or 2 filter criteria.  In such tight spaces where the regular report module cannot be used, the advanced reports come in handy providing a mechanism at the interface level to directly execute the required underlying query and display the outcome in the required format.

  • Exporting the data from each table shown in the advanced reports.
  • Selecting multiple data formats.
  • Making complicated sub-queries.
  • Limits can be used for the records shown.

Advanced Reports is an effective way for the data coming into Advanced Reports are based on SQL queries to control over the data that will be provided in the report, like the ability to use sub-queries.

About SugarCRM

SugarCRM is easily one of the world’s largest CRM (customer relationship management) software platform. Recognized by leading market analysts as a visionary and innovator and used for small to midsize business organizations to maintain a company’s interaction with current and potential customers with full pre-sales and post-sales cycles. It comes with great scope to customize the platform according to the business needs. Making best out of it for the users will increase their productivity and that leads to an increase in the revenue and profitability for the organization.


Evaluating SugarCRM for your business – We can help

Reach out to us so that we can assess and plan a road-map for your CRM implementation. Let’s build a system, which you will use for years to come.

Contact us for a free assessment