I'm using the pivot grid control along with a query tool that the end user will use to create a sql statement that gets excuted and returned as a datatable. So we are unable to format the pivot grid at design time. The issue I have is that one of the possible fields returned is a decimal(9,4) data type wich when placed in the Grand Total (or any total field) it is treated as currancy, how can I have the Total fields treat this differntly?
please see attached images.
I was able to do what I needed by changing my method
Private Sub UpdateGridTypes() For Each field As PivotGridField In adhocpivotgrid.Fields If field.DataType Is GetType(Decimal) Then field.CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric field.CellFormat.FormatString = "D2" field.ValueFormat.FormatType = FormatType.Numeric field.ValueFormat.FormatString = "D2" End If If field.DataType Is GetType(DateTime) Then field.GroupInterval = PivotGroupInterval.DateYear End If If field.Name.Contains("Charge") Then field.CellFormat.FormatString = "C" field.ValueFormat.FormatType = FormatType.Numeric field.ValueFormat.FormatString = "C2" End If Next field End Sub
If I understand you correctly, you retrieve pivot fields from datasource at runtime. In this case it is necessary to specify field's CellFormat property after all fields are retrieved.
Attached is a small sample project that illustrates this approach. Try it and let me know whether or not it meets your requirements.