This article provides an overview of the main elements available in the Aggregate Report Builder. For more detailed information on specific features there are many resources available in our knowledge base, including the following list of articles:
Creating a New Aggregate Report
Tips for Creating Traditional Reports to Use with Aggregate Reports
Copy Aggregate Grid and Reports
Advanced Aggregate Report Options
Definition: Feeder Reports = A feeder report is just a standard report found within your Apricot. While working with aggregate reports any standard report that is found in your “My Reports” area that is used in an aggregate report will then be considered a feeder report. More details are found within this article.
An aggregate report takes data, usually grouped results or totals, that has been collected in one or more “feeder reports”, and allows you to view it in one place. These reports eliminate row-level or individual record level information, but provide you with the ability to manipulate high level data into grids that can highlight different data points.
Many aggregate report grids, each highlighting a different kind of information, can be created from one standard Apricot report. Or several standard reports can be fed into one aggregate report to provide a view of data that has been gathered in multiple forms within the same Apricot database.
What an Aggregate Report Looks Like
Each section of an aggregate report contains one grid, like the one in the example above. These grids can contain information from one or from several standard Apricot reports.
The last row and the last column are not based on information aggregated from the underlying report. Instead they are calculations that display the sum of the information in each row and column.
Creating a Feeder or Foundation Report for an Aggregate Report
Each Aggregate Report is built by using information from one or more than one underlying Apricot report. We call these "feeder reports" because they feed their results directly into an Aggregate Report.
The report section in the example above contains all the fields from one form.You can choose to build your feeder reports without filters and add the filters in the Aggregate Report Builder. Or you may find it easier to filter the feeder report before adding it to the Aggregate Report Builder. It will work either way, depending on the results you are looking for. In the example above, the feeder report contains no filters; we will add filters when we create the aggregate report grid.
The number contained in the "total rows" box in the lower right hand corner is the number that will show up by default in the aggregate report grid. You can adjust this number in the Aggregate Report Builder by adding filters.
Create a New Aggregate Report Category
To create a new aggregate report, select the Administrator tab at the top of the page.
In the left hand menu, select Aggregate Reports.
In the right hand palette, select Create New Category.
Name the category that will contain your aggregate report.
Select Create.
Create a New Aggregate Report
Once you have created a new category, select Create New Report in the right hand palette.
Select the category you created for your aggregate report.
Name your new aggregate report.
Select Create.
Add Data Sources
Once you have created a blank report, select the Add button at the top to select appropriate data sources or feeder reports to populate the aggregate report with data.
Expand the black arrow under Select the Category to select the report category you would like to access.
Expand the black arrow under Select the Report to choose which report you would like to populate the aggregate report.
Expand the black arrow under Select the Report Section to choose which report section you would like to populate the aggregate report.
Select Create.
Once you have added a section of a report to the aggregate report builder, it will show up in the right hand palette. Expand the black arrow next to the report name to view the individual sections that have been added.
Each report section will need to be added individually. To add more, select the Add button to the right of Data Sources.
After you have selected the report category and the report, sections listed under "Select the Report Section" will be grayed out if they have already been added to this aggregate report.
When you have made your selection, click Create.
View Data Sources
Available data sources can be viewed at any time by expanding the white arrow next to Data Sources, or by
Expanding the black arrows next to the report names under Data Source Choices.
Create New Aggregate Report Section or Grid
Once data sources have been added to the builder, you are ready to create a new aggregate report grid. Select Add New Section from the right hand palette.
Name the section.
Select the section type:Single Source: allows for one data source or standard 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 standard 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 standard 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 standard report section to provide the data for each cell of the grid. Each cell can be filtered individually.
Create.
Add Data Sources
Once your aggregate report section has been created, it will be labeled with the section type you selected, like Single Source.
An empty grid with one cell will be displayed.
To add data sources to the grid, click and drag a report section from the Data Source Choices palette and drag it into the cell in the middle of the grid.
Refresh an Expired Grid
If any changes are made to your grid, like adding a new data source, the text (expired) will appear. To refresh the grid to include the most recent changes, select the refresh icon in the bottom right corner of the grid.
A grid will always begin with a "total" row and a "count" column. These can be edited by clicking the row or column and typing in a different heading.
The number that first appears in the grid will correspond directly with the "total rows" that appears in the bottom right hand corner of the data source report section.
To view the feeder report that has been added to this aggregate report section, click the green text of the report name under Data Sources. You may make edits or changes to the feeder report and publish those changes; once the aggregate report sources have been refreshed, these changes will be reflected in the aggregate report.
Add Columns and Rows
To add new rows to the grid, click the + sign at the bottom of the existing rows.
To add new columns to the grid, click the + sign to the right of the existing columns.Rows and columns can be moved within the grid by clicking on them and dragging to them to the desired location.Until filters are added or changed, the same number from the "total rows" area of the underlying report section will appear in each new square of the grid.
Adjust Columns and Add Column Filters
To adjust what is shown in a column of the grid, click on the column heading.
Type into the text box to change the column heading title.
Under Data Source, the name of the feeder report section will be listed. You can click this green text to open the feeder report in a new tab. It can be very useful when you are setting up new filters to have the report section available as a reference.
Click the plus sign to the right of filters to add a new filter.
Under Rule, create a report filter. The options available in the drop down menu will include all the columns in the feeder report you have selected as your data source. If you need to create a filter on a column that does not appear in this drop down menu, you should open the feeder report, add the appropriate column to the section of your report that is being used as a data source, publish the feeder report, and then refresh the data sources in this aggregate report.Creating a filter for an aggregate report is similar to creating a filter for a standard report, however not all the options available in a standard report filter will be available in an aggregate report filter. For more information on creating report filters, please see the Help Center section on Filtering Reports.
When you are finished creating your filter, select Apply.
The ~ symbol will appear in any row header, column header or cell where a filter has been applied.
After you have added filters to a column, the text (expired) will appear.
Click the refresh icon to reload the report section and see the changes that have been made.
Adjust Rows and Add Row Filters
To adjust what is shown in a row of the grid, click on the row heading.
Type in the text box to change the row header title.
Under Data Source, the name of the feeder report section will be listed. You can click this green text to open the feeder report in a new tab. It can be very useful when you are setting up new filters to have the report section available as a reference.
Click the plus sign to the right of filters to add a new filter.
Under Rule, create a report filter. The options available in the drop down menu will include all the columns in the feeder report you have selected as your data source. If you need to create a filter on a column that does not appear in this drop down menu, you should open the feeder report, add the appropriate column to the section of your report that is being used as a data source, publish the feeder report, and then refresh the data sources in this aggregate report.Creating a filter for an aggregate report is similar to creating a filter for a standard report, however not all the options available in a standard report filter will be available in an aggregate report filter. For more information on creating report filters, please see the Help Center section on Filtering Reports.
When you are finished creating your filter, select Apply.
After you have added filters to a row, the text (expired) will appear.
Click the refresh icon to reload the report section and see the changes that have been made.
Which Cells Have Been Filtered
Each row and column that has been filtered will have a ~ to the right of the row or column header.
Add Calculations to a Grid
Calculations can help make sense of the data in an aggregate report grid. These calculations must be added to each cell and cannot be added at the column or row level.
To add a calculation, click into the cell that will display the result of the calculation.
Under Type, select Calculation.
Under Options, select the calculation you would like to use. Available calculations include:SumSubtractMultiplyDivideAverageMinimumMaximumModeStandard Deviation
Then select "row" or "column."
Apply. Then refresh the grid to see the results.
Find and Adjust Records Using Drill Down
If any of the results in the aggregate report grid look incorrect or need to be edited, you can access the records by clicking on the cell in the grid,
And then selecting Drill Down.
This will open a display of all the records that have been fed into the aggregate report. Clicking on a row of data will open the associated record.If you make changes to a record, these will be reflected in the aggregate report as soon as you refresh the data sources in the Aggregate Report Builder.
Changing What is Being Counted
By default, all cells are counting the “Row Count” from it’s respective feeder report. This could be right; however, a lot of times you will want to know a specific client count, for example. In this scenario, you will want to change it to “Distinct Record Count” and then choose the correct form (generally the Tier 1 form) by following the steps below.
Under Type, select Distinct Record Count.
Under Options, expand the black arrow and select the name of the form you would like to be counted in the aggregate report cell.
Hints and Tips
By default, only Administrators can access Aggregate Reports (that includes editing them and running them)
If your organization needs the ability for Standard Users to access to Run aggregate reports please contact the Ongoing Support Team for additional information
The Ongoing Support Team only has the ability to grant access for Standard users to Run reports, please note the Drill Down will not work for Standard users