Macro Functions
Functions are short syntactical representations of useful but complex activities. There are four kinds of functions that can be performed by CDD:
Compute Functions
Date Functions
Get Functions
Full Functions List
Compute Functions
There are several macros used to get budget amounts, for example:
SetBudgets
GetIfasBudget
GetIfasBudgetEx
GetIfasBudgetEs25
CalcBudget
If any of the listed functions are used, they do not pass back a value for any computed budget that comes from the Actual (calculation is AC) or from another fiscal year (calculation is similar to -0001WB).
If you need the actual value, use the ComputeAmount or CalcAmount function. If you need a budget from another fiscal year, you must design the report to include that year in the selection criteria.
GetAmount
NumField = getamount(#,amt1,amt2,...,amt12)
The getamount function is used primarily to get encumbrance figures. There are 12 encumbrance accumulators. Each one contains the outstanding encumbrance as of the end of that fiscal period. The # value passed is a numeric value, or a numeric user defined field that contains a period from 1 through 12. The amounts are typically GLACT.EN01,.GLACT.EN12. The function returns the #'th amount. This simply eliminates a large IF statement. NOTE: This function works the same in both ActiveMacro and the traditional macro language.
ComputeAmount
NumField = computeamount(#,amt1,amt2,...,amt12)
The computeamount function is used primarily to compute actual balances. There are 12 actual accumulators. Each one contains the total GL transaction amount posted in that fiscal period. The # value passed is a numeric value, or a numeric user defined field that contains a period from 1 through 12. The amounts are typically GLACT.ACTUAL01,.GLACT.ACTUAL12. The function returns the sum of the first #' amounts. This simply eliminates a large IF statement. NOTE: This function works the same in both ActiveMacro and the traditional macro language.
SetBudgets
setbudgets LedgerCode , "GLBUDG.BUDGET01" , "GLBUDG.BUDGET02", "GLBUDG.BUDGET03", "GLBUDG.BUDGET04", "GLBUDG.BUDGET05", "GLBUDG.BUDGET06", "GLBUDG.BUDGET07", "GLBUDG.BUDGET08", "GLBUDG.BUDGET09", "GLBUDG.BUDGET10"
The setbudgets function is used to initialize the budget value for a computed budget version. There are 10 possible budget versions, any one of which may be the calculated sum of two or more budget versions. A computed budget version in BusinessPlus is calculated by the system at the moment of display; this feature has merely been replicated for CDD by the Setbudgets Function. Each budget version contains amounts for each budgeted item at all budget levels and across all fiscal periods, thus selection criteria must be used if you wish to see, for instance, only FY97 budgets at the Object Code level. The Ledger Code value passed is the two-character ledger code ID; this may also be the Database Item "LEDGER," or a pre-defined user field. The SYSBUDGETVERSION is a System Field which one may prompt for at run-time; SYSBUDGETVERSION may also be substituted with a two letter Budget Version, or a user-defined field. The amounts are typically GLBUDG.BUDGET01 through GLBUDG.BUDGET10. The function temporarily initializes calculated budgets so that they may be used on the report in the same manner as a hard-coded budget version. This eliminates the need for the user to manually compute budgets. NOTE: This function does not work the same in ActiveMacros as it does in traditional macros. The names of the fields that contain the budget amounts must be passed in to the function. This means you must put quotes around the field name as shown in the function description above.
Also note that since this function does not return a value, it is improper to use parentheses around the parameter list (for more information on this refer to a VBScript reference looking under procedure types and the Call statement).
GetIfasBudget
NumField = getifasbudget (LedgerCode, SYSBUDGET VERSION, _ GLBUDG.BUDGET01, GLBUDG.BUDGET02, _GLBUDG.BUDGET03, GLBUDG.BUDGET04, _ GLBUDG.BUDGET05, GLBUDG.BUDGET06, _ GLBUDG.BUDGET07, GLBUDG.BUDGET08, _ GLBUDG.BUDGET09, GLBUDG.BUDGET10)
The getifasbudget function is used to derive the budget value for a prompted budget version. There are 10 possible budget versions. Each version contains amounts for each budgeted item at all budget levels and across all fiscal periods, thus selection criteria must be used if you wish to see only FY97 budgets at the Object Code level. The Ledger Code value passed is the two-character ledger code ID, this may also be a database item representing ledger, or a pre-defined user field. The SYSBUDGETVERSION is a System Field which one may prompt for at run-time; SYSBUDGETVERSION may also be substituted with a two letter Budget Version, or a user defined field. The amounts are typically GLBUDG.BUDGET01 thru GLBUDG.BUDGET10. The function returns the correct amount for a prompted budget version, within a specific level and budgeted
"object." This eliminates the need for having separate reports for each budget version.
GetIfasBudget25
Same as getifasbudget, but with support for up to 25 budget versions.
GetIfasBudgetEx
NumField = getifasbudgetex (LedgerCode, SYSBUDGETVERSION, "GLBUDG.BUDGET01", , "GLBUDG.BUDGET02", "GLBUDG.BUDGET03", "GLBUDG.BUDGET04", "GLBUDG.BUDGET05", "GLBUDG.BUDGET06", "GLBUDG.BUDGET07", "GLBUDG.BUDGET08", "GLBUDG.BUDGET09", "GLBUDG.BUDGET10")
The getifasbudgetex function combines the functionality of "setbudgets" with the functionality of "getifasbudget." Now instead of performing a two-step process of calling "setbudgets" followed by "getifasbudget," the user can simply make a single call to "getifasbudgetex." Note that the "ex" at the end of the function name indicates that this function has extended functionality. NOTE: As with the other two budget functions, the names of the fields that contain the budget amounts must be passed in to the function. This means you must put quotes around the field name as shown in the function description above.
GetIfasBudgetEs25
Same as getifasbudgetex, but with support for up to 25 budget versions.
GetPeriodAmt
getperiodamt(period,field)
Returns a specific ACTUAL or EN value based on period specified. It doesn't matter what number is placed after ACTUAL## or EN##; all ACTUAL/EN values are automatically passed into the routine.
period Fiscal Period (1-14)
field Table/Column value (i.e. "GLBA_BUDACT_MSTR.GLA_ACTUAL01", "GLBA_BUDACT_MSTR.GLA_EN01")
CalcAmount
calcamount(period,field)
Returns the sum of all values for gla_actualxx or gla_encxx amounts up to and including the period specified. It doesn't matter what number is placed after ACTUAL## or EN##; all ACTUAL/EN values are automatically passed into the routine.
period Fiscal Period (1-14)
field Table/Column value (i.e. "GLBA_BUDACT_MSTR.GLA_ACTUAL01", "GLBA_BUDACT_MSTR.GLA_EN01"
CalcBudget
calcbudget(ledger,vers,field)
An improved version of GetIfasBudgetEx; faster and easier to use.
ledger Ledger Code
versBudget Version
fieldAny of the up to 25 budget fields
Date Functions
Date functions allow one the flexibility to derive useful date values from a single date supplied by the report, for instance the Report Date (expressed as REPORTDATE) or the Current Date (expressed as CURRENTDATE) and to tie those values back to the report in the form of data rows, or selection criteria, or for use in the macro section. In the syntax examples below note that the User Defined field utilized for a particular date function will vary in type between Date and Numeric, depending on the nature of each function.
datefield = datefyb(refdate); Get the fiscal year begin date
datefield = datefye(refdate); Get the fiscal year end date
datefield = datefqtb(refdate); Get the fiscal quarter begin date
datefield = datefqte(refdate); Get the fiscal quarter end date
numfield = datefmo(refdate); Get the fiscal month
numfield = datefqt(refdate); Get the fiscal quarter
numfield = datefy(refdate); Get the fiscal year
datefield = datecyb(refdate); Get the calendar year begin date
datefield = datecye(refdate); Get the calendar year end date
datefield = datecqtb(refdate); Get the calendar quarter begin date
datefield = datecqte(refdate); Get the calendar quarter end date
datefield = datemob(refdate); Get the month begin date
datefield = datemoe(refdate); Get the month end date
numfield = datecom(refdate); Get the calendar month
numfield = datecyr(refdate); Get the calendar year
numfield = datecqt(refdate); Get the calendar quarter
numfield = dateday(refdate); Get day of date
numfield = datemonth(refdate); Get month of date
numfield = dateyear(refdate); Get year of date
numfield = datedaysdiff(date1,date2); Get the number of days between two dates
numfield = datemonthsdiff(date1,date2); Get the number of months between two dates
numfield = dateyearsdiff(date1,date2); Get the number of years between two dates
datefield = dateadddays(date,x); Add x days to date
datefield = dateaddweeks(date,x); Add x weeks to date
datefield = dateaddmonths(date,x); Add x months to date
datefield = dateaddyears(date,x); Add x years to date
Get Functions
GetGLGen
getglgen("KEY","S","GL");
For the GL ledger, get the Org Key Short description. The ledger code portion is optional, and in most cases would be left off the statement.
The first parameter can be KEY or OBJ, and the second parameter can be "S" for Short, "M" for medium or "L" for long. The value returned is the short, medium or long description of the Org Key or Object Code for the active ledger.
IfasGetGLGenIndexed
ifasgetglgenindexed("OBJ",1,"L","GL");
For the GL ledger, get the Object Code Part 1 long description. The ledger code portion is optional, and in most cases would be left off of the statement. The first parameter can be KEY or OBJ. The second parameter can be any numeric value from 1 through 8 corresponding to the Org Key or Object part whose description is desired. The third parameter can be "S" for Short, "M" of medium or "L" for long, indicating which description is desired. The value returned is the short, medium or long description of the Org Key or Object code Part.
GetKeyPart
getkeypart(GLKEY.LEDGER,3,GLKEY.PART03,'1');
For the specific Ledger from the report, get the description of Org Key Part 3 with the specified value of "x" from the report. The first parameter can a database item, a user defined field, or a hard coded two-letter Ledger Code value. The second parameter can be any number from 1-10, corresponding to the Key Part desired (if ENTITY is PART03, then the appropriate value would be 3.) The value returned is the description of the Org Key Part Code specified in the third parameter.
GetObjPart
getobjpart(GLOBJ.LEDGER,1,GLOBJ.PART01,'1');
For the specific Ledger from the report, get the description of Org Key Part 1 with the specified value of "x" from the report. The first parameter can a database item, a user defined field, or a hard coded two-letter Ledger Code value. The second parameter can be any number from 1-10, corresponding to the Key Part desired (if BAL SHEET is PART01, then the appropriate value would be 1.) The value returned is the description of the Org Key Part Code specified in the third parameter.
GetFQA
getfqa (ledger,key,object)
GetFQA function takes 3 arguments, Ledger, Key, and Object, and returns Account Control on the basis of the setting defined in GLUPGN for Presentation, Output Format (reports) for the ledger.
Ledger: Ledger Code
Key: Key
Object: Object Code
For example, in AP1001: GLAccount = GetFQA(OHDTL.GL_LEDGER, OHDTL.GL_KEY, OHDTL.GL_OBJ)
Full Functions List
The following is a list of functions that can be called from within CDD macros. Along with the function name and number of parameters, short descriptions of the function and parameters are also provided.
areequ(value1,value2) Description: Checks if two floating point values are equal.
calcamount(period,field) Returns the sum of all values prior to and including the passed in period. It doesn't matter what number is placed after ACTUAL## or EN##; all ACTUAL/EN values are automatically passed into the routine.
period: Fiscal Period (1-14)
field: Table/Column value (i.e. "GLBA_BUDACT_MSTR.GLA_ACTUAL01", "GLBA_BUDACT_MSTR.GLA_EN01")
calcbudget(ledger,vers,field) An improved version of GetIfasBudgetEx; faster and easier to use.
ledger: Ledger Code
vers: Budget Version
field: Any of the up to 25 budget fields
calcbudgetonly(ledger,vers,field) A special case that does not consider the ACC values. Primarily useful as a diagnostic tool.
ledger: Ledger Code
vers: Budget Version
field: Any of the up to 25 budget fields
computeamount(index,amt1,amt2,amt3,amt4,amt5,amt6,amt7,amt8,amt9,amt10,amt11,amt12) Returns the sum of up to twelve passed in amounts, based on the index.
index: Index Number (1-12)
amt1 – amt12: Up to twelve amount values
computeamount14(index,amt1,amt2,amt3,amt4,amt5,amt6,amt7,amt8,amt9,amt10,amt11,amt12,amt13,amt14) Returns the sum of up to fourteen passed in amounts, based on the index.
index: Index Number (1-14)
amt1 – amt14: Up to fourteen amount values
dateadddays(refdt,days) Returns the resulting Date from adding days to refdt.
dateaddweeks(refdt,weeks) Returns the resulting Date from adding weeks to refdt.
dateaddmonths(refdt,months) Returns the resulting Date from adding months to refdt.
dateaddyears(refdt,years) Returns the resulting Date from adding years to refdt.
datecom(refdt) Returns the Month Number based on refdt.
dateday(refdt) Returns the Day Number based on refdt.
datedaysdiff(refdt1,refdt2) Returns the Number of Days between refdt1 and refdt2.
datecqt(refdt) Returns the Quarter Number based on refdt.
datecqtb(refdt) Returns the Calendar Quarter Begin Date based on refdt.
datecqte(refdt) Returns the Calendar Quarter End Date based on refdt.
datecyb(refdt) Returns the Calendar Year Begin Date based on refdt.
datecye(refdt) Returns the Calendar Year End Date based on refdt.
datecyr(refdt) Returns the Year Number based on refdt.
datefmo(refdt) Returns the Fiscal Month Number based on refdt.
datemob(refdt) Returns the Month Begin Date based on refdt.
datemoe(refdt) Returns the Month End Date based on refdt.
datemonth(refdt) Returns the Month Number based on refdt.
datemonthsdiff(refdt1,refdt2) Returns the Number of Months between refdt1 and refdt2.
datefqt(refdt) Returns the Fiscal Quarter Number based on refdt.
datefqtb(refdt) Returns the Fiscal Quarter Begin Date based on refdt.
datefqte(refdt) Returns the Fiscal Quarter End Date based on refdt.
datefy(refdt) Returns the Fiscal Year Number based on refdt.
datefyb(refdt) Returns the Fiscal Year Begin Date based on refdt.
datefye(refdt) Returns the Fiscal Year End Date based on refdt.
dateyear(refdt) Returns the Year Number based on refdt.
dateyearsdiff(refdt1,refdt2) Returns the Number of Years between refdt1 and refdt2.
getamount(index,amt1,amt2,amt3,amt4,amt5,amt6,amt7,amt8,amt9,amt10,amt11,amt12) Returns amount from one of the twelve passed in amounts, based on the index.
index: Index Number (1-12)
amt1 – amt12: Up to twelve amount values
getamount14(index,amt1,amt2,amt3,amt4,amt5,amt6,amt7,amt8,amt9,amt10,amt11,amt12,amt13,amt14) Returns amount from one of the fourteen passed in amounts, based on the index.
index: Index Number (1-14)
amt1 – amt14: Up to fourteen amount values
getbudgetdesc Calls ifasgetbudgetdesc; same parameters
getcalcstr(ledger,vers) Returns a string representing the fully expanded budget calculation formula.
ledger: Ledger Code
vers: Budget Version
getfqa(ledger,key,object)
GetFQA function takes 3 arguments, Ledger, Key, and Object, and returns Account Control on the basis of the setting defined in GLUPGN for Presentation, Output Format (reports) for the ledger.
Ledger: Ledger Code
Key: Key
Object: Object Code
getglgen("KEY","S","GL");
For the GL ledger, get the Org Key Short description. The ledger code portion is optional, and in most cases would be left off the statement.
Type: “KEY’ or “OBJ”
Desc: "S" for Short, "M" for medium or "L" for long
Ledger: Active Ledger
gethrcode(codeid,codeval,desc) Same as gethrencode, but with a NULL entity value.
gethrencode(entity,codeid,codeval,desc) Returns description of code values defined in Human Resources code tables.
entity: NULL or valid; determines if hr_hrcode is used vs. hr_hrencode
codeid: Code ID
codeval: Code Value
desc: S, L (Short & Long Descriptions)
getifasbudget(ledger,vers,bud01,bud02,bud03,bud04,bud05,bud06,bud07,bud08,bud09,bud10) This is the same as getifasbudgetex, except that the budget amounts are not passed in by string (i.e. with quotes), but rather, actual table.column names are used, or actual numeric values.
ledger: Ledger Code
vers: Budget Version
bud01 – 10: Up to ten budget amounts
getifasbudget25 Same as getifasbudget, but with support for up to 25 budget versions.
getifasbudgetex(ledger,vers,bud01,bud02,bud03,bud04,bud05,bud06,bud07,bud08,bud09,bud10) Returns the value of the IFAS calculated budget value as referenced by the version.
ledger: Ledger Code
vers: Budget Version
bud01 – 10: Up to ten budget amount string names (i.e. "GLBA_BUDACT_MSTR.GLB_BUDGET01")
getifasbudgetex25 Same as getifasbudgetsex, but with support for up to 25 budget versions.
getkeypart Calls ifasgetkeypart; same parameters
getobjpart Calls ifasgetobjpart; same parameters
getperiodamt(period,field) Returns a specific ACTUAL or EN value based on the passed in period. It doesn't matter what number is placed after ACTUAL## or EN##; all ACTUAL/EN values are automatically passed into the routine.
period: Fiscal Period (1-14)
field: Table/Column value (i.e. "GLBA_BUDACT_MSTR.GLA_ACTUAL01", "GLBA_BUDACT_MSTR.GLA_EN01")
getbvstr(ledger,vers) Returns a string representing the budget version requested.
ledger: Ledger Code
vers: Budget Version
ifascommoncode(cate,value,desc) Calls IfasCommonCodeGR with a null ledger value.
ifascommoncodegr(ledger,cate,value,desc) Returns Common Code information.
ledger: Ledger Code
cate: Code Category
value: Code Value
desc: Code Property C# (1-5): Associated Codes D# (1-5): Associated Descriptions V# (1-5): Associated Values S: Short Description M: Medium Description L: Long Description
ifasgetbudgetdesc(ledger,vers,desc) Returns the Budget Version description.
ledger: Ledger Code
vers: Budget Version
desc: S, M, L (defaults to "L")
ifasgetglgen(type,desc,ledger) Returns descriptions of various ledger components.
type: OBJ, KEY, FISCAL MONTH, FISCAL FLAG (defaults to "FISCAL FLAG")
desc: S, M, L (defaults to "L")
ledger: GL, JL
ifasgetglgenindexed(type,index,desc,ledger) Returns descriptions of various ledger components.
type: KEY, OBJ, BUDGET, PERIOD, MISC
index: Index Number (KEY 1-8; OBJ 1-8; BUDGET 1-10; PERIOD 1-14; MISC 1-8)
desc: S, M, L (no default)
ledger: GL, JL
ifasgetkeypart(ledger,index,part,desc) Returns Key Part descriptions.
ledger: Ledger Code
index: Index Number (1-40)
part: Key Part Value
desc: S, D, L ("D" is for "Director"; defaults to "L")
ifasgetobjpart(ledger,index,part,desc) Returns Object Part descriptions.
ledger: Ledger Code
index: Index Number (1-40)
part: Object Part Value
desc: S, D, L ("D" is for "Director"; defaults to "L")
isdbnull(item) Returns zero if report item is a null value.
item: Report item (Datarow, Field, Column)
netsightdrill(path,name,uniquekey) Opens Internet Explorer window with image
path: URL path
name: bt20 name
uniquekey: Unique Key
setbudgets(ledger,bud01,bud02,bud03,bud04,bud05,bud06,bud07,bud08,bud09,bud10) Sets the value in the ten budget fields according to the IFAS calculated budget rules.
ledger: Ledger Code
bud01 – 10: Up to ten budget amounts
setbudgets25 Same as setbudgets, but with support for up to 25 budget versions.