This blog discusses how to create standard reports in SugarCRM using the built-in reports module. Use the Cross Module Reports module to view, create, and manage reports on Sugar modules.

The Reports Home page displays the following information:

• A Search panel where you can use parameters such as the title, module, report type, assigned user or team to search for a report. You can also search for reports that you bookmarked as your favourite reports.
  To search only for reports that you created, select the only my items box.
  To perform an advanced search using additional fields, click the Advanced Search tab.
  To customise and save the search layout and results, click the Saved Search & Layout link on the Advanced tab.
  
• A list of existing reports. To view the details of a report, click its name in the list.
  
• A Shortcuts section that lists the following:
  

  Create Report: Click this option to create a report.
  My Favourite Reports: Click this option to view the reports that you marked as your favourites.
  On a report's Detail View, click Mark as Favourite to add it to the My Favourite Reports list.
  All Reports: Click this option to view the list of existing reports. The list display only reports that you have permissions to view.
  Enterprise Reporting: This option, which is visible only to Sugar Enterprise users, lists enterprise reports created by the administrator using complex SQL queries.

Creating Reports

When you create and save a report, the system saves the report definition. Only the assigned user or a member of the assigned team has the permissions to change the report definition. If the report definition is modified, the system displays an alert when you attempt to run the report. However, Sugar provides a run-time filter option that allows you to modify report filter values when you run the report.

The Report Wizard still retains the original filter values for the report and uses them when another user runs the report. This enables users to modify a shared report for their use without altering the original filter values. Your run-time filter values are preserved for use when you run the report again. However, if another user changes the filters, your run-time filter values will be deleted.

The process of creating a report consists of several steps, which are listed below. You must specify values at every step of the process to proceed to the next step.

• 1. Click Create Report and select one of the following report types.
  

  Rows and Columns: This report displays data in rows and columns that reflect the value of selected fields such as Name and Address.
  Summation: This report displays the total count of records that match the specified criteria.
  Summation with Details: This report, in addition to displaying the total count of records, enables you to group data.
  The group-by data is visually represented in the report output.
  Matrix: This report, which is similar to a summation report, displays totals for all summaries in a grid format.
  You can use a maximum of three group-by columns to group the data and select a layout option to view the report results.

• 2. Select the module for which you want to create the report. This is the primary module.
• 3. On the Define Filters page of the Report Wizard, select the filters. The related modules are listed below the primary module. When you select the primary module or a related module from the Related Modules list, its fields display in the Available Fields list below.
  
  

  For Summation, Summation with Details, and Matrix reports:

  Click Next, and select the modules that you would like to use to group records and then select the fields in the Available Fields pane.
  For matrix reports, you can use a maximum of three fields to group records.
  Click Next and select the display summaries.
  Click Next and, optionally, select a chart type such as Pie chart and Horizontal Bar chart.
  For a Summation with Details report, click Next and select the display columns.
  Select the appropriate Order By button to order the report output by the corresponding label.

  For Rows and Column reports:

  On the Define Filters page, select a module and then select the fields in the Available Fields pane to add them as filters.
  Click Next and select the display columns. Select the appropriate Order By button to order the report output by the corresponding label.

• 4. Click Next to move to the Report Details page.
• 5. Click Preview to view the report results without saving the report; click Save to save the report and run it later; Click Save and Run to save the report and run it immediately.
  
  When you save a report, you can choose to display the SQL query that was used to create the report. You can also assign a user and a team to manage the report. Select the Optional Related Modules box to display records from the primary module even if there are no records from the related modules. When this option is not selected, primary module records will display only if the related module records exist.

Setting Filters

Filters consists of fields, operators, and, optionally, values depending on the operator. The choices for operators vary, depending on whether you have selected a text field, numeric field, or date field. Some examples of an operator are "Starts With", "Ends With", "Today", and "Tomorrow".
You use the Add Filter Group button to create filter groups. You can create nested filter groups with a combination of AND and OR operators for complex queries.

You can use the User Name field to run a report on a specific user. For example, you can run a report on all the opportunities assigned to Joe. You also have the option of using a dynamic filter called "Current User", who is any user that is currently logged in and running the report.

Creating Charts

You can create charts for Summation, Summation with Details, and Matrix reports. You can create a horizontal bar chart, vertical bar chart, line chart, pie chart, and funnel chart. You must specify at least one Group By column and one Summary column to render a chart.

To create a chart, on the Chart Options page of the Report Wizard, enter the following information:

• Chart Type. Select a type, such as Horizontal or Vertical this drop-down list. Note that Line charts and Pie charts are not available for multiple groupings.
• Data Series. Select an available grouping function from the drop-down list, such as Count, AVG, MIN, MAX. The options that display in this drop-down list depends on the group-by options and display columns options that you select to create the report.
• Description. Enter a brief description of the chart.

For useful tips and hints for SugarCRM, subscribe to our Blog feed, follow me on Twitter (@EnableITuk), connect with me on LinkedIn or join our LinkedIn group (SugarUK United Kingdom)