Your SQL questions, answered

Round function in SSRS – Querychat

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.

Example nr.1:

Round(Fields!unitprice.Value), where Fields!unitprice.Value=122.581

Result nr.1:

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.

Example nr.2:

Round(Fields!unitprice.Value,1), where Fields!unitprice.Value=122.581

Result nr.2:

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.

Table: Products

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

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc