June 21, 2012 09:21 by
Scott
Formatting numbers in an SSRS report is a common task. For example, you may want to format a number as currency or percentage.
You can select a format from the number page of the properties window.
You can let sql handle the formatting, so data in the result set is pre-formatted.
DECLARE @Sales MONEY = 32182000.85;
SELECT ‘$’
+ CONVERT(VARCHAR(32),@Sales,1);
Results:
Finally, you can use the newly introduced FORMAT() function in SQL Server 2012. Format() will, according to books online, return a value formatted with the specified format and optional culture. So, instead of converting and concatenating like we did in the previous example, FORMAT() can be used:
DECLARE @Sales MONEY = 32182000.85;
SELECT FORMAT(@Sales,‘c’,‘en-us’);
Results:
FORMAT() accepts the following parameters:
- Value. Actual value that needs to be formatted.
- Format. Value will be formatted to the specified format. Currency, percentage, and date are few examples.
- Optional Culture. Specifies the language. More about cultures on BOL.PARSE()
Consider the following query. Value is formatted to three different languages based on the culture:
Formatting Currency:
DECLARE @Sales MONEY = 32182000.85;
SELECT FORMAT(@Sales,‘c’,‘it-IT’) [Italy]
, FORMAT(@Sales,‘c’,‘fr’) [France]
, FORMAT(@Sales,‘c’,‘ru-RU’) [Russian];
Results:
Formatting percentages:
DECLARE @Per DECIMAL(2,2) = 0.72;
SELECT FORMAT(@Per,‘p0′,‘en-us’)
, FORMAT(@Per,‘p2′,‘en-us’);
Results:
Conclusion:
Similar formatting is ideally done in the presentation layer, reporting services for example. But I would want to let reporting services do minimal processing. FORMAT() simplifies string formatting. It provides functionality that most developers have always wanted.