Note:
If you need to return values from a data source that is not in
the current database connection, you can use the Design
screen to set up the query to another data source.
On the SQL tab of the Retrieval Settings screen, you must enter an SQL query to return values from one or more tables in the current database connection.
|
The query you enter on this tab must be a SELECT statement. The Dynamic Reports module will not execute an INSERT, UPDATE, DELETE, DROP, ALTER or CREATE statement or any stored procedure.
|
Contents [Hide] |
If you will be querying one or more tables for GainSeeker data, standards or processes, there are two shortcuts that you can use to begin the query for you:
Create a new Dynamic report that is an SPC report or DMS report. Define the retrieval settings for this new report, click the File menu, and then click Save. Then copy this new report to an Advanced report.
Create a new Dynamic report that is an SPC report or DMS report. Define the retrieval settings for this new report. On the Columns tab of that report, click View SQL to preview the SQL query that will be used to retrieve data for the selected columns and copy it to the clipboard. Then create a new Advanced report or a new Advanced retrieval in a Mixed report, right-click in the text window on the SQL tab, and then click Paste.
|
You may click in the text window on the SQL tab and type the full SQL statement.
For help with SQL commands and reserved words, table names and column names:
Click to place your cursor in the position where items should be pasted into the statement.
Right-click in the text window
and select an option –
Reserved Words, Tables or Columns –
from the right-click menu.
Each option presents a list of items that can be pasted to the location
of your cursor in the text window.
Select one or more items from the list, and then click OK.
Reserved Words perform specific tasks in SQL.
Clicking this option displays a list of the most commonly-used Reserved SQL Words.
To display a longer list of reserved words, select the Show ALL check box.
To reduce the number of items displayed in the list, enter any portion of the item's name into the Search box. As you type, GainSeeker will automatically hide from the list any items that do not contain the text you have entered. To return to the full list of items, remove all text from the Search box.
To paste one or more reserved words into your SQL statement, select one or more items on this list and then click OK.
The current database connection references a database. To quickly access the names of the tables in this database, click Tables on the right-click menu.
This displays a list of tables in this database.
To reduce the number of items displayed in the list, enter any portion of the item's name into the Search box. As you type, GainSeeker will automatically hide from the list any items that do not contain the text you have entered. To return to the full list of items, remove all text from the Search box.
To paste a table name into your SQL statement, select an item in the list and then click OK.
For any table available to the data source, you can quickly access the names of all columns in that table. Click Columns on the right-click menu.
This displays a screen that allows you to choose column names for any of the tables available to the selected connection.
Click the Table list and select the name of the table for which to find column names.
Select one or more items from
the Columns list, and then
click OK. The column names
you selected are pasted into the SQL statement.
To reduce the number of items displayed in the list, enter any portion of the item's name into the Search box. As you type, GainSeeker will automatically hide from the list any items that do not contain the text you have entered. To return to the full list of items, remove all text from the Search box.
To access column names from another table, repeat these steps.
To test the SQL query you have entered on the SQL tab:
Click the Test tab.
You can reduce the amount of time and system resources that are needed to test the query by selecting the Limit test to 5 rows check box. If you want to see all of the rows returned by the query – even if this means waiting for thousands of rows to be returned – clear the Limit test to 5 rows check box.
Click Test Query.
If your query was successful, it will return a data set like the example below.
You should review the data set to make sure it reflects your intentions for this report.