Get started with queries

With queries, you can compile and slice-and-dice data from multiple sources for analysis and reporting. In this example, you'll combine trial balance data with account and entity dimension tables to analyze transactions for each account and entity.

Note: If you haven't already, review Get Started with Tables to create the tables used in this exercise.

Depending on your familiarity with structured query language (SQL), you can create a query using a drag-and-drop query builder or a syntax editor; in this example, you'll use Builder .

Step 1. Determine your data sources

Before you start your query, consider the data you need to include for specific analysis or reporting requirements. Your query can include data you access in your Workiva workspace as tables or spreadsheets. In this case, you'll use the three tables created in the earlier exercise:

• TB Data, for the trial balance information
• DIM FS Mapping, for the financial account mapping
• DIM Entity, for the entity mapping

Step 2. Create the query and add its data

Now that you know what you want to include, create the query and add the tables' data:

1. From Wdata Home, click Create , and select Query .
   
2. From the Sources panel, click Add Sources , and select Add table data.
   
3. Select the tables to include—in this case, all tables in the Get Started Demo folder—and click Add.
   

Note: From the Sources panel, you can select from all tables or spreadsheets you are at least a Viewer of.

Step 3. Create relationships between the sources

Since your query contains multiple sources, create relationships to show how to tie their data together. As you may recall from when you created the tables, the trial balance data table includes columns that map to the dimension tables' accounts and entities.

1. Select the Relationships tab.
   
2. Under Sources, expand the tables, and move the columns that map between them to their corresponding boxes on the Relationships tab:
   
   
   • Move ACCOUNT_ID from both TB Data and DIM FS Mapping as the first relationship. 
   • Move ENTITY_ID from both TB Data and DIM Entity as the second relationship. 

   Tip: To find a column under Sources, click Filters and search for its name. 

3. For each relationship, select how to combine—or join—its tables' data. In this case, to include all data from the data table on the left and only corresponding data from the dimension table on the right, select Left Join for both.

Step 4. Apply filters to the query

To narrow the query results to only records that meet specific criteria, add filters from the Filters tab. In this example, you can apply filters to include only transactions from 2019 to Q1 2020. Depending on your data and reporting requirements, you can do this multiple ways—as start and end date filters, or by period and fiscal year.

Start and end date filters

To add start and end date filters, move PERIOD_ENDING from TB Data to the Filters tab twice, and enter the criteria for each filter from the Field Properties panel:

• To include transactions from 2019, for the first filter, select >= and, from the Properties panel, enter January 1, 2019 and click Apply.
• To include transactions from 2020, for the second filter, select <= and, from the Properties panel, enter March 31, 2020 and click Apply.

Period and fiscal year filters

To filter by period and fiscal year:

1. Move FISCAL_YEAR from TB Data to the Filters tab twice, and then FISCAL_PERIOD from TB Data once.
2. To indicate to include the 2019 and Q1 2020, set the value for each filter from the Field Properties panel:
   
   
   • To include transactions from 2019, for the first FISCAL_YEAR filter, select =, enter a value of 2019, and click Apply.
   • To include transactions from 2020, for the second FISCAL_YEAR filter, select =, enter a value of 2020, and click Apply.
   • To include only Q1 transactions, for the FISCAL_PERIOD filter, select <=, enter a value of 3, and click Apply. This returns transactions from the first three months—or first quarter—of the fiscal year.
3. To apply the FISCAL_PERIOD filter to only 2020 transactions for Q1 2020, update the filter string to {1} OR {2} AND {3}.
   
   This returns transactions that meet either the first filter—FISCAL_YEAR=2019—or the other two filters combined—FISCAL_YEAR=2020 and FISCAL_PERIOD<=3.
   
   

Parameter values

With these filters, the query will always only include transactions from 2019 and Q1 2020. To extend the use and shelf-life of this query, use variables—known as parameters—to set the filters' values dynamically when the query runs. For example, rather than the static fiscal year values, you can include parameters that ask for the previous and current years when you run the query.

For example, for the first FISCAL_YEAR filter:

1. On the Filters tab, select the filter's value of 2019.
2. From the Field Properties panel, click Create New Parameter next to Value.
   
3. Enter a unique name to help identify the parameter, such as PY or Previous_Year.
   

   Note: Parameter names can include only alphanumeric characters and underscores; no spaces, dashes, or special characters.

4. For Filter type, select the data type of the values. For example, select Integer to ensure only whole numbers for years.
5. Enter a default value of 2019, and click Create.
6. For Value, select the new parameter, and click Apply.

Add parameters for the remaining filters:

• For the second FISCAL_YEAR filter:
  • Enter a name of CY or Current_Year.
  • Select a filter type of Integer.
  • Enter a default value of 2020.
• For the FISCAL_PERIOD filter:
  • Enter a name of Current_Period.
  • Select a filter type of Integer.
  • Enter a default value of 3.

Step 5. Select and sort the query's fields

Now that you've joined the query's sources and narrowed the scope of its data, select the information to include in the results:

1. To select information to include in the results, move its column from the Sources panel to the Fields tab.
   
   In this case:
   • FISCAL_YEAR and FISCAL_PERIOD from TB Data, to analyze monthly or quarterly activity
   • ENTITY_NAME from DIM Entity, to enable breakdown by entity rather than consolidated reporting
   • FINANCIAL_STATEMENT_LINE_ITEM and DISCLOSURE_LINE_ITEM from DIM FS Mapping, to enable breakdown by account mapping and line items
   • VALUE from TB Data, to enable rollup summaries of the values
2. From the Field Properties panel, adjust columns' headers or types or apply any aggregations as necessary. For example, to summarize the values based on the various dimensions, select value on the Fields tab, and, from the Field Properties panel, ensure its Aggregation is Sum of.
3. On the Sort tab, select how to sort the data in the results. For example, to sort by fiscal year and entity, move fiscal_year and entity_name from Unsorted to Sorted, and select whether to sort their data in ascending or descending order.
   
4. To verify the results appear as you'd like, click Run Query , and accept the default parameter values.

Step 6. Save the query

To save the query:

1. Click Save .
   
2. Enter a unique name and description to help identify the query, such as Summary TB Report.
3. For Folder, select the same Get Started Demo folder as its table sources.
4. Click Save Query.

Next steps

After you create your query, you can:

• Create a view of its results to visualize the data as a chart or pivot table with drill-down data.
• Connect its results to a spreadsheet to drive reporting elsewhere in the Workiva platform.