The SSRS FormatPercent function is a Text function that formats the expression or numeric field calculation as a percentage with a trailing % character. By default, this function multiplies the expression by 100, so you don’t have to do it while calculating the percentage.
The syntax of the SSRS FormatPercent function to format the numeric fields and add the percentage symbol is as shown below. Here, the second argument is the decimal precisions.
=FormatPercent(Fields!Sales.Value / Sum(Fields!Sales.Value, "AdventureWorksDataSet"), 0)
To demonstrate the SSRS FormatPercent function, we use the Employee table below, which has 15 records grouped by Occupation. The image shows the records in the Table report.
SSRS FormatPercent function Example
For this, let me add a new column to the right side of the Sales column. Next, right-click the textbox under the GP Sales% Name and choose the Expression. To understand the report, I suggest you refer to the articles on charts, tables, grouping, and format tables in SSRS.
It opens the following expression window to format the sales percentage. The SSRS FormatPercent function below will format the sales value in each Occupation group, find the percentages, and add the % symbol to them.
The first argument divides individual employees’ Sales value by the total sales amount in each Occupation group. The FormatPercent will automatically multiply the expression (result) by 100. The second argument is set to 2, so the decimal precisions will be two digits.
=FormatPercent(Fields!Sales.Value / Sum(Fields!Sales.Value, "Occupation"), 2)
Similarly, let me create a new column to the right of the Sales column and name it Tbl Sale%. Here, we calculate the table percentage (instead of the individual group). Next, assign 1 as the second argument. It means the SSRS FormatPercent function formats the Sales numbers column percentages and assigns the % symbol with one decimal precision.
=FormatPercent(Fields!Sales.Value / Sum(Fields!Sales.Value, "DSet"), 1)
Please click the preview tab to see the report. For more functions >> Click Here!
If you want the percentage of each group against the table, use the below expression.
=FormatPercent(Sum(Fields!Sales.Value, "Occupation") / Sum(Fields!Sales.Value, "DSet"), 2)
