Crystal Reports Formula Types | CLEARIFY

Formula Types

There are three types of functions with which to create formulas:

1. String
2. Math
3. Date

There are also Conditional Programming shortcuts, and Function Operators

String Functions

A majority of your time will be spent create string manipulation formulas.  Why?  Here are some examples

• An existing QuickBooks file setup is not optimized for choosing a particular type of item - you may have to choose items that 'start with' certain characters.
• You may want to organize a list of customers by alphabet (First letter of Customer Last Name is 'A', therefore it is categorized under group 'A'.
• You may desire to show a customer number - which is the information after a 'dash' in the customer name.
• Creating a custom grouping formula using the alphabet as a prefix to ensure the grouping is proper order; but you don't want to display that first character.
• Replace strings within a string.
• Check to see if a string exists within a particular string.
• Check the length of a particular string.
• Create everything to UPPER CASE
• Change UPPER CASE to Proper Case
• A need to convert a number to a string - or vice-versa

Math Functions

The easiest to produce, numeric functions give you the following abilities (and more):

• add, subtract, divide, multiply
• Integers
• Rounding
• Divisors and Remainders
• Absolute Values
• Truncate

Date Functions 

QQube provides you with many different ways of representing dates, so the use of date functions will be less prominent in your tool box for QQube projects. Pay particular attention to the DatePart function as it is the strongest function to add and subtract date time frames.

Programming Shortcuts

There will be many times where you will need to product a formula based upon a condition. For example,

• If an item is service based you might want one result, if it is a non-inventory item, then you might want another result.
• If a transaction falls in one particular month, then return one result; a different month, then another result.

Operators

These contain both the simple pieces of a formula and the more advanced pieces such as passing variables.  Things like addition, subtraction, division can simply be added by typing in the operator in the formula, as opposed to dragging it in the formula workshop editor.