Have you ever found yourself in a situation where your users have developed complex Excel spreadsheets to perform calculations and manipulate data from your SQL Server database? If so, you may have wondered if it’s possible to replace these spreadsheet calculations with SQL Server functions. The good news is that SQL Server does offer a solution to this problem.
One option is to use the XLeratorDB library of functions developed by Westclintech. This library contains over 700 functions that can be incorporated into T-SQL statements. XLeratorDB takes advantage of the SQL CLR architecture introduced in SQL Server 2005, allowing managed code to be compiled into the database and run alongside built-in SQL Server functions.
Let’s say you have a spreadsheet that uses the YIELD function to calculate bond yields. In Excel, this function is readily available, but SQL Server does not have a native YIELD function. However, with XLeratorDB, you can replicate this calculation in SQL Server with a simple statement:
SELECT *,
wct.YIELD(CAST(GETDATE() AS date), Maturity, Rate, Price, 100, Frequency, Basis) AS YIELD
FROM BONDS
This statement will produce the same results as the YIELD function in Excel. The best part is that XLeratorDB functions are easy to use. You can simply copy the function call from the spreadsheet and paste it into SQL Server Management Studio (SSMS), replacing the cell references with column names.
The XLeratorDB library is not limited to financial calculations. It also includes functions for statistics, mathematics, strings, engineering, and financial options. In fact, there are hundreds of additional functions that are not found in Excel. You can explore these functions by opening Object Explorer in SSMS and expanding the Programmability folder under the database where the functions have been installed.
XLeratorDB functions are Intellisense-enabled, meaning that the input parameters are displayed directly in the query tab. The Westclintech website also provides documentation for all the functions, including examples that can be copied and executed in SSMS. Additionally, there are over one hundred articles on the site that provide more detailed explanations and demonstrate various business processes that can be done in SQL Server using XLeratorDB functions and T-SQL.
If you’re interested in trying out XLeratorDB, you can download a 15-day free trial from the Westclintech website. This trial version is fully-functioning and unrestricted. If you need more time to evaluate the software, you can simply download another 15-day free trial.
XLeratorDB is an easy and cost-effective way to add sophisticated data analysis to your SQL Server database without needing to learn anything beyond T-SQL. So why not give it a try and see how it can simplify your complex calculations and improve the performance of your database?