vTip #35 - Setting up your Reporting Add-On Module

vForms has an optional Reporting system that is designed to support the distributed reporting needs of the typical "Net Branch" operation. This system can support a variety of reporting requirements via an integrated template system.

There are currently five static templates:

  • Compliance
  • HMDA
  • 1098
  • Vendors
  • Trust Accounting

The Reporting Process

  1. The vForms user highlights one or more loans (Borrowers, Prospects…).
  2. User clicks on Send / Reporting and selects one of the five templates (the reporting option must be enabled from a licensing perspective).
  3. Each loan file will be read and the data fields will be extracted.
  4. The single data file will be attached to an email message. The file name consists of <branchId>_<template>_<date in yyyymmdd>.rpt.

Note: There is a new field on the Employee options tab that is labeled "Branch Id", which is a required field for Reporting purposes.

Setting up the Database (Excel Style)

Data from .rpt files can be consolidated into two formats, an Excel worksheet or a MS Access database. Since vForms is based on ADO technologies, collection will be extended soon to included SQL server databases.

  1. To create the initial data repository follow these steps:
  2. Create a standard folder on your desktop, this is where the initial Excel spreadsheet will be created.
  3. Using vForms, create a new 'vForm Data' source that points to the folder created in step 1, using Tools / Options / Locations tab. "vForm Data" files are simply Excel spreadsheets striped of formulas and special formatting.
  4. Open your 'vForm Data' using vForms. Click on "Borrowers" and select the data source created in step 2. You will be presented with a list of spreadsheets located in this folder, which should be empty if you created a new folder.
  5. Drag the .rpt file into the 'vForm Data' view, you will be asked to give your new data source a name.
  6. Double click on the new entry created in step 4 to view the imported data. You will note only the first record was imported.
  7. To finish importing the .rpt file, drag the .rpt file into this view.
  8. As new files are received, they can simply be dragged into this view. New records will be added, and changes to current records will be updated.

The unique key fields are <My_BranchID> and <Loan_Number>

Setting up the Database (MS Access Style)

After following the steps to create an Excel style database above, you can move this data into a MS Access database. This is actually the recommended approach, as data validation and use of the query facilities greatly enhances functionality. To migrate the Excel spreadsheet to a MS Access database follow these steps:

Using MS Access create a new MDB

Import the XLS file created above into a new table, allowing MS Access to create a unique auto incremented index (ID) as field #1.

vForms will update / append records in this MS Access table when .rpt files are dropped into the view. The attached BlankCompliance.MDB file was created using the above process and can be saved to your system as an initial example.

Setting up the Data Collection database (SQL Style)

First create an MS Access database, and then use the “Upsize Wizard” to create the necessary SQL tables. In vForms Tools/Options, select SQL as the data source and <server>|<Database> as the location field.

The Data Collection Process

  1. The central office receives one or more .rpt files attached to emails from their branches.
  2. If they are using Outlook, they can drag the email message from the in-tray into the MSAccess view of a running copy of vforms.
  3. The key fields used to determine if records should be added or updated are: <My_BranchId> and <Loan_Number>.

Using vForms with MS Access / Excel data views, you have "Grouping" and "Filtering" options available to you. Just RH-Click into the view and make your selection.