Special Functions For Reports

About Special Functions

Sometimes a value can't be retrieved directly via sql from the report. In this case a special function can be used to compute the desired value. Here is list of the special functions that can be called from the reports. These functions compute the data on the server side and return them into the returnvalue of the report. The functions usually stay in: ICT\Petra\Server\lib\MReporting\MModule\Function.cs. They are called from the report xml file like this:

<calculation id="DummyFunction" returns="PartnerNameAndKey" returnsFormat="text">
  <query>
    <queryDetail>
      <value text="NO-SQL"/>
      <value function="assign(PartnerNameAndKey, {PartnerKey})"/>
      <value function="assign(Amount, add({Amount1},{Amount2}))"/>
    </queryDetail>
  </query>
</calculation>

The assign function is of special use because it assigns a value of another function to a parameter. So it is used in almost every call.

General Functions

These function are usefull for all kinds of reports:

• eq, ne: Test if two values are equal or not equal
• lt, le, gt, ge: Test if two values are less than, less equal, greater than, greater equal
• sub: Parmeter1 - Parameter2
• add: Parameter1 + Parameter2
• addItems: Adds a list of parameters. The first parameter defines how many values need to be added
• mul: Parameter1 * Parameter2
• div: Parameter1 / Parameter2
• mod: Parameter1 % Parameter2
• floor: returns floor integer
• round: returns rounded integer
• not: invert boolean value
• iif: condition, value_if_true, value_if_false
• or, and: Logical operator
• adddays: first parameter is a date, second parameter is a number of days, result is the new date
• length: Returns the length of the string
• ContainsCSV: Checks if the csv list of Parameter1 contains Parameter2
• substring, substr: Returns a substring Parameter1 is the original string, Parameter2 the startindex and Parameter3 the length of the substring
• substringright, substrright: Returns the right part of a string. Parameter1 is the original string, Parameter2 the lenght of the subsring
• substringwithoutright, substrwithoutright: Returns the left part of a string. Parameter1 is the original string, Parameter2 the length of the substring
• concatenate: Concatenate two strings
• concatenateww: Concatenate two strings with a number of whitespaces. Parameter3 defines how many whitespaces should be added between the two strings
• concatenatewithcomma: Concatenate two strings with a comma and one space as separator.
• format: Returns Parameter1 as a formatted string depending on Parameter2. This is used for currency formatting
• formattime: Returns a string in a time format like hh:mm:ss Whereas the ":" can be defined with Parameter1
• assign: Assign Parameter2 to Parameter1. Parameter1 must be a variable
• exists: Checks if a parameter exitst in the report
• isnull: Checks if a parateter is null
• template: Calculates a calculation template
• columnexist: Checks if a column with the given name exists
• conditionrow: Hide this row from the results, if Parameter1 is true
• column: Returns the name of the column. Parameter1 defines the column index
• HasColumns: Checks if the current row has at least one column that is not null or not 0
• HasChildColumns: Checks if the children of the master row have at least one column that is not null or not 0
• HasChildRows: Checks if the master has children
• fatherColumn: Returns the value of the parent row of the given column
• childColumn: Returns the value of the child row of the given column
• SecondLevelColumn: Returns the value of the given column from the second level
• FirstLevelColumn: Returns the value of the given column from the first level
• GetShortCaption: Returns the first line of the caption of the given column
• GetCaption: Returns the complete caption of the given column
• getSumLowerReport: Gets the sum of the lower report of the given column
• getSumLower2Report: Like getSumLowerReport but goes one level deeper
• getSumLowerReportCredit: Like getSumLowerReport but use the debit_credit_indicatior
• log: can be called with 1 or 2 parameters. prints the values of the parameters to the log file. if it is just one parameter, and this is a variable in the parameterlist, then the details of that variable are printed.

Conference

Contains the functions that are related mainly to the Partner Module

• GetConferenceRoom: Gets the Room for a given partner during a conference

• Functions for Accomodation Report:
  • ClearAccomTable: Initializes the calculation
  • CalculatePartnerAccom Calculates: for one partner at one conference the accomodation details
  • FinishAccomTable: Finishes the calculation and copies the results to the reporting results

• Functions for the AgeSummary Report:
  • ClearAgeTable: Initializes the calculation
  • CalculateSingleAge: Adds the details of one partner to the result table
  • FinishAgeTable: Finishes the calculation and copies the results to the reporting results

• Functions for the Addendance Summary Report:
  • ClearAttendanceTable: Iinitializes the calculation
  • CalculateSingleAttendance: Calculate the data of one partner attending the conference
  • FinishAttendanceTable: Finishes the calculation and copies the results to the reporting results

• Functions for the Conference Field Report:
  • InitFieldCostsCalculation: Initializes the calculation
  • ClearFieldCostsCalculation: Deletes the calculated results
  • PrintFieldFinancialCosts: Generate the financial lines in the report for each field
  • PrintFinancialSignOffLines: Generates the financial sign of lines for each field in the report
  • PrintAttendanceSignOffLines: Generates the attendee sign off lines for each field in the report
  • PrintEmptyLineInFieldReport: Prints an empty line to the report
  • CalculateOneAttendeeFieldCost: Calculate the financial details of one attendee
  • GetExtraCosts: Generates the extra cost lines for the attendees in the report
  • HasReportSendingField: Checks if there is any entry in the report results with the given field name
  • HasAttendeeReceivingField: Evaluates if a partner has a receiving field assigned and it hides the attendee if appropriate
  • HasFieldReportDiscount: Checks if a special discount type is used in the field report.

• Functions for Conference Role Report:
  • ClearConferenceRoleTable: Initializes the calculation
  • CalculateSingleConferenceRole: Adds one partner to the results
  • FinishConferenceRoleTable: Finishes the calculation and copies the results to the reporting results

• Functions for the Nationality Summary Report:
  • ClearNationalityTable: Initializes the calculation
  • CalculateSingleNationality: Adds one partner to the results
  • FinishNationalityTable: Finishes the calculation and copies the results to the reporting results

Finance

Contains the functions that are related mainly to the Finance Module

• getAccountDetailAmount:
• getTransactionAmount:
• getAssetsMinusLiabs:
• getNetBalance:
• getGLMSequences:
• getActual: Retrieves the actuals value of a given period
• getActoualPeriods:
• getActualPeriodsIE:
• getActualEndOfLastYear:
• getBudgetPeriods:
• getLedgerName:
• UnitKeyToForeignCostCentre:
• getCurrency:
• getBalanceSheetType:
• getIncExpStmtType:
• getAccountingHierarchy:
• GetLedgerPerColumn:
• getCurrencyPerColumn:
• GetYTDPerColumn:
• getAllAccountDescendants:
• ExtraPaymentNumberFromTransactionNarrative:
• GetMonthName: Get the full month name
• getActualPeriodStartDate

Input: Actual Ledger Number

Oputput: Start date value of table a_accounting_periods defined by the accounting period in a_ledger.

• getActualPeriodEndDate

Input: Actual Ledger Number

Oputput: End date value of table a_accounting_periods defined by the accounting period in a_ledger.

Functions concerning Ledger Dates

• getStartPeriod: Returns status CLOSED, FWD PERIOD or CURRENT for the given period in the given year of the ledger
• periodInLastYear:
• periodInThisYearOld:
• getQuarterOrPeriod:
• getPeriodStartDate:
• getPeriodEndDate: Returns the date of the given period in the selected year of the ledger
• getYTDPeriod:
• getPreviousYearCaption: Returns the year number of the currently selected year
• GetCurrentYearCaption: Returns the year number of the year previous to the currently selected year
• GetNextYearCaption: Retruns the year number of the year after the currently selected year
• getMonthName: Returns the name of the month of the period in the given ledger
• getMonthDiff:

Financial Development

Contains the functions that are related mainly to the Financial Development Module

• getGiftStatistics: Get the number of gifts, min. Amount, max. Amount, average amount per gift, average amount per period, total amount and percentage
• IsLapsedDonor: Checks if the donor is a lapsed donor according to the parameters supplied
• SelectLastGift: Select the last gift and motivation details of the gifts that were given within the given time period
• IsTopDonor: Checks if the amount is within the given range
• MakeTopDonor: Calculations for the Top Donor Report

Partner

Contains the functions that are related mainly to the Partner Module

• GetPartnerLabelValues: Add a new parameter to the result. This is used for the local partner data report.
• GetPartnerBestAddress: Find the best p_partner_location of a partner and export the parameters.
• AddressMeetsPostCodeCriteriaOrEmpty: will return true if the best address is in the given postal region, postcode range and county
• GetPartnerShortname: Get the short name of a partner
• GetFieldOfPartner: Returns the field name of partner. The partner needs to have an entry in PPartner_Filed_Of_Service datatable
• CheckAccountNumber: Checks the validity of a bank account number, according to the country specific check.
• Function which are only used for the publication statistical report:
  • GetCountyPublicationStatistic: Calculates one row of the publication statistical report.
  • CalculatePublicationStatisticPercentage: Fills the values of one line in the publication statistical report.
  • GetNumberOfAllPublications: Calculates the line "Counts" in the publication statistical report.
• DetermineAddressDateStatus: Determine the status of an address given the "date effective from" and the "date good until" dates.
• GetSubscriptions: Get all subscriptions with a defined status of a partner.
• GetFirstEntyFromSQLStatement: Returns the fisrst row of a data table that is the result of a sql statement from the xml file.
• GetPartnerTypes: Returns a comma separated list of all partner types of a partner
• GetOccupation: Get the occupation code and occupation description from p_occupation table
• ConvertIntToTime: Converts a integer value which into a time string like this hh:mm:ss. One example of the integer time is used in p_partner_contact.s_contact_time_i

Personnel

• GetSiteName: Returns the site name of the current site key, which is stored in s_system_parameter
• GetCurrentCommitmentPeriod: Find the commitment period of a given partner at a given time.
• GetType: Returns the p_partner_type.p_type_code_c of a given partner if the type code matches
• GenerateUnitHierarchy: Generate the output of the unit hierarchy report.
• GetMissingInfo: Get a list of the missing information for a short term application
• GetPersonLanguages: Get all the languages of a person
• GetPassport: Get the passport details from a partner
• GetDriverStatus: Get the driver status of a partner
• GetChurch: Add the address details of the supporting church of a partner
• GetLeadershipRating: Get the leadership rating of a short term application partner
• GetDietary: Get the dietary info of a short term application
• GetMedicalInfo: Get the medical info of a short term application
• GetOtherNeeds: Get other needs info of a short term application
• GetPartyType: Get the party type and party type description of a party type
• GetPartnerContact: Get the partner key and partner name of the partner who is used as partner contact
• CalculateAge: Calculate the current age in years from a given date
• CalculateAgeAtDate: Calculate the age in years at a given date
• GetArrivalPoint: Get the full description of an arrival point