Values

Value is the type of balance.

The value expression is made up of one or more different components separated by a : (colon) in a string to precisely define the type of balance required. The components can be in any order and in any case (e.g. "ba:YR1" = "yr1:BA").

These new values should not be used in combination with the older legacy values (e.g. AY, PY).

Components of a value expression

TYPE
SUB
YEAR
PERIOD
ENTITY
FOREIGN EXCHANGE
ROUNDING
DISCRETE
SIGN
CALCULATION SYNTAX
FILTERED BALANCES
CUSTOM BALANCES
MAP/GROUP ADJUSTMENT NETINC AMOUNT
EXTENDED INFORMATION

TYPE

The following can be used to define the type of balances required:

Value	Type of balance
BU	Balance unadjusted (no adjustments)
BA	Balance adjusted (normal adjustments)
BR	Report Balance (normal + reclassifying)
BC	Balance Consolidated (normal+ reclassifying + eliminating)
BB	Balance budget (See note below)
BF	Balance forecast (See note below)
BTF	Consolidated Balance + Tax - Federal Adjustments
BTS	Consolidated Balance + Tax - State Adjustments
BTC	Consolidated Balance + Tax - City Adjustments
OBA	Other basis adjustments in any balance

Note

Functionality of the BB and BF values are further modifiable with a third letter to specify the adjustment type, similar to the standard balance. U, A, R, or C may be used to specify the adjustment type, meaning a value of BBU would link to the unadjusted balance budget, or BFA would link to the normal adjusted forecast balance.

The following can be used to define the type of adjustment required:

Value	Type of adjustment
AN	Adjustments normal
AE	Adjustments eliminating
AR	Adjustments reclassifying
AF	Adjustments federal tax
AS	Adjustments state tax
AC	Adjustments city tax
AB	Adjustments other basis
AU	Adjustments unrecorded - Factual
AL	Adjustments unrecorded - Projected
AG	Adjustments unrecorded - Judgmental

If no type is specified, the default behavior is "BC", the consolidated balance.

EXAMPLES

map("BA:YR2","111") returns the adjusted balance
map("YR2","111") returns the consolidated balance because the a Type value was not specified
map("AN:YR0","111") returns the total of normal adjusting journal entries to map number 111
map("BR:OBA","102") includes the other basis adjustments in the balance for account 102
map("AB","102") returns the other basis adjustment only for account 102

An ADJ modifier will return only the adjustments value for the balance type selected. This modifier can be added as:

EXAMPLES

act("AN:ADJB","114") returns the normal adjustments-only budget balance for account 114
act("AR:ADJF","121") returns the reclassifying adjustments-only forecast balance for account 121
act("AN:ADJCAA","114") returns the normal adjustments-only custom balance (AA) for account 114

SUB

There is support for the balance identifier "SUB". Use this to include the children of a leadsheet, map or group number in a total.

Examples

MAP("BC:SUB", "100") gives the balance of all mapping numbers beginning with "100". It evaluates only map numbers matching the mapping mask of the map number provided. This means it returns the sum of the balances for 100.1, 100.2 etc. and ignores the balances for map numbers 1000, 1001, etc.
GRP("3", BA:SUB","4")
Returns the final balance for all accounts assigned to grouping 3 group numbers that begin with the number 4. In other words, this function links to balances from group numbers 4, 4.10, 4.20, and so on.

YEAR

Value	Type of balance
YR0	Current year for all balance types including balance forecast (BF).
YR1	First prior year for all balance types except BF (see types above). First future year for balance forecast (BF).
YR2	Second prior year for all balance types except BF (see types above). Second future year for balance forecast (BF).
YR3	Third prior year for all balance types except BF (see types above). Third future year for balance forecast (BF).
YR4	Fourth prior year for all balance types except BF (see types above). Fourth future year for balance forecast (BF).

The default value is current year.

EXAMPLES

map("BA","111") returns the adjusted balance for the current year
map("BA:YR2","111") returns adjusted balances for the second prior year
map("BF:YR2","111") returns forecast balances for the second future year

PERIOD

Generic Period

Note: Select either one generic value or one reporting period value, not both.

Value	Type of balance
PER	The active period
PER0	Start of the year balance (opening balance in working trial balance).
PERx	Where x = the number of the period in the particular reporting sequence.

If no period is specified, the active period is used. If period 13 is specified, but there is no period 13 in the file, Working PapersTime assumes the 13 represents the final period in the file, regardless of the reporting period type (Monthly, Quarterly, etc).

EXAMPLES

map("YR0:PER0","111") returns the opening balance of the current year
map("BA:YR0:PER4","111") returns the adjusted balance for period 4 of the current year

Reporting Period (BUCKET)

Value	Type of balance
Y	Yearly
S	Semi annual
T	Thirdly
Q	Quarterly
I	Bi-monthly
M	Monthly
F	Four weekly (thirteen periods)
R	Random

For each of the bucket symbols, and optional numeric period can be specified (for example, "Q3", for quarter 3).

0 = the start of the year balance

X = Period number in the reporting sequence

If no bucket is specified, the active bucket is used.

Note: An optional '@' symbol can be placed in front of the reporting period value as it allows users to extract the reporting period from the FP database (for example, using the PERSTR() function), rather than typing it in manually. In this case, either B or I can be used to represent bi-monthly balances. This allows, for example, formulae of the form:

GRP("M","BR:YR0:@" + PERSTR(),entity(entid,"1.0000.000"))

Note: Entering an invalid period qualifier (that is, one that is not valid in the Engagement Properties) returns a balance of 0. For example, for a client file with a monthly period, entering M14, an invalid period number, returns a zero. Entering Q7 in a quarterly file returns a zero.

EXAMPLES

map("BA:YR0:Q","111") returns the active quarter period adjusted balance for the current year
map("BA:YR2:M4","111") returns the month 4 adjusted balance for prior year 2

PRIOR PERIOD

Value	Type of balance
PPx	PPx returns the balance for the period that is x periods before the current one.

EXAMPLE

map("PP8","111")

If the current period is 6 (that is month 6 of the current year), then PP8 returns the balance from period 10 of the previous year, which was 8 periods ago.

ENTITY

Value	Type of balance
PAR	Limits the balances to the parent entity only in a consolidated file.

The default is to show consolidated balances.

EXAMPLES

Consider the following consolidation structure.

PARENT	--SUB1	--DIV1
		--DIV2
	--SUB2
	--SUB3

map("BA:YR0:PAR","111") returns the current year adjusted balance for only parent, when the context entity selector is set to parent.
map("BA:YR0:PAR",Entity("SUB1","111")) returns the current year adjusted balance for only SUB1
map("BA:YR0",Entity("SUB1","111")) returns the current year adjusted balance for SUB1, DIV1, and DIV2

(For more information on linking balances with consolidated files, see linking consolidated files).

FOREIGN EXCHANGE

The foreign exchange balance represents the foreign exchange rate multiplied by the account balance.

Value	Type of balance
FX	Use this property to have foreign exchange applied to the balance. The default value is the balance without the exchange rate.
FXR	Use this property to retrieve an account's foreign exchange rate for a specific period, reporting bucket (quarterly, monthly, yearly, etc.), and balance type (normal, budget, forecast).

EXAMPLES

map("BA:YR0:FX","111") returns the current year adjusted balance with foreign exchange applied.
ACT("M11:FXR","311") returns the foreign exchange rate for account 311 in monthly period 11.
ACT("BB:Q2:FXR","101") returns the budget foreign exchange rate for account 101 in quarter two.

ROUNDING

The rounding value applied to a balance rounds the balance to the nearest whole number.

Value	Type of balance
RND	Use this property to have the final balance round to the nearest whole number. When rounding a group of accounts, RND sums all the accounts and then rounds the sum of the group.
RNDENT	Use this property to enforce rounding by entity. The balances related to the entity will be summed and then rounded.
RNDTOT	Use this property to enforce rounding on the total of a map() or grp() function. The balances assigned to the map or group will be totalled first and then rounded.
RNDACT	Use this property to round each account individually. When used within a group, each account is rounded and then the grouping is totalled.

EXAMPLE

map("BA:YR0:RND","111") returns the current year adjusted balance rounded to the whole number.

DISCRETE

Discrete monthly balances

There are two ways of denoting monthly balances in CaseView linkage functions, Continuous and Discrete. Continuous refers to the summation of all periods (up to and including the current active period) while Discrete only deals with the current active period.

If you are working on a monthly file and you're currently in period four, the current year continuous balance will include all amounts and transactions from periods one, two, three and four. The discrete balance will show only period four activity.

Value	Type of balance
DISC	Balance for the stated period only (does not include prior periods).

The default value is continuous.

EXAMPLE

map("BC:YR0:M4","111") returns a year to date balance up to the end of the fourth month for map number 111
map("BC:YR0:M4:DISC","111") returns the total of all transactions in the fourth month for map number 111

SIGN

Value	Type of balance
SIGN	Used with map and group number functions uses the sign assigned to the map or group rather than the sign applied to the account itself.

EXAMPLE

MAP("BR:SIGN,"102")

Returns the report balance posted to map no. 111 using the sign of the map no. rather than the sign of the accounts that are assigned to the map no. For example, if map no. 111 contains account 102 which is a debit account with a balance of 200 but map no. 111 has a credit sign then the balance returned would be -200.

Value	Type of balance
RAW	This property ignores the sign field of any account when calculating the balance. This overrides any current sign settings in File \| Document settings \| Client Options.

EXAMPLE

ACT("BR:M4:RAW","102")

Returns the report balance for period 4, of the monthly balances bucket, posted to account 102 and ignores the sign of the account when calculating the balance.

Note

For information on legacy value types from previous versions of CaseView, see Legacy Values. Mixing legacy values and new values in the CaseView linkage screen can cause conflicts and may result in incorrect results.

CALCULATION SYNTAX

Calculations are in the form of:

function(expression1, expression2, expression3, &)

Each expression can refer to a cell or group of cells.

EXAMPLES

For a file in which the function act("BA:YR0:Q2","102") returns a value of 60,000, having the following cell values and using the calculation syntax returns that same value in this way:

				Calculation	Result
Cell A3 A:YR0:Q2			Cell D3 102	act(a3,d3)	60,000
Cell A4 BA	Cell B4 YR0	Cell C4 Q2	Cell D4 102	act(a4+":"+b4+":"+c4,d4)	60,000
Cell A5 BA:YR0	Cell B5 Q2		Cell D5 102	act(a5+":"+b5,d4)	60,000
Cell A6 BA:	Cell B6 YR0:	Cell C6 Q2	Cell D6 102	act(a6+b6+c6,d4)	60,000

FILTERED BALANCES

Balance ID "filt" allows an optional dBase filter to be added to the balance formula.

Syntax: "FILT=dBase Filter Expression".

EXAMPLE

To return the YTD "report" balance of mapping 111, for month 12, filtered on accounts where GROUP4=401, use the following the calculation:

Map("BR:M12:FILT=group4=mask('4','401')" , "111" )

CUSTOM BALANCES

The custom balance value consists of 3 letter codes always starting with C and ending with the two letter custom balance identifier. For custom balance AA, the value would be CAA.

Custom balances identifiers are created in Tools | Options in Working Papers.

Specifying the year for custom balances is the same as other balance types:

Value	Type of balance
YR0	Current year for all balance types including balance forecast (BF).
YR1	First prior year for all balance types except BF(see types above). First future year for balance forecast (BF).
YR2	Second prior year for all balance types except BF (see types above). Second future year for balance forecast (BF).
YR3	Third prior year for all balance types except BF (see types above). Third future year for balance forecast (BF).
YR4	Fourth prior year for all balance types except BF (see types above). Fourth future year for balance forecast (BF).

EXAMPLE

Following is an example of using the custom balance value where AA is the custom balance identifier:

ACT("CAA:YRO","101") will get the current year custom balance with identifier AA for account 101.

CaseView supports adjustments made directly to custom balances, these commands work in relation to these adjustments:

Value	Type of balance
CAAU	Custom Balance AA unadjusted
CAAA	Custom Balance AA normal adjusted
CAAR	Custom Balance AA normal adjusted + reclassifying
CAAC	Custom Balance AA consolidated

Note: If the additional character which specifies the balance type is not added to the equation (for example, CAA is used alone), the balance type will default to Consolidated.

MAP/GROUP ADJUSTMENT NETINC AMOUNT

When using the ACT function with the NETINC account, include any map or group adjustments made to net income based map numbers or group numbers using the following syntax:

Value	Type of balance
GM	Include the total of all map adjustments made to income statement map numbers.
Gx	Include the total of all group adjustments made to income statement group number (where x is the grouping from 1 to 9, or 0 for group 10.

EXAMPLE

Following is an example of using the map/group adjustment net income amount syntax:

ACT("BR:G1", "NETINC") will get the report balance of the net income account, including any adjustments made directly to any income statement group numbers.

Note

The map/group adjustment net income amount syntax should only be used with the ACT function, and should only be used when retrieving the value of the NETINC account.
In order for this to work properly for group numbers, each group number must be denoted as a balance sheet or income statement group within CaseWare Working Papers Engagement - Groupings screen using the Type column.

EXTENDED INFORMATION

The memo field EXINFO in the several databases can be used for storing XML name/value pairs. CaseView linkage calculations are able to extract the XML values.

Example

The AM database record for account 102 has the following XML value collection text stored in its EXINFO field:

<value name="DescB">Additions</value>

<value name="DescC">Depreciation</value>

<value name="DescD">Final</value>

</values>

ACT( "XML->BALD", "102" ) will result in 920.28.

The MAP() or GRP() functions can also be used to access any of its XML balances. Assuming account 102 above is assigned to map number M1000 and group M:

MAP( "XML->BALA", "M1000" ) will result in 700.50

GRP("M", "XML->BALB", "M1000" ) will result in 300.00

For non-numeric values or for XML name/value pairs stored in other database memo fields, the FIELD() function can be used. To do so, add ”r;->XML->ValueName” to the name of the memo field.

FIELD( "AM", "EXINFO->XML->DESCB", "102" ) will retrieve the alphanumeric value Additions

If no database is specified, as in most of the examples above, the equation defaults to looking at the EXINFO field in the AM database. However, this functionality can draw from other databases as well. Assuming the correct XML values have been created in the EXINFO field of the SH database, it is possible to extract a value, ”r;Reference2”, from a document, ”r;1. 2”, of the Document Manager using

FIELD( "SH", "EXINFO->XML->REFERENCE2", "1.2" )