Skip to main content
Solved

Pivot tables - number formatting

  • May 25, 2021
  • 6 replies
  • 1037 views
U
Freshman II
Forum|alt.badge.img

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?

Best answer by lauraj46

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

6 replies

Gabriel Michaud
Captain II
Forum|alt.badge.img+12

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 Founder @ Velixo, Excel-based reporting for Acumatica
U
Freshman II
Forum|alt.badge.img
  • uma47
  • Author
  • Freshman II
  • May 28, 2021

@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.

lauraj46
Captain II
Forum|alt.badge.img+9
  • lauraj46
  • Captain II
  • June 1, 2021

@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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
    U
    Freshman II
    Forum|alt.badge.img
    • uma47
    • Author
    • Freshman II
    • June 2, 2021

    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

     

    lauraj46
    Captain II
    Forum|alt.badge.img+9
    • lauraj46
    • Captain II
    • Answer
    • June 2, 2021

    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

    Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
      U
      Freshman II
      Forum|alt.badge.img
      • uma47
      • Author
      • Freshman II
      • June 7, 2021

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

        Terms & ConditionsAccessibility statement
        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 — 2024  Acumatica, Inc. All rights reserved