What is your favourite thing about Chromeleon CDS? If you have read any of my previous blog posts, I’d guess you would think mine is audit trails or queries.
Reducing Complexity
In much the same way eWorkflow™ procedures can simplify the complexities of sequence creation, a good Chromeleon CDS report template can streamline the intricacies of result calculations and reporting.
Here at Sterling we have multiple methods all with individual reporting criteria. This complexity can lead to inefficiency and errors, especially if calculations are performed manually or in an uncontrolled spreadsheet. In the years BC (Before Chromeleon!) we actually transcribed results into workbooks or sheets and manually made this comparison! Of course this manual process of recording results and comparing them to the criteria was prone to error. But with the implementation of our lab-wide CDS, we thought, “Why not incorporate the criteria into a report template and let Chromeleon CDS automate the reporting process?”
I’d like to show you how Sterling has used Chromeleon CDS report template functionality to help simplify reporting and the interpretation of reporting criteria. In the following example we have used Chromeleon CDS report template functionality to automatically calculate the results and compare them to the reporting criteria.
Reporting Criteria: Limits of Quantification (LOQ)
It is quite common for residual solvent methods to have reporting instructions as follows:
- Report any component not detected as <LOQ (ND)
- Report any component detected but less than LOQ as <LOQ
- Report any detected components >LOQ to 0 decimal places
In theory this is quite a simple set of reporting criteria but in practice how many components do you have? Do they have different LOQ values? Have you rounded the result correctly? What other tests are you running today, what are their criteria?
Example Report Table
Consider the example table in Figure 1
LOQ: Displaying component specific LOQ values
Amount: Undetected components are displayed as (ND) instead of ‘n.a.’ with
Reportable Amount: Rounded reportable results with peaks <LOQ displayed as “<##ppm” with undetected peaks displayed as “<##ppm (ND)”
Hopefully you can see and agree that the last column simplifies the interpretation of the reporting criteria for the analyst. The final reportable amount is clearly shown and meets the reporting criteria.
How Was This Done? Formulas to the Rescue!
Most spreadsheet users will be familiar with formula functions. Did you know they can be used in Chromeleon CDS report templates and tables? Let’s take a stepwise look at the formulas behind the important columns…
Step 1: Create a Custom Component Variable and Column for the LOQ Levels
A Custom Component Variable can be created directly in the Processing Method or in the Console, Tools menu
- Create a new custom variable and set the type to ‘Numeric’ in the drop down and complete the fields as required (Figure 2).
The component specific limits of quantification can then be added for each component to a new column (LOQ) in the component table in the Processing Method as shown in Figure 3. Note the * simply highlights that this is a custom column.
The LOQs can then be added as a column in the report table (by setting the column formula to component.customVar(“LOQ”)) and we can use this to compare our results to the LOQs.
Step 2: Amount Column
We are going to use two functions.
- IF statements are logical arguments, we can use these to test a condition (a calculated result or any other parameter) and return a corresponding TRUE or FALSE value
- ISERROR checks for any error, such as n.a., #N/A, #VALUE!, #REF!, #DIV/0!, or #NAME?, and gives a TRUE or FALSE output
In the ‘Amount’ column we want to check whether there is a calculated peak amount and report “(ND)” if the peak is not found in the chromatogram. To start, open the table properties for the required report column and type ‘if’ into the formula bar then click on parameters, as shown in Figure 4.
The ‘IF’ parameters window will open.
- Set the conditions as shown in Figure 5 (Note: the “” are important to show this is text)
- Click OK to see the full formula: if(iserror(peak.amount),"(ND)",peak.amount)
So what does this all mean? In this example Chromeleon CDS will check if the peak amount is an error (i.e. ‘n.a.’) and show (ND), otherwise the peak amount is shown.
Step 3: Reportable Amount Column
We will use two more functions, ROUND and TEXT, plus a nested IF formula:
- ROUND rounds the selected variable or number to the specified number of decimal places
- TEXT simply converts a value to text
A nested IF is simply one or more IF functions within an IF function. The general formula for a nested IF is: IF(Condition1, Result1,IF(Condition2, Result2, Result3)). Of course you can ‘nest’ even more IF functions to cover all your options!
To hopefully make this a bit clearer, we are asking Chromeleon CDS to test Condition1, if it’s TRUE return Result1, if it’s FALSE test Condition2. If that’s TRUE return Result2, if it’s FALSE return Result3.
The actual formula in Column D is:
IF(ISERROR(peak.amount),"<"+TEXT(component.customVar("loq"))+"ppm (ND)", IF(ROUND(peak.amount,0)<component.customVar("loq"),"<"+TEXT(component.customVar("loq"))+"ppm", peak.amount))
Let’s break this down and convert to plain English!
Blue: Check if the peak amount variable contains an error (e.g. ‘n.a.’), if TRUE return the text ‘<##ppm (ND)’, otherwise perform the next (purple) IF formula.
Purple: If the peak amount rounded to zero decimal places is less than the LOQ return ‘<##ppm’, otherwise display the calculated peak amount (red).
So this would result in a value of ‘<10ppm’ being returned for a component that is detected but at less than its LOQ of 10ppm but ‘<10ppm (ND)’ if the same component is not detected.
By using nested IF formulas in one column of the table we end up with an elegant solution to our result reporting.
Top Tips
- This example uses an integration table but these formulas also work in summary and consolidated tables.
- The same principles can be used for custom calculations in the report template cells.
- The formulas can look complicated as there are brackets everywhere! The easiest way to ensure you get the syntax right is to create each part in an individual column then, once you get the individual columns working, build the final formula.
- To help build the final formula you can copy and paste the component parts from the IF formula in the column properties into cells of the report template. This will give you a text copy that you can build upon and can be pasted back into the combined formula. I prefer to write the whole formula out in a cell as text which can then be copied into the column properties formula.
- Finally did you know you can save your formula as a Custom Formula that can then be reused more simply by calling the Custom Formula instead of having to type out the whole formula each time? This can help eliminate errors in formulas and speed up report creation.
Simple, Efficient, Consistent?
So let’s go back to our reporting criteria:
- Report any component not detected as <LOQ (ND)
- Report any component detected but less than LOQ as <LOQ
- Report any detected components >LOQ to 0 decimal places
Which do you feel is the most useful table in Figure 6 for helping the analyst report the results correctly and eliminate errors?
For me, the first table requires manual result rounding and comparison to the LOQ values which could lead to human errors. The second table automatically gives the analyst the results in the format required and the final reportable results are displayed. ‘Raw’ results and LOQ values are also shown so that both analyst and reviewer can clearly see the reported results are correct.
Time Saved
This example is a simple one, but we have many other analyses, such as impurity analysis that require much more intricate reporting criteria. The time and effort invested in creating and verifying Chromeleon Report Templates to automatically report the results to these specific criteria and to the correct number of decimal places has been returned significantly. It is hard to get an exact figure on the time saved but I would estimate the time taken to evaluate and report these more complex results has been reduced by 25-50%. This is not taking into account the reduction in transcription errors. But more importantly for me the templates ensure data is interpreted and reported in a consistent manner.
Remember To Check It!
Of course the software can help immensely but it is still the responsibility of the analyst and reviewer to check the correct results are reported. Plus the report templates must be verified to ensure correct operation.
During template verification it may be necessary to produce some ‘creative integration’ to check your LOQ values, rounding, greater than and less than arguments are working correctly. This is why we always work with a copy of a sequence that contains data for report template development and verification leaving the original data untouched (and only our administrators can copy sequences with data). And remember to always add detailed explanations as to why a copy has been made in the audit trail comments!
Want To Go One Step Further?
How about adding a specification comparison with conditional formatting to clearly highlight failures?
First create another custom component variable for the specification (Column E in Figure 7). Then append another column (Column I) to the table for the Pass / Fail comparison – this where the magic happens! What we want Chromeleon CDS to do is to check the reportable result (Column H) and find out if it’s text (e.g. <10ppm (ND)) or is less than or equal to the specification (Column E) and report a Pass or Fail.
To add a custom ‘Excel-type’ formula to a table column, there are 2 options: Edit in the report table properties, or type directly into the cell and then copy it down the entire column. In this example we can edit cell I32 with the formula below. This formula will produce a column containing either ‘1’ for in spec results or ‘0’ for those out of spec.
Formula: =VALUE(IF(OR(ISTEXT(H32),H32<=E32),“1”,“0”))
This uses some new variables: VALUE, OR and ISTEXT:
- VALUE: This will return a number from text (ensuring we have a number 1 or 0 at the end, not text).
- OR: This will check the two conditions and return TRUE if either of them is true.
- ISTEXT: Returns TRUE if cell H32 contains text.
On pressing enter, the CDS will ask you ‘Are you really sure?’ as this input will replace a dynamic Chromeleon Report Variable. Answer yes.
Now open the column properties where you can see the calculation in the Formula line as shown in Figure 8.
To activate the conditional formatting and ensure the 1 is now converted into Pass and 0 into FAIL, enter the following in the Format box: [Color10][=1]“Pass”;[Color3][=0]“FAIL”
Do You Know Easier Ways To Do It?
I have been a Chromeleon 7 software user for over seven years and I’m still finding new and better ways of generating and presenting results. I would encourage any Chromeleon CDS users to share their template tips and tricks in the Chromeleon User Group on LinkedIn so we can learn together as a community.
10 Comments
Thermo Fisher Scientific
United States
