Tutorial 7

This tutorial assumes you have the knowledge gained from working with the previous tutorials. After this tutorial you will have the knowledge to add calculated fields that perform aggregations on the children (ie: 2nd grid) of a parent row in the primary grid. These aggregations including counting, totalling and averaging, as well as obtaining the lowest or highest values in the child grid.

Perform the following steps:

• If the Query Designer Tool is already running, please close it down. Invoke the NuVu Query Designer from the NuVu Query program group from the windows Start menu. The main NuVu Query Designer window will be displayed, and a welcome dialog window will also be displayed that shows recently saved/opened queries. We are going to create a open a new query from the previous query (tutorial6), so double-click on the ‘tutorial6′ item that should be displayed inside the Welcome dialog window (if you have successfully performed the previous tutorial and saved it as specified in that tutorial with a name of ‘tutorial6′). The original query will open inside the Designer. We need to now save this as ‘tutorial7′ before we start working with the query, so click on the File->Save As menu item and advise a new Query name of tutorial7 when the save properties are displayed.Click the  button to save the query with the new advised name.
• Click the  (calculated fields) button on the toolbar to invoke the calculated fields dialog window.
• Click the  (add item) button on the update panel near the top of the screen. This will create a new empty row into the calculated fields grid and position you on the fieldname column.
• Advise the details as shown below: Once the details have been advised, click on the expression editor which is located at the bottom of this screen so that the cursor is positioned within that editor.
• Examine the ‘Formulas’ section of the screen. There is an ‘Aggregates’ section, so click that to expose the various formulas that belong to this section. The function of interest is the ‘Count’ function so double-click that function to automatically copy it down into the expression editor. This will paste the ‘Count’ function into the expression editor, with the required parameter highlighted.
• Our calculation is going to simply count the number of children (Orders) that exist for each parent (Customer), so all we have to do is double-click on ANYfield of the Order table and this will then be inserted into the Count function within the expression editor. An example is shown here:
• Click the  (Post changes) button located at the top of the screen to save your new calculated field.
• Click the  button to save all changes to the calculated fields. The calculated fields dialog window will close.
• Click the  button to start execution of the query. A dialog window will appear briefly indicating the various steps that are being performed. Once the query results have been retrieved, this dialog window will disappear and the results will be shown inside the grid on the designer window, as shown below:Note the new query will show the number of orders per customer. You could easily have also substituted the ‘Count’ function for any of the other aggregate functions (for example to total the value of the orders, get the highest value, lowest value or average).
• Finally we will save the changes made to this query (as this will form the base query for the next tutorial), so simply click the  button and close down the query tool.

We are complete with the tutorial. An exported query containing this tutorial is available by right-clicking here and choosing Save Link As (Firefox) or Save Target As (Internet Explorer) to save the query import format file to a file on your hard disk, and then importing that query into the query tool (hint: choose File->Import Query from the main menu of the Query Designer).

End of Tutorial