II will extend report created in post and show you how you can implement formatting for recursive group with the Level function.
Open report from the mentioned post or use another one with recursive Tablix group. Choose any of an item in the Tablix. I chose the Path text box since it will be more illustrative. Click on the Textbox and go to Properties Pane to Padding section, Left, Expression and put following formula.
Picture 1 – Level function
You should see similar settings in Properties Pane as on the picture bellow.
Picture 2 – Level function Properties Pane
Look at the report on picture bellow. You can see that the Path Textbox has dynamic padding based on how many recursive child’s current item has. As states in short description, Level function Returns a zero-based integer representing the current depth level of a recursive hierarchy. It helps in our scenario to change format of Padding property based on which depth level of hierarchy is current item placed.
Picture 3 – Recursive Tablix group with dynamic padding format
With Reporting Services is number formatting so easy. You can continue with our series and get report from here, or just open your report and follow bellow instructions. There are 2 basic places in designer surface where to format figures in your report Tablix.
Through Tablix Text Box Properties
Properties Pane
Tip: if you would like to format more text boxes in Tablix you can do it in few ways.
Select text boxes you would like to format by clicking mouse cursor holding Left shift and change formatting in Properties Pane. (this will work in case that all text boxes would have the same formatting, if one text box format differs from others you will not be allowed to change the format).
You can change formatting in one text box and by copy paste action to other textboxes, formats are transferred.
Download report and open it in a texts editor and replace formatting tags
Let’s look on the report we created in post SSRS Tablix – repeating header. We would like to format Sum Sales Amount column with currency.
Picture 1 Sales Report
Go to text box with Sum Sales Amount value. Choose for example the one from EnglishCountryName group, right click and select Text Box Properties from popup menu.
Picture 2 Textbox Properties
Text Box Properties window is opened, and you can set Currency formatting options. Click on Currency category and choose format you would like to apply. On picture bellow you can see that you have many options to set currency format: how many decimal places, if use 1000 separator, how to display negative numbers which currency symbol to display and where, etc. Regional setting of machine where Reporting Services are installed has impact on default settings in Text Box Properties window. Reporting Services supports regional formatting in different regional environments. For our report I left the default English settings.
Picture 3 Textbox Properties – Number currency format
After formatting the rest of text boxes with Sum Sales Amount you can see formatted report bellow.
Picture 4 Sales report – currency format
In Properties Pane, Number section, Format property you can find currency formatting we set.
Picture 5 Properties Pane – Number format
In other posts I will describe other scenarios and approaches to format data in reports.
