Appearance
Building a Query
Queries are built by Paritor, not on your own database
The query library is maintained centrally and sent out to every customer, so on your database there is no Add button, and a query that came from Paritor is locked - no Edit, no Delete, and no Save as Report. That is deliberate while the central set is still being added to, so that everyone gets the same corrected queries rather than drifting copies.
If the query you need does not exist, ask us for it. It is then written once and everybody gets it.
What you cannot do is change the query. Almost everything else is still yours, and the rest of this section is about that.
What you can do with a query you did not write
| Run it, answering any parameters it asks for | The parameters are where most of the flexibility lives - dates, a centre, a service type |
| Work with the results | Choose columns, sort, filter, total |
| Group, pivot and chart | Turn rows into a shape you can read |
| Send a batch email | Email everybody in the results |
| Send an SMS | The same, by text |
| Set an alert | Be told when the results cross a threshold |
| Put it on a dashboard | As a number, chart or table |
| Feed it to Excel or Power BI | Live, refreshing data outside Xperios |
In other words: the question is fixed, but who you ask it of, how you read the answer and what you do next are not.
How a query is built
On the Master database - where the library is written - a query has four tabs.
SQL is the statement itself. Check Syntax validates it against the database before saving, which catches a mistyped column far more cheaply than running it does. Process queries have no SQL of their own; they run built-in logic instead.
Parameters are the prompts somebody is asked before the query runs. These are what make one query serve many purposes, so they are worth more thought than the SQL often gets - a query with a date range and a centre answers twenty questions; the same query with the centre hard-coded answers one.
Description is a short name, a description, and a longer write-up of what the query is for. This is how somebody finds the right query out of hundreds, so it earns its keep. A query nobody can find is a query nobody uses.
Details holds the housekeeping: tags, author and sharing.
Writing a query that lasts
Two things make the difference between a query that serves for years and one that is quietly wrong:
- Ask for the dates rather than assuming them. "This academic year" written into the SQL is right until September.
- Say what it is for in the description, in the words somebody would search for. "Pupils with no diary" is found; "TuitionApplicant check 3" is not.
