To aggregate and segment data based on specific criteria, you can create queries based on data stored in the Workiva platform as tables and spreadsheets. For example, you can create queries to analyze income by location or for year-over-year comparisons.
To create a query, you can:
- Define its criteria in a drag-and-drop Builder .
- Compose its definition as syntax, if comfortable with Structured Query Language (SQL) .
When you create a query, you define the sources and criteria of the data to include in its results:
- Fields, which provide the information to display in the query results
- Filters, to include only records that meet selected criteria, such as to focus on specific accounts, regions, or time periods
- Calculations, which return values based on multiple columns, such as to sum their amount or concatenate strings
- Relationships, to join related data from multiple sources
Step 1. Create the query
To create the query, start from scratch, or base it on an existing query.
- From Wdata Home, click Create , and select Query .
- From the Query Properties panel, set the query's properties:
- To help identify the query and its intent, enter its unique name and description.
- In Limit, enter the maximum number of records to return.
- To return only records with different values, such as to avoid duplicates, select Show only distinct rows.
- To specify who can view and collaborate on the query, click Permissions , and select each collaborator's access.
- Click Save .
- Edit the query's name and description as necessary.
- Select the folder to save the query in, and click Save Query.
To ease creation, you can save a copy of a query as the starting point for a similar query.
- Copy the existing query from itself or Home :
- From the query, click Save , Save As.
- From Home, select Copy from the query's menu.
- From the query, click Save , Save As.
- Enter the name, description, and location for the new query.
- Click Save Query.
- In the new query, click Permissions , and select each collaborator's access.
- Click Save .
Step 2. Select the query's sources
To select the data to use in the query, you include columns from tables, query results, and spreadsheets you have permission to access in the Workiva platform. From the Sources left panel, click New source, and select the data to include in the query.
If the query has multiple sources, on the Relationships tab, define how to combine—or join—their records.
Note: When you save the query, it automatically removes any sources that appear as Needs Relationship . To retain these sources, define their relationship with other sources.
Step 3. Add any parameters
To define values for the query's filters or calculations when the query runs, such as to slice-and-dice its data in real time, add parameters for their values. From the Parameters panel, you can:
- Add a new parameter and specify its possible values for when the query runs.
- Select a global parameter configured by the Workspace Owner.
Step 4. Define the query results
You can define a query's results in a drag-and-drop Builder or—if familiar with SQL syntax—a SQL editor.
Tip: To undo changes in either Builder or SQL Editor and return to the query's previously-saved version, click Reset . If you collaborate on the query with others, its last-saved version may not be your last save.
To define your query, we recommend the drag-and-drop Builder, especially if you aren't familiar or comfortable with SQL syntax.
- To specify data to include in the query results, select the columns under Sources, and click Add to Fields or drag them to under Fields.
- To browse the Sources panel, click Expand All or Collapse All to show or hide all columns.
- To find a specific source or column, click Show Filters and search by its name.
- After you add the query's fields, from the Field Properties panel, define the properties or calculation of each as necessary.
- On the Filters tab, create filters to set the criteria of the source data to include in the results, such as to focus on specific accounts or time periods.
Tip: To choose the criteria when you run the query, such as to slice-and-dice the data in real time, use parameter values with filters or calculations.
If you're familiar with SQL, click SQL and Edit SQL to compose the query's SQL statement in an editor.
Note: To help preserve your SQL syntax, the editor opens in a read-only status by default. Depending on the complexity of your query, you may not be able to retain the syntax if you click Builder after you start in the SQL editor.
While not every query includes every clause, each part of the SQL statement executes in a particular sequence:
-
FROMandJOIN, to determine the sources of the data being queried -
WHERE, to include only data that meets specific criteria -
GROUP BY, to summarize data based on common values -
HAVING, to include only data from aggregated rows that meet specific criteria -
SELECTorSELECT DISTINCT, to further specify which source data to include -
ORDER BY, to specify how to sort the data in the results -
LIMITandOFFSET, to specify how many—and which—records from the query to include
To ensure data integrity, the SQL editor supports read-only statements like SELECT, but not read/write statements like INSERT or DELETE.
To quickly reference a source column in the syntax, from the Sources panel, right-click it and select Add to Query.
- To browse the Sources panel, click Expand All or Collapse All to show or hide all columns.
- To find a specific source or column, click Show Filters and search by its name.
Note: To scope the query results to specific records, define the filter criteria as SQL syntax. To set a filter's value when the query runs, reference a parameter.
Step 5. Lay out the results
By default, the query results appear unsorted and flat, with all fields as separate columns like a table. To help clarify the data, you can sort the query results based on a field's data.
Tip: To further summarize and pivot the results, lay them out as a cross-tabulation with rows, columns, and values.
To sort the query results, on the Sort tab, move the fields to sort by to Sorted, and select whether to arrange the data in ascending or descending order.
To sort the query results, use an ORDER BY keyword, such as ORDER BY field_header ASC. To sort according to multiple fields, separate each with a comma; ORDER BY field_header1 ASC, field_header2 DESC.
Step 6. Verify and use the results
To view a query's results, click Run Query . If the query includes parameters, set their values, and click Run Query.
After you verify the query results appear correctly, use them throughout the Workiva platform:
- Create a view to analyze its data as a table or chart
- Connect the data to a spreadsheet or the table or chart of a document or presentation
- Export the data to a table or spreadsheet