All Collections
Reading and Building Reports
Advanced Aggregate Report Options
Advanced Aggregate Report Options

This article explains the advanced features of aggregate reporting

Updated over a week ago

Advanced Aggregate Report Options

Additional options are available to create different types of aggregate report grids, to filter results at several levels and to adjust each cell with additional properties.

Grid Types

Each aggregate report section can contain one grid. These grids must be identified as one of the following types when they are created:

  • Single Source: allows for one data source, or traditional report, section to provide the data for the entire aggregate report section. Each row, each column and each cell can be filtered individually which will create a simple grid of data.

  • Source Per Row: allows for one data source, or traditional report, section to provide the data for each row of the grid. Each row and each cell can be filtered individually.

  • Source Per Column: allows for one data source, or traditional report, section to provide the data for each column of the grid. Each column and each cell can be filtered individually.

  • Source Per Cell: allows for one data source, or traditional report, section to provide the data for each cell of the grid. Each cell can be filtered individually.

 

Single Source

A single source aggregate report section draws all of its data from one section in one underlying report.

Each column, row and cell can be filtered. The results will always be related to the "total rows" from the single report section that has been added to this aggregate report section.

Source Per Row

A source per row aggregate report section can draw data for each row from a different underlying report section.

Each row and cell can be filtered, but columns cannot be filtered.

In the example above, the aggregate report section is pulling data from two different Tier 2 records in order to find the total number of services as collected across both forms. This type of report would also work well if you need to count services or people served when that information is collected on un-linked or unrelated Tier 1 forms.

Source Per Column

A source per column aggregate report section can draw data for each column from a different underlying report section.

Each column and cell can be filtered, but rows cannot be filtered.

In the example above, the aggregate report section is pulling data from two different Tier 2 records in order to find the total number of services as collected across both forms. This type of report would also work well if you need to count services or people served when that information is collected on unlinked or unrelated Tier 1 forms.

Source Per Cell

A source per cell aggregate report section can draw data for each cell from a different underlying report section.

Each cell can be filtered, but rows and columns cannot.

In the example above, the aggregate report section is pulling data from five different Tier 1 and Tier 2 records in order to find the total number of people served across all forms.

Filtering Results

Results in an aggregate report can be filtered in several places. Depending on how and where you add filters, the results that are displayed in an aggregate report grid may end up being filtered many times.

Whenever a filter is added, it is noted by a ~.

Filtering a Data Source

Filtering a data source will limit the results that can be used in all sections of the aggregate report. In this way, it is similar to using Global Values in a traditional Apricot report.

  1. To filter a data source, expand the white arrow next to Data Sources

  2. Click the green text of the report section you would like to filter.

  3. When the pop-up window opens, click the plus sign to the right of Filters.

  4. Create a rule.

  5. Select Apply.

Filtering a Column

Filters can be applied to an entire column if the aggregate report section is set to "single source" or "source per column."

  1. To create a column filter, click the column header.

  2. When the pop-up window opens, click the plus sign to the right of Filters.

  3. Create a rule.

  4. Click Apply.

Filtering a Row

Filters can be applied to an entire row if the aggregate report section is set to "single source" or "source per row."

  1. To create a row filter, click the row header.

  2. When the pop-up window opens, click the plus sign to the right of Filters.

  3. Create a rule.

  4. Click Apply.

Filtering a Cell

Filters can be applied to an an individual cell no matter what section type has been selected.

  1. To create a cell filter, click into the cell.

  2. When the pop-up window opens, click the plus sign to the right of Filters.

  3. Create a rule.

  4. Click Apply.

Additional Cell Options

In addition to filtering results, optional properties are available for individual cells. When you click into a cell and the pop-up window opens, they are listed under "Type".

Row Count

When adjusting a row, column or cell several display and function options are listed under "type".

Row count is the default setting and will display the data associated with the "total rows" from the underlying report.

Distinct Record Count

Distinct Record Count is available when an aggregate report section pulls data from more than one form in the database. If your underlying report has more than one columns from more than one form, you will be able to choose which records to count.

  1. Under Type, select Distinct Record Count.

  2. Under Options, select the form you would like to count in this aggregate report section.

  3. Click Apply.

Section Summary

Section Summary can bring over data from the column summaries from an underlying report - for example, if you have an underlying traditional Apricot report that has calculated average age of attendees as a column summary. In order for these values to appear in an aggregate report, you must have column summaries turned on in an underlying report section and then create an aggregate report section that uses that report section as a data source.

  1. To access section summary options, click into a cell.

  2. Under Type, select Section Summary.

  3. Under Options, select the summary value to be displayed in the cell.

  4. Click Apply.

Calculations

Note: For more information on the types of calculations that are available, please see the Knowledge Base article "Aggregate Report Calculation."

Calculations can be added to any individual cell in a grid.

  1. To access the calculations, click into the cell.

  2. When the pop-up window opens, select Calculation under Type.

  3. Under Options, select the calculation you would like the cell to display.

         Sum: adds the results from the surrounding row or column,
         Average: averages the results from the surrounding row or column,
         Minimum: displays the minimum value from the surrounding row or column,
         Maximum: displays the maximum value from the surrounding row or column,
         Mode: displays the value that appears the most times from the surrounding row            or column,
         Standard Deviation: displays the amount of variation within the surrounding row            or column.

  4. Select row, column, or cell.

  5. Click apply.

Number

If you have static numbers that you would like to use in an aggregate report section - a projected goal or a fixed cost, for example - these can be added to a cell by adding a number.

  1. Click into a cell.

  2. Under "Type," select Number.

  3. Under Options, type in the desired number.

  4. Click Apply.

Text

Text can be added to a cell to further clarify reported findings.

  1. Click into a cell.

  2. Under "Type," select Text.

  3. Under Options, type text.

  4. Click Apply.

Did this answer your question?