Using_the_custom_query

Using the custom query builder

The Custom Query Builder provides a means to build custom dynamic link library (DLL) files that access additional data source types, including Watson version 7.x DMLIMS. Further, Phoenix provides templates for creating the DLL files to access custom data sources.

Select File > ODBC > Import > Legacy Wnl.

Select Use Custom Query Builder and press Next.

The Select a Builder dialog is used to set the data resources to access. If the Custom Query Builder has not been used before, this dialog may be empty.

Add resources

1. Press Add.

2. Double-click the appropriate DLL file, such as WatsonLIMS.dll.

Configure resource

Custom resources may need to be configured to make sure that data are imported correctly. Continuing with Watson DMLIMS as an example:

1. Select that item and press Edit.

2. In the Configuration dialog, enter the name of the Watson LIMS tables’ owner.

3. Enter the full directory path for the Watson templates or press Browse to locate the directory on your local machine or on a network server.

4. Press OK to close the Configuration dialog.

Build query

1. Highlight Watson DMLIMS (or another custom query builder) in the Select a Builder dialog and press OK.

2. Choose whether to load a query.

To load a saved query, press Yes. In the Open File dialog, select an existing query (file type *.wts for Watson LIMS) and press Open.

If no query has been saved for this builder, press No and proceed with the following steps.

3. Enter user name, password, and database alias in the login dialog, and press OK.

Note: Request your user name, password, and alias for a custom database system (such as the Watson DMLINS) from the database administrator or Information Technology personnel.

4. In the Study Selection dialog, select a study by clicking on the row and then press Next.

Use the Order Study By field to sort any category, such as study number or ID, study director, etc., to find a listing more quickly.

If no projects are listed in this dialog, contact your IT department.

5. In the Import Data Variables dialog, select the desired variables.

If all available variables are desired, press Select All.

For a subset, drag each variable to the Selected Variables field.

6. A particular combination of variables can be saved as a template. To create a new template:

a. Drag the variables for the template to the Selected Variables field and press New.

b. Enter a name for the template.

The fields will be imported in the order shown in the Selected Variables list.

7. Press Next to continue with data filtering or press Done to import all data in the selected fields.

8. Define a filter in the Filter dialog:

a. Select the field to filter on from the Field box.

b. Choose an operator.

c. Either enter a value in the Field Value field or press List Distinct Values to select from all existing values for the selected field.

d. Press Add to set the filter.

e. Use the And/Or operators to create compound filters such as Age > 12 and Gender = Male.

9. Press Done to load the data into a Phoenix worksheet.

If the Check # of rows returned box was checked, Phoenix reports a count of records returned.