Article last updated on the 24th of January 2020.
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.
- 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
In order to run a data aggregation, access the data aggregation settings page for the business module in question. From there, follow these steps:
- Click the “Run”-button.
- Click the “Run aggregation”-button.
The aggregation will now run:
- The progress of the aggregation can be viewed in the list by clicking “Refresh”.
- The progress is displayed in the “Details”-column of the list item.
- During the aggregation a stop-button is displayed in the "Actions" column, which can be prematurely stopped by clicking the “Stop”-button. In this example, the aggregation is complete, thus the button is not showing.
Upon completion, the aggregated data has been fetched and can be displayed in the specified column, granted that the column is set to be displayed in an available view. In this case, the “Last report date”-column from the configuration example was added to a view on the Projects master list called “Compact”, which is shown below:
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.
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:
|Aggregation type||Valid aggregation field types||Valid target field types|
|First||All types||Text field / Same type as aggregation field
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.