Format report content

Article
11/28/2016

Applies To: Dynamics 365 (on-premises), Dynamics CRM 2016

You can obtain the Microsoft Dynamics 365 organization formatting values for date, time, number, and currency by using the fn_GetFormatStrings SQL function that is available in the Microsoft Dynamics 365 database. This function returns a single-row data table that contains the formatting values. To view the contents of the returned table, execute the following SQL query 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 Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS).
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 365.
2. Use the SQL SELECT statement described above to fill the dataset.
Reference the format field, such as date and time, number, or currency (described below) 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	=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 MSDN: How to: Add, Edit, or Delete a Field in the Report Data Pane. 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 in the Format property of the report item as follows:

=Fields.crm_moneyformatstring.Value

Share via