Round function is widely used to manipulate decimal numbers. It serves us to display a decimal number rounded either to a desired precision scale or to the nearest integer number. In SSRS, round function is a part of common function group which can be used through expressions.
The syntax of the round function in SSRS is the following:
Round(Value as Double/Decimal [,precision scale]),
where precision scale part is optional.
Round(Fields!unitprice.Value), where Fields!unitprice.Value=122.581
In the example above, the round function will return value 123, since it is programmed to return closest integer value of the provided decimal number, as there is no precision scale defined.
Round(Fields!unitprice.Value,1), where Fields!unitprice.Value=122.581
In the example above, the round function will return value 122.6, since it is programmed to return a decimal number rounded to one decimal.
In order to explain how to use SSRS round function, we will use the data provided in the table below. We will add additional columns called “Tax Amount – R0” and “Tax Amount – R2” which will be populated by using round function.
Examples of Round function usage
We have created a report which displays the data from Products table.
Figure 1: Basic report structure
The data displayed in the report is contained in ProductsDS embedded dataset which can be found in report Datasets folder in Report Data tab:
Figure 2: Dataset used in a report creation
If we open the ProductsDS dataset, we will see the query and how the column tax amount is being calculated:
Figure 3: Calculation of Tax Amount column
In the previous picture we see that we haven’t specified the format of Tax Amount column, and later we will see how it will be calculated and presented in the report.
Now we will add three columns that will hold the tax amount calculated as 17% of unit price value, in order to demonstrate different behaviours of decimal numbers with and without usage of round function:
- TAX AMOUNT – ORIGINAL column will not use round function, it will present data as they are calculated
- TAX AMOUNT – R0 column will use round function so that it rounds the number to its closest integer number
- TAX AMOUNT – R2 column will use round function so that it rounds the number to the precision scale of 2
The steps are the following:
1. Create additional column which will hold TAX AMOUNT – ORIGINAL
Figure 4: Adding TAX AMOUNT – ORIGINAL column
2. Add taxamount field from the dataset
Figure 5: Setting TAX AMOUNT – ORIGINAL column value
3. Define header name to the added column
Figure 6: Setting TAX AMOUNT – ORIGINAL column header
4. Create additional column which will hold TAX AMOUNT – R0
5. Add expression to the column value right-clicking on the cell and clicking “Expression…”
Figure 7: Setting TAX AMOUNT – ORIGINAL expression
6. Under Category select Math, and under Item select Round and double-click it so you add it in expression value
Figure 8: Adding Round function to the expression
7. Under Category choose Fields (ProductsDS), and under Values double-click taxamount in order for it to be displayed in the expression value. Close the bracket in the expression value and click OK.
Figure 9: Finalizing expression value containing Round function without defined precision scale
8. Create additional column which will hold TAX AMOUNT – R2
9. Repeat the steps 5 and 6 for this column
10. Under Category choose Fields (ProductsDS), and under Values double-click taxamount in order for it to be displayed in the expression value. Add precision scale of 2 in round function definition, close the bracket in the expression value and click OK.
Figure 10: Finalizing expression value containing round function with defined precision scale
11. Display the report so we can see the difference between the columns containing the Tax Amount value.
Figure 11: Presentation of columns populated based on round function
As we can see, the values in columns containing tax amount differ:
- TAX AMOUNT – ORIGINAL is calculated on 4 decimal places automatically
- TAX AMOUNT – R0 uses round function in order to get the closest integer value of provided decimal number
- TAX AMOUNT – R2 uses round function in order to round provided decimal number to two decimal places
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.