The primary imagined use of an uploaded Excel report is to provide automated distribution of survey field updates. To create such an update, start with a Topline report. Using the Field Selector, pick the survey questions and variables that should appear in the report; make sure to reveal all options, and set order to original. Then, export the report as a spreadsheet by clicking the Excel button under the Actions menu.
When the report is downloaded, open it. The default configuration of such a report is to include each survey field on a separate sheet, with each sheet labeled by the question it contains. Counts and percents for each field option are shown in a table, and there is a chart below. This layout does not need to be preserved. Sheets can be relabeled, tables moved around and placed onto a single sheet, charts tweaked/reformatted/deleted.
Cells containing survey data, however, need to be treated with care. There are comments in those cells which contain JSON strings. These strings are how IntelliSurvey knows where to put the values that populate the report. They can be moved, but they should not be deleted if they are to be included in the report.
Note: Users may need to manually adjust their spreadsheet software's settings to show comments.
Manipulating strings
The JSON strings in the data cells can be manipulated. The following parameters can be added or adjusted:
| Parameter | Inputs | Description |
|---|---|---|
| field_id | Actual question ID e.g., Q1, Q5R9, etc. | Question ID as defined in the survey source and reflected in reports. |
| id | Action option IDs, e.g.,1,2,44, etc. | Option ID(s) as defined in the survey source and reflected in reports. |
| type | count, percent, quota | Toggles between count, percent, or quota. By default, exports will contain fields for each. |
| filter | Any logical statement defining a data cut, data cut ID as reported in Data Cuts applet | The data cut which ought to apply to this field. |
field_id
Other than changing the existing field ID of an existing cell or creating a new string altogether, there is no other customization possible with this field.
Some examples:
| String | Description |
|---|---|
|
{"field_id":"Q5","id":"2","type":"count","filter":"$status eq 'C'"} |
Q5 is the question field |
| {"field_id":"Q7R9","id":"1","type":"count","filter":"$status eq 'C'"} | Q7R9 is the question field |
id
By default, id has a single input that corresponds to a field's option ID. However, it is possible to add additional option IDs so that the cell reports on values for multiple options. To do this, use a comma-separated string within the quotations of the ID input.
Some examples:
| String | Description |
|---|---|
|
{"field_id":"Q19","id":"1,2,3","type":"count","filter":"$status eq 'C'"} |
Retrieves counts for options 1,2, and 3 for field Q19. |
| {"field_id":"Q14R21","id":"3,4","type":"count","filter":"$status eq 'C'"} | Retrieves counts for options 3 and 4 for field Q14R21. |
Filter
If a filter is not specified, the field will default to the data cut selected when the saved Excel export is downloaded.
To add a filter, simply append a comma and "filter" : "filter_here" to the string inside the curly braces. You'll need to do this for every cell in a sheet to which the filter should apply.
Some examples:
| String | Filter Description |
|---|---|
|
{"field_id":"Q1","id":"1","type":"count","filter":"$status eq 'C'"} |
Completes Only |
| {"field_id":"Q1","id":"1","type":"count","filter":"anyChecked($oq,Q1_QUOTA)"} | Overquotas from Q1 |
| {"field_id":"Q1","id":"1","type":"count","filter":"$version == 43"} | Survey version is 43 |
Tip! Use the Expression Builder feature or Data Cuts applet to build filter syntax if you are uncertain of the construction.
To use a data cut ID as a filter, navigate to the Data Cuts applet and note the ID you wish to use:
Then, use that ID:
| String | Filter Description |
|---|---|
|
{"field_id":"Q1","id":"1","type":"count","filter":"19"} |
Data cut with the ID of '19' |
Type
To toggle between counts and percents, modify the type parameter:
| Counts | Percents |
|---|---|
| {"field_id":"Q1","id":"97","type":"count"} | {"field_id":"Q1","id":"97","type":"percent"} |
Caution: Uploaded sheets are not parsed for errors.
Comments
0 comments
Please sign in to leave a comment.