How to Setup an Aggregated Column

This lesson explains how to set up an aggregated column in a data view. An aggregated column is a column manually added to a data view that is populated by the aggregate calculation of columns in a related data view. It is designed to provide a flexible way to craft detailed analytics upon which decisions can be made. Aggregated columns support honors any filters that are placed on a related data views. Also, the resulting aggregated column can itself be filtered and sorted.

Examples of the aggregations available are:

  • Count: A count of the number of rows in the related data view.
  • Count Distinct: A count of the number of unique occurrences of values in a selected  column.
  • Sum: The sum of all values in the selected column in the related data view.
  • Average: The average of all the values of the selected column in the related data view.
  • Max: The maximum value from the selected column. On date fields this will select the most recent date.
  • Min: The minimum value from the selected column. On date fields this will select the oldest date.

By way of  example 'Logical Disks' is a related item to 'Computers'.  Aggregated columns provides the ability to add a column to a Computers data view that has a displays the sum total amount of free disk space for a machine.  Another column could display the logical disk count, max disk space, min disk space, etc.

Aggregated columns can also be nested. This enables a data view to be created that aggregates a column from a related data view that is itself aggregated. At this time Asset Vision supports three levels of aggregated column nesting.

Navigate to Data View Management
  1. Navigate to Admin > Data View Management and select the data view to which you want to add an aggregated column.
  2. Click the Add Aggregated Column button.

Aggregated Attributes

Aggregated Attributes

Provide specifications for the aggregated column:

  • Name: this will be the name of the aggregated column as it appears on the data view
  • Description: additional details about the column. This data will show up in a help bubble when mousing over the column header.
  • Appearance: Determines whether or not the column will be available as a regular Aggregated column, a Related Item, or both.
  • Related Data View: Select the data view from which you want to aggregate data.
  • Aggregate: choose an aggregate value that meets your needs. (Sum, Min, Max, Average, Count, Count Distinct)
  • Value Field: certain aggregates require the selection of a value field on which you want to perform the calculation
  • Link Field: this is the field that links the related item to the main data view.  (ex: Logical Disks are "Installed on" a Computer)

The specifications chosen for the Aggregated Attributes above will provide a sum total of the available free space for all logical disks installed on a given machine.  If you wanted a list of total disk space you would select Disk Space as the Value Field instead of Free Space

Aggregated Column Display

Aggregated Column Display

When you preview the Computers data view you would now see your new aggregated column listing the total amount of free disk space on the machine.

Custom Controls

Custom Controls

In order to make the aggregate data more useful you can add custom controls to aggregate columns in the same manner as regular columns.  Click the edit pencil icon or right-click and edit the aggregated column you added and then select the Custom Control tab.  You can add a RAG (Red, Amber, Green) control to add emphasis to the total amount of free space on a machine.  Select the RAG values and save your changes.

Custom Aggregated Column Display

Custom Aggregated Column Display

The new aggregated column now easily calls out any machines that have low disk space based on the given criteria.  

Drill Down Custom Control

Drill Down Custom Control

The user is also able to use a drill down custom control. This will allow the user to click the value in the aggregate column which will link them to the filtered records in the data view used in the aggregate calculation.

Settings for a Drill Down Custom Control:

  • Custom Control: Selecting Drill Down allows the user to click into the related data view that was used in the aggregate calculation.
  • Group By: Optionally, select a field to group the records by.
  • Order By: Select a field to order by.
  • Sort Direction: Select whether to sort ascending or descending based on the Order By field.
  • Criteria: The user has the option to add additional filtering criteria for the aggregated column calculations. For the Drilled Down custom control, the user should have Installed on ID since that filter will only show the records that are related to the record the user drilled down from.