SugarCRM’s Advanced Reporting Function – What is it really?

The Advanced Reporting function for SugarCRM, available in Sugar Enterprise and Ultimate Editions, takes reporting to a new level with SugarCRM.

Advanced reports enable you to use complex SQL queries that cannot be expressed in standard reports. Only administrators can create advanced reports and make them accessible to the appropriate users. Users can use advanced reports if the administrator grants them permission to view and run them.

Creating Advanced Reports

An advanced report is a combination of three separate elements:

  1. A SQL query to gather data from the Sugar database.
  2. A data format that specifies how to format the report and the SQL query to use.
  3. A report header that contains information such as the report name and the data format. You can associate multiple data formats with a report.

Header information such as the report name and title are defined by the user during advanced report creation. The report is saved and then edited to specify the data format. The data format must include the SQL query used to generate the report output. Depending on the queries and data formats used, the report can contain multiple data sets. Each individual data set can be exported separately.

 

To create an advanced report

1.         Click Manage Advanced Reports in the Actions drop-down list in the Reports tab.

2.         Click Create Advanced Report in the Actions drop-down list in the Advanced Reports tab.

3.         Enter information for the following fields in the Create (Advanced Report) page:

Report Name. Enter a name for the report.

Report Title. Enter a title for the report.

Description. Enter a brief description of the report.

Report Alignment. Select the alignment of the report text from the drop-down list.

Team. Select the name of the new primary team from the drop-down list to change the primary team assignment

Click Select to assign additional teams to the record, and choose the team from the Team List, or click Addto add team fields and enter the team names. For more information on teams, see Assigning Records to Users and Teams.

4.         Click Save.

This displays the new report in the List View of the Advanced Reports page.

5.         Click the report name in the list to view the Detail View of the created Advanced Report.

6.         To select a data format, click Select in the Data Format sub-panel and click the desired data format fromData Format Search or Data Format List..

To create a new data format, click Create New in the Data Format sub-panel. For more information, see To create a data format.

7.         Click Run Report to generate the report.

 

Using SQL Queries

Advanced reports enable you to create reports using complex SQL queries that cannot be expressed in standard reports. You can use a SQL query in multiple reports.

 

To create a SQL query

1.         Click Manage Advanced Reports from the Actions drop-down list in the Reports tab. and then clickCreate Custom Query.

2.         On the Custom Queries page, enter information for the following fields:

Query Name. Enter a name for the query.

Lock Query. Select this box if you want to ensure that nobody edits the query.

Team. To change the primary team assignment, select the name of the new primary team from the drop-down list.

To assign additional teams to the record, click Select, and choose the team from the Team List, or click Add to add team fields and enter the team names. For more information on teams, see Assigning Records to Users and Teams.

Description. Enter a brief description of the query.

Custom Query. Enter the SQL query in this field.

3.         To save the query, click Save.

 

Creating Data Formats

A data format specifies report formatting details such as the text font, text color and the background color. A data format can also specify a SQL query. You can reference other data formats within a data format. A data format can contain more than one SQL query. The query contained in the referenced data format is considered to be the sub-query or the child-query.

When a data format references another data format, the data formats are interlocked. Interlocked reports are useful to produce information from separate queries in a parent-child pattern. A given data format can specify a parent data format, interlocking the two data formats to produce a master Rows and Column report. When the report runs, the query associated with the parent data format is executed and, for each row returned, is associated with the child data format. The values that bind the two queries are specified by the {SUB} parameter in the child query.

For example, if the parent data format contains the following query:

select name, id from accounts

and the child data format contains the following query:

select opportunities.name, opportunities.amount from opportunities

left join accounts_opportunities on accounts_opportunities.opportunity_id = opportunities.id

left join accounts on accounts_opportunities.account_id = accounts.id

WHERE accounts.id = ‘{sub}id{sub}’

The combined data format does the following on execution:

  • Return and format one row of information from the Accounts table.
  • Return and format as many Opportunities rows matching the ID value of the current account.
  • Repeat for each applicable account row.

Note: A given data set identified as a parent in an interlocked data format cannot appear as a parent in another.

To create a data format

1.         Select Create Data Format from the Actions drop-down list of the Advanced Reports tab.

This displays the Data Formats >> Create page.

2.         Enter information for the following fields in the Data Formats Create page:

Data Format Name. Enter a name for the data format.

Report Name. Sugar populates this field with the report name when you associate this data format with the report.

Query Name. Click Select and, in the Custom Query List, click the name of a query to associate it with the data format.

Sub/Child Query. Sugar populates this field with the name of the child query if the selected query has a child query.

Parent Data Format. Click Select to create a child for another data format, click the name of the data format to select it from the Data Format List.

Description. Enter a brief description of the data format.

Table Width %. Select Percentage or Pixels from this drop-down list and enter a numeric value in the adjoining field.

Font Size. Select the font size for the report text from this drop-down list.

Exportable (CSV File Only). Select this box to enable users to export the data format in CSV format.

Header Text Color. Select a color for the report header from this drop-down list.

Show Header. Select this box to display the report header in the report.

Body Text Color. Select a color for the report text from this drop-down list.

Combine with Previous Data Format. Select this box to specify whether to leave a space between data format tables for display purposes.

Header Background Color. Select a background color for the report header from this drop-down list.

Group with Previous Header. Select this box to specify whether to make column widths the same as the data format above it.

Body Background Color. Select a color for the body background from this drop-down list.

3.         Click Save to save the format; Click Cancel to exit the page without saving the format.

 

Scheduling Reports

You can schedule to run a report at periodic intervals. Sugar runs the report at the specified intervals and emails it to you as a PDF file.

To schedule a report

1.         Navigate to the List View of Reports.

2.         Click –none–in the Schedule Report column for any report in the list.

This displays the Schedule Report dialog box.

3.         Enter values for the following fields in the Schedule Report dialog box:

Start Date. Click the Calendar icon and select a date to begin scheduling the report. You can specify the time in hours and minutes from the adjacent drop-down fields.

Time Interval. Select intervals at which the report runs and the output is received, from this drop-down list.

Active. Check this box to activate the schedule. The date and time of the next email is now displayed in theSchedule Report column. If you do not check this box, the schedule is not activated and the Schedule Report column displays –none–.

Next Email. The date and time that you specified displays in this field. This is the time when the report is emailed to you.

4.         Click Update Schedule.

Sugar schedules the report at the specified time and emails the PDF file.