Pivot tables - number formatting

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.

@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

Pivot

Properties

In the excel

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.