izenda-and-udfs

default

markdown

#Izenda and UDFs (User Defined Functions)

[[_TOC_]]

##Question

How do I utilize a complex UDF from my database within Izenda?

##Answer

Here is a step-by-step tutorial on how to use UDFs from your database in Izenda.

**1.** Create the UDF in the database that performs your calculation:

**EXAMPLE UDF:**

**MsSQL**
```sql
CREATE FUNCTION [dbo].[SafeDivide] (@p1 real ,@p2 real )
RETURNS real 
AS
BEGIN
    RETURN ISNULL(@p1 / NULLIF(@p2, 0), 0)
END
```

**Oracle**
```sql
create or replace function "SAFEDIVIDE"
(p1 in NUMBER, p2 in NUMBER)
return NUMBER
as
begin
    RETURN ROUND(nvl(p1 / NULLIF(p2, 0), 0), 7);
end;
```

**2.** Add this function to your [[CustomAdHocConfig|http://wiki.izenda.com/Integration/Tutorials/Customizing-Izenda-Settings]]:
``AdHocSettings.ExtendedFunctions = new string[] { "[dbo].[SafeDivide]" };``

**3a.** You can now choose this function in the 'Fields' tab function drop down for any column. (Functions, as the one above, with more than one input parameter cannot be chosen for a single column in the functions drop-down and must be implemented as an expression.)

![Example UDF](http://wiki.izenda.com/FAQ/FAQ/udfs_example.png)

**3b.** You can also use this function in the [[Expression|http://wiki.izenda.com/FAQ/UserGuides/15.0-Expressions-in-Izenda]] box on the 'Fields' tab's [[advanced panel|http://wiki.izenda.com/FAQ/advanced-field-settings]].

```sql
    SafeDivide([UnitsOnOrder], [UnitsInStock])
```

You can also use the Format dropdown to turn the result into your desired format. See the result below for what happens when we use the above expression in our Northwind Traders DataSource.

![Percent of Stock With UDFs](http://wiki.izenda.com/FAQ/FAQ/udfs_example_2.png)

Uploading file...

Header

Sidebar

![Izenda Logo](http://izenda.com/wp-content/uploads/2014/12/IzendaNewLogoBlueTR.png)

---

* [[Izenda 7 Series Documentation|https://www.izenda.com/docs/]]
* [[Release Notes]]
* [[Upgrade Best Practices|FAQ/Izenda-Update-Best-Practices]]
* [[FAQ]]
* [[Tutorials]]
* [[The Izenda API|/API/AdHocConfig]]
* [[The AdHocSettings class|/API/AdHocSettings]]
* [Developer Links and Guides](/Guides/Developer-Links-and-Guides)
* [[Known Issues]]

---

* <a href="http://www.izenda.com" rel="nofollow" target="_blank">Izenda Website</a>
* [Product Video](https://www.youtube.com/watch?v=X3-yWFq0w5A)
* <a href="http://www.izenda.com/blog" rel="nofollow" target="_blank">Izenda Blog</a>
* <a href="http://www.izenda.com/company/" rel="nofollow" target="_blank">About Us</a>
* <a href="http://www.izenda.com/contact-us/" rel="nofollow" target="_blank">Contact Us</a>

---
* [Guide To Report Design](/Guides/ReportDesign)
* [Making Custom Forms Video](http://www.youtube.com/watch?v=5b2axJlgdFs)

---

* [[Acknowledgements]]

Edit message:

Cancel

Editing izenda-and-udfs

Header

Sidebar