Getting Started with Excel Template Reports

If you are a new LIMS user or new to Excel-based reporting with LIMS, this article will get you started using Excel templates. LIMS includes many internal system reports such as the Analyte Comparison, Sample Summary, Work Order, and Invoice reports. While these system reports are valuable tools, you may need to control the appearance of the reports you use to display your LIMS data. In this case, Excel template-based reports are your solution.

With your initial LIMS installation, LIMS installs a number of example Excel templates that you can copy and modify to create your own reports. On each LIMS workstation, you will find the example templates in folder ‘C:\LIMS\Examples\Excel Export Templates’. Newer templates may exist so always check the File Library in the Customers Only section at LIMS.com for recent additions.

Generating an Excel-based report only requires previewing a system report then exporting its data to the selected Excel template. The underlying data for any system report can be exported to Excel. However, most example Excel templates are designed for, and therefore expect the data from, a specific system report. For example, most of the example final report templates expect data from the Sample Summary report.

Before you can export a LIMS system report’s data to Excel, you must first tell the LIMS where your Excel export templates are located. Use the Folders tab on the Workstation Configuration screen on the Admin menu to specify your Excel templates folder. In a new installation the export templates folder will be blank so begin by using the Browse button and selecting your workstation’s ‘C:\LIMS\Examples\Excel Export Templates’ folder as shown below. Note that Admin privileges are required to make changes on the Workstation Configuration screen. In a multi-user LIMS installation, all workstations should use the same Excel templates so later you should copy all of your export templates to a file server folder and update the Workstation Configuration screen on each LIMS workstation.

Let’s assume your first goal is to create a final report template that you will use to report results for most lab samples. Begin by querying two or more samples and previewing a Sample Summary report. Click the File menu and select ‘Export to MS Excel Template’ or use the equivalent toolbar button shown below.

Now select a template from the list of example templates and click the OK button. Begin by selecting one of the CofA example templates. Although you have queried, previewed, and exported a multiple[1]sample Sample Summary report, the CofA examples are single-sample templates so the resulting report will list only the first sample exported. Close the Excel workbook without saving then repeat the export selecting any of the Final Report example templates.

The screen below shows the results of a Sample Summary report exported to the ‘Final Report Example’ template.

Repeat the Sample Summary report export until you find a ‘CofA’ or ‘Final Report’ example template that produces a report with an architecture you like. Now make a copy of your preferred template and rename the file accordingly.

To open your new template file for editing, either right-click the file and select Open from the popup menu, or open the file within Excel. Note that double[1]clicking an Excel template does not open the XLT or XLTM file; rather it instructs Excel to create a new workbook from the template.

After you open your new template, begin by reviewing the Read Me worksheet, which includes notes on the template’s operation. Next, review the options on the template’s Settings worksheet by hovering your mouse over each commented cell. If the Settings sheet includes a ‘RemoveInfrastructure’ option, you will find it helpful to disable this setting until you are finished updating and testing the template. Leaving all infrastructure worksheets will allow you to explore the LIMS data available for display on your report.

When you export a system report’s data to Excel, LIMS will perform the following tasks:

1. Start Excel if it is not already running

2. Instruct Excel to create a new workbook from the template you selected

3. Run the new workbook’s BeforeTransferFromLIMS macro

4. Write the system report’s underlying data to the new workbook’s LIMSData worksheet

5. Run the new workbook’s AfterTransferFromLIMS macro

An Excel macro contains Visual Basic for Applications (VBA) programming code to perform specific tasks. The BeforeTransferFromLIMS macro in most example templates is empty. However, the AfterTransferFromLIMS macro normally has many VBA statements to perform specific tasks to generate the final report. The notes on the template’s Read Me sheet may describe some of these tasks. If you are adventurous, you can explore the macro’s VBA code and review its green comment lines to better understand the macro’s operation.

Now switch to the first or report worksheet in your template where you will see the images, labels, and formulas that produce the report. The image below is an excerpt of the Report worksheet from the ‘Final Report Example’ template.

The formula in merged cell E5 above demonstrates the use of an Excel named range, a user-defined name given to one or more cells. Use the Name box shown below to view and select named ranges in the template. Selecting the CustomerMailingLabel name will show the cell or cells on the Customer worksheet that comprise the named range. You will find it more helpful to select the CustomerMailingLabel name in a workbook created by exporting to the template so you can explore the data available on the Customer worksheet.

As shown below, you can also use the Name Manager on the Formulas tab in Excel to view and modify the named ranges in the template. In this template, note the cells that comprise the OneAnalysisResults named range.

Selecting OneAnalysisResults from the Name box highlights the cells in this named range. The template’s Read Me worksheet notes that the AfterTransferFromLIMS macro will insert one new row below then copy the OneAnalysisResults named range once for each sample analysis exported from the LIMS (i.e. for each data row on the AnalysisData worksheet). With this knowledge, you can move or expand the named range and modify the cells within to alter your final report

In most example templates, the AfterTransferFromLIMS macro automatically creates named ranges for all the data obtained from the LIMS using the LIMS field names. After exporting to your template, you will find all of the new named ranges listed in the Name box in the resulting workbook. The formulas in the OneAnalysisResults named range above use the named ranges to display report data. Since the names will not exist until the AfterTransferFromLIMS macro creates them in the new workbook, the formulas show the #NAME? error only in the template.

The best way to make changes to template formulas is to create and test the changes within a workbook created by exporting to the template since the workbook will have data and all of the named ranges. Once the new formula is working properly you can copy it to the template. For example, if you prefer to see the analyte name and its units in a single cell, edit the formula in the workbook until it produces your preferred result. The screen below shows an example in cell F25.

Next, select and copy the edited formula from the Formula Bar in the workbook as shown below. Open the template and paste the formula into the template’s cell. It is important to copy and paste using the Formula Bar since copying and pasting the cell will create a link from the template to the workbook.

Test other formula changes and additions and explore the available named ranges by viewing the contents of the Name box or by reviewing the LIMS field names on row one of the available data worksheets such as LIMSData and AnalysisData.

Use these techniques with copies of any of the example Excel export templates to get started creating your own reports to present your LIMS data.