Formatting of BDC Fields

Question

I'm trying to format a field in a BDC (Business Data Catalog) definition, in SharePoint, with a thousand separator.

It doesn't appear to be possible in the BDC XML definition, and only possible through the SharePoint Designer(!). The fields I've got at present are System.Decimal, so it displays as 12345.98, but I'm wanting it to display as 12,345.98.

Do you know if it can be achieved through the BDC XML Definition ?

    <Parameter Direction="Return" Name="@ContactTotals">
      <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Reader">
        <TypeDescriptors>
          <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Record">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Int32" IdentifierName="dim_claims_key" Name="dim_claims_key" />
              <TypeDescriptor TypeName="System.Decimal" Name="total_outstanding" DefaultDisplayName="Total Outstanding (USD)" />
              <TypeDescriptor TypeName="System.Decimal" Name="total_paid" DefaultDisplayName="Total Paid (USD)" />
              <TypeDescriptor TypeName="System.Decimal" Name="total_incurred" DefaultDisplayName="Total Incurred (USD)" />
            </TypeDescriptors>
          </TypeDescriptor>
        </TypeDescriptors>
      </TypeDescriptor>
    </Parameter>
  </Parameters>

Cheers

Nick

Answer 1

XML is a meta-language not intended to format or present information, it describes and stores other vocabularies. That in mind, the answer is: No, you cannot achieve what you asked using XML only.

A recommended way would be to use the XSLT <xsl:decimal-format /> element in the BDC List View or BDC Item View webpart you are using. If you are consuming the data trough other ways you can easily format the output during rendering.

Say you have this portion of code displaying your decimal type:

<xsl:value-of select="$ColName_0" />

You need to encapsulate it with something like (based on the sample in the link):

<xsl:value-of select="format-number($ColName_0, '#.###,00', 'euro')"/>

You can find the XSLT for the webpart in the Modify Shared WebPart menu, or, as you said, using SharePoint Designer.

Answer 2

Seems possible to define Complex Formatting at TypeDescriptor elements. As I don't have an environment to properly test this solution, following definitions seems to be valid and addresses your particular scenario:

    <Parameter Direction="Return" Name="@ContactTotals">
      <TypeDescriptor TypeName="System.Data.IDataReader, ..."
                      IsCollection="true" Name="Reader">

        <!-- note this -->
        <Properties>
            <Property Name="ComplexFormatting"
                      Type="System.String" />
        </Properties>

        <TypeDescriptors>
          <TypeDescriptor TypeName="System.Data.IDataRecord, ..." Name="Record">
            <TypeDescriptors>
              <TypeDescriptor TypeName="System.Int32"
                              IdentifierName="dim_claims_key"
                              Name="dim_claims_key" />
              <TypeDescriptor TypeName="System.Decimal"
                              Name="total_outstanding"
                              DefaultDisplayName="Total Outstanding (USD)" />

                  <!-- note this -->
                  <Properties>
                      <Property Name="FormatString"
                                Type="System.String">{0:#.###,00}</Property>
                  </Properties>
              </TypeDescriptor>
              ...
            </TypeDescriptors>
          </TypeDescriptor>
        </TypeDescriptors>
      </TypeDescriptor>
    </Parameter>
  </Parameters>

Note as warned in MSDN documentation, "ComplexFormatting is slow". Maybe is better to stick with F.Aquino answer