In this
tutorial step, we add SQL queries to the
queries directory. We can also provide associated metadata files to provide additional properties for those queries.
The metadata files are optional, but if provided should have the same name as the .sql file, but with a ".query.xml" extension (e.g., BMI.query.xml) (docs:
query.xsd)
In this topic, we will create two SQL queries in the study schema, then add metadata to one of them.
Add SQL Queries
- Add two .sql files in the queries/study directory, as follows:
fileBasedDemo
│ module.properties
└───resources
├───queries
│ └───study
│ │
BMI.sql
│ │
MasterDashboard.sql
│ └───LabResults
│ DRV Regimen Results.qview.xml
├───reports
└───views
Add the following contents to the files:
BMI.sqlSELECT PhysicalExam.ParticipantId,
PhysicalExam.Date,
PhysicalExam.Weight_kg,
Datasets.Demographics.Height*0.01 AS Height_m,
Weight_kg/POWER(Datasets.Demographics.Height*0.01, 2) AS BMI
FROM PhysicalExam
SELECT "PhysicalExam".ParticipantId,
"PhysicalExam".date,
"PhysicalExam".Weight_kg,
Datasets.LabResults.CD4,
Datasets.LabResults.WhiteBloodCount,
Datasets.LabResults.Hemoglobin,
Datasets.ViralLoad_PCR.ViralLoad_PCR,
Datasets.ViralLoad_NASBA.ViralLoad_NASBA,
Datasets.Demographics.hivstatus,
Datasets.Demographics.cohort
FROM "PhysicalExam"
View the Queries
- From the same study folder, open the query browser at (Admin) > Go To Module > Query.
- Click study.
- You will see your new queries, listed with the others alphabetically.
- Click each query name, then View Data to view the contents.
Add Query Metadata
Using an additional *.query.xml file, you can add metadata including conditional formatting, additional keys, and field formatting. The *.query.xml file should have same root file name as the *.sql file for the query to be modified.
Add a new BMI.query.xml file where we can add some metadata for that query:
fileBasedDemo
│ module.properties
└───resources
├───queries
│ └───study
│ │ BMI.sql
│ │
BMI.query.xml
│ │ MasterDashboard.sql
│ └───LabResults
│ DRV Regimen Results.qview.xml
├───reports
└───views
Save the following contents to that new file:
<query xmlns="http://labkey.org/data/xml/query">
<metadata>
<tables xmlns="http://labkey.org/data/xml">
<table tableName="BMI" tableDbType="NOT_IN_DB">
<columns>
<column columnName="Weight_kg">
<columnTitle>Weight</columnTitle>
</column>
<column columnName="BMI">
<formatString>####.##</formatString>
<conditionalFormats>
<conditionalFormat>
<filters>
<filter operator="gt" value="25"/>
</filters>
<backgroundColor>FFFF00</backgroundColor>
</conditionalFormat>
</conditionalFormats>
</column>
</columns>
</table>
</tables>
</metadata>
</query>
After saving this metadata file, if you still have a browser viewing the BMI query contents, simply refresh to see the new attributes you just applied: The "Weight (kg)" column header has dropped the parentheses, the excess digits in the calculated BMI column have been truncated, and BMI values over 25 are now highlighted with a yellow background.
Tutorial Steps
