Access for Programmers (API)

Data from the Public Plans Database are accessible through our web-based application programming interface (API) at this URL:

https://publicplansdata.org/api/

For those requiring encrypted access to the API, data may also be accessed via the same API at this URL:

https://publicplansdata.org/api/

Query URL Parameters

The API accepts the parameters and values described below. Parameters are specified in the query string of an HTTP GET request to the URL above. (Some example queries are provided below.)

All query parameters and values are case insensitive, so “format=XML” and “Format=xml” are equivalent.

Query Type Parameters

q (required): The type of query to execute. Supported queries are:
- LastUpdate: Lists the last update date for each data set. The result includes the following fields:
  - id: The data set’s database ID
  - table_name: The data set’s name to be used in “QDataSet” queries
  - table_display_name: User-friendly name for the data set
  - last_update: The UTC date and time when the data set was last updated in the format “YYYY-MM-DD HH:MM:SS”
- ListVariables: Lists the set of available variables with the following fields:
  - dataset_id: The databse id of the variable’s data set
  - dataset_name: The name of the variable’s data set for use in “QDataSet” queries
  - FieldName: The variable’s name for use in “QVariables” queries
  - Description: User-friendly description of the variable
  - FieldType: The variable’s data type. These correspond to standard SQL data types
  - Length: For variables of type “varchar,” the maximum number of characters the field can contain. For variables of type “decimal,” the variable’s precision and scale, separated by a comma
- ListDataSets: Lists the available data sets. Results have the following fields:
  - id: The data set’s database ID
  - table_name: The data set’s name to be used in “QDataSet” queries
  - table_display_name: User-friendly name for the data set
- QVariables: Returns a result set with the fields specified by the “variables” parameter
- QDataSet: Returns the data from the data set specified in the “dataset” parameter. Perform a “ListVariables” query to see the variables included in each data set.
variables (required for “QVariables” queries): The set of variables to be included in a “QVariables” query. Variables should be identified by their “FieldName” as returned by “ListVariables.” Multiple variables should be separated with commas.
dataset (required for “QDataSet” queries): The data set to be queried for a “QDataSet” query identified by its “table_name” as specified by “ListDataSets.”

Output Format Parameters

format: The output format for query results. Supported values are:
- xml: XML
- json: JavaScript Object Notation (JSON)
- csv (default): Comma-separated values
includeheader: For CSV-formatted results, whether to include a header row. Supported values are:
- 1 (default): Include header row
- 0: Omit header row

See “Query Results” below for more information on specific output formats.

Filter Parameters

“QVariables” and “QDataSet” queries can be filtered by Public Plans Database ID (ppd_id), Employee Group ID (EEGroupID) or Tier ID (TierID) by supplying comma-delimited lists of numeric IDs in the following parameters, respectively:

filterppdid
filtereegroupid
filtertierid

Records whose ids match any of the values in the supplied list will be returned.

“QVariables” and “QDataSet” queries can also be filtered by Fiscal Year (fy) by specifying four-digit starting and ending years in the following parameters:

filterfystart
filterfyend

To filter by Fiscal Year, both “filterfystart” and “filterfyend” must be specified. The specified range is inclusive, i.e. records whose fiscal year is greater than or equal to “filterfystart” and less than or equal to “filterfyend” will be included.

To be included in the result set, a record must match all supplied filter criteria, i.e. the filter conditions are conjoined with a logical “AND” operator.

Query Results

RESULT META-DATA (XML AND JSON FORMATS ONLY)

XML- and JSON-formatted results will include the following meta-data fields:

status: The exit state of the query. This will have a value of “OK” if the query completes successfully or “ERROR” if an error occurred. (See “Errors” for more information on error reporting.)
date: The UTC date and time the query was executed in the format “YYYY-MM-DD HH:MM:SS”
q: The query type supplied in the “q” parameter
params: The complete parameter string received by the server (including “q”)
recordcount: The number of records returned by the query

See “XML” and “JSON” below for their representations in these formats.

XML

XML-formatted results will be returned as an XML document with the following structure:

result
- resultmeta
  - status
  - date
  - q
  - params
  - recordcount
- recordset
  - record
    - field
      - name
      - value
    - (…etc.)
  - (…etc.)

The top-level “result” element will always have two children: “resultmeta” and “recordset.” The child elements of “resultmeta” correspond to the result meta fields described in “Result Meta-Data.” The “recordset” element will have zero or more “record” children representing the actual query result records. Each “record” element will have one or more “field” children representing that record’s fields. Each “field” will have a “name” child containing the field’s name (e.g. the variable’s “FieldName,” for a “QVariables” query) and a “value” child with its value. Values for character data result fields will be in CDATA nodes.

The elements will be in the namespace “http://crr.bc.edu/pubplans” identified by the prefix “pp”.

JSON

JSON-formatted results will be returned as an array of JavaScript objects. The first element of the array will have properties corresponding to the result meta fields described in “Result Meta-Data.” Subsequent array elements will be the actual result records. Each record will be a JavaScript object with properties corresponding to the record’s fields.

CSV

Fields in CSV-formatted results will be delimited with commas. Field values containing whitespace or commas will be enclosed in double quotes (“) with internal double quotes escaped by a second double quote (“”).

Lines are terminated UNIX-style, i.e. with a “newline” character (“n”), as opposed to a “CRLF” (“rn”). (Note: Recent versions of Microsoft Excel should be able to open the resulting output without any problems, even on Windows.)

By default, the first line of output will contain the result field names, i.e. the “header row.” To omit the header row, pass in the “includeheader” parameter with a value of “0.”

No result meta-data are provided with CSV-formatted results.

Errors

If errors occur executing the query, the “status” result meta field (if included) will be set to “ERROR.” The errors will be formatted as result records with a single field named “Error” whose value will be a text description of the error.

Examples

“ListVariables” XML format:

https://publicplansdata.org/api/?q=ListVariables&format=xml

“ListDataSets” JSON format:

https://publicplansdata.org/api/?q=ListDataSets&format=json

“QVariables” with Fiscal Year, Plan Name, Percent of Required Contribution Paid and 5-year investment return, filtered for Fiscal Years 2009 through 2011 in XML format.

https://publicplansdata.org/api/?q=QVariables&variables=fy,PlanName,PercentReqContPaid,InvestmentReturn_5yr&filterfystart=2009&filterfyend=2011&format=xml

Same as above in default CSV format. Note that the “format” parameter is omitted:

https://publicplansdata.org/api/?q=QVariables&variables=fy,PlanName,PercentReqContPaid,InvestmentReturn_5yr&filterfystart=2009&filterfyend=2011

“QDataset” for “Plan Basics” dataset, filtered for ppd_ids 12, 13 and 14, XML format:

https://publicplansdata.org/api/?q=QDataset&dataset=pensionplanbasics&format=xml&filterppdid=12,13,14

Error: Unknown query type, XML format:

https://publicplansdata.org/api/?q=foo&format=xml

Error: “QVariables” with unknown variables, returning multiple errors:

https://publicplansdata.org/api/?q=QVariables&variables=foo,bar&format=xml

Advanced Search