Export (0) Print
Expand All

Formatting Content

banner art

[Applies to: Microsoft Dynamics CRM 4.0]

Formatting values for time, date, number, and currency can be obtained by using the fn_GetFormatStrings SQL function that is provided in the Microsoft Dynamics CRM database. This function returns a single row data table that contains formatting values. To view the contents of the returned table, execute the following SQL code in Query Analyzer on the Microsoft Dynamics CRM database server:

USE <organization>_MSCRM
SELECT * FROM dbo.fn_GetFormatStrings()

To use the formatting values in reports, follow the steps:

  1. Create a dataset to contain the formatting data. For more information about how to create a dataset, see Microsoft SQL Books Online at http://msdn2.microsoft.com/en-us/library/ms160345.aspx.
    1. Name the dataset DSNumandCurrency or use another name. DSNumandCurrency is the dataset name that is used in reports that are included with Microsoft Dynamics CRM.
    2. Use SQL code with the SELECT statement to fill the dataset.
  2. Reference the format field from the dataset in the Format property of the report item you want to format.

Date and Time Values

Formatting stringReport item Format property value
Date=First(Fields!DateFormat.Value, "DSNumandCurrency")
Time=First(Fields!TimeFormat.Value, "DSNumandCurrency")

For date and time, you should also set the Calendar property of the report item to "=First(Fields!CalendarType.Value, "DSNumandCurrency")".

Number Values

Formatting stringReport item Format property value
Integer=First(Fields!NumberFormat_0_Precision.Value, "DSNumandCurrency")
Decimal with 2-decimal points precision=First(Fields!NumberFormat_2_Precision.Value, "DSNumandCurrency")

The fn_GetFormatStrings function returns the number of format strings with precision values between 0 and 5. You can specify a precision by putting a decimal value between the underscore characters in the field name.

Base Currency Value

Formatting stringReport item Format property value
Base currency with 2-decimal points precision=First(Fields!CurrencyFormat_2_Precision.Value, "DSNumandCurrency")

The fn_GetFormatStrings function returns base currency format strings with precision values between 0 and 5. You can specify a precision by putting a decimal value between the underscore characters in the field name.

Transaction currency

When you create a report on an entity with the transaction currency information, you can retrieve the transaction currency format string from the crm_moneyformatstring column of the filtered view for an entity. After a column has been added to the dataset, you can reference the column on the Format property of the report. For more information about how to add columns to a dataset, see http://msdn.microsoft.com/en-us/library/ms156295.aspx. For example, to retrieve a price unit and the transaction currency formatting information from the quote detail filtered view, use the following SELECT statement:

SELECT priceperunit, crm_moneyformatstring FROM FilteredQuoteDetail

You can reference a new column on the Format property of the report item as follows:

=Fields.crm_moneyformatstring.Value

Note   For date, time, and currency formatting, set the Language property of the report item or the report to "=First(Fields! NumberLanguageCode.Value, "DSNumandCurrency")".

See Also

Concepts

Other Resources


© 2010 Microsoft Corporation. All rights reserved.


Show:
© 2014 Microsoft