How to use ExcelScript.ShowAsCalculation.percentOfRowTotal ?

Question

Hello,

I have this script wich works perfectly to create a PivotTable

async function main(workbook: ExcelScript.Workbook) { try {let plageSource = workbook.getWorksheet("data").getRange("A1:D321");

let feuilleDest = workbook.addWorksheet("TableauCroiseDynamique");

let tableauCroiseDest = feuilleDest.getRange("A2");

let pivotTable = feuilleDest.addPivotTable("TableauCroiseDynamique", plageSource, tableauCroiseDest);

pivotTable.addRowHierarchy(pivotTable.getHierarchy("Chercheur"));pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Typologie"));

pivotTable.addDataHierarchy(pivotTable.getHierarchy("NB"));pivotTable.addDataHierarchy(pivotTable.getHierarchy("%"))

} finally {} }

The thing I want to do is to show this line  " pivotTable.addDataHierarchy(pivotTable.getHierarchy("%")) " as a percentOfRowTotal like you can do it when you create your PivotTable manually.

ExcelScript seems to have this particular function " ExcelScript.ShowAsCalculation.percentOfRowTotal; " wich seems to be what I'm looking for but I don't know how to integrate it in my code.
Any Hints ?

Thanks a lot,
Loris

Answer 1

This is another site. We cannot move it directly to that Office development -Microsoft Q & A.

You may need to create a new one.

Thank you for your understanding.

Answer 2

Tried it like this and get those Errors

and I tried to solve it this way but still get some errors :(

Thanks for your help, can't we moove this thread in Office Development - Microsoft Q&A ?

Answer 3

Try this one:

===========================================

let dataHierarchy = pivotTable.getHierarchy("%");

Let pivotHierarchy = pivotTable.addHierarchy(dataHierarchy, "My Hierarchy");

pivotHierarchy.setShowAs(ExcelScript.ShowAsCalculation.percentOfRowTotal);

===========================================

If issue still persist. You may create a new thread in Office development which is special channel to handle office script issue.

Office Development - Microsoft Q&A

Answer 4

I've tried your suggestion but can't get rid of this error message and get the code running :

Answer 5

To use the percentOfRowTotal function in your code, you can modify the "addDataHierarchy" line to include the "ShowAs" property.

ExcelScript.ShowAsCalculation enum - Office Scripts | Microsoft Learn

Here's an example:

====

pivotTable.addDataHierarchy({

hierarchy: pivotTable.getHierarchy("%"),

showAs: ExcelScript.ShowAsCalculation.percentOfRowTotal

});

====

This will show the data hierarchy as a percentage of the row total. You can also use other "ShowAs" options such as "percentOfColumnTotal" or "percentOfGrandTotal" depending on your needs. I hope this helps!