On many scenarios, pivot tables provide a concise way to summarize information in user-facing applications and dashboards. If you’ve ever wondered if there’s a more flexible way to create a pivot table in Workshop that:
- Can return a dynamic set of columns based on an input
- Inherits some of Foundry’s native quality-of-life features such as ontology formatting
- Lets you define custom aggregation logic for each column
Then this guide might come in handy! We’ll build a dynamic pivot table using an Object Table as the scaffolding for returning aggregation logic in columns using Typescript.
The use case (for context)
Consider a portfolio of companies under the ownership of a private equity (Object type: Company). Some companies (level: L1) can own several other smaller companies (level: L2). We track this hierarchy using a property with API “parentCompanyId”.
These companies are evaluated on a specific set of functions, such that:
- If an L1 company has no L2 companies under it, the L1 company is evaluated directly.
- If an L1 company has at least one L2 company under it, the results should reflect a weighted aggregation logic defined bottom-up.
For this latter case, we needed to create a view such that:
- Each row is related to one of the dimensions being evaluated
- The first column is an ordered list of all of these dimensions.
- Each additional column header is a company name, with the first column representing the L1 company (e.g., the weighted aggregation total) and then the results from L2 companies under it, if any.
Pivot table widget limitations
The pivot table widget currently can’t support all the requirements above. Mainly, one column with a custom aggregation (L1) and the direct results in the others (L2). Moreover, the ordering of the columns is not guaranteed, as one can pre-define a custom order, but that ordering will not be preserved when switching the L1 company for another one that has a different set of L2s under it. The solution below is our current implementation of it, and it works as intended. Leaving the details here for anyone who might be facing similar limitations with the pivot table widget, as this can be adapted/generalized quite easily.
The solution
We begin the code below by defining a static interface that must contain all pre-defined company names. We use the “?” after each key to denote that values are not required for all keys when implementing it. This interface needs to be declared before the class export in your .ts file:
import {Function, Filters, Double, FunctionsMap, ThreeDimensionalAggregation} from "@foundry/functions-api";
import {Objects, ObjectSet, Company, YourObject} from "@foundry/ontology-api";
interface Scores {
CompanyName1?: Double,
CompanyName2?: Double,
…
“Company Name with spaces”?: Double,
CompanyNameN?: Double
}
export class YourFunctions {
…
}
Now, inside the export class YourFunctions, we define a function that will handle the aggregation logic and return a dynamic FunctionsMap<YourObject, Scores>, thus making use of the interface we just defined. The input will be the Company object representing the L1 company that owns several L2 companies. We’ll get all required companies using the parentCompanyId (split into three due to formatting limitations).
@Function()
public async pivotYourObjectsResults(companies: Company[]): Promise<FunctionsMap<YourObject, Scores>> {
let companySet = new Set(companies.map(company => company.companyId));
const resultMap = new FunctionsMap<YourObject, Scores>();
const L1CompanyName = new String(...companies.map(comp => comp.CompanyName));
const L1CompanyResults = await this.get_agg_child_YourObjects(companySet)
const YourObject = await Objects
.search()
.YourObject()
.filter(obj => obj.companyId.exactMatch(...companySet))
.allAsync();
/*Extract distinct dimensionId values
Create a Map to store dimensionId keys with starting (null) values
This will prevent errors if some score is undefined/empty */
const YourObjectMap = YourObject.reduce((acc, item) => {
acc.set(item.dimensionId!, null);
return acc;
}, new Map<string, null | Scores | undefined>());
// Look for companies and YourObjects
const allCompanies = await Objects
.search()
.company()
.filter(comp => Filters.or(comp.companyId.exactMatch(...companySet),
comp.parentCompanyId.exactMatch(...companySet)))
.allAsync()
const YourObjects = await Objects
.search()
.YourObject()
.filter(obj => obj.companyId.exactMatch(...allCompanies.map(company => company.companyId)))
.groupBy(obj => obj.dimensionId.exactValues({maxBuckets: 22}))
.segmentBy(obj => obj.companyName.exactValues({maxBuckets: 100}))
.max(obj => obj.score)
YourObjects.buckets?.forEach(outerBucket => {
const outerKey = outerBucket.key;
let dimension_scores : Scores = {};
outerBucket.value?.forEach(innerBucket => {
const innerKey = innerBucket.key;
dimension_scores[innerKey as keyof Scores] = Number(innerBucket.value !== undefined ? innerBucket.value.toFixed(2) : undefined)
});
dimension_scores[L1CompanyName as keyof Scores] = L1CompanyResults.get(outerKey) !== undefined ? Number(L1CompanyResults.get(outerKey)?.toFixed(2)) : undefined
YourObjectMap.set(outerKey, dimension_scores)
});
// Return results
YourObject.forEach(obj =>{
resultMap.set(obj, YourObjectMap.get(obj.dimensionId!)!)
});
return resultMap;
}
By calling and grouping the YourObjects by the dimensionIds and then by the company names, for each outerBucket (each dimensionId, or row in our resulting table), we then create a Scores instance, which we fill with the statement dimension_scores[innerKey as keyof Scores] = (…) inside the loop. Finally, we set the results for the L1 company with the statement: dimension_scores[L1CompanyName as keyof Scores] = (…).
You might have noticed that the L1 company aggregation is done through the call:
const L1CompanyResults = await this.get_agg_child_YourObjects(companySet)
In this case, get_agg_child_YourObjects is a private function that hosts the custom aggregation logic. In case you’re interested in a similar example, this is a simplified version of it (note that in this example, Company has a one-to-many link to YourObject (in this case, via companyId):
private async get_agg_child_YourObjects(companySet: Set<string>): Promise<Map<string, Double | undefined>>{
let childCompaniesYourObjectPercentageMap = new Map<string, Double | undefined>();
let childCompaniesWeightedYourObjectMap = new Map<string, Double | undefined>();
// Get aggregation weight percentages (number between 0 and 1) by parentCompany, CompanyId
const childCompaniesYourObjectPercentage = await Objects
.search()
.company()
.filter(comp => comp.parentCompanyId.exactMatch(...companySet))
.groupBy(comp => comp.parentCompanyId.exactValues({maxBuckets: 1000}))
.segmentBy(comp => comp.companyId.exactValues({maxBuckets: 1000}))
.max(comp => comp.aggregation_weight)
childCompaniesYourObjectPercentage.buckets.forEach(outerBucket => {
outerBucket.value.forEach(innerBucket => {
childCompaniesYourObjectPercentageMap.set(innerBucket.key, innerBucket.value);
});
});
// Get child companies YourObjects
const childCompaniesYourObject = await Objects
.search()
.company()
.filter(comp => comp.parentCompanyId.exactMatch(...companySet))
.searchAroundYourObject()
.groupBy(obj => obj.companyId.exactValues({maxBuckets: 1000}))
.segmentBy(obj => obj.dimensionId.exactValues({maxBuckets: 22}))
.average(obj => obj.score)
childCompaniesYourObject.buckets.forEach(outerBucket => {
let weight = childCompaniesYourObjectPercentageMap.get(outerBucket.key);
outerBucket.value.forEach(innerBucket => {
let dimensionId: string = innerBucket.key;
if (!childCompaniesWeightedYourObjectMap.has(dimensionId)) {
childCompaniesWeightedYourObjectMap.set(dimensionId, weight!*innerBucket.value);
} else {
let OldValue = childCompaniesWeightedYourObjectMap.get(dimensionId)!;
let newValue: Double | undefined = OldValue + weight!*innerBucket.value;
childCompaniesWeightedYourObjectMap.set(dimensionId, newValue)};
});
});
return childCompaniesWeightedYourObjectMap;
};
Finally, we need one more piece of code that will come in handy for dynamically selecting which properties of the interface Scores we’ll be displaying in workshop based on the same input that we’re providing the previous function. Note that by ordering first by level and then by CompanyName, we ensure the first column will be the L1 company.
@Function()
public async ArrayofYourObjectsForPivotVisibility(companies: Company[]): Promise<Array<string>> {
// Look for companies and YourObjects
const allCompanies = await Objects
.search()
.company()
.filter(comp => Filteobj.or(comp.companyId.exactMatch(...companies.map(company => company.companyId)),
comp.parentCompanyId.exactMatch(...companies.map(company => company.companyId))))
.orderBy(comp => comp.level.asc())
.orderBy(comp => comp.CompanyName.asc())
.takeAsync(100)
const newElement: string = 'property_dimension_id'
const keysArray: Array<string> = Array.from(allCompanies.map(comp => comp.companyName))
.map(element => "derived_column." + element);
keysArray.unshift(newElement)
return keysArray;
}
Final steps: configuring the function-backed columns in workshop.
It’s time to put all of these pieces together in workshop: create an Object Table and feed it with a placeholder YourObject ObjectSet. In this object set, each object is a dimension being evaluated. We use as a basis the YourObjects linked to the L1 Company. We won’t display properties or aggregations directly, but rather setting our custom aggregation function. Set an object set holding the L1 company only as input for the function pivotYourObjectsByDimension, and even if the interface keys were set to optional with the ? and only some of them return values, workshop will fill the full list by default (you’ll have many NULL columns, column names redacted here, each being a company name, as shown below).
To remove unwanted columns (e.g., all companies not in scope when selecting the corresponding L1), scroll down to the configuration of the object table widget, enable the “Variable-backed column visibility”:
As input for the “Visible Columns”, set the output array from the function ArrayofYourObjectsForPivotVisibility. As input for the function, you’ll provide the Company object with the user selection for L1 company.
The final result will loke something like this:
Since the underlying widget is an Object Table widget, Dimension ID is inheriting the color formatting from the ontology, and we have the totals from the aggregations at L1 level as the first numeric column, and then all relevant L2 results, even for companies for which we don’t have results yet. This will refresh automatically using the interface we defined when the user selects another L1s with a different set of L2s.
Conclusion and potential improvements
This process highlights an alternative to using the pivot table widget for enhanced flexibility. Not only that, but it can also be extended for returning non-numeric columns as well on workflows that require custom / complex logic. Defining dynamic interfaces is not yet supported, as FunctionsMap requires that all interface properties are explicitly declared beforehand. A nice improvement/addition would be to have the possibility of declaring dynamic interfaces, such as:
Interface Scores {
[key: string]?: Double,
}
thus removing the requirement of having to pre-define all the keys.
Hope this helps someone who has faced the limitations of the pivot table widget before! Do you agree with this approach?
Any improvements / alternatives you’d like to share? I’d love to read your comments below!
(We‘d greatly appreciate some details if some has intel?)