Skip to content

Search Page Visualizations

Mike Bray edited this page May 1, 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).

Unfiltered

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

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

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

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"]
		}]
	}]
}

Unfiltered

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

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

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

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
}


Unfiltered

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

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

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

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
}


Unfiltered

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

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

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

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,
}
Unfiltered

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

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

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

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
}
Unfiltered
Budget Filters
Award Filters
Both
Clone this wiki locally