Problem
Some unusual, but useful math related functions exist in SQL Server Reporting Services (SSRS); what are these functions and how can I use them? I can specifically use help with generating random data, rounding options, multiplying large numbers and more.
Solution
If you have been working with SQL Server Reporting Services (SSRS) for a while or you have been working with SSRS for just a short time, you may not be aware of some of the more advanced math functions that are available for you to use. These functions help to simplify certain equations or solve issues which may otherwise prove to be quite complex to work out. Items such as random number generation and large scale multiplication give SSRS a great way to achieve results in just a few steps. In addition to those two functions, we will also discuss using: Floor, Ceiling, BigMul, Fix, and Val.
We will use the Adventure Works databases as the basis for our SSRS report design. The 2014 versions of the regular and data warehouse databases are available. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop a report that uses the various functions. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.
SQL Server Reporting Services RND Function
The first function we will use is the RND function which generates a random number with a data type of single (single = precision floating-point numbers ranging in value from -3.4028235E+38 through -1.401298E-45 for negative values and from 1.401298E-45 through 3.4028235E+38 for positive values—see https://msdn.microsoft.com/en-us/library/xay7978z.aspx ). The basic syntax is easy and is shown below: RND(). Remember the results will give you values that include decimal values as shown in the second screen print.
You may be wondering what a random number function could be used for (other than making really neat Financial Statements for your accounting department). One way that I have used the RND function is to generate a set of dates. As shown in the next illustration, we use the DateAdd function to add a random number of days to today’s date. I multiply by 100 to make sure the random values are usually greater than 1. The new results are shown in the second screen print; pretty neat date generator! </p
SQL Server Reporting Services FIX Function
Now let us move from the world of randomness to something more fixed, the FIX function. The FIX function is not at all related to RND; its job is to return the integer portion of a number. No rounding or other data transformation occurs; the function simply cuts off everything to the right of the decimal as shown in the second screen print below. One of the common uses for such a function is when you need to use a number, which contains a whole “bunch” of places to the right of the decimal, but only use the integer portion in a calculation.
SQL Server Reporting Services BIGMUL Function
In a similar vain, if you are working with very large numbers, on financial statements for instance, then the BIGMUL function may come in handy. This allows you to multiply, successfully, two 32bit sized integers (-2,147,483,648 through 2,147,483,647). The syntax, as illustrated next, basically requires two arguments, one for each number to be multiplied. The result of the example numbers is shown in the second screen print; that is A LOT of zeros.</p
If we try to do this same calculation using the old fashion method noted in the below screen print, then an error results which is shown in the second screen print. This error basically says the product cannot be determined because the value is too large to be an integer. </p
SQL Server Reporting Services FLOOR and CEILING Functions
The FLOOR and CEILING functions act in a similar way to the FIX function. The FLOOR function returns the nearest integer value that is less than or equal to the value specified (rounds down no matter the values to the right of the decimal) . Likewise, the CEILING function returns the nearest integer value that is greater than or equal to the value specified (rounds up no matter the values to the right of the decimal). These two functions again assist a report developer to achieve specific rounding requirements when needed for a report. Some calculations require an integer value rounded in a specific way, and these two functions assist in those situations. As shown below, both functions require a single argument. </p
The CEILING and FLOOR function results are shown below.</p
SQL Server Reporting Services VAL Function
The last function in our report developer toolbox is a quasi math function, but the VAL function is still extremely useful. Say you want to extract the Year portion of a field that is Year Month Name combination. There are several ways to achieve this result, but the easiest is to use the VAL function which extracts the numeric portion of a field. As shown below we can use VAL function to get the year from the OrderYearMonth field. The second illustration shows the results of this extraction.
It should be noted, though, in the above screen print, that if the numeric portion of the field value is not first, a 0 is returned as shown in the third column. That column used a field that appeared Month-Year, such as “June-2012” and thus returned a 0. This situation is often handy for striping the numeric portion of street address.
Conclusion
In this tip we discussed and showed examples of using some of the lesser known Math functions available in SSRS. The RND function produces a random number. The FLOOR, CEILING, and FIX functions return the integer portions of a given value, each with their own specifications on how the value will be rounded. The BIGMUL function allows for the multiplication of VERY LARGE numbers up to a 32bit sized integer. Finally, the VAL function extracts the numeric portion of an input as long as the numeric portion appears first.
Next Steps
- Consider these functions for your upcoming reporting needs.
- Check out these resources:
