Format report content

Article
05/31/2016

Applies To: Dynamics CRM 2013

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

Use Formatting Values in Reports

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.
Reference the format field from the dataset in the Format property of the report item that you want to format.

Note

For date, time, and currency formatting, set the Language property of the report item or the report to "=First(Fields! NumberLanguageCode.Value, "DSNumandCurrency")".
Reports that use the Fixed Header property will have their header rendered twice due to a bug in the SSRS Report Viewer component.

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 https://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

Share via