Create and Format Crosstab Reports

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Reports > Crosstab Reports >

Create and Format Crosstab Reports

A Crosstab report can be created from any EQuIS Professional grid report output.

 

Note: Crosstab reports can potentially be created from any data in EQuIS Professional grid output such as tables, views or even output from another Crosstab. However, non grid report data are not fully supported and not all features may be available or work correctly.

 

The example shown here uses the Analytical Results II Report on the Springfield data using the following parameter selections.

 

Parameter

Selection

Location Group

MonitWells

Sample Matrix

WG

Result Analyte Group

_PERC DAUGHTERS

Result Unit

ug/L

 

To create a Crosstab report from the grid report output in EQuIS Professional, click the Crosstab Button as shown below.

 

15068-CrosstabButton

 

This opens the Crosstab Configuration Form, which is shown below.

 

15068-CrosstabForm

 

Configuring the Crosstab

 

The Crosstab Form has the following sections.

 

Available Columns

 

15068-AvailableColumns

 

All of the fields that can be selected for the report are shown in the Available Columns list below the Report Name as shown above. A search bar at the top of the list can be used to quickly find fields from the report.

 

Note: In previous versions, the top label for this section displayed Available Columns. This has been changed; the label now shows the name of the report from which the Crosstab report is being created. It will either be the System report name, the System report name with the appended parameters or the saved user report name, if used.

 

Use the drag and drop feature to place the desired fields in the appropriate location (Column Header(s), Row Header(s) or Tabbed Data section). Select multiple fields by using the Shift and/or Ctrl buttons on the keyboard while selecting fields.

 

This example uses the following report columns in the following sections.

 

Parameter

Selection

Column Headers

CHEMICAL_NAME

Row Header

SYS_LOC_CODE

SYS_SAMPLE_CODE

Tabbed Data

REPORT_RESULT_TEXT

REPORT_RESULT_UNIT

 

Remove any field that has been added to a section by selecting the field in that section and clicking the Delete key. A confirmation dialog will appear to make sure you want to delete that field from that section.

 

Column Header(s)

 

15068-ColumnHeaders

 

Each column header will appear as a row across the top of the resulting report for every unique combination of column header values in the report. Each field can have the following settings.

 

Caption: The displayed column label that appears to the left of the Column header row. The default text that is displayed is the Column (Field) name.

Sort: Crosstab report columns can be sorted first by column headers explicitly set with a sort of "Asc" for Ascending or "Desc" for Descending.  

Display: You can set a column to Merge or to one of several Exceedance formats. Setting Display to Merge will result in Column Header values that are the same to be combined into one merged cell when exporting the Crosstab report to Excel.

Hide: Check this to hide this column header row in the resulting Crosstab report. It may be useful to hide a column header row that is used for sorting or Detection formatting.

 

Row Header(s)

 

15068-RowHeaders

 

Each row header will appear as a column down the left side of the resulting report for every unique combination of row header values in the report. Each field can have the following settings.

 

Caption: The displayed row label that appears on top of each row header column. The default text is the Column (Field) name.

Aggregate: Row headers can display a calculated aggregate value of all the results in that row. The following aggregate options may be selected.

oAverage

oCount, Count (Distinct), Count (Non-Null), Count (Null)

oFirst

oLast

oMaximum

oMinimum

oMedian

oStandard Deviation

oSum

 

For more on Aggregate features, refer to the help article Aggregate Feature.

 

Note: The same field (from the Available Columns list) needs to be added to the Tabbed Data section and have the same aggregate selection set or an error will occur when trying to run the Crosstab.

 

Sort: Crosstab report rows can be sorted first by row headers explicitly set with a sort of "Asc" for Ascending, or "Desc" for Descending.

Display: Row headers can be set to Group By, Group By (Sum), Group By (Avg) or to one of several Exceedance formats. When one of the Group By options is selected, all rows with the same value for the selected report field and will be preceded by a separator row displaying the value the rows have been grouped by.

Hide: Checking this will hide this row header column in the resulting Crosstab report. It may be useful to hide a row header column that is used for sorting or Detection formatting.

 

Note: By default, all headers (both Column Headers and Row Headers) are sorted ascending, in the order they are listed in their respective sections. However, specifying an explicit sorting on that header will cause that header to be sorted before other columns that have not been set to an explicit sorting.

 

Tabbed Data

 

15068-TabbedData

 

The fields selected in the Tabbed Data section make up the body of the report. Each selected field will appear as a column for each corresponding distinct column header value. Each row in these columns will be ordered according to the corresponding row header values. Each field can have the following settings.

 

Caption: The field label displayed above each column of Tabbed Data. The default value is the Column (Field) name.

Aggregate: The same aggregate options as defined in the Row Header(s) section above. For more on Aggregate features, refer to the help article Aggregate Feature.

Referenced Aggregate: Specifying a different field name here along with an Aggregate option selection will result in this column displaying values for this field that match the specified aggregate calculation. For example, selecting the CHEMICAL_NAME field in the Tabbed Data section while setting the Aggregate setting to "Maximum", and setting the Referenced Aggregate setting to "REPORT_RESULT_VALUE" will display the CHEMICAL_NAME value with the maximum REPORT_RESULT_VALUE value for that corresponding row/column.

Format: Incorporate the value in specified text where {0} represents the value. For example, selecting the REPORT_RESULT_TEXT field and setting Format to "data = {0}" will output the text replacing {0} with the actual value. Note that this will not be applied when an Aggregate function is also applied.

Null: Text to be displayed for every value that is empty or null.

 

Options

 

15068-Options

 

The following options can be configured in this section.

 

Separate tabbed columns: Each separate field added to the Tabbed Data section appears as a separate column in the Crosstab report.

Combine tabbed columns: The values from the different Tabbed Data fields are combined into one column per Column header grouping.

Separate tables by each unique: Allows for the simple creation of a separate Crosstab table for each unique value in the selected field.

Limit columns per page to: Allows for a limited number of columns per page. This applies to exporting to pdf, and when printing.

Use Excel Template: Apply the Crosstab report data to a pre-formatted Excel template when exporting to Excel. For more information refer to the help article Creating Excel Templates.

Row aggregates last: If Row Aggregates are used, checking this box will display the Row Aggregates columns at the end of the Crosstab output, rather than in the row headers.

Show duplicates only: Checking this option only shows duplicate values. This is helpful in designing the configuration. See the note below about duplicates.

 

The help article Options offers more information related to the options in this section.

 

Running the Crosstab

 

Click the Go Arrow right - 08(1) button to run the Crosstab.

 

In this example, the following Crosstab report is the result. It outputs to a single grid table including the CHEMICAL_NAME column headers at the top, the SYS_LOC_CODE and SYS_SAMPLE_CODE row headers to the left of the main report columns, and the REPORT_RESULT_TEXT and REPORT_RESULT_UNIT tabbed data columns per each column header value.
 

15068-Output

 

Note: When you run the crosstab, you may find what appear to be duplicate values in the tabbed data section of the crosstab. These cells will be highlighted in the Crosstab report as shown below.

 

Duplicate Values

Duplicate Values

 

This can occur when a value that would make a result unique (i.e. SYS_SAMPLE_CODE, FRACTION, START_DEPTH, MATRIX, SAMPLE_DATE, etc.) is not utilized in either the Column Header or Row Header sections of the crosstab. Duplicates will not necessarily have the same value.

 

To resolve this, return to the Crosstab configuration, and add a Column Header or Row Header to make these values distinct.

 

If you do not want to add any further row or column headers to distinguish the values in the tabbed data, use an aggregate to have a single value.

 

Other Crosstab Actions

 

The toolbar at the top of the Crosstab Form and output provides the following functionality.

 

Config Toolbar: 15068-Toolbar   Output Toolbar: 15068-ToolbarOutput

 

Button

Name

Config / Output

Description

OpenFile

Open

Both

Open a previously saved Crosstab configuration (*.xml file).

Save-01

Save

Both

Save the current crosstab configuration for later use.

Arrow right - 08

Go

Config

Run the Crosstab (create Crosstab table(s) based on current configuration).

Arrow left -08

Go Back

Output

Go back and view/edit the current Crosstab configuration.

Publish FormReportCrosstab-Data-Export(32)

Publish

Both

Publish the Crosstab report to the database. Clicking this will open the Crosstab Publisher Form.

Filter-Standard-32-green

Filter

Output

Adds filters to table column headers

Filter-New(1)

Removes filters from the columns

Pin(1)

Pin

Output

Pin columns for easier horizontal scrolling within Crosstab Report.

Binoculars-02(1)

Find

Output

Find and Replace tool

Settings-32-green

Properties

Output

Opens a Properties pane to the left of Crosstab output. The Properties pane displays advanced display options for the currently selected row, column or cell in the Crosstab output table.

Release Data-Recycle-Bin(32)

Release

Output

Release source data to reduce memory usage.

Crosstab-Windows-08-WF(32)

Crosstab

Output

Create a crosstab from the current output.

Various

Export

Output

Export buttons only show one at a time. Click down arrow next to the displayed export button to see the full list of export options.

 

Excel Export (1)

Export the Crosstab report(s) to Excel.

GoogleEarth32

Export the Crosstab report(s) as a Google Earth file. The Crosstab must include SYS_LOC_CODE, LATITUDE, and LONGITUDE fields as Row Headers.

microsoft_outlook32

Export the Crosstab report(s) as an Excel file attachment in an email message in Outlook.

.PDF Export(1)

Export the Crosstab report(s) as a pdf file.

Access(1)

Export the Crosstab report(s) as an Access file.

File Next-WF(1)

Export the Crosstab report(s) as a text file.

Check Mark(1)

Toggle whether to open the newly created file after exporting. If set, icon will be highlighted by a surrounding box.

Graph-01

XY Chart

Output

Click to create an XY chart with crosstab results. Refer to the help articles below for more information about XY charts.

 

XY Chart Basics: Create, Clear, Save & Load Charts and Parameters

Export Charts

Remove Series from Charts
 

Graph-02

XYZ Chart

Output

Click to create an XYZ chart with crosstab results. Refer to the help article Create and Export XYZ Chart for more information about XYZ charts.