Article last updated on the 14th of June 2023.
Data aggregation is used for summarizing data from an entity onto that entity’s module master page. This is useful for gaining overviews of certain details from entities without having to go to their entity sites. This data can instead be displayed in a column in the business module list.
Here is a visual representation of what data aggregation does:
A case where this feature can be useful is if a project manager wants to be able to see when the latest report on each project was filed from the Projects master site list. Each report could be tagged as such, and this information can be used to aggregate data for when the latest document tagged as report was modified on each project and display this data in a column for each entity in the Projects master site list. This is useful, as it grants an overview of report dates for each project without having to go into each project site and check for report documents and their latest modification date, thereby freeing up time for other tasks. Another example could be aggregation of how many open tasks there are on each entity.
Another case could be using a Union aggregation type to aggregate all participants of various projects of a company to a field on the company, which then can be used to grant these participants rights to see information about the company.
There are currently no requirements for the utilization of this feature.
Data aggregation is set up through the WorkPoint Administration, which can be accessed by following these steps:
- Click the “Home”-button on the solution.
- Click the cog-icon to access the WorkPoint Administration.
- Click the header of the business module for which data aggregation should be set up.
- Click the “Aggregation”-menu item.
3.1. Last Activity
The first option in the Aggregations settings page is to enable or disable Last Activity.
Enabling this feature will create the fields "Last Activity" (date and time field) and "Last Activity By" (person or group field).
These fields are then used to aggregate information about the last activity on each entity on the business module. The "Last Activity" field records the date and time of the last activity on each entity and the "Last Activity By" field records who performed the last action.
To enable "Last Activity", follow these steps:
- Enable the "Enable last activity information on entity" checker.
- Click the "Save and update all entities" button.
Here is shown an example of the fields in use:
Next, we will look at how to add new aggregations for a business module.
3.2. Configuration of aggregations
- On the Aggregations page, click “Add aggregation”.
In this example, a column will be added to a Projects master site list, which will display the latest time and date that a report document has been filed in the various project’s document libraries. The Projects master site list will aggregate this information from the individual entities themselves.
The configuration for this example is as follows:
- The aggregation is set active. Deactivated aggregations will not run.
- A descriptive title of “LatestReportDate” is given to the aggregation. This title is used exclusively to distinguish this aggregation from possible other aggregations.
- We want to aggregate data from lists on entities located on the Projects business module, thus “Project site list” is selected.
- As we wish to aggregate data from report documents on the entities, the “Documents” library is selected for Project Site List.
- The Aggregation Type decides what type of data we want to aggregate. In this case, we wish to aggregate the latest report’s “modified” date. We can do this by sorting the list after the latest modified date in descending order, and then fetching the first item. Therefore “First” is selected in this example. Be aware that some aggregation types only support aggregation from and to certain field types. For more information, check out the Notes section.
- For Aggregation field, we select the “Modified” field, as the data in this field will help us find the latest date that a report was filed.
- For Target field, we can either choose an existing field or create a new one to hold the output of the data aggregation. In this example we create a new field by selecting the “Create Field” option from the menu.
- The new field is given a descriptive name of “Latest report date”.
- Optionally, we can add the target field to the default view of the Projects master site list. In this case, we deactivate this option.
- Through a feature called “Active field mapping”, entities can be marked as active. This is useful for e.g. filtering out e.g. cases or projects that are archived or in other ways inactive. If Active Field Mapping is enabled and set up, the aggregation can be set up to only run on entities set to active. In this case, this setting remains deactivated.
- Data aggregation runs whenever a change to data is made, which is what is needed for this configuration. However, sometimes it is necessary to aggregate data regardless of change to data has been made. In these cases, aggregations can be set up to run on a schedule, e.g. once every day. This is done by enabling the “Run on aggregation scheduler”-option. An example of a scheduled aggregation could be “Number of overdue tasks”, where the CAML query would be relative to current date.
- To filter the output of the aggregation to only consider documents that are tagged as reports and order them in descending order, we use filtering. Thus, the option is enabled.
- Filtering works by setting a CAML query for the system to execute. In this case, the CAML query tells the system to only consider documents where the value of a field called “IsReport” is equal to a value of “True”. The “IsReport” field is a custom field that can be either “True” or “False” (a so-called Yes/No field). Each report document must have a value of “True” in this field for this aggregation to work properly. The query then tells the system to order the results by the value in the “Modified” fields in descending order. Along with an Aggregation Type of “First” in pt. 10, this will return the latest date that a report document was filed for the given entity.
- Optionally, we can test the aggregation by entering the internal ID of an existing entity in the Projects list. In this case, we have an entity with an internal ID of “11”, which already holds several documents that have been tagged as “Yes” in the “IsReport” field. The data aggregation test should therefore return the latest date and time a report document was filed.
- The result displays the name of the entity that the test was run on, as well as the result. In this case, the latest time a report was filed for this entity was the 23th of July 2019 at 3:07:39 PM.
- Once the configuration of the data aggregation is set up, click the “Save”-button.
4. End User Guide
Click here to go to the end user guide article for Data Aggregation.
Data aggregation is performed automatically whenever data changes in a WorkPoint solution. Therefore, scheduling data aggregation jobs is not always necessary. It may, however, be needed if data should be aggregated on a specific schedule regardless of whether data changes.
Please be aware that changes to the "Last Activity Date" and "Last Activity By" fields do NOT trigger aggregation jobs.
5.1. Feature support White List
5.1.1. Aggregation source types
5.1.2. Aggregation types
- “First”: Return the value of the aggregation field of the first item returned
- “Average”: Return the average value of the values in the aggregation field
- “Count”: Return the number of elements
- “Min”: Return the minimum value of the values in the aggregation field
- “Max”: Return the maximum value of the values in the aggregation field
- “Sum”: Return the sum of the values in the aggregation field
- “Concat”: Return the concatenation of the values in the aggregation field
- “Union”: Return the union of the values in the aggregation (multi) field
5.1.3. Aggregation target field
WorkPoint Data Aggregation supports aggregating to target fields:
- Pre-existing fields
- Creation of new fields during aggregation
- Using Active Field mapping to aggregate from active sources only
- Running aggregation on a schedule using Scheduled Jobs
- Filtering using CAML queries
Please note that the field types in the following table are those that can be shown. In some instances no aggregation fields will be shown if the underlying list has no fields matching the criteria:
You should make sure when using any aggregation type, that the fields aggregating from and to match the valid types from the table above. This will ensure correct rendition of the fields in your lists.