Additional Functions

The following are various miscellaneous functions available within Crystal Reports.

Table C-16: Miscellaneous functions

Function	Description	Arguments
DateTo2000 (Date, Number)	Returns a date field with a four-digit year.	Date accepts only valid date fields with either two-digit or four-digit years. Number is a number from 0 to 99 corresponding to the desired windowing year.
DTSTo2000 (DateString, Number)	Returns a date field with a four-digit year	Date String accepts only valid date fields with either two-digit or four-digit years. Number is a number from 0 to 99 corresponding to the desired windowing year.
DTSToDate (DateTimeString)	Evaluates the string specified and returns only the date.	DateTimeString is a string including a Date and a Time value.
DTSToSeconds (DateTimeString)	Evaluates the string specified and converts the Time value to the number of seconds from 00:00:00 (12:00 midnight) to the specified time.	DateTimeString is a string including a Date and a Time value.
DTSToTimeString (DateTimeString)	Evaluates the string specified and returns only the Time value in military format (00:00:00).	DateTimeString is a string including a Date and a Time value.
ExchGetId (address)	Determines whether the address is in X500 or X400 format. Once this has been solved, the function will then determine the ID. If the field is in X500 format, the function will extract the last instance of the "CN=" code (not case sensitive). If the field is in the X400 format, it will extract the SMTP or MS IDs.	address is the address of the sender/recipient (String data type).
ExchGetOrganization (address)	Determines whether the address is in X500 or X400 format. Once this has been determined, the function will then decide on the Organization Name. If the field is in X500 format, the function will extract the last instance of the "/O=" code (not case sensitive). If the field is in the X400 format, it will extract the instance of the "P=" code (not case sensitive).	address is the Address of the sender/recipient (String data type).
ExchGetSite (address)	Determines whether the address is in X500 or X400 format. Once this has been solved, the function will then determine the Site Name. If the field is in X500 format, the function will extract the last instance of the "/OU=" code (not case sensitive). If the field is in the X400 format, it will extract the instance of the "O=" code (not case sensitive).	address is the address of the sender/recipient (String data type).
ExtractString (origin, startString, endString)	Returns the first occurrence (in the origin string) of a string that starts with startString and ends with endString.If endString is not found, the string starting with startString until the end of the string is returned.	origin: String startString: String endString: String
EventNumber (eventNum)	Returns the appropriate text description of the event logged by Exchange that matches eventNum. If there is no appropriate text description for an eventNum, the message "Unknown Event" will be returned with eventNum in brackets after the message.	eventNum is a number value that represents an event logged by Exchange.
ExchGetPath (Path)	Returns the container information in an address field. If the address type is X500, the function will return all of the information from the first instance of the "CN=" code until the last instance of the "CN=" code. If there is only one instance of the "CN=" code, the function will return NULL. If the address type is X400, the function will return all "OU*=" codes (residing between the "P=" and "O=" codes). If the address starts with "DDA:", the function will return all information after the "DDA:" code. If the field is blank, "UNKNOWN ADDRESS" will be returned.	Path is the address of the sender/receiver (String data type).
ByteToText (numberOfBytes)	Returns a string description of the number passed in. If the argument is less than 1024, the result will be in bytes. If the argument is between 1024 to 1048576, the result will be in kilobytes. Otherwise, the result will be in megabytes.	numberOfBytes is a number that represents an amount of storage in bytes.
FRCurrentRatio (CurrentAssets, CurrentLiabilities)	Returns the ratio between CurrentAssets and CurrentLiabilities (for example, CurrentRatio = CurrentAssets / CurrentLiabilities).	CurrentAssets is a Number or formula. In general, CurrentAssets consists of cash, accounts receivable, inventories, prepaid expenses, and short-term marketable investment in a company's balance sheet. CurrentLiabilities is a Number or formula. In general, CurrentLiabilities consists of bank advances, accounts payable, dividends/income taxes payable, and a portion of long-term loans due within one year in a company's balance sheet.
FRQuickRatio (CurrentAssets, Inventories, CurrentLiabilities)	Returns the ratio between CurrentAssets (less Inventories) and CurrentLiabilities (for example, QuickRatio = (CurrentAssets - Inventories)/ CurrentLiabilities).	CurrentAssets is a Number or formula. In general, CurrentAssets consists of cash, accounts receivable, inventories, prepaid expenses, and short-term marketable investments in a company's balance sheet. Inventories is a Number or formula. In general, Inventories consists of raw materials, work in progress, and finished goods in a company's balance sheet. CurrentLiabilities is a Number or formula. In general, CurrentLiabilities consists of bank advances, accounts payable, dividends/income taxes payable, and a portion of long-term loans due within one year in a company's balance sheet.
FRDebtEquityRatio (TotalLiabilities, TotalEquity)	Returns the ratio between TotalLiabilities and TotalEquity (for example, DebtEquityRatio = TotalLiabilities / TotalEquity).	TotalLiabilities is a Number or formula. In general, TotalLiabilities consists of all the current and long-term liabilities in a company's balance sheet. However, some analysts would only include short- and long-term banks debts in calculating this ratio. TotalEquity is a Number or formula. In general, TotalEquity consists of common stocks, preferred stocks, capital surplus, and retained earnings in a company's balance sheet.
FREquityVsTotalAssets (TotalEquity, TotalAssets)	Returns the ratio between TotalEquity and TotalAssets (for example, EquityVsTotalAssets = TotalEquity / TotalAssets).	TotalEquity is a Number or formula. In general, TotalEquity consists of common stocks, preferred stocks, capital surplus, and retained earnings in a company's balance sheet. TotalAssets is a Number or formula. In general, TotalAssets consists of total current assets, net fixed assets, and all other assets in a company's balance sheet.
FRNetProfitMargin (NetProfit, Sales)	Returns the ratio between NetProfit and Sales (for example, NetProfitMargin = NetProfit / Sales).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. Sales is a Number or formula. Sales is the total sales during the period that appears in a company's income statement.
FRGrossProfitMargin (GrossProfit, Sales)	Returns the ratio between GrossProfit and Sales (for example, GrossProfitMargin = GrossProfit / Sales).	GrossProfit is a Number or formula. In general, GrossProfit equals sales minus cost of goods sold in a company's income statement. Sales is a Number or formula. Sales is the total sales during the period that appears in a company's income statement.
FROperatingProfitMargin (OperatingProfit, Sales)	Returns the ratio between OperatingProfit and Sales (for example, OperatingProfitMargin = OperatingProfit / Sales).	OperatingProfit is a Number or formula. In general, OperatingProfit equals gross profit less all selling, administrative, and general expenses in a company's income statement. Sales is a Number or formula. Sales is the total sales during the period that appears in a company's income statement.
FRInterestCoverage (CashFlow, InterestExpenses)	Returns the ratios between CashFlow and InterestExpenses (for example, InterestCoverage = CashFlow / InterestExpenses).	CashFlow is a Number or formula. In general, CashFlow equals the net profit of the company plus all the expense items not involving cash (such as depreciation, capital losses, and so on) minus all the profit not involving cash (such as capital gains, and so on). This information can be found in a company's income statement and statement of change in financial position. InterestExpenses is a Number or formula. InterestExpenses is the total interest expenses that can be found in a company's income statement.
FRCashFlowVsTotal-Debt (CashFlow, TotalDebt)	Returns the ratio between Cash-Flow and TotalDebt (for example, CashFlowVsTotalDebt = CashFlow / TotalDebt).	CashFlow is a Number or formula. In general, Cash-Flow equals the net profit of the company plus all the expense items not involving cash (such as depreciation, capital losses, and so on) and minus all the profit not involving cash (such as capital gains, and so on). This information can be found in a company's income statement and statement of change in financial position. TotalDebt is a Number or formula. In general, TotalDebt is the total short-term and long-term bank borrowings. Some analysts may even include all the short-and long-term liabilities as part of total debt. All this information can be found in a company's income statement.
FRReturnOnEquity (NetProfit, TotalEquity)	Returns the ratio between NetProfit and TotalEquity (for example, ReturnOnEquity = NetProfit / TotalEquity).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. TotalEquity is a Number or formula. In general, TotalEquity consist of common stocks, preferred stocks, capital surplus, and retained earnings in a company's balance sheet.
FRReturnOnNetFixed Assets (NetProfit, NetFixedAssets)	Returns the ratio between NetProfit and NetFixedAssets (for example, ReturnOnNetFixedAssets = NetProfit / NetFixedAssets).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. NetFixedAssets is a Number or formula. NetFixedAssets equals total fixed assets minus accumulated depreciation in a company's balance sheet.
FRReturnOnTotal-Assets (NetProfit, TotalAssets)	Returns the ratio between NetProfit and TotalAssets (for example, ReturnOnTotalAssets = NetProfit / TotalAssets).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. TotalAssets is a Number or formula. In general, TotalAssets consist of total current assets, net fixed assets, and all other assets in a company's balance sheet.
FRReturnOnInvested-Capital (NetProfit, TotalBankDebt, TotalEquity)	Returns the ratio between NetProfit and invested capital, which is TotalBankDebt plus TotalEquity (for example, ReturnOnInvested Capital = NetProfit /(TotalBankDebt + TotalEquity)).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. TotalBankDebt is a Number or formula. TotalBankDebt includes all the short-and long-term bank debts of a company. They can all be found in a company's balance sheet. TotalEquity is a Number or formula. In general, TotalEquity consists of common stocks, preferred stocks, capital surplus, and retained earnings in a company's balance sheet.
FRReturnOnCommon Equity (NetProfit, PreferredDividend, CommonEquity)	Returns the ratio between the net profit distributable to common shareholders (NetProfit -PreferredDividend) and CommonEquity (for example, ReturnOnCommonEquity = (NetProfit - PreferredDividend)/ CommonEquity).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. PreferredDividend is a Number or formula. PreferredDividend is the total dividend paid out to the preferred shareholders during the accounting period being studied. This information can be found in a company's income statement and statement of change in financial position. It can also be derived from a company's balance sheet. CommonEquity is a Number or formula. In general, CommonEquity is the sum of all common shares, contributed surplus, and retained earnings. All of these can be found in a company's balance sheet.
FREarningsPer-CommonShare (NetProfit, PreferredDividend, NumOfCommon-Share)	Returns the ratio between net profit distributable to common shareholders (NetProfit -PreferredDividend) and NumOfCommonShare (for example, EarningsPerCommonShare = (NetProfit -PreferredDividend)/ NumOfCommonShare).	NetProfit is a Number or formula. NetProfit is the amount a company earns during the year from the sale of its products or services minus all the expense items. It is the bottom-line figure in a company's income statement. Some analysts may want to use the net profit before extraordinary items and/or income taxes for calculating this ratio. PreferredDividend is a Number or formula. PreferredDividend is the total dividend paid out to the preferred shareholders during the accounting period being studied. This information can be found in a company's income statement and statement of change in financial position. It can also be derived from a company's balance sheet. NumOfCommonShare is a Number or formula. In general, NumOfCommonShare is the number of common shares issued and outstanding. This information can be found in a company's balance sheet.
FRAccRecTurnover (AccountReceivable, Sales, NumOfDays)	Returns the average turnover of AccountReceivable in days (for example, AccRecTurnover = (AccountReceivable / Sales)* 360).	AccountReceivable is a Number or formula. AccountReceivable is the total accounts receivable in a company's balance sheet. Sales is a Number or formula. Sales is the total sales during the period that appears in a company's income statement. NumOfDays is a Number or formula. In general, it is the number of days in a year. Some analysts use 360, and some use 365. The default is 360 for this function.
FRInventoryTurnover (Inventory, Sales, NumOfDays)	Returns the average turnover of inventory in days (for example, InventoryTurnover = (Inventory/Sales) * 360).	Inventory is a Number or formula. Inventory is the total inventories in a company's balance sheet. In general, it includes all raw material, work in progress, and finished goods. Sales is a Number or formula. Sales is the total sales during the period that appears in a company's income statement. NumOfDays is a Number or formula. In general, NumOfDays is the number of days in a year. Some analysts use 360, and some use 365. The default is 360 for this function.
FRPriceEarningsRatio (MarketPrice, EarningsPerShare)	Returns the ratio between MarketPrice and EarningsPerShare (for example, PriceEarningsRatio = MarketPrice / EarningsPerShare (12 months)).	MarketPrice is a Number or formula. MarketPrice is the current market price of a company's common share. This information is publicly available if the company's shares are traded in a stock exchange. EarningsPerShare is a Number or formula. EarningsPerShare is the amount of earnings that is attributable to each common share. This information is available in a company's financial reports.
FRDividendYield (Dividend, MarketPrice)	Returns the ratio between Dividend and MarketPrice (for example, DividendYield = Dividend / MarketPrice).	Dividend is a Number or formula. Dividend is the indicated annual dividend per common share. MarketPrice is a Number or formula. MarketPrice is the current market price of a company's common share. This information is publicly available if a company's shares are traded in a stock exchange.
Now	Prints the current time on a report. The time is taken from your computer's internal clock.	(no arguments)
Picture (string, picture)	Prints a string or values in a text string in a predetermined format.	string is a text string to be formatted according to the picture format. picture is a text string representing the way you want the characters in the string to be printed.
LooksLike (string, mask)	Enables you to locate field values using a standard DOS wildcard (? = wildcard for single character, * = wildcard for any number of characters). It does this by comparing a string to a mask that contains one or more wildcards. The function returns True if the string matches the mask and False if the string does not match the mask.	string is the text string or field containing text string values that are being compared to the mask. mask is a text string that provides a mask for comparing the value in the string argument.
Soundex (string)	Evaluates a text string and returns a four-character value that symbolizes the way string sounds.	string is one of two or more strings that sound alike.
DateTimeTo2000 (dateTime, number)	Two-digit years (xx): If the Year value is greater than the windowing number, 19 is appended before the two-digits (19xx). If the Year value is less than or equal to the windowing number, 20 is appended (20xx). Four-digit years (19xx): If the last two digits in the Year value are greater than the windowing number, the Year is preserved as found in the date field (19xx). If the two digits in the Year value are less than or equal to the windowing number, the first two digits are changed to 20 (20xx). If the first two digits in the year field are 20, the Year is preserved as found in the date field (20xx).	dateTime accepts only valid date-time fields, with either two-digit or four-digit years. number is a number from 0 to 99 corresponding with the desired windowing year.
DTSToDateTime (DateTimeString)	Evaluates the string specified and returns a DateTime data type.	DateTimeString is a string including a Date and a Time value.
DTSToTimeField (DateTimeString)	Evaluates the string specified and returns a Time data type.	DateTimeString is a string including a Date and a Time value.
DateTimeToDate (dateTime)	Evaluates the DateTime value specified and converts it to a Date value.	dateTime is a DateTime value.
DateTimeToTime (dateTime)	Evaluates the DateTime value specified and converts it to a Time value.	dateTime is a DateTime value.
DateTimeToSeconds (dateTime)	Evaluates the DateTime value and returns the number of seconds that have passed between 00:00:00 (12:00 midnight) and the specified time.	dateTime is a DateTime value.