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. They syntax for a field spec depends on the where the value is pulled from. The field spec can either be a single data item or an expression that derives a value from one or more data items.
The following sections describe the field specs for each type of table stored in the report writer database.
- Valuation Set Output
- Valuation Set Exhibits
- Assumptions
- Deterministic Forecast Output
- Deterministic Forecast Exhibits
- Gain/Loss
- Descriptive Statistics
- Reconciliation of Statuses
- Statement of Assets
- Asset Return Data
- Reconciliation of Assets
- User Defined Data
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.
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
When saving the Valuation Set results to the access database, if you checked the box "For Report Writer, calculate liabilities by subtotal field", each subtotal saved will be available as a choice for column. When using results by subtotal, the alias for the table name is 'VSS'.
| 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 |
| VSS, Val Set CY, Projected_Benefit_Obligation, Greenwich | Total Projected benefit obligation from "Val Set CY" for a subtotal "Greenwich" |
| VSS, Val Set CY, Projected_Benefit_Obligation, Greenwich, C=ActiveTot | Active Projected benefit obligation from Val Set CY" for a subtotal "Greenwich" |
The examples above without a column spec are assumed to be the total value column. ProVal automatically inserts the C=Value. It may be desirable to omit the column in cases where an expression is necessary and there are concerns about the 138 character limit.
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'. The alias ‘AS’ can be used instead of the table name ‘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'. The alias ‘IB’ can be used instead of the table name '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:
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.
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 |
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 |
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, Group, Field, Statistic [, Blank out results]
-
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.
-
Group are the breakpoints are to be extracted.
-
Field is the database field on which statistics are based.
-
Statistic is the code for which statistic is required. The codes are:
- 1=count
- 2=sum
- 3=mean
- 4=standard deviation
- 5=geometric mean
- 6=minimum
- 7=1rst quartile
- 8=median
- 9=3rd quartile
- 10=maximum
- Blank out results (optional) if the number of records in this cell is less than the number specified here, an "*" will be published rather than the actual value. This is useful to suppress salary or pension amounts for groups with only a very small number of participants represented.
| Examples: | Meaning: |
|
ProVal_DescStat,Active,Age=2;Sex=1,Salary,3,5 |
ProVal_DescStat - ProVal Descriptive Statistic (could also use DS) Active - the Descriptive Statistic labelled with Active Age=2;Sex=1 - the second breakpoint for the field age for participants with a code of 1 for Sex Salary - the field we are displaying a value for 3 - display the statistical mean for the participants in this breakpoint 5 - if there are fewer than 5 participants in this breakpoint, publish an * rather than the actual value |
Alternative Syntax, (This is a less precise format that was used prior to ProVal 3.17. This format is still supported although the above format is the preferred approach).
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 |
ProVal Data: Status Reconciliation
Syntax:
Table, DataSetLabel, Row, Col
-
The alias 'SR' should be used.
-
DataSetLabel is the label assigned to a Data Set in the Report Definition of the Report Writer.
-
Row is the status a participant was at the beginning of period.
-
Col is the status a participant was at the end of period.
Row and Col are based on the coded field labels for the status field underlying the status reconciliation. ProVal uses the least number of unique letters for each status. For example, if the only status are "Active", Retired", "Terminated", ProVal will use "A", "R", and "T", respectively. If the statuses also include, "Death" and "Disabled", ProVal will use "De" and "Di".
| Examples: | Meaning: |
| SR, Current Year, -A, T | From the reconciliation labelled Current Year, the participants who were active at beginning of period and are now terminated vested. |
| SR,Current Year,A, | Same as above, except total active participants at the beginning of period |
| SR,Current Year,,T |
Same as above, except total terminated vested participants at end of period |
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. |