A re-usable query is one that uses our Custom SQL extensions that allow a query to be run over and over selecting different results each time.
One of the issues with SQL queries is that if you need to change even a small part to give you different data – it means editing the SQL query itself. The problem here is that most end users do not know enough about SQL to do this – and SQL can be pretty picky about its syntax.
InSights gets around this problem by introducing the concept of re-usable queries – by adding in SQL extensions that allow you to build (at runtime) dialogs that prompt the user to enter values that will be used in the SQL query when it is run. Imagine creating a query that pulls the orders out of a database for a particular date range. Normally this would mean that each month you would have to edit the SQL – possibly making mistakes in the SQL syntax, etc. However, with our SQL Extensions – you can write the query once – and each time you run it choose a different date range using user friendly dialogs that allow for drop down calendars, pick lists, etc.
The example in the above screen shot shows one such example – where we are creating an extension for a String value (Field TitleofCourtesy). Here you can see :”:LUSTRING:Select Title of Employee:True:False:False:[Dr.|Mr.|Ms.|Mrs.];”. This string is evaluated just before the query is run. It realizes that it is a string – and then reads the rest of the extension definition and builds a dialog shown below from that information. This dialog is then used to prompt the user to select or enter the value that will be used to replace the extension string in the SQL command – and then run this new SQL Command.
The above screen shot shows the generated dialog that will be used to prompt the user to select for the Employee Title (Dr., Mr, Mrs, etc.). Once the user clicks the OK button – the substitution is performed – and the new SQL is run. This is a VERY powerful feature of InSights that allows you to write a SQL statement once – and the end-user can generate different results each run – simply by picking or entering a different value each time the dialog is used.
While the Extension string can look somewhat complicated – it really isn’t. In fact, InSights provides helper functions (on the popup menus) that provide dialogs to help you create these strings!
In the screen capture above – you place your cursor in the extension string area – and right click. The shown popup menu appears – and you can choose Edit Custom SQL Tag to edit the string using those dialogs.
The screen capture below – shows you the dialog used to create/edit the String extension string.
The above screen allows you to enter the String Description that will become the title of the new helper dialog. The values that are in the drop down – are entered on in the memobox at the bottom. There are three checkboxes that allow you to force the user to only use the entered values, for any chosen or entered values to Upper Case – and finally whether or not to allow multiple selections.
There are helper dialogs for String fields, Numeric fields, Date Fields – and for allowing you to pick values from another table.
