Report Templates

While 4.0 has drastically improved how PDFs are automatically created simply by building a form, some customers may require the ability to convert their form back into an existing document before it is distributed. With doForms you can download all of your data elements and caption to Excel by simply selecting create report template under the File tab in Build forms. Once your data is in Excel you will notice that your data names are wrapped in brackets {data_name}. When your form is submitted, doForms will replace these fields with the data placed into that field in the form. In Excel all you need to do is move everything around until your template looks like the document you are trying to recreate. Once this is accomplished you save the spreadsheet as a template type. In the Build forms section click on report templates to add your template into the system and then configure your report setting to use the template versus the standard PDF. You can select to generate a PDF from this template or you can leave the spreadsheet as a spreadsheet. Either option can be automatically emailed from the email tool in your form. It is possible to combine multiple forms into a single Excel template as long as all forms have a common key field. This allows you to breakup very large forms into smaller forms yet still consolidate all of the data collected in to a single spreadsheet.

The instructions on how to combine forms is below.

{"project"="Project_Name","form"="Form_Name","common-field"="Data_Name","field"="Data_Name"}

Where: - Project_Name is the name of the project that contains the data; - Form_Name is the name of the form that contains the data; - Data_Name is as previously described; - Common-field is the Data_Name value of the common field.

So for example, assume that I have two forms in a project named "Insurance" for which I want to aggregate the data from two different forms. The first form is named "Car", which contains data about a customer’s car. The second form is named "House", which contains data about a customer’s house. Both forms contain the common-field called "Customer_ID", which is a unique numeric number to identify each customer. If we wish to create {…} tags in the aggregate report to display the customer’s {car_make} and {house_construction} we would use:

{"project"="Insurance","form"="Car","common- field"="Customer_ID","field"="car_make"} {"project"="Insurance","form"="House","common-field"="Customer_ID","field"="house_construction"}

Note that you only need to use the long format of the {…} tag above if data is being read from a form that is different from the form that the report is being generated for. So for example, if we were generating reports for the House form, we could simply use the tag {house_construction}. But if we want to include data from the Car form in the report for the House form, then we would need to use the long format of the {…} tag. Similarly, if you wanted to use the same Excel Report Template for generating the same report for both the Car form and the House form, then you would need to use the long form of the {…} tags.

Data_Name Property in the Build Forms tab: Data_Name Property in the View Data tab”

doForms uses these same data_names to relate (or “map”) the answers being collected in your form to cell locations in the Custom Report Template. For example, the Photo Survey form above contains a text question with the data_name “Subject_Description”. As the name implies, this is where a mobile user enters description text for their photo survey. Note how the Custom Report Template below utilizes the same data_name to specify where these values will be displayed in the report using the “{ }” brackets. This tells the doForms to “map” the answers to the Subject_Description to that particular cell. In the View Data screen above there is a completed record with the Subject_Description = “Dudley Gardens”. Note how this answer value is mapped to the completed report below.

If your form includes Repeatable section or Table containers, the format of the corresponding data_name tags is as follows: {data_name(i)}, where “i” is the repeat number. So in the Excel Template example above, the captions and images are specified as {Caption(i)} and {Image(i)} respectively. This is also the data_name format used to reference data in grid tables.

Creating a Template File

Follow these steps to create a Custom Report Template to use with doForms:

  1. Open Excel and start a new document.
  2. Add tabs/worksheets as desired into the Excel document to better organize your report.
  3. Specify which cells will contain which answers with the {Data_Name} tags.
  4. Add titles, captions and any other text into their own cells and format as desired.
  5. Add any desired graphics, such as logos, using the Excel “Insert” functions (do not use Excel "Smart Art").
  6. Add any desired page breaks using the Excel “Layout” functions.
  7. You can use merged cells to better control the size and position of any images (pictures and signatures).
  8. If your report includes numeric values, add any desired calculations or charts which employ these values.
  9. Use the Excel File > Save function to save the file in one of the Excel 2010 Template formats as shown below (xlsx, xlsm).

Hide Empty Rows and Columns in Custom Reports

You may use script functions to Custom Report Templates that “hide” rows and columns based on the value of a question answer. Normally, to insert the answer value of a question into a Custom Report Template cell, you would include a data-name tag such as {mydata_name}. But suppose you would like to use the value of the mydata_name field to hide selected rows and columns in the Custom Report Template. These new script functions allow you to do just that.

Below are a few examples that illustrate how this script function works.

Example 1: Suppose your tag {mydata_name} is in row 37 but you would like to hide row 37 if the returned value to the field is empty (null). Then instead of using just the {mydata_name} tag in the cell where you want this value placed, use the tag:

{<script> if (value == "") {hideRow("37");}</script>mydata_name}

This script will hide row 37 if the value mydata_name is empty. For example, if mydata_name corresponds to a picture, but no picture was taken, then this script could be used to hide the row reserved for the picture. This would consolidate the format of the report.

Example 2: Suppose you would like to hide row 37 and column P if the returned value to the field “mydata_name” is equal to the text “abc123”. Then instead of using.just the {mydata_name} tag in the cell where you want this value placed, use the tag:

{<script> if (value == "abc123") {hideRow("37"); hideCol("P");}</script>mydata_name}

This script will place the value “abc123” in the desired cell and will also hide row 37 and column P.

Example 3: Suppose you would like to hide rows 30:32,34,37 and columns P,Q, A:H if the returned value to the field “mydata_name” is equal to the number “88.3”. Then instead of using just the {mydata_name} tag in the cell where you want this value placed, use the tag:

{<script> if (value == "88.3") {hideRow("30:32,34,37"); hideCol("A:H,P,Q");}</script>mydata_name}

This script will pace the value “88.3” in the desired cell and will also hide rows 30 to 32,34,37 and columns A to H, P,and Q,.

TIP: When you want to create a Custom Report Template, you can use the Build Forms > File > Create Report Template function to create an Excel file that can be used as a “starting-point” to save time and prevent typos. This file will contain all the {data names}, captions and hints in your form. You can then further edit and format this file in Excel to create the desired Custom Report Template.

IMPORTANT NOTES:

Certain features of Excel will not be supported in the template. These include shape drawings, smart-art, certain chart types, and others. So test your templates carefully before deploying! Images will be scaled to fit the merged cells, so be careful about using the correct aspect ration. In most cases, you may place multiple data_name tags in a single cell. If you specify the data_name for a GPS location, both the latitude and longitude values will be written into the same cell but separated by a comma. For audio and video files, doForms will insert the http:// address of the corresponding media files. Multiple Projects/Forms

Custom Report Templates can also be created and used to aggregate data from different forms into a single report. Note that this is for different forms, NOT for different data records of the same form. The format is as follows:

{"project"="Project_Name","form"="Form_Name","common-field"="Data_Name","field"="Data_Name"} Where: - Project_Name is the name of the project that contains the data; - Form_Name is the name of the form that contains the data; - Data_Name is as previously described; - Common-field is the Data_Name value of the common field.

So for example, assume that I have two forms in a project named “Insurance” for which I want to aggregate the data from two different forms. The first form is named “Car”, which contains data about a customer’s car. The second form is named “House”, which contains data about a customer’s house. Both forms contain the common-field called “Customer_ID”, which is a unique numeric number to identify each customer. If we wish to create {…} tags in the aggregate report to display the customer’s {car_make} and {house_construction} we would use:

{"project"="Insurance","form"="Car","common- field"="Customer_ID","field"="car_make"} {"project"="Insurance","form"="House","common-field"="Customer_ID","field"="house_construction"}

Note that you only need to use the long format of the {…} tag above if data is being read from a form that is different from the form that the report is being generated for. So for example, if we were generating reports for the House form, we could simply use the tag {house_construction}. But if we want to include data from the Car form in the report for the House form, then we would need to use the long format of the {…} tag. Similarly, if you wanted to use the same Excel Report Template for generating the same report for both the Car form and the House form, then you would need to use the long form of the {…} tags.

Common-Field Restrictions:

All values inside the {…} tag are case-sensitive and cannot contain any special characters, including \ / : * ? " > < | = { }. Common-fields are restricted to text, numeric-integer, lookup and barcode question types. Common-fields must be unique. So, in the example above, no two customers can have the same ”Customer_ID”. No single form can contain more than one data record that has the same value of the common-field. So, in the example above, the Car form and the House form can NOT contain more than one record each with the same value for the “Customer_ID” field. If any of the restrictions above are violated, an error will occur in the report that is generated.

Uploading the Template

After the Excel Report Template has been created on your PC (see the View Data tab section), follow these steps to upload it to your doForms website:

Go to the Build Forms tab of your website. Click on the Resources menu. Select Manage Excel Templates. Click Add.

Click Upload Excel File. A “File Upload” dialog will open. Browse the files, then select your Excel Report Template file and click Open.

Give your template a name and description, then click Save.

Your template will be added to the Saved Excel Templates list. Click Close. Repeat the steps above to load additional report templates into your Build Forms tab. Click Close when done.

TIP: After you upload a Custom Report Template, this template becomes available for use with any form in your account, provided that the form has exactly matching data name and common-field values for the {…} tags.

Reports vs. Exports

This article describes how to create and distribute reports generated from the form data submitted to your website. But first, it is important to distinguish between “reports” and “exports”. Exports are a simple conversion of some or all data records for a particular form to another file format. Data are exported to your desktop computer or to a cloud account such as Google Docs/Drive. Exports are always in a spreadsheet-like format (with the exception of KML which is a mapping format). Exports are done from the File menu in either the View Data or Dispatch tabs.

Reports, which are described in this chapter, are highly formatted Excel or PDF files which can include embedded images, signatures and maps. Reports generally involve one completed form data record (i.e., one filled out form), but can also be used to aggregate data from several different but related forms. Reports can be generated from the Row or Data menus in either the View Data or Dispatch tabs. Reports can also be emailed from a mobile device by including an Email Report widget in your forms when you construct them in the Build Forms tab.

Standard Report Template vs. Custom Report Template

Reports can be generated using either a Standard Report Template or Custom Report Template. A “template” is simply a format definition for how the report will look (i.e., the placement of graphics, font type and size, border styles, etc.). The Standard Report Template is an “off-the-shelf” doForms format that you can use for your reports. As described in more detail below, even though it is “standard”, it still provides numerous settings to help you control the content and appearance of standard reports.

In comparison, Custom Report Templates provide you FULL control over the content and appearance of your reports. Custom Report Templates are created by YOU, using Microsoft Excel and uploaded to your website. If you know how to format your Excel documents, then you are already an expert at building Custom Report Templates, which can include almost any formatting, calculation and charting function of Excel.

PDF vs. Excel Output

Once you have created or selected a Report Template, you can also specify whether the resulting report will be produced in Excel or PDF file format. You can create either PDF or Excel output from a Custom Report Template. Currently you can only produce PDF output from a Standard Report Template. The process for controlling reports is as illustrated below:

Report Settings

Report Settings are used to control the format of reports produced from forms. Report Settings can be controlled in two different ways:

Via Report Settings in the Email Report widget that is included in a form. These reports are generated when a form is completed on the mobile app and received by the website, or Via Report Settings in the View Data or Dispatch tabs in the website. These reports are generated from either the Row menu or Data menu in these website tabs. Report Settings in the Email Report Widget

These settings are applied on a widget-by-widget basis. This means that you can set different templates and output types for different Email Report widgets contained in a single from. For example, you might want to have a full report with all fields in Excel format that is emailed internally to your organization, but you want a different set of fields in a PDF format emailed to customers. You can achieve this by including separate Email Report widgets in the form with different report settings.

To access the Report Settings in Email Report widget:

Go to the Build Forms tab. Open the form containing the Email Report widget(s). Click on the Email Report widget. Click on Report Settings in the properties pane. After making any desired changes, click Save. Report Settings in the View Data or Dispatch

These settings are applied on a project-by-project and form-by-form basis. This means that you can set different templates and output type for different forms. It also means that you can set different templates and output type for the same form but in different projects.

To access the Report Settings in View Data or Dispatch tabs:

Go to the View Data or Dispatch tab. Select a project and form. Click on the Options menu. Select Report Settings. After making any desired changes, click Save. Settings Options

Please note that some Settings Options will be selectively displayed based on prior settings.

Emailing Subject Line: When emailing a report, specify if you would like the subject line to contain (1) the name of the form; (2) the record name; or (3) custom text.

When Emailing Report: When emailing a report, specify if report will be sent as a PDF or Excel file.

When Creating PDF File Use: Specify if PDF files will be created using the Standard Report Template or the Custom Report Template (see below for how to specify which custom template to use).

When Creating Excel File Use: Specify that Excel files will be created using Custom Report Template (currently, only the Custom Report Template option is available).

Custom Report Template Settings

Please note that the options in this section will be selectively displayed based on prior settings.

Select an Custom Report Template – Specifies the template file that will be used as the Custom Report Template (see the Creating a Custom Report Template section).

PDF pixels power inch – Use to adjust the resolution of PDF report. A larger value will result in clearer pages, but larger PDF files.

JPEG image quality in PDF - Use to adjust the quality of the JPEG image compression when creating a PDF report. A larger value will result in clearer pictures, but larger PDF files.

Auto-fit rows in PDF – Selecting this option will cause the row height to automatically adjust to the contents.

Add PDF page break after every Excel template file worksheet – If the Excel template file contains multiple worksheet tabs, then selecting this option will place a page break after each tab in the PDF file.

Standard Report Template Settings

This section is displayed only when “Use Standard Report Template” is selected.

Font – Specifies the font to be used. The “System Default” font should be used for most Latin-based alphabets. Specialty fonts should be selected for other alphabets (e.g., Cyrillic for Greek or Russian).

2 column format – The default layout is single-column. Selecting this option will format the report in two columns.

Hide empty fields – Hides any questions that have not been answered.

Shade headings – Shade the “caption” property of Label question widgets in the form.

Display from name – The default is to display the name of the form at the top of the report. Uncheck this option if you wish to hide it.

Display data_names – The default is to display the “caption” property of question widgets. Selecting this option will display the “data_name” property instead.

Display hints – Display the “hint” property of the question widgets in the form.

Display record_name – Display the system generated “record_name”.

Display date_created – Display the system generated “date_created”.

Display mobile_unit – Display the mobile number of the device that submitted this form.

Display Logo – Display a custom logo if one has been uploaded to your doForms website (see Branding section )

Display page numbers – Include page numbers at the footer of the PDF.

Show GPS coordinates on map – Display GPS coordinates (if any) on a location map which will be appended to the report. Use the “Auto-scale” option to let doForms select the best map scale that fits all the GPS points. Use the “Enter scale” option to manually specify a scale. 0 is lowest detail. 20 is highest detail. Highest detail maps may not be available for all locations.

Scale - Changing the scale determines the altitude that is used to set the map. This is the equivalent of zooming in or out which impacts the level of detail displayed. Always use Auto Scale if you have more than one GPS location in your form.

Page widget starts a new page – Creates a page break in the PDF report wherever a Page widget is inserted into a form.

Each loop in a Repeatable Section starts a new page – Creates a page break at the beginning.

Standard Report Template Fields

Select the fields contained in the form that you wish to be included in the report.

General Report Settings (PDF & Excel Reports)

Always populate defaults – Checking this option causes default values to be displayed in reports.

Resize images - Allows you to specify a reduced image size as a percentage of the original – 100%, 66%, 50%, 33% and 25%. This option allows you to better control the format of your reports, and it also allows you to reduce the size of the resulting PDF or Excel files. By resizing the image, your image will appear smaller in the PDF. We recommend reducing the size of your images if your from has many images to reduce the overall size of the PDF. -->