Showing results for 
Search instead for 
Did you mean: 
Team TFS
Team TFS
data-integrity-compliance-shieldForeword by Brian Alliston:

Surrounded by likeminded industry-leading Chromeleon CDS users, I had a lightbulb moment at the International Chromeleon Software Symposium (ICS2018) in Nice last October ...'Spreadsheets are very useful but a bit of a data integrity nightmare. Chromeleon CDS Report Templates are spreadsheets with audit trails…ooh think what we can do with them.' A thought I brought home and discussed with my CDS admin colleague Tegan Rawlinson.

After some discussion, Tegan took the idea and taken in much further, so over to Tegan to explain more.


Everyone knows that, in the world that is GMP, or Good Manufacturing Practice, and data integrity, someone or something should always be monitoring, recording and reviewing every step you make. But what happens when you use software that doesn’t have the capabilities to monitor and document your every move?

In the Sterling labs we were regularly using Microsoft Excel spreadsheets and Microsoft Word documents to calculate and report results. Now don’t get me wrong, these are really useful, powerful applications, however when it comes to GxP and 21 CFR part 11 compliance, a huge amount of work needs to go into making them even close to compliant. This includes purchasing add-on software, time from the IT department, time from analysts checking transcriptions, time from administrators to configure and maintain reports and training and validation of the add-on software and report operation.

After Brian’s revelation at ICS2018 and with all of these things in mind, we realised we already had a spreadsheet application that that could do all of these things in one place without extra expense -- Chromeleon CDS report templates! As a result, we reviewed our use of spreadsheets and Word documents and have made some big changes to how we report results and how much we use spreadsheet applications.

Using and Verifying Chromeleon Report Templates

For every project that has chromatographic testing we have created tailor-made Chromeleon Report Templates that not only look consistent in terms of being the same as all of our other reports, but also directly calculate the results as required by the customer. Of course to make sure that the calculation is correct every time no matter who uses the report, whether they create the sequence via ‘save as’ or an eWorkflow procedure, we have to verify the report template.

Our original method for verification of the report template was a multi-step process:

  • Create a report template that matches the testing requirements

  • Copy all the relevant data into an Excel spreadsheet

  • Carry out the manual calculation

  • Print the results

  • Print the formula

  • Compare the spreadsheet results with the Chromeleon CDS results - they should match!

  • Lock the report template

This method presented us with several problems:

  • If you don’t copy the results over with 14 decimal places there may be a discrepancy between the manual and Chromeleon CDS calculated results

  • This method requires the CDS and Excel to be installed on the same PC – which is something we don’t currently have on every PC

  • Excel needs to have the same level of audit trails as Chromeleon CDS

  • Level of user control is not the same for Excel as Chromeleon software

  • Validation of the Excel calculations is required

  • How do you store the verification record? As a piece of paper that can get lost somewhere or as electronic file that could be edited, lost or corrupted?

  • If it is electronic data, you may have to keep it for seven or more years; documents that were created seven years ago don’t always have the support to be opened on current software

So as you can see, this method had many areas where human error could be introduced and cause compliance and data integrity issues. Therefore we were looking to simplify this process and improve the compliance and data integrity, which is where Chromeleon CDS came in.

A new method for report validation was developed leveraging the spreadsheet and compliance capabilities of the Chromeleon software:

  • Create the report exactly as you would normally for the analytical test, both mathematically and stylistically

  • Add a new sheet to the report template called “Verification.” Into this sheet copy over the data from your sample summary and any other important information you might need for the manual calculation like standard peak areas

  • Expand all the numbers to 14 decimal places and carry out the manual calculation as previously done in Excel

  • Compare the manual calculation to the Chromeleon CDS calculation and check they match

  • Lock all the sheets in the report template and pass to a second person to check (which includes a quick manual check using a calculator)

  • Make it a master report and add it to an eWorkflow procedure for immediate use

[caption id="attachment_20734" align="alignnone" width="1012"]figure-1 Click to enlarge[/caption]

Figure 1 How our report templates generally look

[caption id="attachment_20735" align="alignnone" width="1348"]figure-2 Click to enlarge[/caption]

Figure 2 Chromeleon software information in the purple box, manual calculation in the red box

There are several benefits to this new verification method:

  • It’s fully audit trailed by Chromeleon CDS

  • It complies with current guidelines

  • It’s easy to find the report verification when an auditor asks for it (as it’s part of every sequence), rather than having to rummage through filing cabinets

  • The report function ‘Show formula’ simplifies the second person checking of the templates as it shows where each number has come from and the value that will be used, such as peak area

  • It prevents lost or damaged paperwork and saves on storage!

So this might seem obvious to some but for us at Sterling it was a game changer in how we use Chromeleon software and our approach other spreadsheet applications.

Where next? Stability

I currently work across two teams at Sterling, CDS and Stability Studies. To be honest, until I started these roles I had no idea how much thought goes into managing a piece of software like Chromeleon CDS. For example, what happens to all the stability data once the study is complete?

One task I was asked to take a look at was our final stability report and there were a couple of things I immediately noticed when I started:

  1. The data trending for a five-year study often took more time than actually writing the rest of the report

  2. Stability seems to be a lower priority to get the latest technology, plus the technology that is there when you start a study might not be current when the study ends in five years

So let’s have a bit of background on how we used to handle our stability studies. During the course of the study the analyst would fill in a Word document with all of the stability results. This would then be printed and signed by the analyst, reviewer and then operational quality. Then for the next time-point, the same document would be used again with the new results added to the next column. Finally, the completed document is scanned in order to be sent to the customer electronically. This method has caused us several problems over the years, for example:

  • An analyst could copy and paste to the wrong column by mistake and wipe out all of the previous data

  • The formatting of the document could be changed very easily and by mistake

  • When the final signed version was scanned and sent to the customer, it was often crooked and sometimes even missing pages!

All of that, combined with the lack of controls in Word being the same as spreadsheets, we thought using Chromeleon CDS templates could be the way forward.

[caption id="attachment_20736" align="alignnone" width="866"]figure-3 Click to enlarge[/caption]

Figure 3 Original stability reports exactly as we would send them

We wanted a method that, no matter what type of study is required or how many products or tests are performed, the layout that would be sent to the customer would always be the same. So we developed a new stability reporting method which involved creating a Chromeleon Report Template that has all the testing and results for each test. Then the next sheets contain the data that is trended every time that a new time-point is tested.

[caption id="attachment_20737" align="alignnone" width="1136"]figure-4 Click to enlarge[/caption]

Figure 4 New proforma for the stability reports

[caption id="attachment_20738" align="alignnone" width="1156"]figure-5 Click to enlarge[/caption]

Figure 5 Individual trending page for each test

This method brings us several benefits:

  • Everything is compliant, locked and tracked using the audit trails ensuring data integrity

  • Errors are eliminated giving higher quality data

  • It’s very easy to see the trends

  • Rather than manually writing a long report, a quick .pdf export can be used

  • We get consistent quality of the customer-facing report

  • The report can be altered to fit any study requirement

  • We deliver a more professional report to our clients which they can use for their audits

The Ripple Effect

The Formulations Team, who test final dose products, have taken this one step further and have developed new ways to not only calculate their results but to also create their Certificates of Analysis within Chromeleon CDS. This means that they are able to save all the data for one batch of a product in the same Chromeleon software folder ensuring compliance and making the data easily available for any auditors.

[caption id="attachment_20739" align="alignnone" width="895"]figure-6 Click to enlarge[/caption]

Figure 6 A UV dissolution calculation and report carried out in Chromeleon CDS

[caption id="attachment_20740" align="alignnone" width="610"]figure-7 Click to enlarge[/caption]

Figure 7 Electronic report for all the dissolution calculations

[caption id="attachment_20741" align="alignnone" width="633"]figure-8 Click to enlarge[/caption]

Figure 8 Certificate of analysis created in Chromeleon CDS by the formulations team.


Overall, as a quality-focused team, we are continually pushing to improve compliance and data integrity in the labs, but also improve data quality by eliminating sources of errors. Of course, compliance is a journey that will never end but I feel that all of the improvements that we have made over the past few months have moved us further down the compliance highway and have changed our outlook on lots of different aspects of our work and how we can use Chromeleon CDS to our advantage.

I wonder if this has given you something to think about for your department. If you have any thoughts on how we can improve this even more I would love to hear from you in the comments below.