Custom reports

This feature allows you to use spreadsheets in Excel 2007 (or higher) format (xlsx extension) to generate reports filled with form responses.

Instead of exporting data in a standard format, you can create custom Excel templates with markers (tags) that will be populated with data from form responses. This gives you greater flexibility to create reports tailored to your specific needs while maintaining formatting and visual elements.

Template patterns

HashData supports three different marker patterns for custom report templates:

1. SINGLE pattern: Used to export a single form response.
2. LIST pattern: Used to export multiple responses in list format.
3. FILTER pattern: Used when you need to filter responses based on specific criteria.

IMPORTANT: Each model can use only one pattern. In other words, it is not possible to mix the SINGLE, LIST and FILTER patterns in the same Excel file.

Syntax of accepted patterns

SINGLE pattern

The single pattern is used to generate a custom report based on a single selected response. Use double curly braces to insert specific field values into your Excel template:

{{form_element_field_name}}

For example, if you have an element in the response form with the "Field name" attribute equal to "sales_quantity", you would use {{sales_quantity}} in the Excel spreadsheet cell to be populated with the field value.

LIST pattern

Use square brackets with an index to insert values from multiple selected answers to populate the template. This pattern is enclosed in double curly braces, and begins with the keyword list, followed by square brackets with the index of the desired answer and the name of the form element field.

{{list[index].field_name}}

The index can be:

• A number (1, 2, 3, etc.) to refer to a specific answer in the list. Indexes always start at the value 1, and the number -1 can be used to refer to the last selected answer.
• The use of an asterisk (*) represents the dynamic index: the list index is automatically incremented for each line that contains the marker, making it easier to create markers in lines that are repeated in the spreadsheet.

Examples:

• {{list[1].full_name}} - Inserts the full name of the first selected response.
• {{list[*].full_name}} - Creates a row for each response, filling in the full name, using the dynamic index (*).
• {{list[-1].full_name}} - Inserts the full name of the last selected response.

FILTER pattern

Use this pattern to create filtered response lists based on field values, using the equality operator (=).

Multiple field filters can be created in the same report template. Each filter will generate a list of responses that meet the specified criteria, which can be used to populate the template.

This pattern is enclosed in double curly braces and begins with the keyword filter, followed by parentheses with the filter field and its value, the index of the response in the list of responses that meet the filter, followed by the name of the field to be populated:

{{filter(filter_element_field=value)[index].name_of_field_to_be_filled}}

Examples:

• {{filter(gender=male)[*].full_name}} - Creates a row for each answer that has the gender field equal to male, filling the full_name field of each filtered answer. The use of the dynamic index (*) automatically increments the index in each row that contains the marker.
• {{filter(department="Sales Department")[1].full_name}} - This marker is filled with the full_name field of the first answer in which the department field has the value equal to Sales Department.
• {{filter(department="Sales Department")[-1].full_name}} - Will be filled with the full_name field of the last answer whose department field has the value equal to Sales Department.

Managing custom reports

Adding a new custom report template

1. Open the form and then click on the "Settings" tab.
2. Scroll to the "Custom Report Templates" section.
3. Click on the "Upload File" button.
4. In the dialog that opens:

• Minimum number of responses: The minimum number of responses required for the template to automatically fill in.
• Maximum number of responses: The maximum number of responses that the template can process.
• Click on "Select File" to choose your spreadsheet template, in Excel 2007 or higher (XLSX) format. The maximum supported file size is 20 MB.
• Click on "Upload" to upload the file. The file format (SINGLE, LIST or FILTER) will be automatically detected during upload. If the file is invalid, an error message will be displayed.

How to add a custom report template

Editing a Custom Report Template

You can edit an already loaded template file. It is allowed to change the minimum and maximum number of answers, but it is not allowed to change the file itself (only download the model). In this case, you need to delete the template and upload a new file.

1. Open the form and go to the "Settings" tab.
2. Scroll down to the "Custom Report Templates" section.
3. Click on the desired template to edit it.
4. In the dialog that opens:

• Change the information you want.
• If you want to download the template file, click on the file name.
• Click "Submit" to apply the changes.

How to update information in a report template and download the template file.

Generating a filled custom report

1. Open the form and go to the "Results" tab.
2. Select the responses to be automatically filled in the custom report. IMPORTANT: the order in which the responses are selected (by clicking on the checkbox) will be the order in which they are filled in the form.
3. Click on "Generate custom report".
4. In the new dialog that will open:

• Choose the template.
• Click on "Generate".
• Download the filled file.

How to generate a custom report filled with selected responses.

The Excel file will be generated with all markers replaced by the data from the selected responses. Markers that are not replaced will be removed from the generated file. For example, if there are markers for 10 responses and only 5 are filled out, the remaining 5 will be removed.

Deleting a custom template

1. Open the form and go to the "Settings" tab.
2. Scroll to the "Custom report templates" section.
3. Click the trash can icon of the report template you want to delete.

Practical Examples

Example 1: Individual Certificate (SINGLE pattern)

Use Case: Issue personalized certificates to course participants.

Excel Template: Create a certificate design with bullet points for a single response:

Certificate of Completion

We certify that {{full_name}} successfully completed
the {{course_name}} course on {{completion_date}}.

Workload: {{workload}} hours
Final Grade: {{final_grade}}

________________________
Director's Signature

Custom Template Model Configuration:

• Minimum number of responses: 1
• Maximum number of responses: 1

The system will detect that the model is of the SINGLE standard.

Result: The system will generate a personalized certificate filled with the data of the response that was selected.

Example 2: Attendance List (LIST pattern)

Use Case: Create an attendance list for an event.

Excel Template: Create a table with headers and markers for multiple rows:

No.	Name	Email	Phone	Confirmation Date
1	{{list[1].full_name}}	{{list[1].email}}	{{list[1].telephone}}	{{list[1].confirmation_date}}
2	{{list[2].full_name}}	{{list[2].email}}	{{list[2].telephone}}	{{list[2].confirmation_date}}
2	{{list[3].full_name}}	{{list[3].email}}	{{list[3].telephone}}	{{list[3].confirmation_date}}

Alternatively, the dynamic index format can be used, with an asterisk, which will be incremented automatically:

Nº	Name	Email	Telephone	Confirmation date
1	{{list[*].full_name}}	{{list[*].email}}	{{list[*].telephone}}	{{list[*].confirmation_date}}
2	{{list[*].full_name}}	{{list[*].email}}	{{list[*].telephone}}	{{list[*].confirmation_date}}
3	{{list[*].full_name}}	{{list[*].email}}	{{list[*].telephone}}	{{list[*].confirmation_date}}

Template Configuration:

• Minimum responses: 1
• Maximum responses: 3

The system will detect that the template is of the LIST standard and will allow automatic filling with the data of the selected responses.

Result: The system will make the template with the attendance list automatically filled out available for download.

Example 3: Report by Department (FILTER Pattern)

Use Case: Generate a performance report, filtered by a specific department.

Excel Template: Create sections for different departments:

Marketing Department

Employee	Position	Performance Review
{{filter(department="Marketing")[*].full_name}}	{{filter(department="Marketing")[*].position}}	{{filter(department="Marketing")[*].performance_grade}}
{{filter(department="Marketing")[*].full_name}}	{{filter(department="Marketing")[*].position}}	{{filter(department="Marketing")[*].performance_grade}}
{{filter(department="Marketing")[*].full_name}}	{{filter(department="Marketing")[*].position}}	{{filter(department="Marketing")[*].performance_grade}}

Sales Department

Employee	Position	Performance Evaluation
{{filter(department="Sales")[*].full_name}}	{{filter(department="Sales")[*].position}}	{{filter(department="Sales")[*].performance_grade}}
{{filter(department="Sales")[*].full_name}}	{{filter(department="Sales")[*].position}}	{{filter(department="Sales")[*].performance_grade}}
{{filter(department="Sales")[*].full_name}}	{{filter(department="Sales")[*].position}}	{{filter(department="Sales")[*].performance_grade}}

Template Configuration:

• Minimum responses: 1
• Maximum responses: 10

HashData application will detect that the template is of the FILTER standard.

Result: The system will make available for download an Excel file with the completed template spreadsheet, separated by sections. In the example above, the first 3 responses from the Marketing department and the first 3 responses from the Sales department will be filled in. If the selected responses do not meet any of the filters (department equal to "Sales" or "Marketing"), the system will not fill in anything in the spreadsheet and will delete all markers from the spreadsheet.

Tips and Limitations

• Pattern detection: At upload time, the application will automatically detect which pattern your custom template uses: SINGLE, LIST or FILTER.
• Pattern consistency: A template can only use one type of pattern. In other words, SINGLE, LIST and FILTER cannot be mixed in the same Excel file.
• Maximum size: The maximum size for uploaded template files is 20MB.
• Supported format: Only .xlsx format is supported. Spreadsheets in Excel 2003 or earlier (.xls) format are not supported.
• Format preservation: Comments and additional formatting in your Excel file will be preserved.
• Special characters: Form field names do not accept special characters, such as accents, spaces or special characters. Use only letters, numbers, underscores and dashes.
• Backup: Always keep a local copy of your models.

Common problems and solutions

Problem	Possible Cause	Solution
Message suggesting file repair when opening the generated report	The XLSX format is a proprietary format, maintained by Microsoft®, and is not 100% guaranteed to be compatible with third-party application libraries and suites.	Unfortunately, compatibility issues with more advanced features may occasionally occur. Always check the automatically filled-in file that was generated by HashData.
Error message: "The uploaded file is not a valid template."	Inconsistent marker syntax	Check if all markers follow the same pattern. It is not possible to use more than one type of pattern in the same template. More details in Accepted patterns syntax.
Data is not appearing in the generated custom report	Incorrect field name	Confirm that the field names in the spreadsheet exactly match the "Field Name" attribute in each form element.
Responses appearing in the wrong order	The order in which the responses are selected will be the order in which they are replaced in the template spreadsheet	In the "Results" tab of the form, you must select the responses exactly in the order in which they are replaced in the spreadsheet. Or, try selecting them all at once. More details in Generating filled custom reports
Error loading template	File too large	Reduce the file size to less than 20MB.
Date data does not appear correctly	Incompatible cell formatting	Make sure the cell is formatted as text before inserting the marker.
Filtered responses not appearing	Incorrect filter criteria	Make sure the value in the filter exactly matches the value in the responses, and also that the field name is consistent with the attribute configured in the form element.

---