Summarization table in report

Question

Hello, 
 We were trying to create a summarization table at the end of the report. The table that you can see on the screenshot below is created from matrix. BPQL means that the value is lower than 0.045 but OpenLab can&rsquo;t calculate the average from &ldquo;text values&rdquo; so the average value is Error in case of BPQL partial results. Is there any way how to put BPQL into the average row or can we hide the &ldquo;Error&rdquo; expression in that table (matrix)? 
 
 On another screenshot, you can see other try for creation of summarization table. Table is form from Compound result table so it&rsquo;s more flexible than matrix. For calculating the average, we&rsquo;re currently using aggregator from hidden table. In this case I just want to ask if it&rsquo;s possible to get only one result value in average column. Now as you can see in average column, we get the same final results for every impurity. When we hide duplicates, it hides same expression/numbers in that column not just in row. Is there any way how to merge cells for single compound (impurity) in average column to get only one final result (BPQL or number)? 
 
 Here you can see the table with hidden duplicates in average column. 
 
 At last we attach the summarization table from EMPOWER. Our goal is to create the same or very similar summarization table in OpenLab. We hope that it is possible We are currently using OpenLab 
 
 Thank you very much for your answers.

martin.adams · Accepted Answer

Hello, 
 
 You cannot alter the matrix or table summary calculations, as those are coded into the object. In the table example, you could use an expression like = IF ( RowNumber ( "OLIR_Table_PeakResults2" ) = 1 , FormatNumber ( Peak_RetentionTime , 3 ), "" ) in your Average % column. Where I have Peak_RetentionTime in the true part of the if, you would replace with your current expression. Also, you would need to replace "OLIR_Table_PeakResults2" with "OLIR_yourtablename" in the expression. You can find the exact table name in the object properties. 
 
 Marty Adams

martin.adams · Answer

Hello, 
 
 You can do it, but the solution requires hidden tables and stored variables. I setup a hidden table at the top filtered for only the b samples. In that table, under the concentration field, I store the concentration value as a keyed variable using Compound_Name + Left ( Sample_Name , Len ( Sample_Name ) - 2 ) as the key. This allows me to store a value for b for the sample name where it matches between a and b. In the table for display, I filter it to only show the a samples and the add the stored value for b to a column in the table. One note is older versions did not allow expression-based keys for variables so if find any issue with storing that value try an index aggregator instead. Also you should repeat the displayed table on the same expression as I used for the variable index. 
 
 Marty Adams

martin.adams · Answer

Hello, 
 
 Did you have the display table setup to show missing peaks? 
 
 Marty Adams

Summarization table in report

Top Replies