A general overview of how to use Queries
Queries are a great way to dig deeper into your business data and answer the question you have about what your data is telling you.
The Configur query builder provides you with the ability to:
- Perform calculations
- Group your data
- Apply filters
- Sort your data
- Create super columns
By combining some or all of these components you can create a view of your data which removes the noise and allows you to focus on the right information.
To build your query, you'll construct a sentence that explains what data you want to display. Once you've created your query you can then add it to portals so the rest of your team can access it.
Accessing Queries
Queries can be found by clicking on the Query icon in the main navigation bar
You'll then be taken to the Query landing page where you'll just need to click the + Create Query button to get started.
Adding a name & description
When you create your Query, you'll want to give it a name and maybe even a description so you can easily find it later.
When you first start building your Query it will be untitled. To give your Query a name and a description, click on the 'Edit' link in the header at the top of the page.
Selecting your datasets
Before you can start querying your data you'll need to select the datasets you want to use.
To add your dataset, simply click 'Select datasets' and your data will load, ready for you to start your query.
You can create queries with up to two datasets in Configur but you will need to create a datalink between these two datasets before they can be used together. If your datasets have already been linked, they will appear in the list of options when you click the 'Add another dataset' button, next to the first dataset you have added.
Building a Query will have no impact on the dataset you use as the basis for the query, so you don't need to worry about breaking your data.
To remove a dataset, simply click on the X next to the dataset. NOTE: Removing a dataset will clear your query.
Adding Query Components
Query components are added in three ways:
Add Groups, Filters or Sorts by clicking the large blue + button
Add calculations by clicking on the name of your dataset next to the 'Show me' heading
You can also add Super columns by clicking the Add Super Column button just above the data table.
Ways to manipulate your data
You can manipulate your data by adding Query Components to your query sentence:
- Add calculations
- Group your data
- Filter your data
- Sort your data
- Add super columns
Adding calculations
You add calculations by clicking on the dataset name next to the 'Show me' text. If you have added two datasets, you'll it will say 'Both datasets'.
Once you've clicked it, a list of options will appear where you can select which calculation you'd like to use.
Because calculations need your data to be grouped, it will then ask you to choose which column you want your data to be grouped by. Once you've selected a group, the calculation will be applied.
For more on calculations, read our article Queries - Using Calculations
Grouping your data
The main purpose of groups is to apply calculations to your data but you can add them to your query without also adding a calculation.
When you group your data, it will only show the unique values within the column you have selected.
To add a group, just click on the blue + button and select 'Grouped by' and then choose a column.
To add more groups, simply click on the small + button next to your group.
To learn more about groups, read our article Queries - Grouping Your Data.
Filtering your data
Adding filters in Queries works in exactly the same way as anywhere else on the Configur platform. Quite simply, you can use them to show or remove certain parts of your data.
When you add a filter to your query you'll first select the column you want the filter to apply to, the type of filter you want to apply (greater than, less than, equals, doesn't equal etc.) and then choose a value.
Add filters by clicking the large blue + button and selecting 'Only where'.
To learn more about Filters, read our article Queries - Filtering Your Data.
Sorting your data
Sorting your data does pretty much what is says on the tin. It arranges your data in a particular order. You might want to see your data in order of the highest value first, or in alphabetical order.
To add a sort, simply click the large blue + button and selected 'Sorted by'. Your sort will be applied in ascending order first, but you can click the small icon button next to the column name to switch to descending.
If you add more than one Sort, it will work in the order that they were added. If we sorted by Total Cost and Qty, it would first sort the data by Cost, and then by Qty.
Adding Super columns
Super columns can be added to your Query to manipulate your data in other ways.
There are 3 types of Super columns:
- Number - Use basic formulas to create new data using static values and column data
- Text - Transform text columns into a new format
- Date - Extract or reformat date elements from a date column
Super columns are new columns that get added to the Query. They are not added to the original dataset and they don't change the existing data in anyway.
To add a Super column, simply click on the 'Add Super column' button just above the data table, give it a name and select a Super column type to get started.
To learn more about creating Super columns, read Queries - Adding Super columns.
Other ways to manipulate your data
There are also other ways to control what data is displayed in your query:
-
Show or hide columns
-
Reorder the columns being displayed
Show or hide columns
If you've got specific columns you don't want to display in your query, simply click the 'Show/hide columns' button and select the columns you want to hide. As the creator of the Query, you will still see them in the data table but they'll be faded out but when other users view the Query in a portal, those columns won't be visible.
To make those columns visible again, simply select them from the list.
Reorder Columns
You can also select the order in which your columns are displayed. This particularly useful if you have added a number of measures and groups and want things to be shown in a particular order.
To reorder your columns, just click the 'Reorder columns' button and drag the columns into the order you want them displayed.
Rename Columns
You can also rename your columns. Renaming a column has no impact on the original dataset.
All you have to do is click on the column header and type in the new name. Any changes you make to the column name in the data table will be reflected in the Query sentence, so both data and sentence will match.
