I 1
How to Use the SERVICES_Retrieve Excel File to
Download Data from ATTAINS
Version: 5/15/2019
Purpose: A Microsoft Excel file has been set up to query ATTAINS Web Services and retrieve assessment
decisions about an Organization such as a State, Territory or Tribe. This file can only retrieve Assessment data
for one Organization and Cycle at a time. This document explains what types of data the Excel File provides, and
how to adjust the query input parameters.
Here are some examples of the data this file can be used to query:
Query for a list of all the Assessment Unit IDs in the state and their associated water types
Query for the overall EPA IR Category for each Assessment in a state
Query for all the Assessments where EPA IR Category = 5 in a specific Assessment Cycle (the CWA
Section 303(d) List for that Integrated Reporting cycle)
Query for all the Uses that are Not Supporting
Query for all the waters that were assessed for a specific Use
Query for all the parameters that were Causes of impairment and the Associated Uses
Audience: Anyone who wants to query ATTAINS for Assessment decisions for an Organization using the
predefined SERVICES_Retrieve Excel file. The file connects directly to ATTAINS Web Services and downloads the
data into a predefined tabular format. Excel 2016 or newer, or the Power Query extension, is required to use
this file.
For information about the ATTAINS Web Services, please refer to the "How to Access and Use ATTAINS Web
Services" document.
General Information About the File
This predefined Excel file contains five spreadsheet tabs. General information is provided here, but more
detailed information about the columns in each tab are provided at the end of this document.
1. AssessmentUnits - This tab contains information about the definition of the Assessment Units, such as
the Assessment Unit Identifier, Assessment Unit Name, the Water Type, the Water Size, and whether
the Assessment Unit Status is 'Active' or 'Retired'.
2. Assessments_General - This tab contains information about the overall Assessment decision of the
Assessment Unit (one row per Assessment Unit). It includes the overall EPA IR Category for the water,
any rationale text that was provided, the Cycle Last Assessed and the Year Last Monitored.
3. Uses -This tab provides Assessment decision data about each Assessment Unit and Use combination.
So, there can be multiple rows per Assessment Unit in this tab.
4. Parameters - This tab provides Assessment decision data about each Assessment Unit and monitored
parameter combination. So, there can be multiple rows per Assessment Unit in this tab.
5. Organizations - Most queries require the Organization ID for the state, territory or tribal Organization.
This tab provides the crosswalk between state abbreviation (in the "name" and "code" columns) and
Organization ID (in the "context" column).
-------
Page | 2
How to Query for a Different Organization and integrated Reporting Cycle
This SERVICES_Retrieve Excel file is initially set to query for South Dakota's information (state code = "SD" or
Organization ID = "SDDENR").
1. To edit the file to retrieve a different Organization's data, first look up the Organization ID which is
found in the "context" field of the Organizations tab. You will need this information. For this example,
we will use Tennessee's data (Org ID: TDECWR).
2. Go to the "Data" tab in Excel. Then click on "Queries & Connections".
File Home Insert Page Layout Formulas
fe
Get
Data *
Review View
[J] From Text/CSV E© Recent Sources
From Web |7' Existing Connections
3 From Table/Range
Get & Transform Data
a!
Data
l~1 Queries & Connections
Refresh
All »
[H| Properties
Queries & Connections
That opens the "Queries and Connections" sidebar. At the top, under
the "Assessments Parameters" folder, there are three "Connection
Only" items. This file is set to query for Organization, Cycle, and IR
Category. The values in parentheses show the currently set values,
a. REQUIRED: Double-click on the "OrganizationParm" item (see
the image on the right). That opens the Power Query Editor
(see image below) and shows you the current value (in this
example, 'SDDENR'). It is looking for the Organization ID of the
data to be queried (in this case, 'TDECWR'). Change the value to
TDECWR' and click the "Close & Load" button.
Close Si
Load T
ffl
Close &
Load T
Pi & Properties
L£! gj
Advanced Editor
Refresh
Preview Manage T
Query
Current Value
Choose Remove
Columns' ColumnsT
Manage Columns
rrn rrtl
Keep Remove
Rows" Rows -
Reduce Rows
SDDENR
Manage Parameter
Qj
Iicy fl
[~ Properties
£~f Advanced Editor
Refresh
Preview T iManage T
Query
LfflJ
X
Choose Remove
Columns" Columns"
Manage Columns
rrn rrrI
Keep Remove
Rows- Rows -
Reduce Rows
Queries & Conn.. *' x
Queries Connections
J queries
J Assessments Parameters [3]
II OrganizationParm (SDDE..
Connection only.
II CycieParm (2018)
Connection only.
HI IRCategory (4A)
Connection only.
Other Queries [5]
m AssessmentUnits
372 rows loaded.
E£i Assessments_General
84 rows loaded.
B3 Uses
319 rows loaded.
0 Parameters
700 rows loaded.
ED Organizations
75 rows loaded.
Current Value
tdecwr|
Manage Parameter
-------
Page | 3
b. REQUIRED: Double-click on the "CycleParm" item. In the Power Query Editor window that
opens, change the value to the four-digit Integrated Reporting Cycle year (formatted as YYYY)
that you would like to query for the selected Organization. (For our example, we will use
'2018'.) Then click on the "Close & Load" button.
File
Home Transform Add Column
View
r~k| ^ Properties
jf} Advanced Editor
H-
H
JJ
mm rm
Close &
Refresh
Choose
Remove
Keep Remove
| Load
j
Preview M Manage T
ColumnsT
Columns T
Rows' Rows *
Close
Query
Manage Columns
Reduce Rows
>
Current Value
-------
I 4
Appendix
AssessmentUnits Tab - Contents
Column Name
Column Contents
Comment
organizationldentifier
The unique identifier of the
Organization. An Organization can
be a state or territorial agency, or
a tribe.
The list of Organization IDs can be
found in the "context" column of
the "Organizations" tab in the file.
organizationName
The name of the Organization.
This is typically the state, territory,
or tribal name.
assessmentUnitldentifier
The unique identifier for the water
or section of water.
This is provided by the state. It
typically starts with the state,
territory or tribal abbreviation.
assessmentUnitName
The name associated with the
Assessment Unit ID.
locationDescriptionText
The description of the location, as
provided by the Organization.
stateCode
The state, territory, or tribe
abbreviation.
statuslndicator
The status indicates whether the
Assessment Unit is Active or
Retired.
A = Active
R = Retired
waterTypeCode
The water type associated with the
Assessment Unit.
waterSizeNumber
The size of the Assessment Unit
for the Water Type, as provided by
the Organization.
unitsCode
The units associated with the
Water Size Number.
Assessments_GeneraI Tab-Contents
Column Name
Column Contents
Comment
assessmentUnitldentifier
The unique identifier for the water
or section of water.
This is provided by the state. It
typically starts with the state,
territory or tribal abbreviation.
agencyCode
This indicates the Organization
that made the Assessment.
S = State
T = Tribe
E = EPA
trophicStatusCode
This represents the trophic status
for the Assessment Unit.
rationaleText
This is the rationale for the
assessment conclusion.
-------
I 5
Column Name
Column Contents
Comment
epalRCategory
This refers to the single overall
EPA Integrated Reporting
Category. A brief explanation is
provided in the Comment column.
Please see the EPA Integrated
Reporting Guidance for a full
explanation of the different
categories.
1 = All Uses have been
assessed and all are
supporting Water Quality
Standards.
2 = All assessed Uses are
supporting Water Quality
Standards, but may have
one or more Uses that
were Not Assessed.
3 = Insufficient
Information to make an
assessment decision, or
Not Assessed.
4a = impaired by a
pollutant but already has a
TMDL
4b = impaired by a
pollutant but doesn't need
a TMDL since other
pollution control measures
are in place
4c = impaired by
something that is not a
pollutant
5 = impaired by a pollutant
and still needs a TMDL
cycleLast AssessedT ext
The Integrated Reporting cycle
that an Assessment decision was
last made for the Assessment Unit.
This is a four-digit year in the YYYY
format.
yearLastMonitoredText
The most recent year that
monitoring data was collected for
the Assessment Unit.
Uses Tab-Contents
Column Name
Column Contents
Comment
assessmentUnitldentifier
The unique identifier for the water
or section of water.
This is provided by the state. It
typically starts with the state,
territory or tribal abbreviation.
useName
The name of the designated Use.
useAttainmentCode
The water quality assessment
decision about the Use.
F = Fully Supporting
N = Not Supporting
1 = Insufficient Information
X = Not Assessed
-------
I 6
Column Name
Column Contents
Comment
threatenedlndicator
This indicates whether the water is
N = Not Threatened
considered "Threatened" under
Y = Threatened
Clean Water Act Section 303(d). A
Use that is Fully Supporting but
also "Threatened" is considered
part of the Section 303(d) List of
impaired waters that need a
TMDL.
trendCode
This is the code representing the
water quality trend for this use.
agencyCode
This indicates the Organization
S = State
that made the Assessment.
T = Tribe
E = EPA
Parameters Tab-Contents
Column Name
Column Contents
Comment
assessmentUnitldentifier
The unique identifier for the water
This is provided by the state. It
or section of water.
typically starts with the state,
territory or tribal abbreviation.
parameterStatusName
The assessment decision as to
Cause = cause of
whether the monitored parameter
impairment
was meeting standards or not.
Insufficient Information =
monitored but there was
not enough information to
make an assessment
determination
Meeting Criteria =
monitored but found to be
meeting standards
Meeting Threshold =
monitored but found to be
meeting thresholds. This
is typically used if there
are not actual water
quality standard criteria
for the parameter.
Not Assessed = this
parameter was not
assessed
Not Meeting Threshold = a
parameter that does not
have water quality criteria
but that has accepted
threshold limits was
monitored and found not
to be meeting the
threshold
-------
| 7
Column Name
Column Contents
Comment
parameterName
The parameter that was
Examples: pH, Temperature,
monitored and assessed.
Aluminum
associatedUseName
This is the designated use that is
related to the parameter.
parameterAttainmentCode
This is the code indicating the
Insufficient Information =
attainment status for this
monitored but there was
parameter for this specific Use.
not enough information to
make an assessment
determination
Meeting Criteria =
monitored but found to be
meeting standards
Meeting Threshold =
monitored but found to be
meeting thresholds. This
is typically used if there
are not actual water
quality standard criteria
for the parameter.
Not Assessed = this
parameter was not
assessed
Not meeting criteria =
Does not meet the water
quality criteria
Not Meeting Threshold = a
parameter that does not
have water quality criteria
but that has accepted
threshold limits was
monitored and found not
to be meeting the
threshold
cycleFirstListedText
This is the cycle that the
Assessment Unit was first listed for
this cause of impairment
parameter.
cydeScheduledForTMDLText
This is the cycle when the
organization anticipates
submitting the TMDL for EPA
approval.
CWA303dPriorityRankingText
This is the DWA 303(d) priority for
High
developing a TMDL
Medium
Low
associatedActionldentifier
This is the unique code (Action ID)
identifying the Action that
corresponds to this cause of
impairment.
-------
I 8
Column Name
Column Contents
Comment
pollutantlndicator
This is a flag indicating whether or
not the cause of impairment is a
pollutant.
Y = Yes, it is a pollutant
N = No, it is not a pollutant
Organizations - Contents
Column Name
Column Contents
Comment
name
State Code
code
State Code
context
Organization Identifier
------- |