Skip to content

Search Page Visualizations

Jump to bottom
Mike Bray edited this page May 16, 2017 · 17 revisions

Search Page Visualizations

The Search page contains four main visualizations:

The queries behind each of these visualizations are dependent on the combination of user-selected filters. There are two types of filters - Award and Budget. For the purpose of this document, Budget filters are defined as the below five filters, while Award filters are all other filters:

  • Time Period (FY only)
  • Object class
  • Budget function
  • Federal account
  • Funding agency

When using /transactions/total, File C fields will need to be prepended with awards__financial_set__.

When using accounts/awards/total, most award parameters will need to be prepended with award__, with the exception of award parameters that begin with award__financial_set__, which can be queried without any prefix (because the prefix represents File C, which this endpoint represents).

Spending Over Time

Unfiltered

/tas/categories/total/

Spending over time by default should show all spending, including non-award spending, both of which are aggregated within /tas/categories/total/.

{
	"field": "obligations_incurred_by_program_object_class_cpe",
	"group": "submission__reporting_fiscal_year",
	"order": ["submission__reporting_fiscal_year"],
	"aggregate": "sum",
	"filters": []
}
Budget Filters

/tas/categories/total/

This endpoint utilizes File C linkage to apply budget filters to spending data. We use /tas/categories/total/ because we want to search within both award and non-award spending.

{
	"field": "obligations_incurred_by_program_object_class_cpe",
	"group": "submission__reporting_fiscal_year",
	"order": ["submission__reporting_fiscal_year"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"field": ["reporting_period_start", "reporting_period_end"],
			"operation": "range_intersect",
			"value": "2017",
			"value_format": "fy"
		}]
	}, {
		"combine_method": "OR",
		"filters": [{
			"field": "treasury_account__budget_function_title",
			"operation": "in",
			"value": []
		}, {
			"field": "treasury_account__budget_subfunction_title",
			"operation": "in",
			"value": ["Other income security"]
		}]
	}, {
		"field": "object_class__major_object_class",
		"operation": "in",
		"value": ["10"]
	}]
}
Award Filters

/transactions/total/

Once award filters (anything that is not a budget filter) are applied, we can only search within awards, since that is the only place where the filter data exists. This requires an endpoint change to /transactions/total/. It is safe to use /transactions/total/ because we are not grouping on a File C field, so there is no risk of multiple aggregations occurring.

{
	"field": "federal_action_obligation",
	"group": "action_date__fy",
	"order": ["action_date__fy"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"field": "federal_action_obligation",
			"operation": "range",
			"value": [1000000, 25000000]
		}]
	}]
}
Both

/transactions/total/

Because award filters are included, we can only search within awards, which requires us to use /transactions/total. It is safe to use /transactions/total/ because we are not grouping on a File C field, so there is no risk of multiple aggregations occurring.

{
	"field": "federal_action_obligation",
	"group": "submission__reporting_fiscal_year",
	"order": ["submission__reporting_fiscal_year"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"field": ["period_of_performance_start_date", "period_of_performance_current_end_date"],
			"operation": "range_intersect",
			"value": "2017",
			"value_format": "fy"
		}]
	}, {
		"combine_method": "OR",
		"filters": [{
			"field": "award__financial_set__treasury_account__budget_function_title",
			"operation": "in",
			"value": []
		}, {
			"field": "award__financial_set__treasury_account__budget_subfunction_title",
			"operation": "in",
			"value": ["Other income security"]
		}]
	}]
}

Spending by Category

Budget Category

Unfiltered

/tas/categories/total/

This visualization by default should group all spending (both award and non-award) by their budget function titles. We are using /tas/categories/total/ because we want both award and non-award spending.

{
	"field": "obligations_incurred_by_program_object_class_cpe",
	"group": ["treasury_account__budget_function_title"],
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [],
	"limit": 5,
	"page": 1
}
Budget Filters

/tas/categories/total/

We use /tas/categories/total/ because we want to return spending for both award and non-award spending.

{
	"field": "obligations_incurred_by_program_object_class_cpe",
	"group": ["treasury_account__budget_function_title"],
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		"field": "object_class__major_object_class",
		"operation": "in",
		"value": ["10"]
	}],
	"limit": 5,
	"page": 1
}
Award Filters

/accounts/awards/total/

We use /accounts/awards/total/ because this endpoint provides linkage via File C to award-only spending, grouped by budget function. We are only searching within awards because award filters can only be applied to awards. We cannot use /transactions/total/ because we are grouping on a File C field, which has the risk of multiple aggregations occurring in the /transactions/total endpoint but not on /accounts/awards/total/.

{
	"field": "transaction_obligated_amount",
	"group": ["treasury_account__budget_function_title"],
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"field": "award__awarding_agency__toptier_agency__name",
			"operation": "in",
			"value": ["Department of Health and Human Services"]
		}]
	}],
	"limit": 5,
	"page": 1
}
Both

/accounts/awards/total/

We can only search within awards because award filters are included in this query (and we can only apply award filters to award spending). As a result, we will use /accounts/awards/total/ just like in the Awards Filter query. We cannot use /transactions/total/ because we are grouping on a File C field, which has the risk of multiple aggregations occurring in the /transactions/total endpoint but not on /accounts/awards/total/.

{
	"field": "transaction_obligated_amount",
	"group": ["treasury_account__budget_function_title"],
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"combine_method": "AND",
			"filters": [{
				"field": "award__date_signed",
				"operation": "greater_than_or_equal",
				"value": "2016-10-01"
			}, {
				"field": "award__date_signed",
				"operation": "less_than_or_equal",
				"value": "2017-09-30"
			}]
		}]
	}, {
		"field": "award__financial_set__object_class__major_object_class",
		"operation": "in",
		"value": ["10"]
	}],
	"limit": 5,
	"page": 1
}

Awarding Agency

Unfiltered

/transactions/total/

This visualization returns only award spending, grouped by Awarding Agency. It does not return non-award spending because Awarding Agency is an award filter - which can only be applied to awards. Awarding Agency is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field": "federal_action_obligation",
	"group": "awarding_agency__toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [],
	"limit": 5,
	"page": 1
}
Budget Filters

/accounts/awards/total/

This query utilizes the /accounts/awards/total/ endpoint to take advantage of the endpoint's File C linkage in order to query using budget filters. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field": "transaction_obligated_amount",
	"group": "award__awarding_agency__toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		"field": "object_class__major_object_class",
		"operation": "in",
		"value": ["40"]
	}],
	"limit": 5,
	"page": 1
}
Award Filters

/transactions/total/

This query utilizes /transactions/total/ because it only needs award spending. This is because Awarding Agency is an award filter - which can only be applied to awards. Awarding Agency is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field": "federal_action_obligation",
	"group": "awarding_agency__toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"field": "federal_action_obligation",
			"operation": "range",
			"value": [1000000, 25000000]
		}]
	}],
	"limit": 5,
	"page": 1
}
Both

/accounts/awards/total/

Because budget filters are included the query, we use the /accounts/awards/total/ endpoint. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field": "transaction_obligated_amount",
	"group": "award__awarding_agency__toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [
		{
			"combine_method": "OR",
			"filters": [{
				"field": "transaction_obligated_amount",
				"operation": "less_than_or_equal",
				"value": 1000000
			}]
		}, {
			"field"	: "award__type",
			"operation": "in",
			"value": ["A", "B", "C", "D"]
		}, {
			"field": "object_class__major_object_class",
			"operation": "in",
			"value": ["40"]
		}
	],
	"limit": 5,
	"page": 1
}

Funding Agency

Unfiltered

/tas/categories/total/

This visualization by default should group all spending (both award and non-award) by their toptier Funding Agency names. We are using /tas/categories/total/ because we want both award and non-award spending.

{
	"field": "obligations_incurred_by_program_object_class_cpe",
	"group": "treasury_account__funding_toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [],
	"limit": 5,
	"page": 1
}
Budget Filters

/tas/categories/total/

We use /tas/categories/total/ because we want to return spending for both award and non-award spending.

{
	"field": "obligations_incurred_by_program_object_class_cpe",
	"group": "treasury_account__funding_toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		"field": "object_class__major_object_class",
		"operation": "in",
		"value": ["40"]
	}],
	"limit": 5,
	"page": 1
}
Award Filters

/accounts/awards/total/

We use /accounts/awards/total/ because this endpoint provides linkage via File C to award-only spending, grouped by Funding Agency. We are only searching within awards because award filters can only be applied to awards. We cannot use /transactions/total/ because we are grouping on a File C field, which has the risk of multiple aggregations occurring in the /transactions/total endpoint but not on /accounts/awards/total/.

{
	"field": "transaction_obligated_amount",
	"group": "treasury_account__funding_toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [{
		field: "award__place_of_performance__location_country_code",
		operation: "equals",
		value: "USA"
	}],
	"limit": 5,
	"page": 1
}
Both

/accounts/awards/total/

We can only search within awards because award filters are included in this query (and we can only apply award filters to award spending). As a result, we will use /accounts/awards/total/ just like in the Awards Filter query. We cannot use /transactions/total/ because we are grouping on a File C field, which has the risk of multiple aggregations occurring in the /transactions/total endpoint but not on /accounts/awards/total/.

{
	"field": "transaction_obligated_amount",
	"group": "treasury_account__funding_toptier_agency__name",
	"order": ["-aggregate"],
	"aggregate": "sum",
	"filters": [
		{
			"combine_method": "OR",
			"filters": [{
				"field": "transaction_obligated_amount",
				"operation": "less_than_or_equal",
				"value": 1000000
			}]
		}, {
			"field"	: "award__type",
			"operation": "in",
			"value": ["A", "B", "C", "D"]
		}, {
			"field": "object_class__major_object_class",
			"operation": "in",
			"value": ["40"]
		}
	],
	"limit": 5,
	"page": 1
}

Recipient

Unfiltered

/transactions/total/

This visualization returns only award spending, grouped by Recipient. It does not return non-award spending because Recipient is an award filter - which can only be applied to awards. Recipient is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field":"federal_action_obligation",
	"group":["award__recipient__legal_entity_id","award__recipient__recipient_name"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[],
	"limit":5,
	"page":1
}
Budget Filters

/accounts/awards/total/

This query utilizes the /accounts/awards/total/ endpoint to take advantage of the endpoint's File C linkage in order to query using budget filters. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field":"transaction_obligated_amount",
	"group":["award__recipient__legal_entity_id","award__recipient__recipient_name"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
		{
			"field":"object_class__major_object_class",
			"operation":"in",
			"value":["40"]
		},
		{
			"field":"object_class__major_object_class",
			"operation":"in",
			"value":["40"]
		}
	],
	"limit":5,
	"page":1,
}
Award Filters

/transactions/total/

This query utilizes /transactions/total/ because it only needs award spending. This is because Recipient is an award filter - which can only be applied to awards. Recipient is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field":"federal_action_obligation",
	"group":["award__recipient__legal_entity_id","award__recipient__recipient_name"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
	{
		"combine_method":"OR",
		"filters":[{
			"field":"federal_action_obligation",
			"operation":"less_than_or_equal",
			"value":1000000
		}]
	}],
	"limit":5,
	"page":1,
}
Both

/accounts/awards/total/

Because budget filters are included the query, we use the /accounts/awards/total/ endpoint. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field":"transaction_obligated_amount",
	"group":["award__recipient__legal_entity_id","award__recipient__recipient_name"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
	{
		"combine_method":"OR",
		"filters":[{
			"field":"award__total_obligation",
			"operation":"less_than_or_equal",
			"value":1000000
		}]
	},	{
		"field":"object_class__major_object_class",
		"operation":"in",
		"value":["40"]
	}],
	"limit":5,
	"page":1,
}

CFDA

Unfiltered

/transactions/total/

This visualization returns only award spending, grouped by Recipient. It does not return non-award spending because Recipient is an award filter - which can only be applied to awards. Recipient is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field":"federal_action_obligation",
	"group":["assistance_data__cfda_number", "assistance_data__cfda_title"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[],
	"limit":5,
	"page":1
}
Budget Filters

/accounts/awards/total/

This query utilizes the /accounts/awards/total/ endpoint to take advantage of the endpoint's File C linkage in order to query using budget filters. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field":"transaction_obligated_amount",
	"group":["award__transaction__assistance_data__cfda_number", "award__transaction__assistance_data__cfda_title"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
		{
			"field":"object_class__major_object_class",
			"operation":"in",
			"value":["40"]
		},
		{
			"field":"object_class__major_object_class",
			"operation":"in",
			"value":["40"]
		}
	],
	"limit":5,
	"page":1,
}
Award Filters

/transactions/total/

This query utilizes /transactions/total/ because it only needs award spending. This is because Recipient is an award filter - which can only be applied to awards. Recipient is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field":"federal_action_obligation",
	"group":["assistance_data__cfda_number", "assistance_data__cfda_title"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
	{
		"combine_method":"OR",
		"filters":[{
			"field":"federal_action_obligation",
			"operation":"less_than_or_equal",
			"value":1000000
		}]
	}],
	"limit":5,
	"page":1,
}
Both

/accounts/awards/total/

Because budget filters are included the query, we use the /accounts/awards/total/ endpoint. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field":"transaction_obligated_amount",
	"group":["award__transaction__assistance_data__cfda_number", "award__transaction__assistance_data__cfda_title"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
	{
		"combine_method":"OR",
		"filters":[{
			"field":"award__total_obligation",
			"operation":"less_than_or_equal",
			"value":1000000
		}]
	},	{
		"field":"object_class__major_object_class",
		"operation":"in",
		"value":["40"]
	}],
	"limit":5,
	"page":1,
}

Industry Codes

Unfiltered

/transactions/total/

This visualization returns only award spending, grouped by Recipient. It does not return non-award spending because Recipient is an award filter - which can only be applied to awards. Recipient is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field":"federal_action_obligation",
	"group":["contract_data__product_or_service_code"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[],
	"limit":5,
	"page":1
}

For NAICS, group by:

["contract_data__naics", "contract_data__naics_description"]
Budget Filters

/accounts/awards/total/

This query utilizes the /accounts/awards/total/ endpoint to take advantage of the endpoint's File C linkage in order to query using budget filters. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field":"transaction_obligated_amount",
	"group":["award__transaction__contract_data__product_or_service_code"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
		{
			"field":"object_class__major_object_class",
			"operation":"in",
			"value":["40"]
		},
		{
			"field":"object_class__major_object_class",
			"operation":"in",
			"value":["40"]
		}
	],
	"limit":5,
	"page":1,
}

For NAICS, group by:

["award__transaction__contract_data__naics", "award__transaction__contract_data__naics_description"]
Award Filters

/transactions/total/

This query utilizes /transactions/total/ because it only needs award spending. This is because Recipient is an award filter - which can only be applied to awards. Recipient is not a File C field and we are not applying File C filters, so we can safely use /transactions/total/ without risk of multiple aggregations occurring.

{
	"field":"federal_action_obligation",
	"group":["contract_data__product_or_service_code"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
	{
		"combine_method":"OR",
		"filters":[{
			"field":"federal_action_obligation",
			"operation":"less_than_or_equal",
			"value":1000000
		}]
	}],
	"limit":5,
	"page":1,
}
Both

/accounts/awards/total/

Because budget filters are included the query, we use the /accounts/awards/total/ endpoint. We cannot use /transactions/total/ because we are applying a File C filter, which may result in duplicate values being aggregated.

{
	"field":"transaction_obligated_amount",
	"group":["award__transaction__contract_data__product_or_service_code"],
	"order":["-aggregate"],
	"aggregate":"sum",
	"filters":[
	{
		"combine_method":"OR",
		"filters":[{
			"field":"award__total_obligation",
			"operation":"less_than_or_equal",
			"value":1000000
		}]
	},	{
		"field":"object_class__major_object_class",
		"operation":"in",
		"value":["40"]
	}],
	"limit":5,
	"page":1,
}

For NAICS, group by:

["award__transaction__contract_data__naics", "award__transaction__contract_data__naics_description"]

Spending by Geography

Unfiltered

/transactions/total/

This visualization populates the map by returning award spending data, grouped by state code. This query only returns award data because place of performance and recipient location are award filters, which can only be applied to awards.

{
	"field": "federal_action_obligation",
	"group": "place_of_performance__state_code",
	"order": ["item"],
	"aggregate": "sum",
	"filters": [],
	"limit": 60
}
Budget Filters

/transactions/total/

This query returns award-only spending, with budget filters applied. It is safe to use /transactions/total/ because we are grouping only by place of performance or recipient location (which are not File C fields and thus won't be aggregated multiple times.)

{
	"field": "federal_action_obligation",
	"group": "place_of_performance__state_code",
	"order": ["item"],
	"aggregate": "sum",
	"filters": [{
		"field": "award__financial_set__object_class__major_object_class",
		"operation": "in",
		"value": ["10"]
	}],
	"limit": 60
}
Award Filters

/transactions/total/

This query returns award-only spending, with budget filters applied. It is safe to use /transactions/total/ because we are grouping only by place of performance or recipient location (which are not File C fields and thus won't be aggregated multiple times.)

{
	"field": "federal_action_obligation",
	"group": "place_of_performance__state_code",
	"order": ["item"],
	"aggregate": "sum",
	"filters": [{
		"combine_method": "OR",
		"filters": [{
			"field": "federal_action_obligation",
			"operation": "less_than_or_equal",
			"value": 1000000
		}]
	}],
	"limit": 60
}
Both

/transactions/total/

This query returns award-only spending, with budget filters applied. It is safe to use /transactions/total/ because we are grouping only by place of performance or recipient location (which are not File C fields and thus won't be aggregated multiple times.)

{
	"field": "federal_action_obligation",
	"group": "place_of_performance__state_code",
	"order": ["item"],
	"aggregate": "sum",
	"filters": [
		{
			"field": "award__financial_set__object_class__major_object_class",
			"operation": "in",
			"value": ["10"]
		}, {
			"field": "federal_action_obligation",
			"operation": "less_than_or_equal",
			"value": 1000000
		}
	],
	"limit": 60
}

Spending by Award

Unfiltered
Budget Filters
Award Filters
Both
Clone this wiki locally