The WrapLinkAggregate widget performs mathematical operations over a group of wraps 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. If you need to aggregate values from all the wraps that match a particular condition, use the WrapLinkAggregate widget instead, and specify a non-unique key that matches more than one wrap.
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 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.
With the basic format WrapLinkAggregate, 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. With the extended format WrapLinkAggregate2, a default value can be assigned directly in the function call, removing the need for a separate holder cell.
The WrapLinkAggregate widget is a visual guide that helps you insert a basic 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.
If you wish to use the extended WrapLinkAggregate2() function, you also need to insert the function manually into the cell using the Function Reference section below.
Requesting a lot of data using many WrapLinks or WrapLinkAggregates, e.g. for a complex dashboard showing hundreds of entries, can be very time-consuming because of all the database operations required to provide the data one cell at a time. Using the MyWraps2 function, you can request a single data extract containing all the data you need by defining a single MyWraps link and having all the data returned with item separators in a single cell of the dashboard Wrap.
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.
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.
This section lets you specify all but one of the keys and values that together form the Unique Key that designates the wraps 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.
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:
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.
This section lets you select the “filter” cell in the source wraps 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.
Select the “filter” cell in the source wraps that you want the WrapLinkAggregate to operate on.
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.
Select the main function of the WrapLinkAggregate widget. For a description of the various aggregation functions, look up the corresponding function names in Excel.
The format specifier is used to say how the calculated value will be displayed. For more information, study the Microsoft C# format specifiers. Examples:
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 wraps.
If you prefer to assign a default value in the function call instead, insert the function call manually using the extended WrapLinkAggregate2 format as described below.
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.
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.
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:
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.
Wraps often link to each other. To provide the most recent information, linked wraps must be recalculated for every access to ensure that they in turn inherit the current values from their own linked wraps and return the most recent values to their caller.
Recalculation is normally instantaneous, but when a large number of wraps are affected by the same operation, e.g. a dashboard-style wrap that retrieves information from numerous other wraps, recalculating all the affected live wraps in a cascading fashion can have a noticeable effect on the wrap’s response time. Users must always wait for all the initiated recalculations to complete before the updated wrap appears on the screen.
In many cases, however, the information being gathered is historical. As an example, a dashboard of KPIs for the previous year, month, or week only reflects events in the past. For historical data, the recalculation of all the instances is unnecessary because the data never changes. It is good practice to freeze all wraps that no longer can change since it tells the system that they never again have to be recalculated.
Even with live wraps, the most recent changes can be irrelevant, which makes the recalculation unnecessary. You should open such wraps using the openmode=fast parameter in the link. With this option, all database operations will reuse the results from the most recent recalculation of each instance instead of recalculating it again, drastically reducing the response time when there are many links.
So, before any results are returned from a wrap:
The value in the source cell designated by the filter parameter is aggregated (sum, min, average, etc) for all wraps matching the link and the unique key. The result is copied into the target WrapLinkAggregate cell. The aggregated value is also copied into the holder cell, if any, to trigger any dependent calculations.
If you request an undefined aggregation function in the WrapLinkAggregate formula, e.g. &function=BAD.f1, the WrapLinkAggregate displays a red “exclamation” icon.
If you haven’t specified a filter parameter,
If the cell name in a filter parameter does not exist, the WrapLinkAggregate displays a red “not available” icon. 
If a wrap has an empty or null value in the source cell designated by the filter parameter, it will not be included in the aggregation. If no wraps return a usable value, the WrapLinkAggregate displays a red “not available” icon
If you link to a group of wraps using a unique key that does not match any existing wraps in the database,
If the link points to a wrap that is not installed, a red “undefined” icon is displayed. 
If the link in the function call is empty, i.e. =WrapLinkAggregate(“”), nothing is displayed. This may be useful if you want to use an IF() statement inside the WrapLinkAggregate formula and return an empty link if the function shouldn’t be executed.
If there is no link in the function call,
Read more under Conditional request below.
One of the purposes of a WrapLink is to construct a valid hyperlink to another wrap. This link is a standard Uniform Resource Locator (URL) that contains your WrapSite’s name, a folder path, the name of the wrap, the names of the fields forming the key, and the filter name.
A URL must conform to certain rules, of which the most important is that the following characters have special meaning: ! * ‘ ( ) ; : @ & = + $ , / ? # [ ] Some of these characters are allowed in the names for Excel files, worksheets and cells, but we strongly recommend that you never use any of the reserved characters.
If you have a very special need to use one or more of the reserved characters (aside from their reserved meaning), they must be encoded. The reserved character /, for example, if used in the “folder path” component of a URL, has the special meaning of being a delimiter between folder names (path segments). If / needs to be used in a folder name, then you must percent-encode it to %2F or %2f instead of using the raw /. You can use the EncodeURL() function in Excel 2013 or later to percent-encode any names that may contain reserved characters.
If a wrap is frozen, any dynamic data pulled into the wrap using WrapLinks, WrapLinkLists, WrapLinkAggregates or MyWraps2 will no longer be updated. A frozen wrap always reflects exactly the conditions when it was frozen.
WrapCreator does not prevent a wrap from linking to itself, or including itself in a linked aggregate or list. If you create a nested design like this, you should be aware that WrapLinks don’t always return the most current value. To maintain reasonable performance, linked data is cached and refreshed only in certain situations.
Example: Wrap A has a cell todaysdate that is a WrapLink to a cell in Wrap B which fetches the value from wrap C. The cell value in a Wrap C will not be updated until the Wrap is saved, say on the Monday. The new value will not reach and be reflected by Wrap B until it is opened, which may be on the Tuesday. The linked cell in Wrap A will not be updated until Wrap B has both been opened and saved with the new value it got from Wrap C, which may be on the Wednesday. Now imagine if Wrap A links to itself for a value. The same delay will occur, so the source value (that is linked to) will often be more current, and different, from the linked value – in the same Wrap.
The circumvention is simple, instead of using a WrapLink to fetch todaysdate from the same wrap, simply use a direct cell reference like =todaysdate in the target cell.
In WraplinkLists, the list returned by a nested call will not include a cell value that hasn’t previously been saved. Example: If you use a WraplinkList in a Wrap template to return all the car numbers for which there are instances of the Wrap, then create a new instance of the Wrap for car 7013, this car number will not appear in the WraplinkList for the 7012 Wrap until you save the 7013 wrap and then open the 7012 wrap.
In WraplinkAggregates, the aggregate returned by a nested call will be calculated using the most recent values in the database. Example: A WraplinkAggregate is calculated in wrap template A as a SUM of a cell in all A wraps. There are only three A wraps with the most recently saved values for the cell being 1, 2 and 3, respectively. The sum will be returned as 6, regardless of which of the three wraps you open. If you change the source value in the third wrap to 8, the aggregate will not be recalculated and the sum will not be affected until you save the third wrap with the new value. You then have to open either of the three wraps to have its aggregate refreshed to return 11.
Due to a design constraint, the WrapLink family of functions and MyWraps2 may not return the expected results for frozen wraps when placed in hidden cells. If you want to hide the result of a linking function, you should place it on a separate hidden sheet.
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.
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:
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")
=@WRAPLINKAGGREGATE("link")
=@WRAPLINKAGGREGATE2("link", default_value)
link is a quoted text string that identifies the wrap you want to pull data from, provides the common key for the multiple wraps you are selecting, and what value you want to be returned from them. Read more below.
default_value provides a default value that the function returns in special cases described in the Results section above. The default value can also be used when you test the Wrap already in Excel. If no default value has been assigned in the function call, an empty string is returned. For the basic WrapLinkAggregate() function, this functionality is instead provided by a holder cell – read more below.
The different values that are returned by the function call are described in detail in the Results section above.
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.
The link starts with the name of the wrap you want to link to, e.g. Class395-Vehicle-PackPredictor in the example above.
The link must contain the Key that selects the group of wraps 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.
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")
The filter parameter returns a field value from the selected source wraps. 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.
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.
The format specifier is used to say how the calculated value will be displayed, formatted like the Microsoft C# format specifiers. Examples:
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%.
The state of a Wrap at any time is Live or Frozen. A WrapLinkAggregate can be restricted to live wraps only. If you specify state=live, the wrap will only select the wraps that have not been frozen.
Some live Wraps will be AwaitingSignature (waiting for the logged-in user to sign the wrap) or SignedByMe (when the signature has already been made). The state Any includes all Wraps.
=@WRAPLINKAGGREGATE("Class395-Vehicle-PackPredictor?mileage=" & mileage & &car=" & car_type & "&Filter=progress.percent&Function=MIN.p1&State=Live")
WrapLinkAggregate is a top-level function and cannot be nested inside another function like IF. If you want to make the function call only if a certain condition is True, you can place an IF statement around the link, and return an empty link to deactivate the function:
=WrapLinkAggregate(IF(required_field="","","link"))
When a Wrap only provides historical information, e.g. as a dashboard, you should always consider appending &openmode=fast to the link to it. This greatly increases performance. Read more under Openmode=fast above.
You can use a Holder Cell with the basic WralInkAggregate() function 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 extended WrapLinkAggregate2() function does not support holder cells.
=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, "")