TF 3.3 – Example Of How To Create a Highly Customized Report Template

TraceFinder has a Excel-like report template creation tool. It was first introduced as a plug-in beta release in TF 3.1 and a final product in TF 3.2. After feedback from the user based we made some modifications that should allow for easier template editing and creation. The more feedback we receive the better we can make this tool in the future.

The example below goes into detail how to create some very custom data table information and flagging. This includes taking values that are stored as numbers or TRUE/FALSE text and changing then to read PASS/FAIL with highlighted, bolded and color changed text. It also includes setting up test range criteria for the information to be flagged against without the number having to appear in the data table as being read directly from the results table in the batch to give the PASS/FAIL flag.

The report is a Batch report that shows only compounds that were found to be positive in a concentration above the limit of reporting used set in the Master Method template. It uses typical fields in the header and contains a blend of regular data columns and custom columns in the table. It is separated by the Sample ID as a line unto itself between each set of sample results.

Custom Report page

Below is shown the data fields used to create the table.

The statement in the Table Filter ensures that only compounds that have result greater than the Limit of Reporting (LOR) value, set in the Master Method template, will be displayed in the report.

Setting the sorting by the Sample ID and having it be the top most data field in the selector, allows for that value to be used as the Repeat Header Per Group field. By unchecking the box, the field won’t display on the row, but will appear in the separation row above only.

Custom Report page data feild table

The listings below are the custom fields created to do the special flagging a show data. If you notice these are all the same small scripts that you would use in Excel to create the information. The difference is that the underlying individual results don’t have to be on the sheet, in a hidden column or a second worksheet. It just reflective of the results in the batch. The functions are entered into the Formula text cell when the data field is selected.

Custom Report page data feild table formula field

The way these statements work is whatever is designated as the “=” state(True or False) the first text will be set to display for that state. All other states will render the second text.

To get the data field to use in the functions below simply click on a data element in the selector, highlight the text in the Formula cell and copy/paste it into a custom column’s formula text field and then edit the function around it.

IS Flag

IF(quanresults.ISTDAreaOutOfRangeFlag=False, “Pass”, “Fail”)

This function looks at the internal standard out of range flag. It creates the condition that if the flag is FALSE, meaning it is not indicating a failure of the flag criteria, the show “Pass” in the cell. If the flag is TRUE, meaning that the criteria indicates it has failed, the show “Fail” in the cell.

IR Flag

IF(quanresults.irFlag=False, “Pass”, “Fail”)

This function shows that if the Ion Ratio criteria has been meet by all confirming ions then the flag will be False. This state will then display “Pass” in the cell. If the flag is True then “Fail” will be displayed. The way these statements work is whatever is designated as the “=” state(True or False) the first text will be set for that state. All other states will render the second text.

IR2

quanresults.ionRatio(1,2)

One limit of the tool is that the table builder can only natively create one column for a set of information. However, you can create a custom column to show 2nd, 3rd, etc… of a data entry. One instance of this is a second confirming ion ratio. The setting (1,2) will display the result for quantitative peak 1, confirming peak 2.

mz Flag

if(quanresults.mzDelta(“Report”) > “2.5”,”Fail”, “Pass”)

This function sets the high precision number of the delta mass, derived from the expected mass minus the actual measured mass, which is always an abolute number, to display “Fail” if its greater than 2.5. If not or less then display “Pass”.

RT Flag

IF(ABS(quanresults.RTDelta(1,0)) >=0.06, “Fail”,”Pass”)

This function declares with the ABS statement that the RT delta result number which can be positive or a negative number will be evaluated by its positive equivalent. If it’s greater than or equal to 0.06 then display “Fail”, in all other cases display “Pass”.

Ion CoE Flag

IF(AND(quanresults.RTDelta(1,1) < 0.01,quanresults.RTDelta(1,1) > -0.01),  “Fail”,”Pass”)

This function combines two conditions the value of these two confirming ions, designated by the values (1,1), must fall outside these two conditions to display “Fail”, if it falls between them then display “Pass”.

Lastly I set conditional formats for each column separately for the custom flagging that if the cell value was “Fail” that it would change the Font to bold and red and the borders of the cell red.

Custom Report page conditional format

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 July 17, 2015, in Uncategorized. Bookmark the permalink. Leave a comment.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: