Creating a Data Source in GridWorX (Simple SQL Designer)

A Data Source is a handle provided for Unified Data Browser that is used to connect to a database table. The Data Source stores SQL commands to select, insert, update, and delete entries in the database table. SELECT configuration is required. Insert, Update, and Delete configurations are optional. Follow these instructions to create a Data Source.

To Create a Data Source:

1. Right-click on your database connection and choose New Data Source.

New Data Source Selection

2. Click on the Edit Command Text icon and select Simple SQL Designer. You may want to change the name of the GridWorX Data Source.

GridWorX Data Source with SQL Editing Options

3. Click the ellipsis [...] button at the far right of Table Name field to open the Select Table dialog box. This dialog box lists all of the tables in the connected database. Navigate to the table and select it, then click OK. The dialog box closes and the SQL Command Designer screen displays information for the table you selected.

4. The Columns Name column lists the names of all the columns in the selected table. Put a checkmark in the checkbox of each column you want to read data from. You can also change the sequence of columns by highlighting a column name then clicking the Move Up or Move Down button. When you are finished, click OK.
   
   SQL Command Designer with Orders Table

5. You can also add and/or edit SQL column names/expressions to each column by clicking the Add Expression button. In the SQL Expression dialog box, you can edit the Expression Title and SQL Column Expression fields. When you are finished, click OK. Then click the Next button to continue.
   
   You can also build an Expression Filter, which allows you to manually edit SQL filtering expressions. You can use the Columns button (to select a column from the database table), Operators button (to select addition, subtraction, multiplication, division, or modulus/percentage), and Functions button (to choose mathematical, string, and date/time functions) to select the corresponding SQL elements for building your SQL filtering expression. Click OK when you are done.

SQL Expression Dialog Box

6. Now that you have selected the database column information to be displayed, you can define parameters for filtering or restricting data from those columns. Click on the Records Filtering tab. Enter any filters you would like to apply to the data by clicking the button with the + (a green plus sign) icon on it.

SQL Command Designer Dialog Box (Records Filtering Tab)

7. The Filtering Criteria dialog box appears. This dialog box helps you write proper syntax for SQL filtering expressions. In the Filter Type, you can select Relational Filter or Expression Filter. If you choose Expression Filter, the dialog box looks like the example shown below. You can write a SQL expression for the filter. The Columns button allows you to insert column names from your selected database table.

Filtering Criteria Dialog Box

If you choose Relational Filter as your filter type, complete the fields as follows:

1. • Column Name to Filter On -  the drop-down list contains the columns you can filter on. Choose one.
   • Relation Type - select an SQL function (Equal To, Not Equal To, Greater Than, etc.)

   • Relates To - select the item for comparison. You can choose one of the following:

    

2. [Constant] - Click the button with the database icon to select a value from the database.

Filtering Criteria Dialog Box with Constant Value

[Parameter] - Click the button with the @ icon on it (indicated) to select a parameter.

Filtering Criteria Dialog Box with Parameter Value

You can also choose a database column value directly from the Relates to field, as shown in the following figure.

Filtering Criteria Dialog Box with Selected Value

8. You can create additional filters by repeating the prior two steps.

In the SQL Command Designer, the query syntax appears at the bottom in the Query Preview box, but the tree-like structure gives you a roadmap for how the filters are combined using AND / OR Boolean operators and parentheses. An example is provided below.

Each filter node is first combined with its “children,” and then the resulting expression is combined with the “siblings.” Click the Toggle Bool button to switch between the AND / OR boolean operators for the selected filter. You will notice how the icon to the left of the filter changes, too.

SQL Command Designer Dialog Box with multiple filters

9. To sort the database records, go to the Sorting tab, as shown in the figure below. Here you can specify the priority by which the columns and the records in each column are sorted. You can choose to sort on any of the selected columns or leave them unsorted.

To determine the order in which the data will be displayed within in each column, select that column from the drop-down list and select one of the following sorting options:

• Ascending: Sorts the data in the column in ascending order, from least to greatest.

• Descending: Sorts the data in the column in descending order, from greatest to least.

SQL Command Designer Sorting Tab

10. Click OK to create your Data Source. The GridWorX64 Server Data Source dialog box appears with the SELECT command you just created.

11. Click the Test Select Command icon to test your Data Source.

GridWorX 64 Server Data Source

12. The Query Results dialog  box appears and lists valid records retrieved using the parameter values you provided for the query. You may need to test of change your query if you do not see the results you expected. Click Close to close this dialog box.

Query Results Dialog Box