Skip to Content

Wiki source code of 03a. Query

Last modified by Ross Beck on 06/11/2025, 14:24
Show last authors
1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 [[CXAIR >>doc:Technical Documentation.CXAIR.WebHome]]allows users to explore their data using ‘natural language’ queries as well as rapidly filtering results by clicking the on-screen values or generating different [[Filters>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2b\. Filters.WebHome]]. By making the process of querying simple, the user can retrieve the data they require without having to rely on running pre-canned reports or SQL queries.
6
7 To access the Query functionality, click **Query** at the top of the screen.
8
9 = {{id name="Interface"/}}Interface =
10
11 Searches are executed in specifically organised Search Engines. The available Search Engines are displayed in the **Search within:** drop-down list above the query bar. Upon selection, contained Indexes can then be specified. Restricting a search to a specific index allows more control over the results that are displayed.
12
13 Using the default settings, the number of results returned is displayed under the query bar, with links to the subsequent pages of results below. If [[Compact View>>doc:Technical Documentation.CXAIR.User Guide.6\. User Preferences.WebHome||anchor="Compact View"]] is enabled in the [[User Preferences>>doc:Technical Documentation.CXAIR.User Guide.6\. User Preferences.WebHome]], the number of results are displayed above the query bar.
14
15 Navigate between different pages by clicking the specific page number or by clicking the arrows to display the next or previous pages of results. The number of results per page can be configured in the [[User Preferences>>doc:Technical Documentation.CXAIR.User Guide.6\. User Preferences.WebHome]].
16
17 Click the **Hide Header** arrows below the solution icon to hide the query bar and provide more screen space for the search results. Click the arrows again to reverse this action.
18
19 == The Query Bar ==
20
21 To conduct a search, enter the required information into the query bar and click **Search**. Alternatively, press the **Return** button on the keyboard. Click **Clear** to remove the current query.
22
23 With the **Enable Voice Searching** option enabled in the [[User Preferences>>doc:Technical Documentation.CXAIR.User Guide.6\. User Preferences.WebHome||anchor="Query"]], searches can also be conducted via voice control by clicking the **Microphone** icon and speaking 'Search <search term>'. By speaking 'Load <report name>', reports can also be loaded using the voice control functionality.
24
25 When the **Enable Report Searching** is enabled in the [[User Preferences>>doc:Technical Documentation.CXAIR.User Guide.6\. User Preferences.WebHome||anchor="Query"]], it is also possible to load reports directly from the query bar by typing the report name and clicking a result.
26
27 = Search Terms =
28
29 There are a multitude of different search terms available to effectively navigate data:
30
31 == Exact Match ==
32
33 To search for an exact match, simply type the required text in the query bar. The term must match a complete word within the value of any row or column and the results will highlight where the match was made in the record.
34
35 For example, searching for 'bread' would return 'Bread', 'Bread Mix', 'Wholemeal Bread' but not 'Breaded Chicken'.
36
37 == Combining Search Terms ==
38
39 Combining terms provides a more focused set of results. Combining two or more separate words will return results that only contain every separate word entered.
40
41 == Searching Fields ==
42
43 To search within a specific field, type the field name followed by a colon before entering a query.
44
45 This query is case sensitive and the spaces must be replaced with underscores (**_**). The required search term must be prefixed and suffixed with quotes (**“”**). The following format must be used: **<field_name>:“<value>”**.
46
47 == Wild Card Searches ==
48
49 Wild card searches allow users to query data using fuzzy matching.
50
51 By replacing a single character with a **?** and multiple characters with ** * **, the search will look to replace the wild card characters with a character in the search string.** * ** can only be used at the beginning or end of a search term.
52
53 This is especially useful when not all of the information is known to the user. For example, replacing the last three digits of a postcode with **???** will return results that match the exact string all other possible variations based on the wild card characters.
54
55 == Fuzzy Searches ==
56
57 Typing the tilde (**~~**)** **symbol at the end of a single word term allows users to perform a fuzzy search. This will return matches that are similar to the word typed.
58
59 To specify the level of similarity required, type a value between 0 and 1 after the search. A value closer to 0 will produce fuzzier results and if no value is typed, the default parameter, 0.5, is used.
60
61 == Proximity Searches ==
62
63 This allows users to find words that are separated by a specific number of words in a document. Type the tilde (**~~**) symbol at the end of a phrase followed by the number of words that separate the term.
64
65 == Range Searches ==
66
67 Range searches allow users to match documents whose field values are between the lower and upper value specified by the search. Range searches can be inclusive or exclusive of the upper and lower bounds and results are sorted automatically.
68
69 Use square brackets (**[]**) to perform inclusive range searches and use curly brackets (**{}**) to perform exclusive range searches.
70
71 The following format must be used:
72
73 **<field>:[“lowervalue” TO “uppervalue”]** for inclusive range searches.
74
75 **<field>:{“lowervalue” TO “uppervalue”}** for exclusive range searches.
76
77 Brackets can also be mixed to provide an inclusive or exclusive search at the start or end of the range search.
78
79 == {{id name="Username Searches"/}}Username Searches ==
80
81 Using the **user.name** syntax in conjunction with a corresponding username field, saved queries can dynamically filter the results based on the user who is running the query.
82
83 For example, with a field called **user** in the data, running **+user:"{user.name}"** will return a different result depending on who is running the report, allowing a single report to be written and shared across a wide user base.
84
85 Please note the CXAIR username must match the username present in the data being queried.
86
87 = Grouping Syntax and Queries =
88
89 Using parentheses (**( )**), it is possible to group clauses to form sub-queries. This is especially useful when controlling Boolean logic in a query. For example, grouping a query in brackets then using the **AND** operator would only return results that matched the sub-query and the additional string.
90
91 Effective use of parentheses to segregate search terms is paramount to ensuring the components are applied in the correct order. For example:
92
93 **(-Field1:" " OR -Field2:" ")** will produce very different results to **(-Field1:" ") OR (-Field2:" ")**. This is due to how the underlying query engine combines search terms, with the second example displaying the correct syntax to combine the two separate entities.
94
95 = Column Comparisons =
96
97 To compare values between two columns, the following syntax is supported:
98
99 (% style="width:736px" %)
100 |(% style="width:163px" %)**Function**|(% style="width:346px" %)**Description**|(% style="width:224px" %)**Example** **Usage**
101 |(% style="width:163px" %)=|(% style="width:346px" %)Equal to|(% style="width:224px" %)+FieldA=FieldB
102 |(% style="width:163px" %)<>|(% style="width:346px" %)Not equal to|(% style="width:224px" %)+FieldA<>FieldB
103 |(% style="width:163px" %)<|(% style="width:346px" %)Less than|(% style="width:224px" %)+FieldA<FieldB
104 |(% style="width:163px" %)>|(% style="width:346px" %)Greater than|(% style="width:224px" %)+FieldA>FieldB
105 |(% style="width:163px" %)<=|(% style="width:346px" %)Less than or equal to|(% style="width:224px" %)+FieldA<=FieldB
106 |(% style="width:163px" %)>=|(% style="width:346px" %)Greater than or equal to|(% style="width:224px" %)+FieldA>=FieldB
107
108
109
110 = Advanced Date Queries =
111
112 The Query functionality has the ability to parse date values using the system date, allowing users to specify an offset value from a particular field as well as other derived values such as **TODAY**, **NOW**, **MONDAY** etc. **YESTERDAY** can be derived by using the query **TODAY-1**, for example.
113
114 This functionality allows the creation of self-updating queries and reports. For example, creating a report that only displays information from the last seven days.
115
116 == {{id name="DPS"/}}Date Parsing Syntax ==
117
118 The following syntax is available when performing advanced date queries:
119
120 (% border="1" cellspacing="0" class="GridTable1Light-Accent12" style="border-collapse:collapse; border:solid #90c5f6 1.0pt; width:593px" %)
121 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="background-color:#1eb1ed; background:#1eb1ed; border-bottom-width:90c5f6; border-bottom:solid #59a9f2 1.5pt; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; width:444.45pt" width="593" %)(((
122 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
123 (% style="color:white; height:1pt; line-height:115%" %)**Point in Time**
124 )))
125 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
126 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
127 (% style="height:11.5pt; line-height:115%" %)NOW
128 )))
129 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
130 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
131 (% style="height:11.5pt; line-height:115%" %)TODAY
132 )))
133 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
134 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
135 (% style="height:11.5pt; line-height:115%" %)MONDAY
136 )))
137 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
138 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
139 (% style="height:11.5pt; line-height:115%" %)TUESDAY
140 )))
141 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
142 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
143 (% style="height:11.5pt; line-height:115%" %)WEDNESDAY
144 )))
145 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
146 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
147 (% style="height:11.5pt; line-height:115%" %)THURSDAY
148 )))
149 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
150 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
151 (% style="height:11.5pt; line-height:115%" %)FRIDAY
152 )))
153 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
154 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
155 (% style="height:11.5pt; line-height:115%" %)SATURDAY
156 )))
157 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
158 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
159 (% style="height:11.5pt; line-height:115%" %)SUNDAY
160 )))
161 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
162 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
163 (% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_DAY
164 )))
165 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
166 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
167 (% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_DAY
168 )))
169 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
170 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
171 (% style="height:11.5pt; line-height:115%" %)FIRST_MINUTE_OF_DAY
172 )))
173 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
174 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
175 (% style="height:11.5pt; line-height:115%" %)LAST_MINUTE_OF_DAY
176 )))
177 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
178 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
179 (% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_WEEK
180 )))
181 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
182 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
183 (% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_WEEK
184 )))
185 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
186 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
187 (% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_WEEK
188 )))
189 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
190 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
191 (% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_WEEK
192 )))
193 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
194 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
195 (% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_MONTH
196 )))
197 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
198 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
199 (% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_MONTH
200 )))
201 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
202 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
203 (% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_MONTH
204 )))
205 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
206 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
207 (% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_MONTH
208 )))
209 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
210 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
211 (% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_QUARTER
212 )))
213 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
214 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
215 (% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_QUARTER
216 )))
217 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
218 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
219 (% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_YEAR
220 )))
221 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
222 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
223 (% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_YEAR
224 )))
225 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
226 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
227 (% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_YEAR
228 )))
229 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
230 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
231 (% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_YEAR
232 )))
233 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
234 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
235 (% style="height:11.5pt; line-height:115%" %)FIRST_SECOND_OF_FISCAL_YEAR
236 )))
237 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
238 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
239 (% style="height:11.5pt; line-height:115%" %)LAST_SECOND_OF_FISCAL_YEAR
240 )))
241 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
242 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
243 (% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_FISCAL_YEAR
244 )))
245 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
246 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
247 (% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_FISCAL_YEAR
248 )))
249 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
250 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
251 (% style="height:11.5pt; line-height:115%" %)FIRST_DAY_OF_FISCAL_HALF_YEAR
252 )))
253 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
254 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
255 (% style="height:11.5pt; line-height:115%" %)LAST_DAY_OF_FISCAL_HALF_YEAR
256 )))
257 (% style="height:1.0pt" %)|(% nowrap="nowrap" style="background-color:#1eb1ed; background:#1eb1ed; border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
258 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
259 (% style="color:white; height:1pt; line-height:115%" %)**Differentials**
260 )))
261 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
262 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
263 (% style="height:11.5pt; line-height:115%" %)SECONDS
264 )))
265 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
266 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
267 (% style="height:11.5pt; line-height:115%" %)MINUTES
268 )))
269 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
270 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
271 (% style="height:11.5pt; line-height:115%" %)HOURS
272 )))
273 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
274 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
275 (% style="height:11.5pt; line-height:115%" %)DAYS
276 )))
277 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
278 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
279 (% style="height:11.5pt; line-height:115%" %)WEEKS
280 )))
281 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
282 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
283 (% style="height:11.5pt; line-height:115%" %)MONTHS
284 )))
285 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
286 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
287 (% style="height:11.5pt; line-height:115%" %)QUARTERS
288 )))
289 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
290 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
291 (% style="height:11.5pt; line-height:115%" %)YEARS
292 )))
293 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
294 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
295 (% style="height:11.5pt; line-height:normal" %)YEAR_SAME_WEEK
296 )))
297 (% style="height:11.5pt" %)|(% nowrap="nowrap" style="border-bottom-width:90c5f6; border-left-width:1.0pt; border-right-width:1.0pt; border-style:solid; border-top-width:90c5f6; border-top:none; width:444.45pt" width="593" %)(((
298 (% style="margin-bottom:0cm; text-align:left; padding:0cm 5.4pt 0cm 5.4pt;text-align:left" %)
299 (% style="height:11.5pt; line-height:115%" %)WORKINGDAYS (When enabled by the system administrator)
300 )))
301
302 Please note that using the **SECONDS** differential over thousands of records will increase the processing time.
303
304 When using the **TODAY** query, note that the current system date and a time of 00:00:00 is used, while using the **NOW** query would use the current system date and time.
305
306 === Examples ===
307
308 If **TODAY**=Thu Jul 09 00:00:00 BST 2019
309 Then **TODAY+1HOURS**=Thu Jul 09 01:00:00 BST 2019
310
311 If **NOW**=Thu Jul 09 10:24:20 BST 2019
312 Then **NOW+1WEEKS**=Thu Jul 16 10:24:20 BST 2019
313
314 If **MONDAY**=Mon Jul 06 00:00:00 BST 2019
315 Then **MONDAY+1DAYS**=Tue Jul 07 00:00:00 BST 2019
316
317 If **LAST_SECOND_OF_DAY**=Thu Jul 09 23:59:59 BST 2019
318 Then **LAST_SECOND_OF_DAY+1HOURS**=Fri Jul 10 00:59:59 BST 2019
319
320 If **FIRST_DAY_OF_MONTH**=Wed Jul 01 00:00:00 BST 2019
321 Then **FIRST_DAY_OF_MONTH+1WEEKS**=Wed Jul 08 00:00:00 BST 2019
322
323 If **LAST_DAY_OF_YEAR**=Thu Dec 31 00:00:00 GMT 2019
324 Then **LAST_DAY_OF_YEAR-12WEEKS**=Thu Oct 08 00:00:00 BST 2019
325
326 If **TRANSACTION DATE**=Thu Nov 30 00:00:00 GMT 2019
327 Then **MONDAY(Transaction_Date)-7DAYS**=Thu Nov 23 00:00:00 GMT 2019
328
329 If **TRANSACTION DATE**=Thu Nov 30 00:00:00 GMT 2019
330 Then **+Transaction_Date:["FIRST_DAY_OF_QUARTER" TO "LAST_DAY_OF_QUARTER"]**
331 Will return all dates between 01/10/2019 and 31/12/2019
332
333 If **TODAY**=Wed Jul 10 2019 and the working day calendar in the **Locale** tab in **Settings** is set between Monday and Friday
334 Then **TODAY-5WORKINGDAYS**=Wed Jul 03 2019
335
336 If the current date is Mon Oct 25 2021
337 Then **+Week_Commencing:"FIRST_DAY_OF_WEEK-1YEAR_SAME_WEEK"**
338 Will return 19/10/2020, the first day of the same week in the previous year. Running this syntax for a week commencing field would return the values for the equivalent week in previous years.
339
340 Please note that when specifying the date used as a reference, the value must be wrapped in the TODAY syntax in the following format:
341
342 **<Date Field>:"TODAY(<DATE>)<Differential>"**
343
344 For example: **+Transaction_Date:"TODAY(20/09/2021)-1DAY"**
345 Will return all data for 19/09/2021
346
347 When using the YEAR_SAME_WEEK differential for a specific values, the value must be wrapped in the NOW syntax in the following format:
348
349 **<Date Field>:"NOW(date value)-1YEAR_SAME_WEEK"**
350
351 For example: **+Transaction_Date:"NOW(07/04/2019)-1YEAR_SAME_WEEK"**
352 Will return all data for 08/04/2018, the equivalent day of the same week in the previous year.
353
354 = Dynamic Views =
355
356 When configured by an administrator, Dynamic Views can be used to filter using distinct counts for selected fields using the following syntax:
357
358 **+DYNAMICVIEW: "<Dynamic View Name>"**
359
360 For example:
361
362 **+DYNAMICVIEW: "Distinct Employees"**
363
364 = Escaping Special Characters =
365
366 Escaping a character allows users to enter a character without it applying its function in the Query screen when performing field-specific searches.
367
368 The special characters include:
369
370 **+ - && || ! ( ) { } [ ] ^ " % ? : \**
371
372 For example to escape **\**, use **~\~\** before the desired character.
373
374 To escape the other special characters, use **\** before the desired character. They can also be used with wildcards, for example searching for a quote " character someone in a string would be: "*\"*"
375
376 = Boolean Operators =
377
378 Boolean operators allow terms to be combined through the **AND**, **OR**, **NOT** and **REPLACE** logic operators.
379
380 By default, queries will default to using the **AND** operator. To change the operator used, click the **Cog** icon above the search results and click **Show Boolean Operators**. The available operators will now appear above the search results and can be changed by clicking the desired option. To dismiss these options, click the **Cog** icon and click **Hide Boolean Operators**.
381
382 The selected operator can be overwritten by what is typed into the query bar. For example, if the operator is set to **NOT** but a search is conducted with **+**, **AND** will be performed.
383
384 == OR ==
385
386 The **OR** operator links two terms and finds a matching document if either of the terms exist in a document.
387
388 == AND ==
389
390 The **AND** operator matches documents where both search terms exist anywhere in the text of a single document.
391
392 == + ==
393
394 Using this operator ensures that any term after the **+** symbol must appear somewhere in a field of a single document.
395
396 == NOT ==
397
398 The **NOT** operator excludes documents from the search results that contain the term after **NOT**.
399
400 == - ==
401
402 Using this operator ensures that any documents that contain the term after the **-** symbol is excluded from the results.
403
404 == REPLACE ==
405
406 The **REPLACE** operator will replace all current search terms, excluding filters, with the newly selected search term.
407
408 = Inverting a Query =
409
410 Once a search has been performed, the **Invert Query** option can be used to quickly perform the exact opposite search, the equivalent of including a **NOT** in front of the search term.
411
412 To invert the current search, click the **Cog** icon then click **Invert Query**. The query bar will have a red border when this option is active.
413
414 = Strict / Lazy Syntax =
415
416 While in the Query screen, it is possible to switch between **Strict** and **Lazy** syntax for the current Index in the current session using the option exposed when the **Cog** icon is clicked.
417
418 With **Lazy Syntax** applied, if a field used within a query no longer exists or the name is incorrect (such as a spelling mistake or the field has changed name), the field is ignored and is not used as part of the query. With **Strict Syntax** applied, data is not displayed when there are incorrect field names in the query syntax. Instead, an error message is displayed.
419
420 = Adding Results to a Query =
421
422 To add a value displayed in the results panel to the current query, click it. The search will be automatically executed, allowing rapid filtering of large datasets.
423
424 = Sorting Data =
425
426 If enabled by the administrator, it is possible to sort the **Query** screen results using any available column.
427
428 Click the arrows below the column heading and choose between **A-Z**, **Z-A** or **No Sort**. The arrows will turn blue and highlight the sorting direction once a selection has been made.
429
430 = Selecting Unique Column Values =
431
432 To select one or more unique values from a column, first click the relevant column heading. This will display a dialog box containing the unique column values.
433
434 To display the record count for each unique value, click the **Show Counts** button. If the current search or filter restricts the values that are displayed in the unique column count, click **Show Others** to display all values restricted by the search.
435
436 With the **Show Extra Calculations On Headers** option enabled in the [[User Preferences>>doc:Technical Documentation.CXAIR.User Guide.6\. User Preferences.WebHome||anchor="Query"]], additional information for numeric fields is displayed. The minimum, maximum average and sum is calculated from the displayed values.
437
438 Click the **…** button to manually enter values or select the required values from the list and click **Apply** to complete the process.
439
440 = External Query Links =
441
442 [[CXAIR>>doc:Technical Documentation.CXAIR.WebHome]] can provide links from data in the results panel to other applications.
443
444 If external query links have been enabled by the administrator, an **Open** button will be linked to each row of data in the results panel.
445
446 = Display Fields =
447
448 To add, remove or change the order of the columns displayed in the table view, click the **Cog** icon then click **Display Fields**.
449
450 Move fields between the **Available** and **Selected** boxes using the left and right arrows and use the up and down arrows to order the selected fields.
451
452 Click the **Set as Default** checkbox to save the display options as a default setting. Click **Apply** to save changes, or click **Close** to discard changes.
453
454 = Editing Index Components =
455
456 As an administrator user, the following options allow fast access to each index component in the admin area.
457
458 Click the **Cog** icon to access the **Edit Search Engine**, **Edit Index**, **Edit Data Source Group** and **Edit Data Source** options. The **Build Data Source Group** option will build the current Data Source without having to navigate to the admin area.
459
460 = Selecting Rows =
461
462 To select rows, click the **Cog** icon then click **Show Row Select Options**. A checkbox will then appear next to each row to allow easier viewing of records when scrolling horizontally. Hide these checkboxes by clicking the **Cog** icon then click **Hide Row Select Options**.
463
464 = Index User Preferences =
465
466 Click the **Cog** icon then **Index <Index Name> Preferences** to access the user preferences for the currently loaded Index.
467
468 To automatically run a search term when the Index is selected and the query bar is empty, enter the required term in the top textbox. Alternatively, click **Select Query** to load a saved query.
469
470 Use the **Display Query Results as** drop-down list to specify the default Query layout, as detailed below, and specify whether to display columns that do not contain any values using the **Display Blank Columns** drop-down list.
471
472 Click the **Mark All Reports as Read** option to mark all new reports as read for the Index.
473
474 = Query Layout =
475
476 There are a number of different layout options for the results panel in the Query screen. Their availability is configured by the administrator.
477
478 == Table ==
479
480 The default layout, displaying rows of data in the style of a worksheet.
481
482 == List ==
483
484 Displays a summary of the data in a list format.
485
486 == Unstructured ==
487
488 Used for indexes containing unstructured data such as documents, this view allows users to see the text in a view of wrapped paragraphs with header and title information similar to a table structure. This view will recommend other search terms based on the term entered into the query bar.
489
490 == Fixed List ==
491
492 Another view for unstructured data, this view provides a web-style search results summary and will display each section in a uniformed structure.
493
494 == HTML ==
495
496 Displays data using HTML formatting.
497
498 == {{id name="Word Cloud"/}}Word Cloud ==
499
500 Displays the data as a series of words, with the size of each word representing the relative amount of records for each value.
501
502 === Word Cloud Configuration{{id name="Word Cloud Configuration"/}} ===
503
504 To alter the word cloud options, click the **Cog** icon in the control panel located above the word cloud.
505
506 The word cloud window view is divided into two windows by default. Use the **Window Width** slider to adjust the size ratio. When set to the maximum, only a single word cloud is displayed. Type a value into **Window Height** textbox to control the size of the display in pixels. To specify options for individual windows, use the **Left Window** and **Right Window** tabs.
507
508 Type a required value in the **Maximum number of words to display** textbox to limit the number of words to display per page and specify the **Minimum font size** and **Maximum font size** options to customise the size range of the displayed text.
509
510 The **Rotation** drop-down list provides the option to rotate the words by a specified angle in degrees while the **Random** option will display fields in random directions and positions rather than in uniformed directions.
511
512 Enable the **Percentage of Total** option to size words based on a percentage of all data, and enable the **Stop Words** option to exclude the words configured by the system administrator.
513
514 To hide the contents of the query bar when viewing the word cloud, click the **Hide search terms** checkbox and to set any changes as the default configuration, use the **Set as default** radio buttons.
515
516 Click **Apply** to save changes, or click **Close** to discard any changes.
517
518 ==== Control Panel ====
519
520 The control panel, located above the word cloud, is used for a variety of options.
521
522 To change the field used in a word cloud window, select the required value from the **Field** drop-down list. Use the icons located on the right of the panel to toggle between one and two windows and to select a variety of charts and colours.