TF3.3 Example of a Sample Report Using Compound Groups

This a series of reports based on one set of common elements. The only difference is the activation of elements in the grid. This shows how one report can simply be utilized to create multiple reports. Utilizes basic Excel functions.

This particular report makes use of two fields in the method that contain free text entries on a compound basis and are repurposed. The fields are used to add a comment for labeling a compound and to calculate a flag based on library score.

Click here to download the templates. Sample Quantitation-Sorted by Compound Group then Comment With Library01

Simply unzip into the folder C:\TraceFinderData\3.3\Templates\ReportTemplates

Grouped Sample Report

UnGrouped Sample Report

The data table utilizes many custom functions to create this functionality. These functions are typical Excel function statements. All the examples I used to construct this report were found by searching for them on the net.

Sample table

The data table utilizes a sort function by utilizing the Compound Group as the first sort. By checking the Repeat Header Per Group element and unchecking the Grid Entry allows for the element not to shown in the grid but raises it to the row above the set of rows that represent the Compound Group. This give a natural division between the groups. The second sort can be by compound name, or by comment. In these reports we utilized the comment to label the compounds as a parent or metabolite. By using the secondary sort in Descending order you can ensure the parent is listed first in the group and metabolites next in alphabetical order.

The data table is constructed as listed below.

  • Compound Name – peakresult.compoundName
  • RT(Custom Column)– if(peakresult.peakfound = false, “N/F”, Text(peakresult.rt, “0.00”))
    • This function declares a N/F for not found compounds, but sets the RT to a limit of 2 decimal places
  • RT Drift – peakresult.rtDrift
  • Quan Peak – peakresult.detectedMass
  • MZ Delta PPM (Custom Column)– IF(peakresult.peakfound = false, “N/F”,(quanresults.mass-quanresults.detectedMass)/quanresults.mass*1000000)
    • This function sets the cell to show N/F in case of a peak not being found. If a peak is found it calculates the PPM mass difference and sets the value to be positive.
  • Response – quanresults.totalresponse
  • Calculated Concentration (Custom Column)– if(peakresult.peakfound = false, “N/F”, if(sample.issolventsample,””,quanresults.reportedinsampleconcentrationprefix & Text(quanresults.reportedinsampleconcentrationdouble, “0.000”)))
    • This function sets the cell to show N/F in case of a peak not being found. It sets the value of the calculated amount to be converted to TEXT and limited to 3 decimal places of precision.
  • Outcome (Custom Column)– IF(quanresults.calcAmount= “N/F”, “Absent”,  IF(VALUE(quanresults.calcAmount)>= VALUE(compound.LOR), “Present”, “Absent”))
    • This function sets the cell to show “Absent” in case of a peak not being found. The next if statement sets the VALUE of the stored results to number. At times in reports, the results may be a string or text, by using VALUE the text is set to a number. The number of the results for Calculated Amount is compared to the Limit of Reporting value set in the method. If it is greater than the LOR value then “Present” is reported in the cell. In all other cases “Absent” is reported. This function could simply report the presence or absence of the compound utilizing the LOR flag as being TRUE or FALSE. However, that flag is only present for Unknown/Specimen sample types. This function allows the statements to be utilized across all sample types.
  • Library Score % – quanresults.libraryMatchScore
  • Lib Score Threshold – compound.CASNumber
  • Library Flag (Custom Column)– IF(INT(quanresults.libraryMatchScore)>= INT(compound.CASNumber), “Pass”,”Fail”)
    • This statement converts the values of the Library Match Score and the value entered into the Case Number to Integers. The value then can be utilized to evaluate for being above the threshold value which the Case Number value was utilized for. If it’s above the value then the flag is “Pass”, otherwise the cell will reflect “Fail”.
  • Comment – compound.LIMSID

The RED text in the Outcome column utilizes the settings below.




About humphrjk

Product Manager for Analytical Software for Chemist. Analytical Chemist, Mass Spectrometry Scoutmaster Troop 4277 Skipper Ship 4277 Vice Commodore Texas Sea Scouts

Posted on August 5, 2015, in Custom Reports, Reporting and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: