Hello - I’m trying to create a pivot table in Workshop with more than 1 column group
There is no option to add a 2nd grouping for columns once on is selected. The current work around is having 2 pivot tables next to each other in workshop which doesn’t look good or work efficiently. I haven’t found a real workaround besides creating the table in Contour.
This will help our finance team create future projections if we could look at one table displaying monthly totals by currency.
There is only an option to add more row groupings but this needs to be done on columns.
I cannot create a report in Workshop that shows estimated payments in columns with 2 currencies.
Where as I can pull this exact report in Contour by just adding a 2nd column to group by.
Hi @Nick_Mainella,
I just posted a guide on how to workaround some of the current limitations of the pivot table widget which I think could prove useful to you, if you haven’t found a different solution yet.
While not a direct solution, it should be an easy one to adapt from the one covered in this guide.
Best,
Thank you @sboari. Going to try this with our team and we’ll let you know if we find any limitations!
@Nick_Mainella as I am not seeing this yet in our enrollment, does it come with the option to set the columns collapsible? From your screenshot it doesn‘t look like 
Okay It is now also enabled for us and I can confirm, no expandable column groupings possible 
External Image
Hi Phil- No it doesn’t do that and I’m not sure if I’d want it to do that in my case because if a user removes the ‘currency’ column grouping the data would add $500K CAD + $500K USD and display $1 M. So that data should get filtered before displaying it/removing groupings. I’m sure there’s other cases where it would be useful to use that & I do like the flexibility of expandable rows.
What I think it needs is a filter option to only show the columns by selected currency with a filter- only display ‘USD’ or only display values after today. It would be nice if users could do that on Pivot Tables like we can on Object Tables.
Hi @Nick_Mainella I understand your example. In mine I would have two Date column groups (year, quarter, month) where I would love to have each collapsible. I would start with the year and let users double click into (quarter-month) if they want to go down this granularity.
Hope this is added at some point as well.
Ya I see your point. The work around I have for that only works vertically but I put 2 of the same properties in the row grouping and one is by Month & one is by Week. Then we just display 1 at a time to view by month or week.
But it would be alot cleaner if we could do that on the columns. It should be a toggle here where you select the Time Interval, but on the column lol.
Also by grouping by Qrt would be nice.
Yes, your workaround is also what we do and it works if one does not rely on other row groupings. Otherwise you always need to drag around the date grouping to the level you want to see it. Nothing the users we work with will ever accept to do.
Hopefully a moderator or someone from Pally PD is reading this to add a +2 on that feature request 
Anyways step by step we hopefully come to the point of unleashing the potential pivot tables have 
In working with Pivot tables in Foundry, we’ve consistently encountered limitations with the available low-code, out-of-the-box features — they simply don’t meet the depth and flexibility our use cases require. Sboari, your guide was instrumental in reinforcing our decision to fully commit to using functions as the primary solution. Additionally, the VC code and OSDK approach in demonstrated by Chad Wahlquist on YouTube clearly confirmed that this is the right direction for achieving maximum capability and customization in Foundry. Here is the video: https://www.youtube.com/watch?v=2lgwr7trSgw
Are you aware you can back a pivot table by a function since recently ?
This might be a fairly involved approach for goals which have workaround with the existing low-code configuration options, but this approach should give you full flexibility.
This is an example function:
interface PivotData {
entity: string;
period: string;
metrics: {
totalValue: number;
growthRate: number;
}
}
function generatePivotData(): PivotData[] {
const entities = ["EntityA", "EntityB", "EntityC", "EntityD", "EntityE", "EntityF"];
const periods = ["Period1", "Period2", "Period3", "Period4"];
// Placeholder data
const data: {[key: string]: number[]} = {
EntityA: [100, 150, 200, 250],
EntityB: [10, 15, 18, 20],
EntityC: [50, 70, 80, 90],
EntityD: [30, 40, 50, 60],
EntityE: [5, 8, 12, 15],
EntityF: [20, 25, 30, 28]
};
const pivotData: PivotData[] = [];
entities.forEach(entity => {
periods.forEach((period, index) => {
const currentValue = data[entity][index];
const previousValue = index > 0 ? data[entity][index - 1] : 0;
const growthRate = index > 0 ? ((currentValue - previousValue) / previousValue) * 100 : 0;
pivotData.push({
entity,
period,
metrics: {
totalValue: currentValue,
growthRate: parseFloat(growthRate.toFixed(2))
}
});
});
});
return pivotData;
}
Of course be aware that triggering a lot of functions can incur a significant cost, but this should give you a flexible approach.
I first shed a tear of joy seeing this feature is live. Now, after having done some testing, I got brought back down to earth seeing the costs this incurs. Would you be able to share best practices how to define object type architecture and function strategy to be most cost-efficiently used for this?
Imagine the simple example you have a N-dimensional product hierarchy and you just want to combine the sales and orders in a pivot.
I think the function-backed pivot holds a lot potential, but really needs to be orchestrated well.
Besides the cost this is super news. Thanks