Creating a Custom Report Template

Follow

The examples below show a Custom Report Template and the corresponding report generated by doForms. Although the example shows an Excel file result, the Custom Report Template can be used to generate either Excel or PDF files.



Custom Report Templates are created using Excel. These template files are simply Excel worksheets that have been “Saved as… Excel Template”. 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.
 
But to understand how to build Custom Report Templates in doForms, you need to understand how the “data_name” property of the questions in your forms are used to “map” data values into a report.

{data_name} Tags

Data_names are unique identifiers that you set up in the Properties of each of the questions in your form. These unique identifiers are used throughout the doForms data collection and data management process to organize your data as shown below.

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:

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

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.

Have more questions? Submit a request

Comments

Powered by Zendesk