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.
Good guess but you’d be wrong; I love Chromeleon CDS report templates. There is nothing better than taking a newly supplied analytical method, with some slightly ‘odd’ calculations or complex reporting criteria and creating a report template that does the job.
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.
It is quite common for residual solvent methods to have reporting instructions as follows:
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.
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
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.
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.
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:
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.
So let’s go back to our reporting criteria:
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.
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.
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!
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.
This uses some new variables: VALUE, OR and ISTEXT:
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”
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.
Hello, i'm trying to use conditional formatting with chromeleon 7. I want to color text according to 2 conditions. My area must be between 2 values. I put the formula "IF(AND(...", but chromeleon doesn't know the formula "AND" and puts me an error.
Do you know why ? How can i do ?
Thank you !
Hello @jle, Welcome to the community!
We will work to find you an answer quickly.
@lfernandes Could you help with this question about Chromeleon? Thank you!
@peter-zipfell will be able to help provide you and answer Wednesday or Thursday. But he asks if you can let him know
the exact version of Chromeleon 7 you are using, e.g., 7.2.10 or SR5, etc.
Are you trying to use the Excel way of applying conditional formatting? The Chromeleon way requires a slightly different approach. It's probably easier if i refer to the initial article, where you will notice in the Column Properties that the Chromeleon formula is slightly different to Excel. It's made up of two components, the calculation formula and the format entry field that activates conditional formatting.
In your example you need to use the Chromeleon style as opposed to Excel. To consider the area between two values then you probably need to enter a formula like if(peak.area>0.3 AND peak.area<0.86,1,-1). I left my numbers in as they relate to the dataset i happen to have open in Chromeleon, replace as needed. This would work for a peak area column field, but you would need to adjust the formula if you need to refer to a partic cell or cell range. For the conditional formatting i would use something like [Green][>0]"Pass";[Red][<0]"Fail";General.
You may or may not see a boolean error, depending on if you encounter n.a. If this can't be ignored then you will need to adjust the formula again to something like if(iserror(peak.area),0,if(original formula). In addition, you will need to amend the conditional formatting (format) to consider [Black]  "no peak". This part i haven't tested fully, but hopefully you get the idea?
If you need further help then please free to get back in touch. I would also be interested if you have success too.
Thank you very much for your reactivity and your help, its working ! 😀
I had a boolean error but with "if(iserror..." its ok.
That's great news!
I hadn't checked the "if(iserror.......", but it's a common way to handle those n.a.'s when you don't want them. Did you have to tweak the formula for that one? I hadn't checked it out in detail.
I answered without trying the formatting for "no peak"...
One of my current formula is : if(iserror(peak.area);0;if((peak.area>0) AND (peak.area<61,4);1;-1)) with format : [Color10][=1]"Pass";[Color3][=-1]"Fail";General > Its working
When i put : [Color10][=1]"Pass";[Color3][=-1]"Fail";[Color1][=0]"No peak";General i have an error message "Invalid number format"
I tried with : [Color10][=1]"Pass";[Color3][=-1]"Fail";[Color3][=0]"No peak";General i have the same error message "Invalid number format"
Do I really need to add a format for "no peak" ?
Not necessarily. The 'no peak' shows without the conditional format entry, right?
It will probably shows as black text anyway, so If you're happy then i would leave it for now.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.