Reporting Services Number Formatting Expressions In SSRS

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…

If you would like to become an SSRS (SQL Server Reporting Services) certified professional, then visit Mindmajix - A Global online training platform: "SSRS Training and Certification Course". This course will help you to achieve excellence in this domain.

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.

                                                          (Related Page: Adding Charts To A SSRS Report (Report Builder)

Try it

1. In the 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
=MonthName(Fields!Month.Value)
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 thousand separator. Click OK.

6. Preview the report by clicking the Preview tab at the top of the window.

Frequently Asked SSRS Interview Questions & Answers

List of Related Microsoft Certification Courses: