Asking the data a question

Xperios Essentials, section 4 of 7. What a query is, why parameters matter, and how to get one that does not exist yet.

What you will be able to do

• Run a query and read its results without needing anybody's help
• Use parameters to make one query answer your particular question
• Reshape, filter and export a result without changing the query
• Know how to get a query that does not exist yet

Step 1 of 6

A query is a saved question

A query is a saved, reusable question, usually written in SQL, that returns a set of rows. The Query page lists them grouped by area and by kind, and you search or filter by name like any other list. Run opens the results in the same tab. Run In New Window opens them in a new one, which is the easy way to compare two sets of values side by side.

Use the arrow keys to move between steps.

A query is a saved question — press Escape or click to close

1. A query is a saved question

   A query is a saved, reusable question, usually written in SQL, that returns a set of rows. The Query page lists them grouped by area and by kind, and you search or filter by name like any other list. Run opens the results in the same tab. Run In New Window opens them in a new one, which is the easy way to compare two sets of values side by side.

2. The library is central, so ask us

   Queries are maintained centrally. New ones are added to the Master set and sent out to every organisation, so there is no Add on your own query list, and a query that came from Paritor is locked. No edit, no delete. If the query you need does not exist, or you want one with one more column, ask us under Assistance. It is written once and everybody gets it.

   Say the name of the query you were using and what decision you are trying to make. The columns matter less than the decision.

3. Parameters are where the flexibility lives

   Parameters are where the flexibility lives. A query with a date range and a centre answers twenty different questions. The same query with the centre written into the SQL answers one. So when you run something, read the parameters it asks for before you accept them. That is the query bending to your question, rather than you needing a new one.

4. The results are yours to reshape

   Once it has run, the results are a table, and everything you do to them happens in your browser. Sort by clicking a heading. Choose which columns to show. Group, pivot, chart, filter, export to Excel. None of it changes the saved query and none of it goes back to the database. The one thing you cannot change from here is the question itself.

5. Be told, rather than remembering to look

   Two things worth knowing. You can set a threshold alert on a query's row count, so Xperios tells you when the number crosses a line rather than you remembering to check. Alerts work on queries that take no parameters. And you can save a result as a dashboard item or as a report, which is how a question you keep asking becomes something you simply glance at.

6. Check Syntax, and why it exists

   If you ever watch a query being built, in Master, the SQL has a Check Syntax button beside it. It validates the statement against the database before it is saved, which catches a mistyped column far more cheaply than running it does. Worth knowing the button exists, because the error it prevents is the one people assume is a broken query.

The recap

• A query is a saved question that returns rows. Run opens it here, Run In New Window opens it alongside.
• The query library is maintained centrally. A query from Paritor is locked - no edit and no delete - and if the one you need does not exist, ask us and every organisation gets it.
• Parameters are what let one query answer twenty questions. The same query with the value written into the SQL answers one.
• Everything you do to the results happens in your browser - sorting, columns, grouping, pivoting, charting, exporting. The question itself is the one thing you cannot change there.
• An alert on a query tells you when its row count crosses a threshold, for queries that take no parameters.
• Check Syntax validates the SQL against the database before saving, which catches a mistyped column cheaply.

Now take the test

Six questions, in Xperios under Training. Everything it asks is in The recap above.

If you want the detail

• Queries — the list, Master queries, and asking for one
• Building a query — including Check Syntax
• Running a query — exporting, alerts, favourites
• Working with results — columns, grouping, pivots and charts