software

Tutorial 9

Adding a filter that prompts for a value at runtime

Sun, 02/28/2010 – 20:47 — NuVu Support Team

This tutorial assumes you have the knowledge gained from working with the previous tutorials. After this tutorial you will have the ability to filter the results of the query based on a prompt that is requested at runtime.

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 new query from the previous query (tutorial8), so double-click on the ‘tutorial8′ 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‘tutorial8′). The original query will open inside the Designer. We need to now save this as ‘tutorial9′ before we start working with the query, so click on the File->Save As menu item and advise a new Query name of tutorial9 when the save properties are displayed.Click the  button to save the query with the new advised name (tutorial9).
  • Click the  (prompts/variables) button on the toolbar to invoke the prompts/variables dialog window. The prompts/variables dialog allows you to specify static variables as well as prompts that are requested when the query is executed.
  • Click the  (add item) button on the update panel near the top of the screen. This will create a new empty row into the prompts/variables grid and position you on the ‘Variable Name’ column.
  • Advise the details as shown below:                        
  • Click the  (Post changes) button located at the top of the screen to save your new prompt variable.
  • Click the  button to save all changes and close the dialog window.
  • Click the  (filter) button on the toolbar to invoke the filter dialog window. The filter dialog allows you to specify the row (record) selections that must take place before the results are returned to you for display.
  • The filter dialog window will show all tables that have been selected for the query, with the ability to advise specific filter criteria for each table. Our example has both the Customer and Order tables, so first ensure that you click on the Customer table first. There should already be a hard-coded filter that excludes “USA” from the query. Highlight the “USA” (including the double-quotes), and then double-click the {ExcludeCountry} variable to automatically replace the “USA” with that variable in the expression editor, as shown in the following screenshot:
  • Click the  button to close the filter dialog window and return you to the main Query window.
  • Click the  button to start execution of the query. Because this query now has a prompted variable – the Prompts dialog window will open and allow you to advise a value for the ‘Exclude Country’ prompt. The default is ‘Austria’ – as shown here:
  • Click the  button to accept the default value. 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 all countries except ‘Austria”. Compare this to the results obtained in the previous tutorial. You can experiment with different prompt values (eg: United Kingdom, Australia etc) by rerunning the query and advising them.
  • There is a second variation to the ‘prompt’ function that allows for a selected number of values to be prompted for in the form of a drop-down list. In our example we are only going to allow the following countries to be excluded: USA, Austria or Finland.
  • Click the  (prompts/variables) button on the toolbar to invoke the prompts/variables dialog window. Your single ‘ExcludeCountry’ prompt will be visible in the grid.
  • Click the  (modify item) button on the update panel near the top of the screen. This will now allow you to advise any changes to the prompt item. Press tab until the ‘Expression’ column is highlighted, and replace the contents as follows:
  • Click the  (Post changes) button located at the top of the screen to save your new prompt variable.
  • Click the  button to save all changes and close the dialog window.
  • Click the  button to start execution of the query. Because this query now has a prompted variable – the Prompts dialog window will open and allow you to advise a value for the ‘Exclude Country’ prompt. This time there is not a free-format input field, but rather a drop-down box from which a selection must be made, as shown here:
  • Click the  button to accept the selected item from the drop-down list. 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.
  • 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