[Guide] Dynamic pivot tables using Object Table widget + Typescript

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:

  1. Can return a dynamic set of columns based on an input
  2. Inherits some of Foundry’s native quality-of-life features such as ontology formatting
  3. 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!

7 Likes

Hi @sboari. Big kudos! This really is a creative workaround and we‘ll give it a try! However, also bit of a pity that we have to become so creative on pivot tables… Pivot tables are a great way to condense a lot of information into small real estate. And we often times use them to let the users navigate to the scope „on which they want to perform an action“. So fingers crossed that the enhancements rumouring here in the forum for „early 2025“ will come soon and alleviate some pains with pivots :sweat_smile: (We‘d greatly appreciate some details if some has intel?)

One question on your solution: Do you have an overview of how much compute your implementation costs per run? Depending on the pricing model or your foundry instance - I can imagine that this will produce quite significant costs (in case of compute based costing). We lately had some „surprises“ how much compute just a few function-backed columns can produce compared to simple aggregates through linked columns.

3 Likes

I agree with Phil-M, Pivot tables are vital for financial reporting. In one of our projects, we’re replacing hundreds of SAP-BW reports with Workshop, focusing on pivot tables. However, we underuse the ontology and linked Object types, relying on workarounds with custom Object types. Future updates will hopefully address these gaps.

Sboari thanks for the guide because we can use it regarding our earlier question that you solved of the derived properties, so thanks again.

2 Likes

Thanks for your comments, @Phil-M, @Palmaker! Hope you found it useful and can adapt something like this to your workstreams.

Regarding:

We haven’t monitored costs for this function yet, so I can’t provide specific comments on costs at this point. In terms of performance, the compute time for this function is roughly ~1 sec per run.

From experience on developing functions using similar aggregation patterns (async API calls / mapping results / final loop to put it together), we haven’t noticed a significant bump on costs before and after including them in our workshop apps, even for cases where functions are dealing with much larger input object sets.

Just out of curiosity, for the increase in costs you mention, can you comment on whether you were running multiple derivate columns as independent functions or as a single function? How much compute time was it taking per run?

Best,

Hi @sboari we had them as individual function backed columns. But did not try if compute is different when they are combined.
Question to you: can you interact with the table? I mean like the „active/multi selector“ object(s). I understand in your case a row is not actually an object but something derived?

Hey @Phil-M. If you’re accessing the same underlying objects and have many derived columns, it may be possible to bundle those up in a single function using an interface like the one described at the beginning of the guide and make use of less API calls overall for your derived properties. As far as I understand it, this may help in reducing overall compute costs associated with running functions for derived columns.

Regarding this:

Question to you: can you interact with the table? I mean like the „active/multi selector“ object(s). I understand in your case a row is not actually an object but something derived?

to use FunctionsMap for derived properties, you use an underlying obect set in an object table. In this case, it’s the with the placeholder name YourObject. You can interact with it via the active object selection in an object table. That lets you interact with that row/rows for multiple selection in the table.

Best,

Hm, I am intrigued. Last question: does inline edit functionality works?
Each row would be a „key figure“ that is either source-backed, user editable or a derived of the two (like the sum). The columns would be timely aggregates (month quarter year).
If these requirements resemble with you - yes it is a classical BI planning view.

I’m not sure I completely follow, but let me provide a few extra notes on applicability of this approach below:

From the guide above, YourObject (the base object type) stores information on some dimensions being evaluated. The L1 in this case is user-selected, and using this method, you get the custom aggregation results for the L1 and its L2s as an order set of dynamic, derived columns, which is not currently possible using the pivot table widget.

When using a return type of FunctionsMap, the returned columns are not directly editable, as they represent an aggregation of some form and are not stored in the underlying object set.

However, all of the object table widget functionalities should work for the underlying object set in the object table. This means that:

  • Selection (single/multiple) will select the current row(s) from the table
  • Inline edits will work for any properties you choose to enable direct edit
  • If the derived custom aggregation(s) depend on those editable properties, they will automatically re-compute upon applied inline edits.

This approach is quite useful in workflows that permit adjusting the values of the constituents (L2s) and you need to automatically show updated values for the L1, which is why we’ve gone the compute route rather than pre-aggregating the information from source.

Hope these notes help clarify the approach! Let me know what you think.

Best,

Sboari, I know I bother you but as Typescript expert can I ask you quick question ( lost half a day on just one error which is: Property ‘aggregate’ does not exist on type ‘ObjectSet’. Use case: I have an Object Type (api NlFpo), with properties: bookdate , value and eac. Function: creates date range from input: startDate and endDate. Filters ((sum)value + (sum) eac) based on the date range.

Here is my code:
import { Function, LocalDate, Double } from “@foundry/functions-api”;
import { Objects } from “@foundry/ontology-api”;

export class MyFunctions {
@Function()
public async computeAggregatedSum(startDate: LocalDate, endDate: LocalDate): Promise<{ sumValue: Double; sumEAC: Double }> {
const result = await Objects.search()
.nlFpo()
.filter(fpo => fpo.bookdate.gte(startDate).lte(endDate))
.aggregate({
$select: {
sumValue: { $sum: “value” },
sumEAC: { $sum: “eac” } // Ensure this matches the schema exactly
}
});

return {
  sumValue: result.sumValue ?? 0,
  sumEAC: result.sumEAC ?? 0
};

}
}

A suggestion is appreciated

Hy there @Palmaker!

This following suggestion assumes you’ll be accessing sumValue and sumEAC as return variables in workshop, but please feel free to further clarify on the intended use for the return types.

The issue with your code is that the function is trying to access a property called “aggregate”, which doesn’t exist in your object.

Try this modified code:

import { Function, LocalDate, Double } from “@foundry/functions-api”;
import { Objects } from “@foundry/ontology-api”;


interface returnValues{ sumValue?: Double, sumEAC?: Double }


export class MyFunctions{
   @Function()
   public async computeAggregatedSum(startDate: LocalDate, endDate: LocalDate): Promise<returnValues> {
      let returnStruct: returnValues = {};
      const result = Objects.search()
         .nlFpo()
         .filter(fpo => fpo.bookdate.gte(startDate).lte(endDate))
        
      const result_sumValue = await result
         .sum(fpo => fpo.value)
        
      const result_sumEAC = await result
         .sum(fpo => fpo.eac)
        
      returnStruct.sumValue = result_sumValue?.valueOf()
      returnStruct.sumEAC = result_sumEAC?.valueOf()        
    
   return returnStruct;
   }
}

After testing and publishing your function, you would access these values in workshop by creating a new Struct, function-backed variable:

and then access each field (sumValue, sumEAC) by creating a new Numeric → Struct field variable in workshop

Hope this helps!

Best,

He super thanks for your reply. I already had a look at it and it’s a big help. I don’t think you have an idea how your support is appreciated. Thanks again. Lex

1 Like

It works thanks again, big help

1 Like

Thank you so much @sboari for the detailed response. I went ahead and created this custom table using the code below:

interface TableObject {
row: string;
originalCost: string;
modifiedCost: string;
delta: string;
}

@Function()
public getCostBucketDifference(es_bom : _v0EsBom): TableObject[]{
    const lineItemsArray = es_bom.esBomLineItem.all();
    const lineItemsWithOriginalCost = lineItemsArray.filter(li => li.fromDuplicate === true);
    const modifiedlineItems = lineItemsArray.filter(li => li.lineItemStatus === 'Removed');

    const originalCostSum = lineItemsWithOriginalCost.reduce((acc, li) => {
    acc.fromCb1 += li.fromCb1 || 0;
    acc.fromCb2 += li.fromCb2 || 0;
    acc.fromCb3 += li.fromCb3 || 0;
    acc.fromCb4 += li.fromCb4 || 0;
    acc.fromCb5 += li.fromCb5 || 0;
    acc.fromCb6 += li.fromCb6 || 0;
    acc.fromCb7 += li.fromCb7 || 0;
    acc.fromCb8 += li.fromCb8 || 0;
    acc.fromCb9 += li.fromCb9 || 0;
    acc.fromCb10 += li.fromCb10 || 0;
    acc.fromCb11 += li.fromCb11 || 0;
    acc.fromCb12 += li.fromCb12 || 0;
    acc.fromCb13 += li.fromCb13 || 0;
    acc.fromCb14 += li.fromCb14 || 0;
    acc.fromCb15 += li.fromCb15 || 0;
    acc.fromCb16 += li.fromCb16 || 0;
    acc.fromCb17 += li.fromCb17 || 0;
    return acc;
},{ fromCb1: 0, fromCb2: 0, fromCb3: 0, fromCb4: 0, fromCb5: 0, fromCb6: 0, fromCb7: 0, fromCb8: 0, fromCb9: 0, fromCb10: 0, fromCb11: 0, fromCb12: 0, fromCb13: 0, fromCb14: 0, fromCb15: 0, fromCb16: 0, fromCb17: 0});

const modifiedCostSum = lineItemsWithOriginalCost.reduce((acc, li) => {
    acc.fromCb1 += li.fromCb1 || 0;
    acc.fromCb2 += li.fromCb2 || 0;
    acc.fromCb3 += li.fromCb3 || 0;
    acc.fromCb4 += li.fromCb4 || 0;
    acc.fromCb5 += li.fromCb5 || 0;
    acc.fromCb6 += li.fromCb6 || 0;
    acc.fromCb7 += li.fromCb7 || 0;
    acc.fromCb8 += li.fromCb8 || 0;
    acc.fromCb9 += li.fromCb9 || 0;
    acc.fromCb10 += li.fromCb10 || 0;
    acc.fromCb11 += li.fromCb11 || 0;
    acc.fromCb12 += li.fromCb12 || 0;
    acc.fromCb13 += li.fromCb13 || 0;
    acc.fromCb14 += li.fromCb14 || 0;
    acc.fromCb15 += li.fromCb15 || 0;
    acc.fromCb16 += li.fromCb16 || 0;
    acc.fromCb17 += li.fromCb17 || 0;
    return acc;
},{ fromCb1: 0, fromCb2: 0, fromCb3: 0, fromCb4: 0, fromCb5: 0, fromCb6: 0, fromCb7: 0, fromCb8: 0, fromCb9: 0, fromCb10: 0, fromCb11: 0, fromCb12: 0, fromCb13: 0, fromCb14: 0, fromCb15: 0, fromCb16: 0, fromCb17: 0});

const deltaSum = {
    cb1: originalCostSum.fromCb1 - modifiedCostSum.fromCb1,
    cb2: originalCostSum.fromCb2 - modifiedCostSum.fromCb2,
    cb3: originalCostSum.fromCb3 - modifiedCostSum.fromCb3,
    cb4: originalCostSum.fromCb4 - modifiedCostSum.fromCb4,
    cb5: originalCostSum.fromCb5 - modifiedCostSum.fromCb5,
    cb6: originalCostSum.fromCb6 - modifiedCostSum.fromCb6,
    cb7: originalCostSum.fromCb7 - modifiedCostSum.fromCb7,
    cb8: originalCostSum.fromCb8 - modifiedCostSum.fromCb8,
    cb9: originalCostSum.fromCb9 - modifiedCostSum.fromCb9,
    cb10: originalCostSum.fromCb10 - modifiedCostSum.fromCb10,
    cb11: originalCostSum.fromCb11 - modifiedCostSum.fromCb11,
    cb12: originalCostSum.fromCb12 - modifiedCostSum.fromCb12,
    cb13: originalCostSum.fromCb13 - modifiedCostSum.fromCb13,
    cb14: originalCostSum.fromCb14 - modifiedCostSum.fromCb14,
    cb15: originalCostSum.fromCb15 - modifiedCostSum.fromCb15,
    cb16: originalCostSum.fromCb16 - modifiedCostSum.fromCb16,
    cb17: originalCostSum.fromCb17 - modifiedCostSum.fromCb17
};

let resultTable: TableObject[] = [
    {
        row: 'Specialties',
        originalCost: originalCostSum.fromCb1.toString(),
        modifiedCost: modifiedCostSum.fromCb1.toString(),
        delta: deltaSum.cb1.toString()
    },
    {
        row: 'Steel',
        originalCost: originalCostSum.fromCb2.toString(),
        modifiedCost: modifiedCostSum.fromCb2.toString(),
        delta: deltaSum.cb2.toString()
    },
    {
        row: 'Weld Rod',
        originalCost: originalCostSum.fromCb3.toString(),
        modifiedCost: modifiedCostSum.fromCb3.toString(),
        delta: deltaSum.cb3.toString()
    },
    {
        row: 'OS Process',
        originalCost: originalCostSum.fromCb4.toString(),
        modifiedCost: modifiedCostSum.fromCb4.toString(),
        delta: deltaSum.cb4.toString()
    },
    {
        row: 'Labor',
        originalCost: originalCostSum.fromCb5.toString(),
        modifiedCost: modifiedCostSum.fromCb5.toString(),
        delta: deltaSum.cb5.toString()
    },
    {
        row: 'Intra-Co-Freight',
        originalCost: originalCostSum.fromCb6.toString(),
        modifiedCost: modifiedCostSum.fromCb6.toString(),
        delta: deltaSum.cb6.toString()
    },
    {
        row: 'Overhead',
        originalCost: originalCostSum.fromCb7.toString(),
        modifiedCost: modifiedCostSum.fromCb7.toString(),
        delta: deltaSum.cb7.toString()
    },
    {
        row: 'Inter-Co-Profit',
        originalCost: originalCostSum.fromCb8.toString(),
        modifiedCost: modifiedCostSum.fromCb8.toString(),
        delta: deltaSum.cb8.toString()
    },
    {
        row: 'Freight',
        originalCost: originalCostSum.fromCb9.toString(),
        modifiedCost: modifiedCostSum.fromCb9.toString(),
        delta: deltaSum.cb9.toString()
    },
    {
        row: 'Freight to Eagle Pass',
        originalCost: originalCostSum.fromCb10.toString(),
        modifiedCost: modifiedCostSum.fromCb10.toString(),
        delta: deltaSum.cb10.toString()
    },
    {
        row: 'Warranty',
        originalCost: originalCostSum.fromCb11.toString(),
        modifiedCost: modifiedCostSum.fromCb11.toString(),
        delta: deltaSum.cb11.toString()
    },
    {
        row: 'Rebate',
        originalCost: originalCostSum.fromCb12.toString(),
        modifiedCost: modifiedCostSum.fromCb12.toString(),
        delta: deltaSum.cb12.toString()
    },
    {
        row: 'Other',
        originalCost: originalCostSum.fromCb13.toString(),
        modifiedCost: modifiedCostSum.fromCb13.toString(),
        delta: deltaSum.cb13.toString()
    },
    {
        row: 'Surcharges',
        originalCost: originalCostSum.fromCb14.toString(),
        modifiedCost: modifiedCostSum.fromCb14.toString(),
        delta: deltaSum.cb14.toString()
    },
    {
        row: 'X-Ray',
        originalCost: originalCostSum.fromCb15.toString(),
        modifiedCost: modifiedCostSum.fromCb15.toString(),
        delta: deltaSum.cb15.toString()
    },
    {
        row: 'Bucket 16',
        originalCost: originalCostSum.fromCb16.toString(),
        modifiedCost: modifiedCostSum.fromCb16.toString(),
        delta: deltaSum.cb16.toString()
    },
    {
        row: 'Bucket 17',
        originalCost: originalCostSum.fromCb17.toString(),
        modifiedCost: modifiedCostSum.fromCb17.toString(),
        delta: deltaSum.cb17.toString()
    }
];

return resultTable;

The output comes as below when I use the live preview of the function. However, I am unable to ingest this output in the workshop app using a widget/variable.

hey there @adityal2810!

you are almost there

From your last screenshot:

The output comes as below when I use the live preview of the function. However, I am unable to ingest this output in the workshop app using a widget/variable.

It seems you’re trying to return just a “value” in which the “row” label acting as the identifier for the row and then deriving properties called “originalCost”, “modifiedCost” and “delta” for each of these.

However, you need to adjust this a bit for usage in workshop. For that, you need to return a type called FunctionsMap (you need to import it on your import statement from the functions api as:

import {FunctionsMap} from "@foundry/functions-api";

Usage examples in the docs cover how to return these derived properties:
Vertex • Graphs • Derive properties using Functions • Palantir

Essentially, you need to return a type:

FunctionsMap<YourObjectType, TableObject[]>

for this, first remove the “row” key from TableObject. These values should come from your object array as a property.

The return type will look similar to this in the test preview in code repo:

In which the “key” part is an object per row, and the “value” part is the return from your interface.

Hope this helps.

Best,

Hi SBoari,

I hope you’re doing well. I’m reaching out because I’ve hit a roadblock with a function I’m working on, and you’re my last hope to get it resolved.

I have an Object type NlFpo with the properties **ProjectManager (**in code: projectVerantwoordelijke) and projectId. On a Workshop dashboard, I’ve set up a filter list to select ProjectManager, but it only shows one project, even though a ProjectManager can be associated with multiple projects.

The function needs to:

  1. Iterate over all related projects for a selected ProjectManager
  2. Generate the respective dashboards
  3. Create and save PDFs

To save the PDFs, I created an FPODashboard Object type, but I can’t get the function working as expected. I’ve tried different approaches, but I’m stuck. Would you be willing to take a look? Your help would mean a lot, and I’d really appreciate any insights you can share. I attached the code.

Thanks in advance!

import { Function, LocalDate, Double, FunctionsMap } from “@foundry/functions-api”;
import { Objects, ObjectSet, NlFpo, FpoDasboards } from “@foundry/ontology-api”;
import { WorkshopDashboard, PdfExportOptions } from “@foundry/workshop-api”;
import { Uuid } from “@foundry/functions-utils”;

export class ProjectReportGenerator {
@Function()
public async generateProjectReports(
projectVerantwoordelijke: string,
projectId: string,
dashboardId: string
): Promise {
// Retrieve all projects associated with the ProjectManagerID
const projects = await this.getProjectsByManagerId(projectVerantwoordelijke);

    // Create a folder for storing PDFs
    const folder = await this.getOrCreateFolder(`Reports_${projectVerantwoordelijke}`);

    // Fetch all dashboards and manually filter
    const dashboardSet = Objects.search();
    const dashboards = await dashboardSet.fetchAll(); // Use fetchAll() to fetch all objects
    const dashboard = dashboards.find((d: WorkshopDashboard) => d.id === dashboardId); // Explicitly define type
    if (!dashboard) {
        throw new Error(`Dashboard with ID ${dashboardId} not found`);
    }

    // Iterate over each project and generate a PDF
    await Promise.all(projects.map(async (project) => {
        const pdfFile = await this.generatePdfForProject(dashboard, project);
        await this.savePdfToFolder(folder, pdfFile, projectId);
    }));
}

private async getProjectsByManagerId(projectVerantwoordelijke: string): Promise<NlFpo[]> {
    // Fetch projects linked to the ProjectManagerID
    const nlFpoSet = await Objects.search(NlFpo);
    const nlFpos = await nlFpoSet.fetchAll(); // Use fetchAll() to fetch all objects

    // Manually filter projects
    return nlFpos.filter(nlFpo => nlFpo.projectVerantwoordelijke === projectVerantwoordelijke);
}

private async generatePdfForProject(dashboard: WorkshopDashboard, project: NlFpo): Promise<File> {
    const pdfOptions: PdfExportOptions = {
        filters: { projectId: project.ProjectId },
        fileName: `${project.ProjectId}_report.pdf`
    };
    return await dashboard.exportToPdf(pdfOptions);
}

private async savePdfToFolder(folder: FpoDasboards, pdfFile: File, projectId: string): Promise<void> {
    const fileName = `${projectId}_report.pdf`;
    await folder.createFile(pdfFile, { name: fileName });
}

private async getOrCreateFolder(name: string): Promise<Folder> {
    const existingFolderSet = Objects.search(FpoDasboards);
    const existingFolders = await existingFolderSet.fetchAll(); // Use fetchAll() to fetch all objects
    const existingFolder = existingFolders.find(folder => folder.name === name);

    if (existingFolder) {
        return existingFolder;
    }

    return Objects.create().FpoDasboards(Uuid.random(), { name: name });
}

}

Strange thing here. I was at the stage to implement a “Custom Sboari” ObjecttablePivot function in the Object table widget and ran into the following problem. As of your example (1) in the Object table columns configuartion I selected +Add column → Function-backed property (2). By Select a Function (3) I couldn’t select the desired function. The function itself gives in the Live Preview in code repo all the required columns with aggregations. In an older version I could follow your expamle and could choose the function, Any thoughts. See pics

I tried to implement the function into the Object table but didn’t work. I tried the metric card and the custom widget but didn’t work either. But what works is the Vega chart where you can add the function, as I use a filter in the Workshop dashboard select the filtered Object set and then adding JSON into the Vega-lite SPEC it was worth a small celebration.