In this topic, we will create two SQL queries in the study schema, then add metadata to one of them.
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.sql
SELECT 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
MasterDashboard.sql
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"
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.
| previousnext |
| expand allcollapse all |
