Creating Electronic Data Deliverables from a Report Template

LIMS includes two example electronic data deliverable (EDD) templates that you can copy and modify to create your own templates to supply electronic data. You will find the example EDD templates in folder C:\LIMS\Examples\Excel Export Templates on any LIMS workstation. The EDD example templates create data files with any sample characteristics, such as sample ID, location, and collected date, and the results of one or more analytes all on a single row. However, when you need to create electronic data with the results for each analyte on a separate row, you can easily modify an example report template to get the job done.

For example, let’s assume you have a customer who wants their sample results delivered electronically with the following data compiled on each row:

Sample ID, Location, Collected Date, Analyte, Result, Units, Date, Method

The Final Report Example.xlt template displays the results for each analyte on a separate row one sample after another so it is a good template to modify. Below we will modify this template to create your EDD template.

Begin by making a copy of file Final Report Example.xlt and rename the new file My EDD.xlt. Copy the new file to your designated Excel export templates folder (see the Workstation Configuration screen for your folder) then right-click the new template file and choose Open.

Like most LIMS example templates, this template’s macro uses lookup formulas and named ranges to populate the Report worksheet with exported LIMS data. Select the OneAnalysisResults named range from the Name box to “see” the cells that comprise the named range. This template’s macro inserts new rows below and copies the OneAnalysisResults named range once for each sample analysis on the report. For example, if you export three samples each with five analytes, the template’s macro will insert 14 rows below and copy OneAnalysisResults 14 times resulting in a report with 15 rows of data. The screen below shows the OneAnalysisResults named range in the unmodified template.

Since the OneAnalysisResults named range must exist for the macro to work correctly, be sure not to delete its row. Delete all rows above the OneAnalysisResults named range except the row with column labels then delete all rows with data below the OneAnalysisResults named range. Delete the logo image, remove all formatting from the label row and unmerge any merged cells. Select the OneAnalysisResults named range to verify it is still present and your template should look like the screen below.

Next, adjust your column widths. The original report template uses narrow column widths to allow more flexibility in positioning data in the report’s header rows. Increase the width of all columns on the Report worksheet.

Disable the Remove Infrastructure option on the Settings worksheet then save the template. Export a Sample Summary report to the template and you will see all the data available on the LIMSData and AnalysisData worksheets. The existing formulas in the OneAnalysisResults named range (our row 2 in the screen above) retrieve data from the AnalysisData worksheet, which is populated by the template’s macro using data exported from the LIMS and by querying the LIMS for additional data. However, the AnalysisData sheet does not include the sample’s characteristics (e.g. project, location, sample type, etc.) so we cannot simply copy and modify one of the existing formulas to list Location and Collected Date.

Since each sample characteristic field is available on the LIMSData worksheet, we can use the sample ID in column A to look up any sample characteristic. And since our template’s macro creates named ranges for all the data on the LIMSData worksheet using the row one field names, we can use the LIMS field name in our formula. Close the test workbook and open your new template again.

Using the sample ID in cell A2, the formula =MATCH(A2, SampleID, 0) will find the row number where an exact match for the sample ID is found in the LIMSData worksheet’s SampleID named range.

With a sample’s LIMSData worksheet row number we can retrieve the data in the same row for any other field on the LIMSData worksheet with the formula

=INDEX(field_name, row_number).

Using the MATCH function within the INDEX function, add the following formula to cell B2 to list the sample’s location:

 =INDEX(Location.Name, MATCH(A2, SampleID, 0))

Add the following formula to cell C2 to list the sample’s collected date:

=INDEX(CollectedDate, MATCH(A2, SampleID, 0))

The simple formulas above work well for required sample fields like location and collected date. However, when used with optional fields like received date, customer sample ID and description, this formula will list a zero if the sample field is blank. To suppress zeros for blank fields use the IF and ISBLANK functions:

=IF(ISBLANK(expression), “”, expression)

With this function if the result of “expression” is blank an empty string is displayed, otherwise the result of the expression is listed. Replacing “expression” in the above formula with our original formula (without the equal sign) results in the following formula you can use with any LIMS sample characteristic field:

=IF(ISBLANK(INDEX(Location.Name, MATCH($A2, SampleID, 0))), “”,INDEX(Location.Name, MATCH($A2, SampleID, 0)))

Simply replace the two occurrences of “Location.Name” with any valid LIMS field name from row one of the LIMSData worksheet to list data for the field. Also, note the absolute cell address $A2 in the above formula which will preserve the sample ID column letter if you copy the formula to another column.

Now add column labels in cells B1 and C1. Finish the template by moving the existing column labels and formulas for analysis, result, units, analysis date, and method to columns D though H, delete the unused label and formula for technician, then set a date format for columns C and G. The screen below shows our finished template.

Re-enable the Remove Infrastructure option on the Settings worksheet then close and save your template. Query and export a Sample Summary report to your new template and you will have a workbook of electronic data to deliver to your client. Save the resulting workbook as an Excel file or as a CSV file

Modify one of the existing example EDD templates or create your own from an existing report template and give your customers electronic data in the format they need.

When you need to create electronic data with the results for each analyte on a separate row, you can easily modify an example report template to get that job done as well.