# Share Custom Data Tables as Stored Searches for Project Reporting

Developer Note

This documentation focuses on the creation and propagation of custom data tables by XNAT Administrators. If you are interested in creating a custom report page in support of a new data type, see XNAT Developer Documentation.

Any ongoing study will have regular needs to report on their subjects and their data gathering. XNAT allows you to generate custom data tables, then share them among a selected set of users (or site-wide) to meet this need.

## Generate and Save a Stored Search

The UI for managing site-wide stored searches, or "bundles", will be updated in a future release of XNAT 1.7. As such, this documentation will be updated.

Any user can generate a new data table based on one or more XNAT data types, by using the Advanced Search or by customizing an existing data table. Once a new search has been created, the user can save it as a stored search and it will appear in their list of stored searches in the top navigation.

The user's view of their own stored searches in the top navigation

## Sharing a Stored Search – or Bundle – with Other Users

Stored Searches are also referred to as bundles in the XNAT Admininstration UI. These are searches that other users are allowed to view. They are a useful tool for organizing subsets of data into easily viewed combinations. The definition of each bundle is stored in the database in the same manner as your data. The bundles are loaded into the application server's context when they are first requested. Each bundle has a list of users who are allowed to view it. These users will have a link to the bundle in their navigation menu.

You can administer bundles at Administer > Bundles. You can also upload complex bundle definitions via REST API (see below).

A bundle has several key variables:

AttributeDescription
IDThis is the primary identifier of a bundle. It must be unique to this bundle.
Brief DescriptionThis is a label which will be used to identify this bundle in the UI.
DescriptionThis is a text field which exists for internal reference only, to describe the purpose of the bundle.
Pivot Data Type (Root Element)Every bundle must have a pivot data type, which is the root data set of the of the bundle. It is used to join data types together. All other data types in the bundle will be joined to this data type (rather than to each other). XDAT builds a bundle by starting with the root data type, joining other data types to the root data type, processing the WHERE clause to limit the rows in the result set, and outputting the result set according to the specified displayed fields.
Sort By Data TypeOptional: Specify a data type to use to sort the resulting data table
Sort By FieldOptional: Specify a field (or column of data) of the specified "Sort By Data Type" to use to sort the resulting data table.
Display FieldsThe display fields of a bundle identify which columns are displayed in the bundle. These fields reference <DisplayField> elements from the display documents (used in the listings). The element_name and field_ID variables are used to specify which previously defined <DisplayField> elements should be included in the bundle. The sequence is specifies the order that the fields (columns) should appear in the bundle. The type is the resultant type of the data fields. The header is the text that appears as the column header.
WHERE Clauses

WHERE clauses are used to limit the rows which are included in the bundle. Without any WHERE clauses, all of the rows for the pivot data type would be included in the bundle.

See below for tips on building WHERE clauses.

Limited AccessIf set to TRUE, then the bundle will only be visible to "Allowed Users" specified below. If FALSE, then every user in the site can view the bundle.
Allowed UsersThese are the logins of the users who are allowed to view this bundle.

### Building WHERE Clauses to Filter Your Bundle's Results

Users can manipulate data table filters in the XNAT UI to create simple WHERE clauses in their stored searches. However, there are limitations on what these filters are designed to do. For more complicated (e.g. multi-leveled) clauses, you may need to use XML to define the bundle. If this is the case, use the REST API to upload your bundle, as specified below.

An example XML structure of your WHERE clause would look like this:

 <xdat:search_where method="AND">
<xdat:child_set method="AND">
<xdat:criteria override_value_formatting="0">
<xdat:schema_field>xnat:mrSessionData.DATE</xdat:schema_field>
<xdat:comparison_type><</xdat:comparison_type>
<xdat:value>01/01/2017</xdat:value>
</xdat:criteria>

<xdat:criteria override_value_formatting="0">
<xdat:schema_field>xnat:mrSessionData.DATE</xdat:schema_field>
<xdat:comparison_type>>=</xdat:comparison_type>
<xdat:value>01/01/2016</xdat:value>
</xdat:criteria>
</xdat:child_set>
</xdat:search_where>

This example WHERE clause would provide a "between" filter that allows you to set boundary values for the MR Session Date field (xnat:mrSessionData.DATE), returning only sessions that were captured in the year 2016.

Note the use of method="AND" for all criteria assigned to the date field. This is an example of a search that cannot be performed in the UI for filtering data tables, since multiple filters on the same column of data will always use "OR" logic for joining results. Why is this important? A filter set to return all MR Sessions (after Jan 1, 2016) OR (before Jan 1, 2017) would return every MR Session in the database.

As of XNAT 1.7, a BETWEEN filter has been added to the data table filtering UI to get around this particular issue.

The display_field or schema_field is the data field which should be used to process the clause. Whether using a display or schema field, the field should start with the root data type of the field:

• If it references a pre-defined display type, then it should reference the display field's ID, i.e. data_type.Field_ID
• If it references a schema field, it should reference the field in the same manner that is used in the display documents, i.e. data_type/schema_field
This allows you to limit the result set by a pre-defined field (which may be derived) or directly from a data value.

The comparison_type variable specifies what comparison expression to use in the WHERE clause. It can be one of the following values:

OperatorDefinitionSupported Field Types
=equals
• integer
• float
• date (MM/DD/YYYY format)
• string
• boolean
<less than
• integer
• float
• date (MM/DD/YYYY format)
<=less than or equal to
• integer
• float
• date (MM/DD/YYYY format)
>greater than
• integer
• float
• date (MM/DD/YYYY format)
>=greater than or equal to
• integer
• float
• date (MM/DD/YYYY format)
LIKEFuzzy match for string searches.
Surround your search term with wildcards like so:
%term%
• string

The value variable specifies the value to use in the WHERE clause comparison.

The custom_search and override_value_formatting variables can be used to directly specify an unconventional WHERE clause

• custom_search can include the text of the WHERE clause. This text must be valid SQL as it will be included directly in the SQL WHERE clause
• override_value_format specifies that XDAT should not try to format the expression with characters like ' or %

## Upload an XML Stored Search Definition via the UI

It is easy to upload an XML definition of a stored search via the UI. Go to Upload > XML in the top nav to get started. See here for more details: Uploading Data via StoreXML. Once you have uploaded the stored search, you can access it and edit user permissions in the Admin UI by going to Administer > Bundles (Stored Searches).