Variables and Custom Calculations in OpenLab CDS

I am working on OpenLab CDS 2.5 and have spent the whole day trying to either use the Custom Calculation Editor or to achieve my result using Report Templates but I have not been successful with either! 

I have given up on doing this with the Custom Calculation Editor. On the Report Template:

  • I have six replicate samples run at different dilutions in one sequence
  • I have created a Sample Statistics table which lists all six injections for each of my compounds and then gives me the average and the SD. I have designed it so that even if a compound is absent in a sample, the concentration is given as 0 and still gets included in the average.
  • I would now like to take those averages and report them in a separate table for certain compounds and I would like to do certain calculations using these averages. But this is where I get stuck! I have tried creating a variable for the "Average" parameter in my sample statistics table, but I am unable to get this Variable to deliver the average values that I want in my new table/calculations

These are the calculations I want to do in the separate table:

Compound Summary
CBDA = Average from sample stats table
CBD = Average from sample stats table
THCA = Average from sample stats table
D9THC = Average from sample stats table
Total Potential CBD = 0.877*CBDA(%) + CBD
Total Potential THC = 0.877*THCA(%) + D9THC
Ratio Total Potential CBD/THC = Total CBD / total THC

If there is an easier way to do this using the Custom Calculation Editor then I am all ears. But any help would be greatly appreciated! 

Are there any Agilent resources that help explain the syntax used for reporting and calculations? I do have the Report Template Editor guide.

  • Is there nobody able to help me with the formulae for these custom calculations or reports?

  • Hi hayleym, i can help you with this. I am unable to attach the rdl. that i have configured. I will do a friend request and i can perhaps then email it to you if still needed.

  • Hello Hayleym

    quick question, I notice you have 10 tables for the compounds. Did you make 10 different tables, or did you use the repeat setting on compound_name to make the report auto repeat?

  • The group repeat is on Compound Name. Would be interested to see what you come up with as i went round the houses to get this one to work. This is what i did:

    1. I added a Compound_Concentration column to the Sample statistics table and saved this as an Aggregator (COMPCON) using Category Compound_Name.
    2. Added a table repeating on Compound Name and added custom field column to call in the values =Avg(COMPCON(Compound_Name)). This  creates a table for each compound displaying the Average. I saved the Col0 (custom field column) as Variable Conc with Unique Key Compound_Name. This table can be made invisible.
    3. Added 2 text boxes which were then grouped and used Group Repeat -1 (this is a trick so you can apply format/values). First text box uses Value Compound_Name, second text box calls in =Cocn(Compound_Name) from Step 2. Filtered group for Compound_Name. Added 2 text boxes for each compound did same for these. Grouped all these together for convenience as can then be made invisible by selecting Properties, Advanced, Visibility (uncheck Visibility).
    4. Used Save Expression Result As to save the value in the second text box as Variable CBDAConc, CBDConc, etc
    5. Populated the Compound Summary table with the corresponding Value by selecting the appropriate Variable. Eg. =ROUND(CBDAConc,3) Rounding used otherwise will be get different results when manually calculate to check report calculations
    6. The table and composite group i have added do not need to be displayed in the report and can be made invisible by selecting Properties, Advanced, Visibility (uncheck Visibility)

    Regards

    1. I added a Compound_Concentration column to the Sample statistics table and saved this as an Aggregator (COMPCON) using Category Compound_Name.
    Since the group is on repeat, I would also have used an aggregator on compound_name. Slight smile
      • Added a table repeating on Compound Name and added custom field column to call in the values =Avg(COMPCON(Compound_Name)). This  creates a table for each compound displaying the Average. I saved the Col0 (custom field column) as Variable Conc with Unique Key Compound_Name. This table can be made invisible.

      (i think this table does not even have to repeat), but it is indeed the next logic step to extract the average and store it as a variable.

      • Added 2 text boxes which were then grouped and used Group Repeat -1 (this is a trick so you can apply format/values). First text box uses Value Compound_Name, second text box calls in =Cocn(Compound_Name) from Step 2. Filtered group for Compound_Name. Added 2 text boxes for each compound did same for these. Grouped all these together for convenience as can then be made invisible by selecting Properties, Advanced, Visibility (uncheck Visibility).

      Used Save Expression Result As to save the value in the second text box as Variable CBDAConc, CBDConc, etc

      This is indeed how you could make all of them into a seperate specific variable

    • Then all he needs to do is summon his variables seperately / do the calculations needed.
      I had approximately the same thing in mind, but i would do some calculations in a table instead of converting everything in the 2 text boxes.
      After you have conc(Compound_name), i would have generated a table filtered on CBD and CBDA, show the averages in 2 columns (2nd one is invisible and saved as a normal variable). Then in between the visible and invisible column, make a new one thas has the calc "Total Potential CBD = 0.877*CBDA(%) + CBD" . If CBDA was the first line, it would be 0,877*New Variable + conc(Compound_name). Then make the upper line invisible in conditional formatting and save this column as another variable for the later ratio.
      Then i would repeat this for THCA(%) and D9THC.
      Lastly you can calculate the ratio's with the variables made from the "total" columns.

      different way of doing the same...

  • Hello,

    I'm doing a similar analysis and have been having trouble with how to configure the file. Could you pass along the rdl please?

  • could you send me the RDL? I'm doing a similar analysis and am having trouble creating the report file myself. Can't seem to make a useable variable at step 4. Thanks!

  • Was this helpful?