Wednesday, April 25, 2007

Formatting number fields

I have been setting up many reports for a MS CRM system using SSRS and there are a multitude of places where numeric and dollar amounts are being printed. I could just use one of the standard formatting codes provided by SSRS, but then if the customer changes their settings in CRM, it will not be reflected in the custom reports.

I realized today that there is a database function (found under the Programmability/Functions/Table-valued Functions) available to me in CRM called fn_GetFormatStrings() that returns most of the number formats I would need. The function returns a single record (you query it in SSRS like it was a table) and each field contains the number formatting string. Each field is named something appropriate, such as "NumberFormat_2_Precision" and the string looks like this: "###,###,###,##0.00;-###,###,###,##0.00;0.00". So to make use of that, we have created a query in the RDL called DSNumberFormat that pulls that one record and then in the Format for that textbox, we make a reference to that using the expression
=First(Fields!NumberFormat_2_Precision,"DSNumberFormat")

No comments: