Create a Tabular Report with an EDD Template

In “Create Electronic Data Deliverables from a Report Template” in LIMS Insights Issue 26 we saw how to create electronic data by modifying an example Excel report template. Similarly, we can create a report from an electronic data deliverables (EDD) template.

Each of the example Excel multiple-sample final report templates installed with LIMS is a generic template which lists all the analytes for each sample exported to the template. If you need a multiple[1]sample report with only specific analytes and sample characteristics, with one sample per report row, you can use the Define Report option on the Samples menu to create a simple user-defined report (UDR). However, if you want complete control of the report’s appearance, create the report from an EDD template.

For example, let’s assume you need a simple tabular report with one sample per row listing a few specific analytes along with each sample’s Sample ID, Location, and Collected Date. Begin by making a copy of file EDD Example – Specific Analytes.xlt located in folder C:\LIMS\Examples\Excel Export Templates and rename the new file My Report.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 EDD worksheet with exported LIMS data. Select the OneSampleResults named range from the Name box to “see” the cells that comprise the named range. This template’s macro copies the OneSampleResults named range to rows below, one row for each sample exported to the template.

The formulas in the OneSampleResults named range retrieve sample characteristics and results for specific analytes using the LIMS field names and analyte names in row 1, so row 1 must remain intact but we will hide it later.

Most sample characteristic fields are included in the EDD template. Since we only need Sample ID, Location, and Collected Date for our report, delete all the remaining columns through the Conclusions column leaving the three example analyte columns.

Beginning in column D, enter your exact LIMS analyte name in row 1 cells, an appropriate column label in row 2 and copy the formula from cell D3 to the row 3 cell for each analyte column. Note that analysis result columns do not have to follow sample characteristic columns. However, the row 3 formulas for sample characteristics and analysis results differ so make sure you copy the correct formula to your columns. See the row 1 cell comments for additional information.

Adjust column widths, row heights, and cell formatting as necessary. The screen below shows an example with four BTEX analytes.

To add your own report header rows, select any row 2 cell then use Insert | Insert Sheet Rows from the Cells group on the Home tab (or use Alt+I,R). Note that by inserting rows, Excel properly adjusts formulas and the location of the OneSampleResults named range as shown in the screen below.

Add any report header text, images, formulas and formatting then hide row 1. Note that this template’s macro does not insert rows as it copies the OneSampleResults named range, which means any cells with data below the OneSampleResults named range will likely be overwritten when exporting data to the template. To add a footer with page number or disclaimer text, use the Header & Footer option in the Text group on the Insert tab.

Since the template’s macro does not reference the first worksheet by name, you can rename the sheet as we have done here. Save and close your template then test by exporting a multiple-sample Sample Summary report to the template.

When you need a simple tabular style report with data for each sample on a single row, create your own Excel template from an EDD template and design the report appearance you prefer.