Combining SQL reports into HTML forms

In an HTML form template that you design, you define what data you want to pull from one or more SQL reports, and how you want that data to display. The data is combined into a Form Definition. In eStat, you can use a form definition as a replacement for or supplement to the standard shipment details page. In CarrierHub, you can use a form definition as a replacement for the standard Trip Sheet.

Prerequisites

SQL reports

Before you begin, all SQL reports you want to use in your HTML form template must exist. These can be current reports, and new ones that you create. Each report name must be unique.

HTML form template

You design an HTML form to use as a template for your form definition. In the template, you provide the formatting the report will have when viewed. Also:

In CarrierHub, you identify the data from your SQL reports that you want to see on your Trip Sheet.
In eStat, you identify the data from your SQL reports that you want to see on your shipment details page.

When designing the template:

Always use the [$Date$] code to display the current date.
Identify the name(s) of the SQL reports and the table column data that you want to use from those reports. Each report name must be entered using this format:

[$Rpt_StopsReport.stp_state$]
- The code $Rpt_ is always first.
- Next is the SQL report name, such as StopsReport.
- The SQL report name is followed by a period, the table column data you want pulled from the SQL report, and a dollar sign, such as .stp_state$.
If the report will retrieve multiple data elements, such as stops on an order, you must enter a repeater code, such as:




Note: The integrated report name in this example would be StopsReportRepeater. However, "repeater" is not a required term to make a repeating section. We used it in our example to help the code.

Report examples

HTML form opened with Microsoft Visual Studio:

HTML form opened with Internet Explorer:

Creating a form definition from a template

Using your HTML form template, you create a new HTML form definition. In the form definition, you assign the report variables to the SQL reports from which the data will be pulled.

The new form definition combines the data that is retrieved from the multiple SQL reports, and outputs it into a single form. This form definition is then available for you to select as your Trip Sheet (CarrierHub) or shipment details page (eStat).

Use this procedure to create a form definition.

Go to Admin > Reporting > Manage Report Forms.
The Manage Report Forms page opens.
Click Add New Form.
Form fields display.
In the Form Name field, enter the name of your new form definition.
In the Form Definition field, click Browse and select the HTML form template you designed.
Click Process Form Definition.
Report Assignment fields display.
- The first field lists the variables from the SQL in your HTML form template, such as:
- The second field lists the SQL reports available in Trimble Web Products. In our example, these are named the same.
These must be connected, or assigned, so the correct data is pulled from the associated report and included in the form definition.
Assign the variables to the reports.
1. In the first Report Assignments field, select one of the HTML variables.
2. In the second Report Assignments field, select the SQL report you want to assign to the HTML variable.
3. Repeat these steps to assign all HTML variables to the associated SQL reports.
Note: If the report will retrieve multiple data elements, such as stops on an order, you must assign the repeater code variable to the same SQL report as the data you want to be repeated.
Click Save Form Definition.
If you do not want to save your form definition, click Cancel.

The system displays a message saying your form was saved.

Note: If you failed to associate every variable with an SQL report, the system displays this message:
Do one of the following:
- If using CarrierHub, continue with Using a custom report form as a trip sheet to use the form.
- If using eStat, continue with Using a custom report form with the shipment details page to use the form.

Modifying a form definition

If changes are made to the SQL reports used in the form definitions, the definition does not have to be reprocessed. The Report Assignments in the definition are not affected.

If changes are made to the HTML form used in the form definition, the Report Assignments are broken. The form must be re-imported and processed, which populates the Report Assignment fields. You must then reassign the variables in the HTML form to the SQL reports.

To re-import an HTML form definition and reassign the fields, do the following:

Go to Admin > Reporting > Manage Report Forms.
The Manage Report Forms page opens.
In the Defined Forms field, select the form definition to modify.
Click Edit Selected Form.
The Report Assignments field appears.
Click Change Form Definition.
A blank Form Definition selection field appears.
Click Browse and select the updated HTML form.
Click Process Form Definition.
The Report Assignment fields display.
Assign all variables from the first Report Assignments field to the appropriate SQL reports in the second field.
Note: See Creating a form definition from a template for additional information.
Click Save Form Definition.
If you do not want to save your edits, click Cancel.

You will receive a confirmation message that the form was saved successfully.

Deleting a form definition

Use this procedure to delete existing form definitions you no longer need.

Go to Admin > Reporting > Manage Report Forms.
The Manage Report Forms page opens.
In the Defined Forms field, select the form you want to delete.
Click Edit Selected Form.
The page expands to show the Delete Form Definition button.
Click Delete Form Definition.
A message confirms the form definition is removed.

Note: The form definition is removed immediately. The system does not ask for confirmation that you want to delete it.