Export (0) Print
Expand All

Format Report Content

[Applies to: Microsoft Dynamics CRM 2011]

You can obtain the formatting values for date, time, number, and currency by using the fn_GetFormatStrings SQL function that is available 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 database server:

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

To Use Formatting Values in Reports

  1. Create a dataset to contain the formatting data. For information about how to create a dataset, see Microsoft SQL Books Online.

    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 that you want to format.

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

Date and Time Values

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

 

Formatting string Report item Format property value

Date

=First(Fields!DateFormat.Value, "DSNumandCurrency")

Time

=First(Fields!TimeFormat.Value, "DSNumandCurrency")

Number Values

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.

 

Formatting string Report 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")

Base Currency Value

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.

 

Formatting string Report item Format property value

Base currency with 2-decimal points precision

=First(Fields!CurrencyFormat_2_Precision.Value, "DSNumandCurrency")

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

See Also

Microsoft Dynamics CRM 2011
Send comments about this topic to Microsoft.
© 2013 Microsoft Corporation. All rights reserved.

Community Additions

Show:
© 2014 Microsoft