 |
 |
Custom Query Input
Contents
- Introduction
- What is the Custom Query Input
- A Normal Query
- A Simple Tutorial
- Customising your Query Further
Introduction
 |
Please Note! Within this document, the HRT application is used as an example, however other AIS applications also support the Custom Query. While some features and images shown here may be specific to HRT, the basic principle of using the Custom Query will remain the same for other AIS applications. |
|
Please Note! In this document the Personnel report from HRT will be used as an example, using the EP division of CERN and the ALICE Experiment. Due to the security restrictions placed around the data in HRT you may be unable to access any data by following the instructions laid out in the examples. Since access in HRT is defined in part by your organic unit, we suggest substituting your own organic unit for the ones used in the example. No actual data found in HRT appears in this document. |
What is the Custom Query Input?
The Custom Query Input (CQI) is a means of combining the contents of various other input objects in order to create complex, precise queries. It allows you to combine data with boolean logic operators AND, AND NOT and OR to refine your query further. In its initial blank state the Custom Query Input looks like this:
|
Please Note! This is a very powerful input, but its use can have a dramatic impact upon a query's running time, as well as upon the number of records retrieved. The Custom Query Input should only be used where the desired results cannot be obtained in any other way. |
A Normal Query
Under normal conditions a HRT query is constructed from user input typed into the form objects on the report page to restrict the search to various criteria. In HRT these criteria refer to information about a person, such as which Organic Unit they work for, what their status at CERN is, which Experiments they work for etc.
 |
Consider this trivial example. This report will return all CERN personnel who are Staff Members in EP Division and who work on the ALICE experiment. This report works fine at this level of simplicity, but suppose you want to find all Staff Members and Fellows in EP who work for ALICE? This is not an unreasonable request, but it cannot be done with HRT in this fashion since you can only choose one item at a time from the Status input. To retrieve this data one must use the CQI. |
A Simple Tutorial
Of Boolean Logic...
Let's try and build the query we wanted in the Introduction above; to see the Personnel information for all Staff Members and Fellows in EP Division who work on the ALICE experiment. We must construct the query piece-by-piece so that the CQI can understand properly what we want. To better understand this let's take a closer look at exactly what we want.
 |
This Venn diagram shows how the criteria you have selected overlap to produce the data required, represented by the two white areas at the centre of the four ovals. By default, HRT joins data together with an AND condition (one could think of a query as a sentence. "Find everyone who is a Staff Member AND works for EP Division AND works on the ALICE Experiment."). AND conditions are represented on the Venn diagram where two or more ovals overlap. The problem arises in our case because the Staff Member and Fellow ovals do not overlap, so we cannot use an AND condition to find our data. There is no-one at CERN who is both a Staff Member and a Fellow at the same time. This situation cannot exist. We must use an OR condition to find the Fellows and the Staff Members at the same time. |
The data we require is shown by the two white areas on the Venn diagram.
Let's think about what constitutes these white areas. The left-most
region can be written;
Everyone who is a Fellow AND works for EP Division AND works on the ALICE Experiment
The right-most region can be written
Everyone who is a Staff Member AND works for EP Division AND works on the ALICE Experiment
If we simply join these sentences together, we can express the two white areas at once. Remember that since the zones do not overlap we must join them with an OR condition.
Everyone who is a Fellow AND works for EP Division AND works on the ALICE Experiment
OR
Everyone who is a Staff Member AND works for EP Division AND works on the ALICE Experiment
Building a query with the CQI works in exactly the same way. You might find it easier at the beginning to visualise your problem as a series of sets. If your sets overlap then join with an AND condition, otherwise use an OR. Now that we have visualised our problem, lets put the data into the CQI.
Adding Data to the CQI
In the previous section we visualised our problem as sets, and derived two sentences joined with an OR condition to represent our problem. To build a query to solve the problem we simply build each sentence using the HRT form and then add it to the CQI.
In its initial state the CQI is empty, awaiting data to be added. Only one of the controls will be unghosted letting you simply Add data to it.
Fill the relevant data into the Advanced form, taking care not to press Enter after dealing with each criteria. Pressing Enter may execute the query, and we do not want to do this until the query is completed. For example, type EP into the Organic Unit input, select Staff Member in the Status input, and type ALICE into the Experiment input. Once all this data is entered press the Add button on the CQI.
The CQI should start to look like this:
Now we can see the the CQI has taken our criteria and constructed a sentence out of it, similar to the one we derived above. Notice also that the control buttons have become unghosted. You may notice that the sentence might include something along the lines of and At Cern 'Y'. This is just a hidden condition of some HRT reports, and is nothing to worry about. It is simply there to ensure that only people who are currently at CERN are included in your results.
The CQI is now ready to accept our second sentence. This can be constructed by simply changing the Status Input to Fellow, since the other inputs should already be in the correct state. When you have done this click on Add [Or] on the CQI. The second sentence should appear in the CQI as follows:
Ignore the additional details which have appeared on the CQI for now, because HRT is ready to run the report, so hit the Retrieve button at the bottom of the main form, and Presto! You should see before you a list of all Staff Members and Fellows at CERN who work for EP Division on the ALICE experiment.
Customising Your Query Further
Correcting Mistakes
Erasing Rows
If you make a mistake when entering data into the CQI then do not despair. The CQI has recently been improved to allow you the chance to correct mistakes without having to clear the entire query and start again.
There are two places a mistake within the CQI will lie; in the criteria or in the condition. If a mistake is made in the criteria then you will have to delete it, and re-enter it. You will notice a series of Delete buttons down the left-hand side of the CQI. There will be a delete button for each row, except where rows have been joined together. To delete a row which contains a mistake simply press the delete button which corresponds to it. If you have more than two rows pay attention to the conditions which join them together. Deleting a row may affect how multiple data sets are joined.
Changing the boolean conditions
If a row is not joined to its neighbours with the correct condition then it is a simple matter to change it. Down the right hand side of the CQI you will notice a column marked Change which contains hyperlinks of boolean conditions. To change the condition for a row simply click on the condition you'd like to change it to.
Resetting the CQI
If you feel that a query is too riddled with mistakes to be worth correcting, or if you'd simply like to start building a new query you can press the Clear Query button on the CQI to reset it. This action may be advantageous over pressing the form's Reset button since it will maintain the state of the other input objects, and will not return you to a simple form, if this form has one.
Joining Data Sets together
|
Consider once again our earlier example. We used this sentence to describe it;
A shorter way of writing this sentence might be;
|
One could enter data into the CQI so that this second sentence is used instead. AFter doing so it may look a little like this.
Although this second sentence might appear to mean the same thing you might be surprised to learn that running this query would retrieve a list of all persons in EP Division, who work on the ALICE Experiment and are Staff Members as well as a list of all the Fellows at CERN, irrespective of their division and experiment. The reason why this occurs is because the conditions joining the criteria together are executed in a strict sequence and this affects the overall result.
Consider the following simple mathematical sum: 2 x 3 + 4 = 14
In maths we were taught at school to calculate the multiplications before the additions, hence the answer to the above sum should be 10, and not 14 as written. To correctly derive 14 from the example we should rewrite it thus; 2 x ( 3 + 4 ) = 14. The CQI is performing in exactly the same way. It performs all its AND conditions before doing the ORs. Hence all the EP/ALICE Staff Members were found, and then all the Fellows found from our example sentence.
A more correct way of writing the shorter sentence would be:
Everyone who works for EP Division AND works on the ALICE Experiment AND ( is a Staff Member OR is a Fellow ).
The inclusion of the brackets would yield the correct results. In order to bracket off the Staff Member and Fellow conditions within the CQI simply click on the join hyperlink alongside the Staff Member criterion. This link joins a criterion to the one immediately beneath it and encapsulates the condition joining them together.
You can see how brackets have appeared around the Status Code criteria now, and that the condition joining them has moved to the left alongside them. You can still change this condition using the hyperlinks on the right, and you can "unjoin" the criteria by pressing the unjoin hyperlink on the left. If you delete criteria that are joined together then both criteria will be erased.