Reporting Services Number Formatting Expressions
Expressions and number formatting
As well as the standard text formatting options, you can format the style of numbers and dates. To get to these more advanced options, select the cell and then right click on it. From the menu, choose Text Box Properties…
As well as changing the visual styling of data, you can apply functions to the data as well. A useful built in function is MonthName(). This function converts numbers from 1 to 12 into the corresponding month name, e.g. 1 becomes January, 2 becomes February etc. When you modify data in this way, it becomes an expression.
You can open the expression editor by opening Text Box Properties (right click on a cell) and on the General page of the dialog, look for the fx button to the right of Value and to the right of Tooltip. Click this button to open the editor. Expressions can be built up using the selections below the main window, or typed in directly.
1. In Design view (click the Design tab at the top of the window), select the Month data cell by clicking on its edge. Be sure to select the cell and not the cell contents. You’ll know you’ve done it when the cell gets a thick border.
2. Right-click on the selected cell and choose Text Box Properties… from the menu.
3. Make sure General is selected, and then click the fx button next to the Value drop down list. This will open the expression editor.
4. Edit the expression so that it reads
Make sure you add both the parentheses. The MonthName function will convert numbers into month names, so where our report has a 12, it will display “December”, etc. Click OK and then OK again to return to the report design surface.
5. In the same way, open the Text Box Properties for the Total cell. Select Number from the page list on the left, and format the text box as Currency, with no decimal places and a thousands separator. Click OK.
6. Preview the report by clicking the Preview tab at the top of the window.