ADVANCED TOPIC


User Defined Fields (UDFs) or Formulas are use to create new fields in the fields selector that are created by you. They can be used for any purpose and follow all SQL standards related to your company's database provider. (i.e. If you are reporting from an Microsoft SQL Server you would need to used Microsoft SQL Server syntax.)


Home section of the main menu on the Data tab:




User Defined Fields Manager


The Formulas Manager list all generated UDFs.


Make a plan when naming UDFs so they are meaningful later. By default all UDFs are public.


Easily transform existing data fields or create new ones.

Solve problems by building expressions into new data elements.


UDFs can encapsulate reusable logic and build fields that are “composable”. Built from pieces that can be reused and put together in many ways to meet the needs of users.


UDFs hide the steps and the complexity used to construct data values.


Purpose: Speed Critical Decision.


UDFs are unique in the sense that data elements within SQL Expressions are represented in business terms (aliased field names) not cryptic database terms. This makes it easier for the user to design an expression to construct a formula immediately without having to wait for the help of a DBA.


Features:

  • Extremely useful when business opportunities arise
  • Constructed using your own business aliases
  • Validated with active database provider
  • Executed faster on the larger database server machines
  • Can be nested (barring recursion)
  • Will resolve even if fields are not part of selection list
  • Blocks SQL injection
  • Maintain all security permissions to data
  • Shareable
  • Will not fail when alias names are changed
  • User Defined Formulas have notoriously been difficult to manage.
  • Something easy in a spreadsheet may be very hard when reporting from a database environment. But why? For some of the same reasons users struggle with data access, they struggle with formula creation. New data elements needed for a formula are not accessible to them.


DSF is Easier:


As you may have guessed Formulas are handled completely differently in DSF.


The end-user can select ANY field from a business data category and add it to their formula.




When defining a UDF you must select a business category. This will determine where in the Field Selector it will show up. It also defines what other field selections you can use when defining your UDF.

UDFs cannot span business categories.  




Any field can be selected from the field selector by double-clicking it. If you type an open bracket '[' in the definition field an intellisense auto fill will display the possible options:


These are the same selections as found on the hierarchical field selector list but in a dot form. As you type each dot the system will display the next level of fields to choose from.


A similar system is used if you are not within an open bracket '['. However, this time the system attempts to use any possible database provider functions that may be of useful. 


When utilizing the function help if you press enter, the top selected function syntax template will be inserted into the definition window.



After enter key:


These helper tools are just that; helper tools. It is your responsibility to make sure that the end result is in a valid form for your target data provider.




The above example is a simple CASE statement that evaluates the Gender field and converts it to a long version of same.


The preview tab below confirms it is valid and displays a sample of the return values:



When used on a view there is a clear indicator that the field is a UDF:




A faster way to create a UDF is to right-click on the field selector on the business category you wish to create a UDF.


This will automatically select the business category and bring you directly to the definition window of a new UDF:




You should notice that the new UDF called MyQuickField is using the previously created UDF called MyOwnField.


There is no practical limit to the number of nested UDFs you can create however, the system will not allow a circular reference.



In the example of MyQuickField we are converting the MyOwnField value to 'Man' or 'Woman' based on the result of the MyOwnField UDF.


Note: DSF executes all UDFs on the database server not on the local machine the way Excel or other reporting tools do. You can also filter views on UDFs thus reducing network traffic and removing server load used  to return large amounts of data that is not required. Refer to the DSF Speed whitepaper for a further explanation.


Security Note (Enterprise Only):


All security permissions to access the data elements in a formula are applied by default.

If a user does not have permission to access a field value then the results of the formula will return nothing. Any reports based on the formula will not just bomb out like what traditionally happens in other reporting systems.


The report will skip the formula field but return all other data correctly. Sometimes the result of a formula is not exposing classified data even though internally the formula needs the classified data for the calculation.