When authoring a Report Writer Template, you’ll need to insert Form Fields in the template document to refer to data items in the Report Writer (numbers, dates, and character strings). You might then choose to edit the Form Field's "field spec", for example, to calculate a derived value (negate the value, add two values together, take the maximum of two values, etc.).
This article documents the syntax of the field spec. The field spec, which specifies the table and other parameters, is entered into the Form Field's Help Text area. See Templates: Inserting Form Fields for instructions on how to insert fields and edit them manually.
The field spec can either be a single data item or an expression that derives a value from one or more data items.
Expressions
An expression consists of data items and operators. Data items in an expression are enclosed within double quotes ("). Operators include +, -, *, /, #min, and #max -- see Expression Operators for more.
| Examples: | Meaning: |
| "ProVal_ValSet_Data, Val Set CY, Annuities_Inforce" - "ProVal_ValSet_Data, Val Set PY, Annuities_Inforce" | Difference between the current and prior year's annuities inforce |
| -"ProVal_USMinPPA, Val Set CY, Tgt_NC" | Negation of Target normal cost from the Minimum Contrib exhibit |
| #IF "ProVal_ValSet_Data,Current Year,Market_Assets,C=Value">0 #THEN 'funded' #ELSE 'unfunded' #ENDIF | Choose between the strings 'funded' and 'unfunded' based on the asset value. Note that strings appear in single quotes; double quotes are reserved for data items. Also, the Form Field's type in MS Word should be set to Regular text. |
The syntax for individual data items is discussed below.
Data item syntax
The field spec for a data item differs depending on which Access Table the data item is coming from. The idea in each case is to specify enough information to select one value from the table. For instance, the field spec 'ProVal_ValSet_Data, Val Set CY, Annuities_Inforce' means that the data comes from the ProVal_ValSet_Data table, using the runid associated with the DataSet Label "Val Set CY", and the table row where Name is 'Annuities_Inforce'. In this case, the default table column is 'Value'.
Each field spec consists of several parameters separated by commas. In every case, the table name is the first parameter. The order of the parameters is important. Optional parameters are shown in [brackets]. Upper and lower case letters, and spaces before and after the commas, are not significant.
As a helpful tip, while in the template document, it might be necessary to press the Alt + F9 keys to switch between Microsoft Word form field codes and their results.
ProVal Data: Valuation Sets
Syntax:
Table, DataSetLabel, Variable [, C=Column]
- Table is the name of the table in Microsoft Access. The alias ‘VS’ can be used instead of the table name ‘ProVal_ValSet_Data’.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Variable is the string to look up in the ‘Name’ column of the specified Table
- Column is the column name in the specified Table. If omitted, the default is the ‘Value’ column. Other choices are:
- ActiveTot
- ActiveRet
- ActiveTerm
- ActiveDth
- ActiveDis
- ActiveEmpContrib
- InactiveTot
- InactRet
- InactVested
- InactSurvivor
- InactDis
| Examples: | Meaning: |
| ProVal_ValSet_Data, Val Set CY, Annuities_Inforce | Annuities Inforce from "Value" column from run with the label "Val Set CY" |
| ProVal_ValSet_Data, Val Set PY, Annuities_Inforce | Same as above but for run with label "Val Set PY" |
| ProVal_ValSet_Data, Val Set PY, Annuities_Inforce, C=ActiveTot | Same as above but from "ActiveTot" column |
| VS, Val Set PY, Annuities_Inforce, C=ActiveTot | Same as above, using abbreviation for table name |
| ProVal_USMinPPA, Val Set CY, Tgt_NC | Target normal cost from the Minimum Contrib exhibit |
ProVal Data: Headcount & Benefits
Syntax:
Table, DataSetLabel, Basis, R=RowNum, C=ColNum
- Table is a pseudo-name of the table, 'ProVal_Headcount_Benefits'. The alias 'HB' may also be used. Note that this is not actually an Access table.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- RowNum is the origin-1 row number.
- ColNum is the origin-1 column number.
Example:
The field spec 'ProVal_Headcount_Benefits, Current Year, UC_AL, R=2, C=3' would select the value 32,724,785 from the Headcount & Benefits data shown below:
ProVal Data: Active Age/Service
Syntax:
Table, DataSetLabel, Age Breakpoints, Service Breakpoints, Show, R=RowNum, C=ColNum
- Table is a pseudo-name of the table, 'ProVal_Active_Age_Service'. Note that this is not actually an Access table.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Age Breakpoints *
- Service Breakpoints *
- Show: one or more of C, AS, TS, and CB to show Count, Average Salary, Total Salary, and Cash Balance respectively.
- RowNum is the origin-1 row number.
- ColNum is the origin-1 column number.
* Do not use commas (e.g., “5,10,20,30”) in the Age and Service Breakpoints specifications; use spaces instead (e.g., "5 10 20 30").
Example:
The field spec 'ProVal_Active_Age_Service, Prior Year 1, From 20 to 65 step 5, 5 10 20 30, C AS TS, R=6, C=2' would select the value 166,704 from the Active Age/Service data shown below:
ProVal Data: Inactive Counts / Benefits
Syntax:
Table, DataSetLabel, Age Breakpoints, R=RowNum, C=ColNum
- Table is a pseudo-name of the table, 'ProVal_Inactive_Counts_Benefits'. Note that this is not actually an Access table.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Age Breakpoints *
- RowNum is the origin-1 row number.
- ColNum is the origin-1 column number.
* Do not use commas (e.g., “5,10,20,30”) in the Age Breakpoints specification; use spaces instead (e.g., "5 10 20 30").
Example:
The field spec 'ProVal_Inactive_Counts_Benefits, Current Year, From 20 to 65 step 5, R=8, C=2' would select the value 7,741 from the Inactive Counts/Benefits data shown below:
ProVal Data: Assumptions
Syntax:
Table, DataSetLabel, Variable
- Table is the name of the table in Microsoft Access: 'ProVal_ValSet_Assumptions'
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Variable is the string to look up in the ‘Name’ column of the specified Table
| Examples: | Meaning: |
| ProVal_ValSet_Assumptions, Val Set CY, Cost_Method | Cost method for the current year valuation set |
ProVal Data: Assumption Tables
Syntax:
Table, DataSetLabel, Variable, R=RowNum, C=ColNum, S=SexIdentifier
- Table is the name of the table in Microsoft Access.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Variable is the string to look up in the ‘Name’ column of the specified Table
- RowNum is the origin-1 row number
- ColNum is the origin-1 row number
- SexIdentifier describes whether the assumption is sex distinct or unisex (values are Male, Female or NA)
| Examples: | Meaning: |
| ProVal_ValSet_AssumpTab1U, Val Set CY, Actuarial_Ret_Rates, R=2, C=3, S=NA | Unisex rate from row 2, column 3 of Actuarial Retirement Rates from run with the label "Val Set CY" |
| ProVal_ValSet_AssumpTab2S, Val Set PY, Accounting_Trm_Rates, R=7, C=2, S=Male | Male rate from row 7, column 2 of Accounting Termination Rates for run with label "Val Set PY" |
Notes:
For tables that include a projection scale – only the base rates are displayed.
ProVal Data: Val Set Exhibits
Syntax:
Table, DataSetLabel, Variable
- Table is the name of the table in Microsoft Access: see Exhibit Names in Word and Access.
Note that the exhibits 'ProVal_ContSched' and 'ProVal_AcctgBase' have special rules, see below.
The following aliases may be used in place of table names:Table name Alias
ProVal_Acctg_Funded_Status AFS ProVal_AggNCQual ANC ProVal_DevExpense DE ProVal_FundTgt FT ProVal_MaxBase MB ProVal_MaxFundTgt MFT ProVal_USMaxPPA MAX ProVal_USMinPPA MIN ProVal_USShrtflChg SC - DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Variable is the string to look up in the ‘Name’ column of the specified Table
| Examples: | Meaning: |
| ProVal_USMinPPA, Val Set CY, Tgt_NC | Target normal cost from the Minimum Contrib exhibit |
ProVal Data: Val Set Exhibits (Schedule of Employer Contributions)
Note that this is an Exhibit but has special syntax requirements.
Syntax:
Table, DataSetLabel, Variable, Column
- Table is the name of the table in Microsoft Access: 'ProVal_ContSched'.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Variable is the string to look up in the ‘Name’ column of the specified Table
- Column is the column name in the specified Table.
| Examples: | Meaning: |
| ProVal_ContSched, Prior Yr 1, Total_Excluding_CB, ContAmt | Total contributions, excluding the credit balance, for the valuation set wit the “Prior Yr 1” label. |
ProVal Data: Val Set Exhibits (Accounting Amortization Bases)
Note that this is an Exhibit but has special syntax requirements.
Syntax:
Table, DataSetLabel, Variable, Column
- Table is the name of the table in Microsoft Access: 'ProVal_ContSched'.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
- Variable is the string to look up in the ‘Name’ column of the specified Table
- Column is the column name in the specified Table.
| Examples: | Meaning: |
| ProVal_AcctgBase, Current Year, Total_PSC, Amort_Charge | Total prior service cost amortization charge for the valuation set with the ‘Current Year” label |
ProVal Data: Forecasts
Syntax:
Table, DataSetLabel, Variable [, C=Column] [, Y=Year]
-
Table is the name of the table in Microsoft Access. The alias ‘DP’ can be used instead of the table name ‘ProVal_DetProj_Data’.
-
DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
-
Variable is the string to look up in the ‘Name’ column of the specified Table
-
Column is the column name in the specified Table. If omitted, the default is the ‘Value’ column. Other choices are:
-
Year is the forecast year found in the ‘Year’ column of the specified Table. 0 represents the initial valuation date, 1 the first forecast year, etc. If omitted, the default is 0.
| Examples: | Meaning: |
| ProVal_DetProj_Data, DetProj Baseline, Annuities_Inforce | Annuities Inforce from "Value" column for run labeled "DetProj Baseline" |
| DP, DetProj Baseline, Annuities_Inforce, Y=2 | Same as above but for year 2 of the projection |
| DP, DetProj Baseline, Annuities_Inforce, Y=2, C=ActiveTot | Same as above but from "ActiveTot" column |
ProVal Data: Forecast Exhibits
Syntax:
Table, DataSetLabel, Variable, Year
-
Table is the name of the table in Microsoft Access: see Exhibit Names in Word and Access. You may also use the aliases listed under 'Val Set Exhibits'.
- DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
-
Variable is the string to look up in the ‘Name’ column of the specified Table
-
Year is a four-digit calendar year (eg., 2008)
| Examples: | Meaning: |
| ProVal_DetProj_DevActAssets, Baseline, Market_Value,2010 | Market value of assets in 2010 for baseline forecast |
ProVal Data: Gain / (Loss)
Syntax 1:
Table, DataSetLabel, Variable
-
Table is the name of the table in Microsoft Access: 'ProVal_GainLoss_Summary' or 'ProVal_GainLoss_BySource'. The other gain/loss tables use syntax 2 below.
-
DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
-
Variable is the string to look up in the ‘Name’ column of the specified Table
Syntax 2:
Table, DataSetLabel, Variable, C=Column
-
Table is the name of the table in Microsoft Access: 'ProVal_GainLoss_Benefits' or 'ProVal_GainLoss_StatusRec'. The other gain/loss tables use syntax 1 above.
-
DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
-
Variable is the string to look up in the ‘Name’ column of the specified Table
-
Column is the column name in the specified Table.
| Examples: | Meaning: |
| ProVal_GainLoss_Summary, GL CY, LiabGainLoss | Amount from "Value" column of table ProVal_GainLoss_Summary where RunID matches run with the label "GL CL" |
| ProVal_GainLoss_BySource, GL CY, GL_TotGainLoss | Total gain/(loss) from "Value" column of table ProVal_GainLoss_BySource where RunID matches run with the label "GL CL" |
| ProVal_GainLoss_StatusRec, GL CY, NewEnt, Active | Headcount for new entrants from the "Active" column |
ProVal Data: Descriptive Statistics
Syntax:
Table, DataSetLabel, R=RowNum, C=ColNum [, T=TableNum]
-
Table is a pseudo-name of the table 'ProVal_DescStat'. The alias 'DS' may also be used. Note that this is not actually an Access table.
-
DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer
-
RowNum is the origin-1 index of the descriptive statistics row.
-
ColNum is the origin-1 index of the descriptive statistics column.
-
TableNum is the origin-1 index of the table produced as part of descriptive statistics. If omitted, this defaults to 1. This does not refer to a table in Microsoft Access.
| Examples: | Meaning: |
| DS, Age/Service, R=1, C=1 | Value from row 1, column 1 and table 1 of the Descriptive Statistic labeled "Age/Service" |
| ProVal_DescStat, Age/Service, T=2, C=3, R=4 | Note that R= C= T= may be specified in any order |
Supplemental Data: Reconciliation of Market Value of Assets
Syntax:
Table, DataSetLabel, OutlineLevel
-
Table is the name of a pseudo-table, ‘ProVal_Reconciliation_of_Market_Value_of_Assets’. The alias 'MVA' may also be used. This pseudo-table does not exist in the Access database; rather it is encoded into the PVRW_RptDefs table. To read or write this information, use the ProVal API functions specially made for this purpose. See the ProVal API Users Guide in the ProVal installation folder for more information.
-
DataSetLabel is the label assigned to the Valuation in the Report Definition of the Report Writer (e.g. ‘Current Year’ or ‘Prior Year’). It specifies the column in the table.
-
OutlineLevel specifies the row of the table (e.g. (1)(b)(iv)).
| Examples: | Meaning: |
| ProVal_Reconciliation_of_Market_Value_of_Assets, Current Year, (1)(b)(iv) | The value in row (1)(b)(iv) for the valuation column corresponding to the label ‘Current Year’ |
Supplemental Data: Statement of Assets
Syntax:
Table, DataSetLabel, OutlineLevel
-
Table is the name of a pseudo-table, ‘ProVal_Statement_of_Assets’.The alias 'SA' may also be used. This pseudo-table does not exist in the Access database; rather it is encoded into the PVRW_RptDefs table. To read or write this information, use the ProVal API functions specially made for this purpose. See the ProVal API Users Guide in the ProVal installation folder for more information.
-
DataSetLabel is the label assigned to the Valuation in the Report Definition of the Report Writer (e.g. ‘Current Year’ or ‘Prior Year’). It specifies the column in the table.
-
OutlineLevel specifies the row of the table (e.g. (1)(b)(iv)).
| Examples: | Meaning: |
| ProVal_Statement_of_Assets, Current Year, (1)(b)(iv) | The value in row (1)(b)(iv) for the valuation column corresponding to the label ‘Current Year’ |
Supplemental Data: Asset Return Data
Syntax:
Table, Period, AssetClass, Variable
-
Table is the name of the table in Microsoft Access, 'PVRW_AssetReturnData'. The alias 'AR' may also be used.
-
Period is a four-digit calendar year (e.g., 2008) or “Average”.
-
AssetClass is the name of the asset class or one of three special names: "Market Value of Assets", "Actuarial Value of Assets", or "Composite Return". Note that for these three names there is no "Mix" value.
-
Variable is “Return” or “Mix”
| Examples: | Meaning: |
| AR, 2007, S&P 500 Index, Return | The return for the “S&P 500 Index” in the year 2007 |
| PVRW_AssetReturn, 2007, S&P 500 Index, Mix | The asset mix (i.e., percent of the total portfolio) for the “S&P 500 Index” in the year 2007 |
| AR, Average, Market Value of Assets, Return | The average return on the Market Value of Assets. |
Supplemental Data: User Defined Data
Syntax:
Table, Category, Name
-
Table is the name of the table in Microsoft Access, 'PVRW_UserDefinedData'. The alias ‘UD’ may also be used.
-
Category is the category name.
-
Name is the name of the data item.
| Examples: | Meaning: |
| UD, Actuary, LastName | The 'LastName' value in the 'Actuary' category. |
| PVRW_UserDefinedData, Client, Zip | The 'Zip' value in the 'Client' category. |