Solved

Pivot tables - number formatting


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?

icon

Best answer by lauraj46 2 June 2021, 15:15

Hi @uma47 ,

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

View original

6 replies

Userlevel 6
Badge +7

Hi @uma47,

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.

@Gabriel Michaud Thanks for the reply. I had a look. It works if it is a “Value” in the pivot table, but not when we have a number field as a row or a column of the pivot table. Unfortunately mine are all rows.

Userlevel 2
Badge

@uma47 

I 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

GI

Pivot

 

Properties

 

In the excel

 

Userlevel 2
Badge

Hi @uma47 ,

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

@lauraj46 Thanks. We did a OData and now its with the customer. Hopefully they are satisfied.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved