Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Unknown but Useful Functions


By:   |   Last Updated: 2015-09-15   |   Comments (2)   |   Related Tips: > Reporting Services Functions

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 on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. 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.


RND Function
RND Function

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!

RND Data

Date Results

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.

Fix_Function

fix funciton results

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.

BigMul

BIGMUL results

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.

Regular Multiply

Multiply Error

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.

ceiling

floor

The CEILING and FLOOR function results are shown below.

ceiling and floor results

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.

VAL Function

VAL results

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


Last Updated: 2015-09-15


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, August 16, 2019 - 8:53:36 AM - Subie Madh Back To Top

Thanks for the article and listing some of the SSRS built in functions, but it would be really great if there was more practical information.

What developers really need are real world examples of using the functions and some of the more creative ways they can accomplish certain tasks or requirements. 

For example, why exactly would you want to generate a random date?  Is this a lottery application?

The CEILING function:  "Some calculations require an integer value rounded in a specific way, and these two functions assist in those situations."  Ok,true, but what specific ways?  Perhaps you could use it to control the amount of detail records per page by using it in a grouping along with the rownumber function?  What other practical applications is it capable of. 


Wednesday, September 07, 2016 - 1:54:08 AM - Khalid Hamada Back To Top

 Very informative, thanks for for tips


Learn more about SQL Server tools