Connect pivoted data to spreadsheets with crosstab queries

Need to connect pivoted data based on query results to a spreadsheet? First lay out a query as a crosstab, and then connect it to a sheet.

Step 1. Create the query

To specify the data to pivot, create a query in Wdata. You can create a query with a drag-and-drop builder or, if comfortable with Structured Query Language (SQL), a SQL editor.

When you create a query, you specify:

• The sources to pull from, such as Workiva spreadsheets or Wdata tables
• Relationships between the sources and how to join their data
• The fields from those sources to use as rows and columns in the pivoted data
• Any filters to apply to focus on specific data

Step 2. Set up the crosstab layout

By default, the query's results appear in a flat, tabular layout. To pivot the results, use a cross-tabular—or crosstab-layout:

1. From the query's Layout panel, select Enable Crosstab.
   
2. Specify how the query fields should appear:
   • Under Rows, leave fields to appear vertically.
   • Under Columns, move fields to appear horizontally.
   • Under Values, move fields to use with calculated formulas, such as DISTINCTCOUNT or SUM.
3. To set the order in which records appear, specify how to sort the results:
   • In Builder, on the Sort tab, move the fields to sort by to Sorted, and select whether to view their data in ascending or descending order.
   • In SQL, use an ORDER BY keyword, such as ORDER BY field_header ASC.
4. After you set up the crosstab results, save and run the query to verify the layout of the pivoted data.

Step 3. Connect the query to a sheet

To use the pivoted data in a spreadsheet, connect the query to a sheet. When you create this connection, it displays the query results in the sheet and locks its cells to ensure data integrity. To update the sheet with the latest query results, you can refresh the connection.

Note: You can connect up to 100 queries to a spreadsheet.

To create the connection:

1. In the spreadsheet, open or add the sheet to contain the pivoted data.
2. From the Connections panel, under Incoming, click Add connection, Wdata query.
3. Select the query with the pivoted data, and click Next.
4. Set any parameters for the query, and click Connect.