Aggregating data for reports is a crucial step in the reporting process. In this Connected Learning Path, we'll create a Wdata Query to consolidate our data, providing a clearer view of our overall data totals. This Query will subsequently be used to drive our reports.
| Primary Business Use Case | Derive meaningful insights such as calculating totals |
| Primary Learning Objective | Query creation and performing aggregation on a field |
| Prerequisites | Complete the CLP | Uploading Data to a Table |
Step 1: Create a Query
To aggregate our data, we will use Wdata Queries. First, we need to go through the steps to create the Query.
- From Wdata, click Create and Query
- On the far right tool panel, click Query Properties, if it is not already open
- In the Query name field, name the Query CLP Employee Detail Query
-
Click Save
-
For additional help, see Create a Query documentation
-
Step 2: Add Source
When building a Query, the first step is to specify the source of the data that needs to be generated. In our case, we are pulling data from our CLP Employee Detail Table. When the source is added, the Query will have access to the data that lives in the source, as well as the columns and structure of the data.
- Click New source
- Select Table
- In the Add Table data screen, select the CLP Employee Detail Table that was created during the CLP | Uploading Data to a Table Path
- Click Add
Step 3: Add Fields to Query
Now that our source has been identified, we need to tell the Query what data from the source data we want to see. We can pick and choose the information we need to fit our use case.
- Click the arrow next to the CLP Employee Detail source to twirl down and show all columns
- Drag and drop the following fields to the Fields area:
- DEPARTMENT
- COUNTRY
- FTE (full time equivalent)
Step 4: Create Calculations
Queries are powerful tools for aggregating data and displaying the totals needed for reports. In the Employee Detail Table, we want to see a total count of employees by department and country.
- Select the FTE field in the Fields area, opening the Field Properties panel on the right
- Check the Aggregation value and update to Sum Of to calculate a total of the field’s values
- In most cases, numerical values are automatically summed in Queries
- Click Apply
- For additional help, see Define Query Fields and Calculations documentation
Step 5: Sort Query Results
To make our Query results easier to read and understand, we will add a sort order.
- Navigate to the Sort tab
- Click and drag the Department field to the Sorted area
- Click and drag the Country field to the Sorted area below Department
Step 6: Save and Run Query
We will now save our Query to make it available for use in the future, and Run the Query to verify our results are accurate.
- Click Save
- Click Run Query
- When it completes, you will see the output in the Results area
To take it a step further, check out the next path: Run a Query and Export Results!