Advanced use of Data Explorer workbooks
In both the Form submissions and dataset data section of the Monitor tab and the Your data section of the Export tab, there is an Advanced mode button that allows you to enable a set of more powerful Data Explorer tools. Meant for more expert users, advanced mode allows you to:
- Configure multiple workbooks. By default, each form has one workbook on the Monitor tab and one on the Export tab. In advanced mode, you can configure as many additional workbooks as you like, tailoring each to a particular view, team, or workflow.
- Attach datasets to workbooks. Advanced mode also allows you to attach server datasets to workbooks, so that you can supplement incoming form data with earlier listing data, QC results from outside systems, and more.
- Download and upload workbook definitions. Finally, advanced mode includes Download and Upload buttons that allow you to export and import workbook definitions. These definitions are Excel spreadsheets, similar to form definitions and edited in a similar way; instead of defining fields in a form, however, these workbook definitions define summaries in a Data Explorer workbook.
Configuring multiple workbooks
Once you enable advanced mode for a particular form, you will be able to add, edit, and delete an arbitrary number of workbooks for that form. These workbooks will all share the same form data, but you can configure field and relationship summaries – as well as filters and exclusions – independently for each workbook. That way, you can tailor each workbook to the appropriate view, team, or workflow. The only real restrictions are that each workbook must have a title and unique ID, and you must retain the original "default" workbook. The default workbook will be used, for example, if somebody using the review and correction workflow clicks a shortcut to open the Data Explorer with all submissions awaiting review.
Attaching data to workbooks
When using the Data Explorer to review and explore incoming form data, it's sometimes helpful to merge in additional data from other forms or outside systems. For example, you might have data from an earlier listing survey, publicly-available demographic information for geographical areas, or the results of statistical checks done using your chosen analytics software. In advanced mode, you can link that data to your Data Explorer workbooks, so that it's available for generating field and relationship summaries.
To attach data to any Data Explorer workbook, first load it into a server dataset. Then, once you've enabled advanced mode for the relevant form, click the Attach button to attach that dataset to the appropriate workbook. When you do, you'll just need to specify a few options in order to configure merging and save the attachment.
All SurveyCTO needs to know is which form field links to which dataset column. For example, you might have a form field named districtid that contains a unique district ID, and a matching dataset column named dist. To be able to merge data successfully, the values in your chosen form field should match with values in your chosen dataset column, and your chosen dataset column should uniquely identify rows in your dataset. In the example, the district ID values in the form's districtid column should match the values in the dataset's dist column, and the dataset should only include one row per dist.
Once you've attached data to your workbook, SurveyCTO will automatically merge that data in whenever it can. So continuing with the same example, say that your districts dataset is named "districtdata" and that, in addition to the dist column, it has a population column. Whenever there's a form submission with a value in the districtid column that matches the dist value in a dataset row, the value from that row's population column will be merged in as districtdata.population. In effect, it'll be like the incoming form data also included the district population, in a field named districtdata.population.
In fact, all attached dataset columns will be merged into the Data Explorer workbook, as if they were fields in the form itself. When you add field or relationship summaries, the dataset fields will be added to the bottom, underneath the form fields, as additional options. And when you view an individual submission's details, the merged-in dataset data will also appear down at the bottom (if a match was found in the dataset data). The merged-in data essentially extends the form data, providing additional information that might be helpful in exploring or reviewing incoming data.
That's all there is to it. Just two minor restrictions worth mentioning: you can't merge using a form field that's inside a repeat group, and you can't merge using date or time fields.
Working with workbook definitions
Once you've enabled advanced mode for a form, you'll find Download and Upload buttons available to export and import workbook definitions from the Monitor and Export tabs. These definitions are Excel spreadsheets, similar to form definitions and edited in a similar way; instead of defining fields in a form, however, these workbook definitions define summaries in a Data Explorer workbook.
We don't expect you to manually create workbook definitions the way you might be used to creating form definitions, however. Rather, we expect you to create new workbooks in your web browser, using the Data Explorer. Then, once you've created a workbook that you like, you can use the Download option to export it to a spreadsheet.
Once you've downloaded a workbook definition, you can archive it, share it with colleagues, or make copies. You can also edit it, of course. One common use case is to create a workbook for one version of a survey (perhaps for round one or country one), export it, tweak it a little bit (perhaps for round two or country two), and then import the tweaked version for another form or even on another server.
Each workbook includes the following worksheets:
- summaries: This worksheet defines all of the field and relationship summaries in your Data Explorer workbook, as well as how they are grouped. Each row represents either a summary or the beginning or end of a group. Tip for copying: be sure to correct the field names in the field and field_2 columns to match with your target form.
- global_filters: This worksheet defines global filters, which are used to focus or narrow the summaries shown in your Data Explorer workbook. For example, you might filter for "F" values in your gender field, in order to focus all summaries on results for female respondents.
- global_exclusions: This worksheet defines global exclusions, which are used to omit particular submissions from the summaries shown in your Data Explorer workbook. For example, if you decide that a particular submission is invalid or an outlier, you might choose to exclude it from the workbook overall.
- settings: This worksheet defines overall settings for your Data Explorer workbook, including the title and unique ID. Tip for copying: be sure to update the title and unique ID whenever you make copies of a workbook.
- summaries-help, global_filters-help, global_exclusions-help, and settings-help: These worksheets have some quick-reference help on the rows and columns that make up the summaries, global_filters, global_exclusions and settings worksheets.
The best way to learn about the format of Data Explorer workbook definitions is to export a workbook and take a look, consulting the four help worksheets as needed.