Overview	Top

LabVantage Adhoc Query tools provide an interface to build and execute SDI-centric queries without using SQL. Queries are generated by Red Hat Hibernate, a Java open source object-relational persistence and query tool. For more information, visit www.hibernate.org. LabVantage dynamically generates object-relational mappings according to the data model. At runtime, Hibernate Query Language (HQL) is generated from the search criteria. Hibernate then generates SQL script using the corresponding database dialect.

Building Adhoc Queries	Top

The necessary order of things:

Making SDCs and Columns Searchable

Before querying SDC columns, you must make them "searchable". In an Adhoc Query page, only searchable columns can be queried.

a.	In System Admin, choose Reporting → Adhoc Searchables.
b.	Choose the SDCs you want to make searchable by checking the box, then clicking "Save".
c.	Choose the SDC columns to query by clicking the radio button, then clicking "Manage Searchable Columns".
d.	Check the columns you want to make searchable. For the "Column title", enter the string that will identify the column in the AdhocQuery page. Click "Save". NOTE: Each searchable SDC is listed in the left frame of this page, so you can just click one to manage its columns.
e.	If you want to add columns that are not in the SysColumn table, see Adding Extended Columns.
f.	If you want to choose another SDC, click "Back to Manage Searchable SDCs" and repeat steps b through d. Otherwise, you can now proceed to Adding and Maintaining Queries to query the columns.

Adding Extended Columns

You can add "Extended Columns" to the list of Searchable Columns. Extended Columns are SDC columns that are not in the SysColumn table (such as those that define links between SDCs, or those created by executing database functions).

a.	Under the Extended Columns tab, click "Add Extended Column". For the "Column Alias", enter an identifier for this column. This will be used as the columnid in the SysExtendedColumn table. For the "Column title", enter the string that will identify the column in the Adhoc Query page.
b.	Enter the "Column Definition", using these examples as syntax guidelines: • Foreign Keys Assume you are currently managing the Sample SDC, which has a foreign key pointing to the Batch SDC. To query the Batch Description: Foreign Key in Sample SDC SDC with Primary Key Column Definition batchid Batch batchid.batchdesc • Reverse Foreign Keys Assume the same foreign key link exists (s_sample.batchid linked to the Batch SDC), but now you are managing the Batch SDC. To query the Sample Id in the Batch: Column Definition s_sample_batchid.s_sampleid The syntax for reverse foreign keys is: 1. The table with the foreign key (s_sample), followed by an underscore 2. The foreign key column (batchid), followed by a dot 3. Any column in the table to which the foreign key points (s_sampleid) When you search reverse foreign keys in the Adhoc Query page, remember that the query is on the column in the table to which the foreign key points. For example, if you are searching the Batch SDC and you: Search for... In the column... It returns... 8 s_sample_batchid.s_sampleid All Batches associated with Sample identifiers containing the number 8. • Operators You can also use the built-in concatenation, date, and arithmetic operators supported by the DBMS. Here are some examples using Oracle: Data Type Operation Column Definition String Concatenate the Sample identifier and description. s_sampleid||sampledesc Date Increment the Sample creation date by one day. createdt+1 Number Divide the Sample concentration by 3. concentration/3 NOTE: Remember to make a column searchable if you want it to appear in an Adhoc Query page.
c.	Choose the "Data Type" (String, Date, or Number). This determines the Criteria Arguments.
d.	To test your Column Definition entry, "Save" all of your changes, then click "Test Extended Column Syntax". This checks the database to verify that the links exist, as well as checks syntax. It either returns the query results (below), or throws an error.

Supported Detail Tables of Searchable SDCs

For a supported detail table, you can add an Extended Column (with a "Column Definition" of the form detailtableid.columnid) to a Searchable SDC. For example, a Column Definition of sdidataitemspec.condition can be defined as an Extended Column for a Sample. The Spec Condition can then be added as a Searchable attribute when searching for Samples using the Adhoc Query page. These detail tables are supported for the specified SDC Definition Option:

An Adhoc Query List page is provided for adding and deleting queries, as well as controlling access to them.

Adding Queries

To add new queries:

a.	In System Admin, choose Reporting → Adhoc Queries. The Saved AdHoc Query List page loads.
b.	Click "Add". The Adhoc Query page opens.
c.	Build the query in the Adhoc Query page.

Maintaining Queries

These buttons in the Saved Adhoc Query List page let you maintain queries:

Button	Description
Make Sharable	Lets other users access the selected queries. These will be displayed in the Select a Saved Query list.
Make Not Sharable	Prohibits other users from accessing the selected queries in the Select a Saved Query list.
Make Shareable and Lock	Lets other users access the selected queries, but the query can be modified only through the System Admin menu by a user with the System Administrator Role. The locked query is displayed in the "Adhoc Search" panel for the relevant SDI List page. Below is an example of a locked Sample query on the Sample List page:
Delete	Deletes the selected queries.
Group By	Groups queries by SDC or user.

Description

Queries are built in the Adhoc Query page:

	Tool	Description
1.	SDC Selector	This dropdown list contains all searchable SDCs. Choose the SDC you want to query. The input field on the root node and the expandable non-root nodes let you enter a few characters to filter searchable attributes.
2.	Searchable Columns	Searchable Columns and relationships for the selected SDC. To add a column, you can drag and drop the label (1) to the desired Criteria Group, View Columns, or Sort By. Alternatively, you can also click (2) to add to Criteria Group, (3) to add to View Columns, and (4) to add to Sort By Columns.
3.	Criteria Group	Set of query conditions (described in Criteria Groups).
4.	New Criteria Group button	Creates a new Criteria Group.
5.	Returned Records	Records returned by the query.
6.	View Columns	Additional columns you want to show in the Returned Records list.
7.	Max Results and Sort By Columns	Lets you choose the maximum number of records to show in the Returned Records list, and the columns by which to sort.
8.	Select a Saved Query	Lets you open and edit a previously saved query. Only Shared queries are visible to other users.
9.	Toolbar	Page operations.

Relationship	Display
Foreign Keys	Foreign keys in the SysColumn table are expandable to show searchable columns in the table with the primary key.
Reverse Foreign Keys	Must be added to the SysExtendedColumn table by adding an Extended Column. These display the single Extended Column.
Data Sets and Data Items	Partial identifier of Parameter List, Parameter, or Parameter Alias can be entered to display a list of matching Data Sets in the format specified by the AdHocQuery page configuration. The list is generated as you enter the identifier. Choose the Data Item to query.
SDI Documents	This shows SDI Documents (Documents associated with SDIs). For information about configuring Document Fields to show in an Adhoc Query page, see Adding Document Fields in the AdhocQuery Page Type. The SDI Document Field is an expandable node with a search field. If the Fields are in Categories, expanding the node shows Categories; otherwise, only the Field is shown. All Fields must be predined (meaning you must create Field SDIs in the "Field Definitions" tramstop... not in a Form). Entering search text into the search field gets all matching fields and displays them directly under the Document Field node. Drag or double-click Document Fields to criteria and view columns. NOTE:   Search results may be in different Documents and Field instances. Therefore, a Field in the view column may return more than one column. In this case, the result column header is shown in the format documentid(ver 1 ) fieldlabel( inst 1 ).
SDI Attributes	This shows SDI Attributes. For information about configuring Attributes to show in an Adhoc Query page, see Adding Attribute Fields in the AdhocQuery Page Type.

Criteria Groups

A Criteria Group defines a set of query criteria that can be joined to:

You can create multiple Criteria Groups with the New Criteria Group button:

Criteria Arguments

Arguments are provided for each Data Type (String, Date, Number), SDIs, and RefTypes:

Query Operations

Button	Description
Search Now	Runs the query. Alternatively, press the "Enter" key (to run the query after changes are made).
Count Results	Shows the number of records returned.
Save Query	Saves the query.
Delete Query	Deletes the query.
Export to Excel	Exports the returned records to an MS Excel spreadsheet. For a large number of records, use "Search and Export All".
Export to PDF	Exports the returned records to an Adobe Acrobat PDF file.
Search and Export All	Runs the query and exports the returned records to an MS Excel spreadsheet in a single operation. Use "Search and Export All" rather than "Export to Excel" when you must retrieve thousands of records (up to 100,000 in Excel format). "Export to Excel" actually renders the results in the Adhoc Query page. Such rendering can consume enough memory to be problematic. Using "Search and Export All" executes a direct export to file, so rendering is not an issue. After clicking the button, a dialog offers the Excel export option (note the limits on the number of records) and shows the export status:

Grouped Returns

When returned results are grouped, the number of groups is shown along with the total number of returns in each group. Collapsing the groups continues to display the number of returns in each group.

List Page Integration	Top

General Operations

In List pages, you can:

Grouping

When SDIs are displayed in groups, the total number of returns in each group is shown. Collapsing the groups continues to display the number of returns in each group.

Creating Adhoc Query Pages	Top

Use the AdHocQuery Page Type to create an Adhoc Query page (see Basic Principles for information regarding page creation).

System Search Integration	Top

The System Search feature can be integrated into Adhoc Query pages. To do this:

A "Text Search" field is inserted above the Adhoc criteria. This field can be used alone or in combination with other Adhoc Query criteria to narrow the search criteria. Values in this field are saved with the Adhoc Query.

In the example below, Sample is queried solely on the presence of "S-", which returns all Samples with an OOB automatically-generated key. 

Text Search further narrows the search to all Samples containing S- and ending with the number 4 as shown below. Note the wildcard syntax (see System Search Options). 

Text Search can also be used by itself, as in the example below.