Crosstab Export

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.

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

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.

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.

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. 

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.

This is the Datasheet form of the Crosstab Templates table. 

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

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

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.

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 installed, an error will occur as Excel cannot set the page setup properties. See here for more details.

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 your computer to usethe Microsoft Print to PDF printer.

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.

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

Some parameter’s values cannot be saved in a numerical field. These values might be based on qualitative characteristics such as colors, ignitability, effervescence, or something else that doesn't have a number value associated with it. (This doesn't mean that categorical data cannot have numerical values however,) In these situations, result values can be imported using the CatResults field found in the DTS8 or a User-Defined import. These values will be stored in Analyses,CatResult field and the Value field is left empty.

Before you can use a Cat. Results, they must be in the CategoricalResultType lookup table with the parameter they represent and the meaning. Note that these must each be a unique record set containing the Parameter, Cat Result and CatResultMeaning. Cat. Results can be edited and added using the Cat. Result Types button in the Miscellaneous section of Manage Lookups form in the Editor.

Categorical Result Types form.

Categorical Result Types form in Datasheet view.

To display an analysis with cat. results, check the Display Categorical Result in the Display Options form as shown below. 

Cat. Results are shown in the Value and Flag column when doing a List in Select Data.

Note: Enviro Data Versions prior to 8D, had an indexing error in the CategoricalResultType table that prevented the storage of the same CatResult with more than one parameter. Contact Geotech Support for a fix if you encounter this issue.