Crosstab Export

How can I use Crosstab Export Templates to create a custom header and footer on my tables?

Crosstab Export Templates The crosstab export now permits selection from multiple customizable crosstab templates that can display site name and…

Crosstab Export Templates

The crosstab export now permits selection from multiple customizable crosstab templates that can display site name and address and station name information. Custom logos can also be printed on the Excel reports. Templates features and examples including their output, are provided below and allow you to use a custom headers and footers with the crosstab. How to set templates up and modify them is outlined below along with samples of each templated provided. Note that while you must select an Export Template, you can choose a blank one from the Select Export Template dropdown shown below. If none are shown, click Locate to select one.

Crosstab Wizard Grouping Formatting tab select template

The crosstab templates are saved in the Enviro Data C:\Enviro\EData8\Templates folder. Because each template file contains coded instruction on the format to be created, Geotech strongly recommends that the first time you open one, promptly do a Save As and save it with a different name in the C:\Enviro\EData8\Templates folder and add it to the Datasheet. This way you will have your own copy that can be used to create new custom formats. A several example templates are provided with Enviro Data.

crosstab example using template 8d

Note: for informatoin on the Version 8D feature adding NL to reg. limits or NM to results please see the FAQ on How can I show in a crosstab that no measurement taken for a sample or that there was no Reg. Limit for a specific parameter?

Crosstab templates are saved in the Enviro Data C:\Enviro\EData8\Templates\CrossTabTemplates folder shown below. Because each template file contains coded instruction on the format to be created, Geotech strongly recommends that the first time you open one, promptly do a Save As and save it with a different name in the C:\Enviro\EData8\Templates\CrossTabTemplates folder and add it to the list of available templates as shown below. This way you will have an original copy that can be used to create new custom formats. A several example templates are provided with Enviro Data. Instructions for modifying these templates can be found in the User Documentation. 

crosstab template files 8d

The Crosstab Templates from is used to select and locate the crosstab templates. You can also add a friendly Description to these files and if needed you can also delete any existing template files that are not needed. Any new template (XLSM) files should be copied into your C:\Enviro\EData8\Templates\CrossTabTemplates folder or a shared location on a server.

crosstab template selection form 8d

This is the Datasheet form of the Crosstab Templates table. 

selecting blank crosstab template 8d

For more information on creating, editing and using crosstab templates please see the Crosstab Export Template section of the Documentation. 

What causes a 'Record To Large' error when creating a crosstab?

When retrieving a large number of samples to produce a crosstab, you may encounter the following error.  A query can…

When retrieving a large number of samples to produce a crosstab, you may encounter the following error. 

Visual Basic Run-Time Error 3047 Record is too large.

A query can be only 1000 character wide. When the crosstab export runs it attempts to group column information and build queries to fall within this limit. The text in a query can be only 1000 character wide. Currently the crosstab export pulls 150 columns for each query. So if you select a large number of samples, the export may actually run 3 queries to put the data on the Excel sheet.

This problem can occur in cases where the FieldSampleIDs and/or other information are very long, or if you have a lot of reg limit exceedances, since the background color is part of the data export.

The solution is to reduce the number of samples selected, check field sample IDs, and other items to reduce the size of the query.

Other factors may also be in play in that the VIEWER may need some maintenance. To correct this go to Maintain Database --> and click Clear Temporary Tables then on the Access menu go to Database Tools and click Compact and Repair Database.

How can I display multiple regulatory limits in my crosstab report?

The crosstab report will display up to 12 limits but to display multiple regulatory limits in a crosstab or other…

The crosstab report will display up to 12 limits but to display multiple regulatory limits in a crosstab or other report that supports them, you must first setup a regulatory limit group and then add the limits you require to this group. When creating groups, we recommend naming your group in a way you can identify it as a group such as  using an “_” (underscore character) in front of the name to differentiate a single limit from a group.

Additionally, you must have checked the Display Reg Limits check box shown below. 

display reg limits in crosstab

For more information see the FAQ on Why are my regulatory limits not showing up in reports?

Why am I getting a Run-Time Error 1004 when creating a Crosstab Export?

The Crosstab Export in Enviro Data Version 2012 B 1.987 and Version 8 uses Microsoft Visual Basic for Applications (VBA)…

The Crosstab Export in Enviro Data Version 2012 B 1.987 and Version 8 uses Microsoft Visual Basic for Applications (VBA) in Excel to create the final spreadsheet report. When the export is run, a VBA macro attempts to set or get the printer page setup properties for the sheet(s) in the Excel workbook. If a printer is not instaled, an error will occur as Excel cannot set the page setup properties. See here for more details

run time error1004

To correct this error when you already have a printer installed, you need to make that printer the default. If you do not have an installed printer, add a printer to Windows and make it the default printer. We recommend setting as a printer the Microsoft Print to PDF.

To change or set the printer In Windows 10 open Settings --> Devices --> Printers & Scanners and select/install a printer. Generally you should let Windows 10 manage the default printer. To allow this, check the check box shown at the bottom of the figure below.

printers scanner settings

In older versions of Windows, the settings to add a printer and make it the default may be found in the Control Panel.

set up printer