Submittable's Advanced Reporting tool works similarly to Submittable's standard reporting feature (included with all accounts) but provides powerful additional tools to consolidate and pull data from multiple forms simultaneously. Advanced reporting also enables users with levels 4 and 5 permissions to use formulas and see your report results with an array of visualization tools powered by Flexmonster Pivot Tables.
Start an Advanced Report
Select Reports > Advanced Reports.
On the Advanced Reports page, give your report a name.
Use the date range selection boxes to select a date range for the data you want to be included.
From the Forms dropdown list, select the forms from which you want to pull data. You can select from any of your existing forms that have submissions in them, and there is no limit to the number of forms that can be selected.
Choose from the selection options for Submitter Information, Form Attributes, and any other fields from your selected forms. Just as with Submittable's standard reporting tool, Submitter Information is taken directly from submitter profiles (i.e., First Name, Last Name, Email) and Form Attributes are standard identifiers from all forms (i.e., Submission ID, Status, Labels, etc.). Fields from your selected forms are comprised of the specific information requests within the form.
Merging Fields
If you have similar data from separate forms, Advanced Reporting enables you to merge fields across these forms in order to create a custom consolidated field within Advanced Reporting. For example, if you have fields in each separate form for Funds Received, you can combine these into one field in your report by selecting each of them and using the Merge tool. To do this:
Click on the Merge button and a dialog box will appear.
Give a name to your merged field, such as Approved Funding.
Select the fields in each form that you want to merge. In the example below, similar fields for this data have been merged. In the Grants form, the field is called Approved Funding, and in the Grant Application form, the field is called Amount Committed.
Click Merge at the bottom of the dialog box to merge the data from these similar fields.
A new column called Merged Fields. Approved Funding will appear in your report preview that shows the data from these merged fields.
Any Merged Fields will also appear in the column on the left, beneath the form data fields. You can click the pencil icon to the right of any Merged Field to edit your selections before producing your report.
Once you have selected and/or merged the data you want to analyze, click Generate Report. This pushes your chosen data points into the data organization and analysis stage, where you can customize the look and layout of your report. You will see a screen that looks like this, with a variety of customization options above your selected data:
After you've clicked on Generate Report, you can rearrange and delve into report data in numerous ways.
1. Change the ascending/descending order of any data column by clicking the arrow that appears when you roll over the top right of any column:
2. Rearrange columns by clicking and dragging column headers to the right or left to place them where you like:
3. Apply a variety of filters to your data. If you click on the gear icon in the upper left of any column you will see a dialog box:
If your column is using numbers you will see a Values button. Click on Values to filter your report according to values. To do this, enter a number value into the box on the right such as 10000 (see image below). Then select a rule to apply your filter. In the example below, the filter is set to search for results that have a value greater than or equal to 10000. The result will show only where an applicant requested $10000 or more. Other selections enable you to quickly filter for a selection of top-end values, bottom-end values, equal to or not equal to, and more:
Use Cases
To pull data from multiple forms:
Organizations can pull data from any number of forms by adding forms to a report via the dropdown. From there, you can create a custom report with the data you care about by clicking on specific form questions and attributes in the fields section. From here, click Generate Report and you will be taken to a new view where you can manipulate and drill into the data.
Many organizations need to pull an end-of-year or quarterly report to see the total amount of money they awarded. Organizations may look at the amount they awarded year-over-year.
Organizations can pull data from any number of forms by adding forms to a report via the dropdown. From there, they can create a custom report with the data they care about by clicking on specific form questions and attributes in the fields section. From here, click Generate Report and you will be taken to a new view where you can manipulate and drill into the data.
To merge data from different forms:
Many organizations currently use an internal field to track the amount that they award an applicant and they need to easily see how much they awarded in a single year. If an organization runs 6 programs each year, then they will want to be able to pull a report on the “awarded amount” field from across all 6 programs. Similarly, they may want to see how many people they served over the course of the year from all 6 programs.
If more than one form is added to a report, then the merge button will appear on the field component. Click “Merge” to select the columns you want to merge. You can select one field only from each form to merge together. After naming and merging, a new column will appear in the preview. You can deselect the merged column in the fields component or edit the merged column from the “Merged Fields” section in the fields component.
Screen Recording
Filter data
Use Case
An org might want to see all submissions from across multiple forms that have been awarded more than $10,000 and are from NYC and Chicago.
An org might want to see all submissions that have been declined from across all their programs this quarter only, from female applicants above the age of 60.
Description
Orgs can filter columns in a report by clicking on the gear icon in the column header.
Screen recording
Move columns
Use Case
After an org creates the report, they may want to reorganize the order of the fields when looking at the Flat Grid view.
Description
Click on the middle of the column header and drag it either left or right depending on where you would like it to land.
Screen recording
Filter Columns
Use Case
Orgs may want to sort the data in a column from descending to ascending order.
Description
Hover over the column header to reveal an arrow on the right side. Click on the arrow to sort the column from ascending to descending. Click again to sort from descending to ascending.
Screen recording
Pivot Tables
Use Case
Orgs want to be able to summarize, sort, reorganize, group, count, and total or average data stored in a database. It allows its users to transform columns into rows and rows into columns.
Description
Toggle between the flat view and compact view (pivot table) by clicking on “options” → “compact form” → “apply.” You can rearrange the layout, rows, and filters by clicking “fields” or clicking on the arrow icon in the upper right corner.
Screen recording
Visualizations
Use Case
Orgs want to be able to see a bar chart with all of the areas that they are funding (ie: Department of Agriculture, Tourism, Health, Art Education, etc.). Or they may want to see a pie chart of the total sum of grants paid by region.
Description
Click “Charts” and select a chart from the dropdown. You can toggle between the grid view and charts view by clicking “grid.”
Screen recording
Calculations
Orgs need a way to calculate the total amount requested or the total amount awarded. For example, an organization might want to filter their submissions to see Hispanic women under 20 years old in NYC who have been awarded in the last year and calculate that amount across many different forms. Or they may want to see the total amount requested by Males in NYC over the age of 60.
Description
You can see the grand total (count or sum) for any column by clicking “options” → “grand totals” → Show grand totals.
You can perform calculations on multiple columns. Click “Fields” → Click on the calculator icon next to “cancel.” Here you can name your new column and drag existing columns into the area below to perform calculations. In the screen recording below, I create a new column that adds the amounts from two other columns.
Screen Recording
Formatting Cells
Use Case
The way numeric values are formatted can be defined in a report. Orgs need to be able to present numeric values in the format that makes the most sense for their data. For example, an organization may want to display the amounts paid with a comma separator and $ sign.
Description
You can format specific columns by hovering over Format → Format Cells. There, you will select a value to format. You can then format that value column by alignment, value separators, decimal places, symbols, etc.
Screen Recording
Conditionally Format Cells
Use Case
Orgs need a way to format a cell or a range of cells based on specified criteria. For example, an organization may want to easily see all of the amounts that have been paid out that are over $10,000.00
Description
You can add conditional formatting to a cell by hovering over Format → Conditional Formatting. You will then click on the + to start a new designation. Then choose the value to format, choose the conditions, and then add stylized formatting to make the cells that you are formatting stand out.
Screen Recording
Export Data
Use Case
As an org, I want to be able to export my review data so that I can look at it outside of the Submittable platform and share it with colleagues.
Description
To export the report, you will hover over Export and then choose if you want to export the data to HTML, a CSV file, an Excel file, an image, or a PDF. You can also choose to print the report.
Screen Recording
Questions? Interested in adding Advance Reporting to your organization's account? Ask your account manager.