Hi, I have created a pivot table for shipment data and it has lots of number fields that are presented as text, when you export to excel. Is there a way to format the number columns so, it will show left aligned in the table and will have the correct format when exported to excel?
Solved
Pivot tables - number formatting
Best answer by lauraj46
Hi
I am seeing the same behavior on the export to Excel with the formatted row values. My apologies for the confusion. I don’t know if it’s possible to export the row values from a pivot table to Excel as numbers.
Have you considered using OData to add a data connection in Excel to the Generic Inquiry? If you go that route, you could link to the GI and create the pivot all in Excel.
Laura
+11Hi
I just tried the same here with the Invoiced Items pivot and the numbers are formatted correctly (see attached example). Can you try one of the standard pivot tables to see if it works fine for you as well ?
Do you have any formulas or calculation done in the Generic Inquiry linked to this pivot table? If you are able to share the contents of the GI (send screenshots and/or use the Export to XML option from the toolbar), please do so and I’ll try to help.
+8I think that you can make it work if you use CDec to convert your text field to a decimal, and specify a schema field in the Generic Inquiry. After that you can use the Format property in the pivot table to specify the formatting as needed.
In my test it formatted correctly both in the pivot and on export to Excel.
Hope that helps!
Laura
thanks @lauraj46. However I am still struggling to get the outcome needed. My Pivot has SOOrder.Openorderqty as a row field.
GI
Pivot
Properties
In the excel
+8Hi
I am seeing the same behavior on the export to Excel with the formatted row values. My apologies for the confusion. I don’t know if it’s possible to export the row values from a pivot table to Excel as numbers.
Have you considered using OData to add a data connection in Excel to the Generic Inquiry? If you go that route, you could link to the GI and create the pivot all in Excel.
Laura
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.