7 - Filter Data in a Report
Last updated 26/03/2023
There are two ways to build queries to filter the results you require in Five, you can use:- Five's Query Builder; or
- Five's SQL Editor.
Navigate to Queries
1. Select Visual in the menu.2. Select Queries in the sub-menu.
Figure 1 - Queries menu item
Add the Daily Volume Query
1. Click the Add Item button.2. Type Daily Volume in the Title field.
3. Type DailyVolumeQuery in the Action ID field.
info
All action IDs in Five need to be unique. This is why Query is added to DailyVolume in the Action ID field, so when we build a chart via Five's Chart Wizard in the next chapter these action IDs will be unique.
4. Click in the Query field to open Five's Query Builder.
Figure 2 - Add the Daily Volume query
5. Click the Add Table Button.
Figure 3 - Add Table button
6. Select Stock.
Figure 4 - Add the Stock table
7. Click the Add Table button again.
8. Select StockPrice.
Figure 5 - Add the StockPrice table
9. Drag both tables to be positioned on the builder.
10. Double click the PriceDate field in the StockPrice table.
11. Double click the StockCode and Name fields in the Stock table.
12. Double click the Volume field in the StockPrice table.
Figure 6 - Add the PriceDate, StockCode, Name, and Volume fields
13. Click the lookup icon in the Sort field for the PriceDate field and select Ascending.
14. Click the lookup icon in the Sort field for the StockCode field and select Ascending.
Figure 7 - Sort field
15. Type BETWEEN '2020-01-01' AND '2020-01-31' in the Criteria field.
Figure 8 - Sort field
tip
You can click the Run button in the Query Builder app bar to see the results of the query!
16. Click the Save button in the Query Builder app bar.
Figure 9 - Save the query
17. Click the Fields tab.
Figure 10 - Fields tab
info
The fields used in your query will be listed here and can be edited if required.
18. Select the StockPrice.PriceDate record.
Figure 11 - StockPrice.PriceDate record
19. Click the lookup icon in the Display Type field and select _Date
Figure 12 - Edit the StockPrice.PriceDate record
20. Click the Save button in the form app bar.
Figure 13 - Save the StockPrice.PriceDate field
21. Click the Save button in the form app bar above the list.
Figure 14 - Save the Daily Volume query
warning
Even if you do not need to edit any fields, the Fields tab must be clicked before you save the form for your query fields to stay in the list.