Format Report Content
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
-
Create a dataset to contain the formatting data. For information about how to create a dataset, see Microsoft SQL Books Online.
-
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.
-
Use SQL code with the SELECT statement to fill the dataset.
-
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.
-
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")". |
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 |
|
|
Time |
|
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 |
|
|
Decimal with 2-decimal points precision |
|
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 |
|
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.
Note