In a file based module, the queries
directory holds SQL queries, and ways to surface those queries in the LabKey Server UI. The following file types are supported:
- SQL queries on the database (.SQL files)
- Metadata on the above queries (.query.xml files).
- Named grid views on pre-existing queries (.qview.xml files)
- Trigger scripts attached to a query (.js files) - these scripts are run whenever there an event (insert, update, etc.) on the underlying table.
In this step you will define a custom grid view on the existing "LabResults" table, which is a provisioned table within the example study folder you loaded. Notice that the target schema and query are determined by the directories the file rests inside -- a file named/located at "study/LabResults/SomeView.qview.xml" means "a grid view named SomeView on the LabResults query (dataset) in the study schema".
Additionally, if you wish to just create a default view that overrides the system generated one, be sure to just name the file as .qview.xml
, so there is no actual root filename. If you use default.qview.xml, this will create another view called "default", but it will not override the existing default grid view.
Create an XML-based SQL Query
- Add two directories (study and LabResults) and a file (DRV Regimen Results.qview.xml), as shown below.
- The directory structure tells LabKey Server that the view is in the "study" schema and on the "LabResults" table.
│ DRV Regimen Results.qview.xml
The view will display CD4 and Hemoglobin results for participants in the "DRV" cohort, sorted by Hemoglobin.
- Save DRV Regimen Results.qview.xml with the following content:
<?xml version="1.0" encoding="UTF-8"?>
<customView name="DRV Regimen Results" xmlns="http://labkey.org/data/xml/queryCustomView">
<filter column="DataSets/Demographics/cohort" operator="contains" value="DRV"/>
<sort column="Hemoglobin" descending="true"/>
- The root element of the qview.xml file must be <customView> and you should use the namespace indicated.
- <columns> specifies which columns are displayed. Lookups columns can be included (e.g., "DataSets/Demographics/cohort").
- <filters> may contain any number of filter definitions. In this example, we filter for rows where cohort contains "DRV".
- <sorts> may contain multiple sort statements; they will be applied in the order they appear in this section. In this example, we sort descending by the Hemoglobin column. To sort ascending simply omit the descending attribute.
See the Grid View
Once the *.qview.xml file is saved in your module, you can immediately see this custom grid view without rebuilding or restarting.
- In your study, click the Clinical and Assay Data tab, then click LabResults.
- You can also reach this query via the schema browser: (Admin) > Go To Module > Query, click study, then LabResults then View Data.
- Open the (Grid Views) menu: your custom grid DRV Regimen Results has been added to the list.
- Click to see it.
Next, we'll add custom SQL queries.