The WrapLinkAggregate widget

The WrapLinkAggregate widget performs mathematical operations over a group of wrap instances that fulfill certain criteria. Some of the more popular functions are SUM, MAX, MIN, AVERAGE and COUNT.

A common WrapLink uses a unique key to return a single value from a single wrap instance. If you need to aggregate values from all the wrap instances that match a particular condition, use the WrapLinkAggregate widget instead, and specify a non-unique key that matches more than one wrap instance.

Example: If you use a key that designates a particular depot for rolling stock, you can use the WrapLinkAggregate widget to obtain information such as:

  • The number of railway cars that require a certain maintenance operation.
  • The average time a similar maintenance operation has taken to perform in the past.
  • The anticipated working hours required to perform the pending maintenance.

The WrapLinkAggregate widget is often used to calculate key performance indicators. KPI data per day, week, month, or other periods can be automatically saved using a scheduling agent. Live data is updated on a regular basis so that your analysis is always based on fresh information. Historical data is securely preserved for long-term trend analysis.

You can use a Holder Cell to provide a dummy aggregate value (count, average, sum, etc) to test the wrap already in Excel. When the wrap is run on the server, WrapLinkAggregate extracts live data from the cloud database and returns the live, aggregated value in the holder cell.

The WrapLinkAggregate widget is a visual guide that helps you insert a wraplinkaggregate() function into the current cell. If you want to link to a Wrap that doesn’t yet exist on the Wrapsite, you need to insert the =wraplinkaggregate() function manually into the cell using the Function Reference section below. This is because the visual guide relies on the Wrapsite properties to provide a definition of the Wrap you want to link to, including its Unique Keys and the field you want to be aggregated. Without this information, the guide cannot give you much help anyway.

Insert a WrapLinkAggregate widget

  1. Select the cell where you want the WrapLinkAggregate widget to appear.
  2. Switch to the WrapCreator ribbon and click on Insert Widget.
  3. Select the WrapLinkAggregate widget on the Widget tab in the task pane.
  4. Enter the required parameters on the Cell tab and press Apply.
  5. Verify that a default value has been inserted into the cell.

Parameters

Screenshot of a WrapLinkAggregate widget

Cell name

We encourage you to assign cell names to calculated cells, so that they get correct headings in MyWraps reports. A convenient MapToNames tool can save time by automatically using the field label as the cell name.

Wrap Selector

Select the source wrap from which you want to pull data. If you have a database background, you can think of this as selecting a table.

Formula Builder

This section lets you specify all but one of the keys and values that together form the Unique Key that designates the wrap instances that you want to pull data from. To continue the database analogy, this key will select one or more rows in the source table. You must leave one key unselected – the resulting key must not be unique if it is to return values from more than one wrap instance.

As a developer, you may want to construct the key dynamically using runtime values from other cells in the wrap. You can insert cell references here or by editing the inserted wraplinkaggregate() function call as described below.

In the example above, the wrap link is assembled dynamically, using the field label in column D of the same row. The WrapLinkAggregate functions in column E always use the string “CL334_Delivery_” followed by the section name in column D:

  • In this case, D3 = Cleaning creates a link in E3 to CL334_Delivery_Cleaning.
  • In row 4, D4 = Cab, and the link in E4 points to CL334_Delivery_Cab.
  • With Pass_Saloon in D5, the link in E5 becomes CL334_Delivery_Pass_Saloon etc.

To clarify this, we expanded the list of existing wraps in the screenshot to make it easy to see the wraps names that correspond to the different section names.

Note the warning in red below the wrap selector. The formula has been manually edited in a way that isn’t supported by the wizard.

Aggregating

This section lets you select the “filter” cell in the source wrap instances that you want the WrapLinkAggregate to operate on (the column, if you think of the wrap as a table). After the call has been made, the aggregated value will be inserted in the cell where the =wraplinkaggregate() function call resides, and in the holder cell, if there is one.

Cell

Select the “filter” cell in the source wrap instances that you want the WrapLinkAggregate to operate on.

Cell Type

The Cell type is used to specify how the cell value will be handled. The prefix “sig-” means that you are extracting a value from a wrap signature cell.

Aggregate Function

Select the main function of the WrapLinkAggregate widget. For a description of the various aggregation functions, look up the corresponding function names in Excel.

Format Specifier

The format specifier is used to say how the calculated value will be displayed. For more information, study the Microsoft C# format specifiers. Examples:

  • f1 – fixed, one decimal
  • p1 – percent, one decimal

Holder cell

While you test the wrap in Excel, you can provide a test value in a special holder cell to verify that other formulas in the wrap return the correct results. When you upload the wrap, live data automatically replaces the test value in the holder cell. For a WrapLinkAggregate widget, the holder cell contains the results of the operation, e.g. the SUM or AVERAGE that was calculated over the selected wrap instances.

Location

If you enable a holder cell for the widget, the cell immediately to the right of the WrapLinkAggregate widget is chosen by default. You can change the location of the holder cell by clicking on the “pen” symbol and then selecting a different cell as the holder cell.

Hidden

Holder cells sometimes contain information that you don’t want to show to the user of the wrap. In this case, tick this box to make the contents of the holder cell invisible in the wrap.

Dummy value

This is the value that the holder cell will contain during testing in Excel. When the wrap is running on the server, real values from the database are used, and the dummy value has no meaning. There are three possible types of values you can put here, depending on the situation:

  • If the server will return a number at runtime, the holder cell must contain a numeric value also during testing in Excel. Example: 100
  • If the server will return a text string, you must enter a text string here, and it must be delimited with double-quotes. Example: “JaneDoe”
  • If the server will return a timestamp, you must enter a timestamp in the OADate (or OADateTime) format used by OLE Automation, i.e. as a decimal number.

Special rules for timestamps

The OADate format is easy to handle for Excel developers because it is the same as an ordinary Excel serial number, i.e. the standard decimal number that Excel uses for timestamps internally.  The integer part counts the number of days and the decimal part tells the time of day. When you use an OADate in a holder cell, it must be formatted as a decimal number, not using a date or time format.

Read more about testing Wraps in Excel.

Known issues

Frozen wraps become static

If a wrap instance has been signed with the Freeze option, any dynamic data pulled into the wrap using WrapLinks, WrapLinkLists and WrapLinkAggregates will no longer be updated. A frozen wrap always reflects exactly the conditions when the freeze signature was signed.

Update the Wrap Package regularly

Before you create your first wrap, you must install the Wrap Package that you downloaded from your wrapsite so that WrapCreator has all the information it needs about your wrapsite and how it is set up. The Wrap Package synchronizes Excel to your wrapsite and should be updated periodically to give WrapCreator access to the most recent wrap and user definitions.

Remove a WrapLinkAggregate widget

To delete a field, widget, wrap function or signature that has been stored at least once in the live production database is not a trivial task. The instructions below are only applicable during the initial development of a wrap, before it has ever been used in production. Read more about making changes to your wraps.

To remove a WrapLinkAggregate widget:

  1. Ensure that the task pane is visible.
  2. Select the widget’s cell.
  3. The task pane should now show the WrapLinkAggregate widget’s settings on the Cell tab. If it doesn’t, you may have selected the wrong cell, or already removed the widget by mistake.
  4. If the WrapLinkAggregate widget designates a holder cell, right-click on this cell and select Clear contents.
  5. In the settings for the WrapLinkAggregate widget, locate the Remove button at the bottom and click on it.

Function reference

Screenshot of a WrapLinkAggregate widget

Example

In the example above, the following cell formula is inserted in the cell.

=@WrapLinkAggregate("CL334_Redelivery_"&D3&"?reid="&ReID&IF($A$5="All";"";"&Unit="&$A$5)&"&Filter=reid.int&Function=COUNT")

As you can see, the developer has edited the formula to use cell references instead of static values. This example uses plain cell references like D3 and $A$5 because they are easier to use with Excel’s Fill Down.

The same technique works also with named cells. If a wrap has the mileage and car type in named cells you could use the following formula:

=@WRAPLINKAGGREGATE("Class395-Vehicle-PackPredictor?mileage=" & mileage & "&car=" & car_type & "&Filter=progress.percent&Function=MIN.f1")

Format and parameters

=@WRAPLINKAGGREGATE("link")

link is a quoted text string that identifies the wrap you want to pull data from, provides the common key for the multiple instances you are selecting and what value you want to be returned from them

Link parameters

The parameters for a link are specified in the query string. The query string is appended to the wrap name following a question mark. Within the query string, parameters are separated by ampersands. In the second example above, the query string begins with a question mark and sends three parameters to the source wrap: mileage, car, and filter.

Wrap name

The link starts with the name of the wrap you want to link to, e.g. Class395-Vehicle-PackPredictor in the example above.

Components of the Key, e.g. mileage=, car=

The link must contain the Key that selects the group of wrap instances you want to pull data from. The Key is usually assembled from several values, e.g. for a bogie overhaul you would need to identify the car type and the mileage interval for the overhaul. You specify the components of the Key using the field names that the wrap is using for these values. In the second example above, the Key is provided by the mileage and car parameters.

Widening the scope

The WrapLinkAggregate Furmula Builder requires that you leave one key unselected. You can extend the scope of the aggregate by editing the formula and remove additional keys. In the example below, we removed the car parameter so that aggregation takes place over all car types.

=@WRAPLINKAGGREGATE("Class395-Vehicle-PackPredictor?mileage=" & mileage & "&Filter=progress.percent&Function=MIN.f1")
filter

The filter parameter returns a field value from the selected source wrap instances. The values in the field with this name are passed to the aggregation function and the result displayed in the same cell that the =wraplinkaggregate() formula occupies, and in the holder cell, if there is one.

function

Select the main function of the WrapLinkAggregate widget, e.g. function=COUNT. For a list of available functions, see the dropdown menu for the Aggregate Function setting for the widget. For a description of the various aggregation functions, look up the corresponding function names in Excel.

Format Specifier

The format specifier is used to say how the calculated value will be displayed. For more information, study the Microsoft C# format specifiers. Examples:

  • f1 – fixed, one decimal
  • p1 – percent, one decimal

The format specifier is an optional second part of the function parameter, preceded by a period. In the example above, the function=MIN.p1 setting means the function will return the lowest occurring value for the progress.percent field in the selection, and display it as xx.x%.

state=live

A WrapLinkAggregate can be restricted to live wrap instances only. If you specify state=live, the wrap will only select the wrap instances that have not been frozen.

=@WRAPLINKAGGREGATE("Class395-Vehicle-PackPredictor?mileage=" & mileage & &car=" & car_type & "&Filter=progress.percent&Function=MIN.p1&State=Live")

The holder cell

You can use a Holder Cell to provide a dummy aggregate value (count, average, sum etc) to test the wrap already in Excel. When the wrap is run on the server, WrapLinkAggregate extracts live data from the cloud database and returns the live, aggregated value in the holder cell.

Format and parameters

=HOLDER(cell_name, dummy_value, hidden, holder_item)

cell_name points to the WrapLinkAggregate cell using its cell name.

dummy_value contains a value you want the cell to provide during testing in Excel – at runtime, real values from the server are used instead. The dummy value must have the same data type as the value that will later be returned by the server.

hidden if set to TRUE, makes the contents of the holder cell invisible in the wrap.

holder_item is always empty for WrapLinkAggregates.

=HOLDER(error_rate_avg, 0, TRUE, "")