Run Custom Reports

Custom reporting allows users to build reports from scratch. A user defines what columns should be included, column order, sort order, filters, and various grouping and aggregate options. Custom reports can be saved and exported.

Financial reports such as balance sheet, income statement, cash flow, etc., are not available in custom reports and can be found on the Financials page.

1. Navigate to the Reports page.
2. In the Custom Reports window, click [Create] next to the type of report that you want to create.
   • This directs you to a screen with a blank slate for you to build your report.
     
     
     
3. Click on field names in Available Fields to add fields to the report.
   • Using the search box to locate fields is highly recommended.
     • If your initial search doesn't yield the field you're looking for, try searching related terms. 
   • The field type is listed in [brackets].
     • Array - A collection of variables.
     • Boolean - A field that can be one of two values, yes/no or 1/0.
     • Date - The date associated with a piece of data, formatted as YYYY-MM-DD.
     • Integer - A whole number.
     • Numeric - A field with numbers. That can include ID's and amounts.
     • Varchar - A variable character field with an indeterminate length. These are fields that contain non-standardized text. 
       
       
       
     • After you add fields, they will appear in the Current Fields section.
       

Once you have fields in your report, there are a variety of options available. 

Add the Profile Id field to any report that contains that field to enable left-side menu bulk actions such as Add to Campaign.

Add the Funit Id field to any report that contains that field to enable left-side menu 990 options.

Reorder Columns

Columns display in the order they were added to the report. Reorder columns by clicking and dragging the horizontal lines next to the field name.

Sort Columns

The data is not in any particular sort order when you first build a report. Apply a sort by clicking a field name, either in Current Fields or in the headers above the report, then click Ascending or Descending next to Sort. Whichever button you click will then turn green. In the image below, Grant Amount, which is highlighted in the Current Fields and header row, is sorted by Descending order.

The sort is also indicated in Column Sort Priority on the left side of the image. You can add additional sort criteria. When more than one is applied, the second criteria applies within the initial sort criteria. You can reorder the sort priority by clicking and dragging the horizontal lines in the Column Sort Priority section.

To remove a sort, click on that field name, then click the sort that's green.

Update the Results Per Page

Custom reports default to 20 results per page. Adjust this by entering a new number in Results Per Page. To not impair page load time, do not set to greater than 2,000 results per page.

Alternatively, you can click the button next to Show All. This is not recommended if your report has more than 2,000 records. The record total can be viewed at the bottom of the report.

Filter Columns

You can filter by any field that is available in the report. It does not have to be a visible column in your report to be used as a filter. 

1. Click Filter. 
   
   
   
2. Click on a field name. 
   • Use the search bar to easily locate fields.
     
     
     
3. Apply your filter.
   • Filter options vary by the type of field selected. 
   • Many fields will have both an ID option and a Name option, as in the example above.
   • Using the ID option will give you the option to click [checkboxes] and select one or more items from a list. In this instance, the list would be the fund groups. 
   • Filters on names or other words have options to look for exact match to the specified text, begins with the specified text, or contains the specified text. 
   • Many filters have an Is/Is Not option. Is indicates the filter should return what you're specifying. Is Not indicates the filter should return everything but what you're specifying.
     
     • This is often used in conjunction with a Null checkbox. For instance, if filtering on Description, setting parameters to Is Not and checking Null will exclude all records with a blank description.

Once a filter has been applied, it's visible above the report headers. To remove a filter, click the X next to the filter. To modify a filter, click the blue link next to the field name and modify the filter. 

A report can have many filters, but you cannot filter on a field more than once in one report. 

Add Column Totals

Totals can be added to columns that have numerical data. Click a field name in current fields or in the report headers, then click Total Column. 

Group By

Add grouping to a column to organize the report by groups. Click a field name in current fields or in the report headers, then click Group By. The column values become section headers.

You cannot group by more than one column. To remove grouping, click the X next to the group by criteria, located in the filters section.

Aggregates

There are six aggregate options that can be used to convert data formats and consolidate your report data. Not all aggregate options are available for all field types. 

• Count - The number of rows in a column.
• Dcount - The distinct number of instances of data in a column.
• Sum - The sum on an amount.
• Avg - The average of an amount.
• Min - The minimum amount in a field.
• Max - The maximum amount in a field.

Aggregates can be used to consolidate raw data into summaries. For instance, this basic report that contains one row per grant can be consolidated to display one row per grantee with sum and count of grants and earliest grant date. 

1. Click Grant ID, then click the Count aggregate. 
2. Click Grant Amount, then click the Sum aggregate. 
3. Click Grant Date, then click the Min aggregate. 

After clicking the aggregate, the field name is changed to include the aggregate description and the original field could be added into the report again. For instance, you could also add Grant Date (Max) by adding Grant Date to the report. This would expand the report to a row per grantee per date, and then applying Max to the field would again condense the report.

Video Overview

Here is a short summary that was recorded when custom reporting was first released.

A video outlining the difference between filter, canned, and custom reports is also available in Filter, Default, and Custom Reports.