Reporting – Analyze Data (Tables, Graphs, etc.)

Reporting – Analyze Data – Overview

Tool Location: Reporting > Analyze Data…

The Analyze Data form is the data analysis and interpretation tool.  It gives access to all data collected, and offers several filtering and viewing options. The following tasks can be done with this tool:

·         Produce data tables and reports in multiple different formats (eg. dates across top, locations across top, etc)

·         Create graphs with any number of series using both Y axes.

·         Analyze data with the environmental statistics module.

·         Compare data with user defined criteria.

·         Work with individual or multiple intervals (measure points) at the same time

·         Save commonly used data selections and graph templates

Reporting – Analyze Data – General Usage

image

Note: On some computers Access will shrink the form slightly and the tabs across the top are not shown. If this occurs, just resize the form (by click and dragging an edge) until you can see the tabs.

Users select the intervals and parameters they wish use to create a table, graph, report, etc. Selected data is sorted into sets by the user. Individual graphs or reports are shown for each set.

Data filters are used to filter the data by dates or value qualifiers etc. The user can create output using just the data or include comparisons to applicable criteria. Users can create their own graph templates allowing them to choose and reuse their own colour and symbol schemes, axes scales, etc.

Reporting – Analyze Data – Data Selection

Allows the user to quickly choose the desired interval(s) and data they wish to work with.

Search Options (Top Left)

Quickly filter locations and intervals by making selections from the drop down lists. Choose between Master Locations, Locations and Intervals using the 3 buttons at the top of the drop down lists. Drill up or down the master location-location-interval hierarchy using the up 1 level, down 1 level  buttons (for example, see what intervals are associated with a location by selecting a location first, then pressing the ‘down 1 level’ button.

Note: Temporal data is always associated with an interval and therefore 1 or more intervals must be selected before search for associated data.

Available Data (Top Right)

Once one or more intervals have been selected and the Search button pressed, the list boxes will display a summary of the data available for those intervals. Shown is the parameter name, the date of the first and last record in the database, and the number of records in the database for that parameter for all the selected intervals.

The entire parameter list can be quickly filtered by choosing a parameter group from the list box at the top right. User defined parameter lists can also be selected from this box.

The user can selected the desired parameter(s) they wish to work with by clicking 1 or more parameters in this list. In basic mode each parameter clicked on is added to the Selected Data area. In advanced mode, parameters are not added to the Selected Data area until the down arrow is pressed. This allows the user to manually build up the Selected Data one interval at a time if desired. For example, after adding one parameter to the Selected Data area, the user could re-search the intervals and choose a completely different parameter.

Sets are used to determine if data should appear on the same or individual graphs and reports. Use the drop down list to determine how the sets should be auto-assigned. Sets can also be changed on an individual basis in the Selected Data area.

Selected Data Area (mid section)          

This area of the form displays the currently selected intervals and parameters. The items shown here are used to create the table, graph, report etc. For each item in this list you may choose which graph axis the series will be shown on, what type of graph to show, which Set the parameters is part of, and which trend line, if any, should be shown.

Output Options (lower section)

The lower section of the form is used to select how the data should be output.

Output Types

Two lists are provided for choosing output types. The first list shows the main types such as table, graph or report. The second list displays sub-types of the main selection. See the section Output Types below for further details.

Preview Button

When a format (table, graph, report etc) is selected to the left of this button, pressing this button will display the data in the selected format.

View Raw Data Button

Displays data in database format.  Data can be edited through this function and no filters are applied except for the date filters.

Save/Load Button

Saves or loads templates for reporting. When saving, all selections shown in the mid sections of the form are stored as well as selections on the Report Layout tab. No data filtering options are stored. Clicking on this option also brings up the list of stored graph templates for editing.

Current Graph Template

Allows the user to select the graph template to be use for the graph.

Reset Form Button

Clears all selections from the form.

Output Types

Table

Displays results on screen, suitable for clipping into reports or other documents.  Five sub-types are available:

Normalized:   This option displays data in a format that is the same as that used by the Database. This is the display used when more than one interval is selected.

Spreadsheet-dates at top: Displays parameters down the left side with dates across the top.

Spreadsheet-locations at top: Displays parameters down the left side with intervals across the top.

Spreadsheet-reading names at top: Displays intervals down the left side with parameters across the top.

Spreadsheet-locations and names at top: Displays parameters down the side and locations and names across the top.

Graph

Displays results on a graph, where data permits, that can be saved as an image or sent to a printer. Axes and style (line or area) can be chosen.  Graph templates can be selected, if available.

Report 

Prints selected data to a report which can be sent to the default printer. Four sub-types are available

Spreadsheet-dates at top: Produces a report displaying parameters down the left side and dates across the top.

Spreadsheet-locations at top: Produces a report displaying parameters down the left side and intervals across the top.

Spreadsheet-reading names at top: Displays data with the dates listed down the left side and the parameters across the top.

Spreadsheet-locations and dates at top: Displays data with parameters listed down the left side and the intervals and dates across the top.

Exceedance Summary Report: Produces an Exceedance Summary Report from a template.

Oxford Monthly Report: Displays a list of data for a selected month.

Statistics

Sends the data to the statistics module. A separate manual is used to discuss operation of the statistics module.

Reporting – Analyze Data – Data Filtering

image

Displays a variety of options for managing the reporting of data. These options allow the user to easily filter their data down to the dataset they want to use.

Value Grouping

When preparing a data set for output, you will likely want to choose one of the grouping options listed here. This option basically tells SiteFX what to do in the event that more than one value is available for the selected time period. For example, if the option was set to ‘show the max value for each day’, then if two values for a particular interval and parameter fell on the same day, such as a water sample and a duplicate sample, then the maximum of these values would be used.

Another example is if the option was set to ‘show the average for each quarter’, then if an interval had daily precipitation data for an entire year then the resultant data set would contain only four values....one for each quarter.

When more than one values falls in that period the resultant data set contains a code to tell the user how many values fell in that period. That is shown by the (*v) code where * is the number of values in that period. There is also an additional code used for value modifiers such as ‘<’ or ‘ND’. If more than one value is found in that period and 1 or more of those values had a value qualifier, then a (*q) code is shown to tell the user how many of those values contained modifiers.

When working with normalized (database format) tables or reports, the user can choose the ‘show all data’ option. In these cases, if more than one value occurs on the same date or time period, all values will be shown.

Treatment of Value when <, ND, MDL Present

These options control how SiteFX handles a value when a <, ND (Non-Detect), or MDL (Method Detection Limit) is found along with the value.   This is typical for chemistry data and would appear similar to the following examples. (DL = Detection Limit).

Sample Location                  Sample Date         Qualifier               Value     Units      DL

Location A                            January 2, 2010                                    5.6           mg/L       5.0

Location A                            January 2, 2010     <                              5.0           mg/L       5.0

Location A                            January 2, 2010     ND                          5.0           mg/L       5.0

Location A                            January 2, 2010     <                                              mg/L       5.0

Depending on the needs of the user, it is often necessary to set data that has a < or ND modifier to 0 or half of the detection limit prior to reporting or graphing, etc. When the user has selected to use the detection limit or half the detection limit, SiteFX will first use the data in the value field of the database if it exists, then the value in the MDL field if it does not. To provide additional detail, the options on the Data Filtering form are handled as follows.

Detection Limit

This is the default and most commonly used option.

 

If < or ND is found, if a value is present, the value is used

If < or ND is found, if a value is not present, the DL is used

 

If < or ND is not found, if a value is present, the value is used

If < or ND is not found, if a value is not present, do nothing

 

0.0

If < or ND is found, then use a value of 0

 

If < or ND is not found, if a value is present, the value is used

If < or ND is not found, if a value is not present, do nothing

 

½ Detection Limit if < Detection Limit

If < or ND is found, if a value is present, use ½ the value

If < or ND is found, if a value is not present, use ½ the DL

 

If < or ND is not found, if value is present, the value is used

If < or ND is not found, if value is not present, do nothing

 

½ Detection Limit if < ½ Detection Limit

If both a value and MDL are present, if value > ½ DL, use value

If both a value and MDL are present, if value <= ½ DL, use ½ DL

 

If only a value is present, use the value

 

Data Grouping

Data Grouping – Interval, Location, Master Location

These data grouping options are used to group interval data by interval, location or master location. For example, if you had a borehole with 2 monitoring intervals inside it, you may wish to know the maximum water quality values for the entire borehole, not just each interval. In this case you would choose to ‘group by location’.

In another scenario you may have a borehole nest containing 5 boreholes and want to know the maximum water level elevations for the entire nest. In this case you could choose ‘group by master location’.

Differentiate by Sample Type

All temporal data (especially chemistry data) can be assigned a sample type through SiteFX. The most common example is sample versus sample duplicate for QA/QC purposes.  When unchecked, SiteFX ignores the sample type and therefore data can be grouped and averaged. When checked, SiteFX includes the sample type on tables and reports and therefore the data will be separated based on sample type. This would allow for a side by side comparison of sample versus duplicate data.

Group Data by Reading Type on Reports

On most reports, when this option is unchecked reading names (eg. chemistry) will be listed alphabetically from a to z. When checked, the data will be grouped first by the reading type, then alphabetically. For example, reading types could include types such as metals, VOCs, water levels, climatic, etc.  These types and the readings or parameters within them can be defined by the user within SiteFX.

Date Range

There are 3 ways to specify the date range you wish to use for filtering your data.

1)       Choose a relative time period. The first drop down list contains relative time periods such as ‘the previous two weeks’ or ‘the previous sixty days’.  Selecting an option from this list makes it very quick to filter out the data you wish to use since you don’t need to specify exact dates.

2)       Specify start and end date. When an precise start and end date is required., those dates can be input here. Either type in the dates or double click for a calendar.

3)       Year and Month or Quarter. To jump right to a particular year or year and month (or quarter) choose this option. Instead of one particular month you can also choose ‘All months’ thereby selecting the entire year.

Data Exclusions

Your data set can be quickly filtered further by applying one or more of the following data exclusions.

Qualifiers

Check this option when you want to exclude data based on the value modifier. You can choose one or more qualifiers.

Sample Type

Check this option when you want to exclude data based on the sample type.

Exclude records using the ‘exclude’ flag

If you have found data in your database that you want to exclude but there is no existing filter for it, you can open up the database table using the View Raw Data button and place a check mark in the ‘Exclude’ field for the record(s) you want excluded. Then, by checking this filter on the ‘Data Filter’ tab you can exclude these records. Be sure to record a comment as to why a particular record was excluded.

Exclude MDL in Spreadsheet Tables

For each chemistry record in the database there is an MDL field. In many cases the MDL for a particular parameter changes from time to time or between labs so when the data is displayed in a table or report the data becomes grouped by each different MDL. In most cases this is not desirable so by checking this option, the MDL is left off of the table or report.

Exclude Labels On Graph

Chemistry data often contains values modifiers such as ‘<’ or ‘ND’ (non-detect). Also, by grouping the data by time period, SiteFX may also add qualifiers to the values. The user can choose to hide or show these modifiers on a graph by checking or unchecking this option.

Reporting – Analyze Data – Report Layout

The report layout tab contains options to add headers and footers onto reports. This form is shown below.

image

 

Reporting – Analyze Data – Data Screening

The data screening tab contains options to show certain columns, choose base comparison datasets, and flag data that meets come criteria.

 

image

Reporting – Analyze Data – Notes/Tips

·         Use one or more graph templates to have the graph appear how you want every time. Graphs scales and colours etc can be saved in your template.

Reporting – Analyze Data – Data Extraction

The data analysis screen operates by drilling down through the data as follows.

1.        Initially no intervals are displayed. The list of intervals can be displayed by pressing the find button, and further narrowed down by selecting a filter from one of the drop down boxes.

2.        Select an interval from the window and press the Search button.  The Available Data box will now be populated with a list of data for the intervals selected.

3.        Initially all parameters are displayed.  The list of parameters display can be narrowed to a specific group by selecting the desired group from the Parameter Groups box.

Note: In addition to parameter names, the first and last date of the corresponding chemistry records are displayed as well as the number of data points and the units of measure.

4.        Select one or more parameters from the list (using Shift and Drag, Shift and Click or Control and Click).  The selections will appear in the window in the middle of the form.

5.        Select which data should be displayed using the options under Data Filtering tab. The system will default to showing the maximum value for each day, with no time constraints.  Either of these constraints can be changed using the options on the left side of the form. Select other options on the tab for further filtering of the data as necessary.

6.        Choose how the data should be displayed by choosing an option under the window at the bottom left, either a table, graph or report.  Select an option that appears to the right and a graph template if necessary, and press Preview

Reporting – Analyze Data – Saving Settings

It may be necessary to periodically return to the same data set to produce monthly graphs or reporting.  Combinations of intervals and parameters can be saved and recalled for this purpose.

1.        To save settings, select the required intervals and parameters as described in the Data Extraction section.

2.        Press the Save/Load button.

3.        A new window appears.  Press the Save button.

4.        Type in the template name when prompted. The template now appears in the Saved Selections window.

Reporting – Analyze Data – Recalling Settings

1.        Press the Save/Load button.

2.        Select the desired setting name from the Saved Selections window and press the Load button.  The selected data should now be visible.

Reporting – Analyze Data – Editing Data

While analyzing data with the Analyze Data form, the values for parameters selected in the Available data list can be edited by pressing the View Raw Data button.  Changes made are written directly to the database, there are no saving steps, and consequently no undo functions.