Income Statement

I was able to create an acceptable income statement with the attached view. I needed to install the the Views Raw SQL module and Views Aggregator Plus module.

$view = new view();
$view->name = 'income_statement';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'ledger_account_entry';
$view->human_name = 'income statement';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'income statement';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'views_aggregator';
$handler->display->display_options['style_options']['columns'] = array(
'raw_field_1' => 'raw_field_1',
'type' => 'type',
'name' => 'name',
'raw_field' => 'raw_field',
);
$handler->display->display_options['style_options']['default'] = 'type';
$handler->display->display_options['style_options']['info'] = array(
'raw_field_1' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
'has_aggr' => 0,
'aggr' => array(
'views_aggregator_first' => 'views_aggregator_first',
),
'aggr_par' => '',
'has_aggr_column' => 0,
'aggr_column' => 'views_aggregator_sum',
'aggr_par_column' => '',
),
'type' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
'has_aggr' => 0,
'aggr' => array(
'views_aggregator_first' => 'views_aggregator_first',
),
'aggr_par' => '',
'has_aggr_column' => 0,
'aggr_column' => 'views_aggregator_sum',
'aggr_par_column' => '',
),
'name' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
'has_aggr' => 1,
'aggr' => array(
'views_aggregator_group_and_compress' => 'views_aggregator_group_and_compress',
),
'aggr_par' => '',
'has_aggr_column' => 0,
'aggr_column' => 'views_aggregator_sum',
'aggr_par_column' => '',
),
'raw_field' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
'has_aggr' => 1,
'aggr' => array(
'views_aggregator_sum' => 'views_aggregator_sum',
),
'aggr_par' => '',
'has_aggr_column' => 1,
'aggr_column' => 'views_aggregator_sum',
'aggr_par_column' => 'Net Income',
),
);
$handler->display->display_options['style_options']['caption'] = 'Income Statement for the year';
$handler->display->display_options['style_options']['totals_per_page'] = '1';
$handler->display->display_options['style_options']['totals_row_position'] = array(
1 => 0,
2 => '2',
);
/* Relationship: Account entry: Account aid */
$handler->display->display_options['relationships']['account']['id'] = 'account';
$handler->display->display_options['relationships']['account']['table'] = 'ledger_account_entry';
$handler->display->display_options['relationships']['account']['field'] = 'account';
$handler->display->display_options['relationships']['account']['required'] = TRUE;
$handler->display->display_options['relationships']['account']['bundle_types'] = array(
'income' => 'income',
'expenses' => 'expenses',
);
/* Relationship: Account entry: Transaction tid */
$handler->display->display_options['relationships']['transaction']['id'] = 'transaction';
$handler->display->display_options['relationships']['transaction']['table'] = 'ledger_account_entry';
$handler->display->display_options['relationships']['transaction']['field'] = 'transaction';
$handler->display->display_options['relationships']['transaction']['required'] = TRUE;
/* Field: Raw SQL: Raw field */
$handler->display->display_options['fields']['raw_field_1']['id'] = 'raw_field_1';
$handler->display->display_options['fields']['raw_field_1']['table'] = 'views_raw_sql';
$handler->display->display_options['fields']['raw_field_1']['field'] = 'raw_field';
$handler->display->display_options['fields']['raw_field_1']['exclude'] = TRUE;
$handler->display->display_options['fields']['raw_field_1']['raw_sql'] = 'ledger_account_entry.eid';
/* Field: Account: Account type */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'ledger_account';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['relationship'] = 'account';
/* Field: Account: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'ledger_account';
$handler->display->display_options['fields']['name']['field'] = 'name';
/* Field: Raw SQL: Raw field */
$handler->display->display_options['fields']['raw_field']['id'] = 'raw_field';
$handler->display->display_options['fields']['raw_field']['table'] = 'views_raw_sql';
$handler->display->display_options['fields']['raw_field']['field'] = 'raw_field';
$handler->display->display_options['fields']['raw_field']['group_type'] = 'sum';
$handler->display->display_options['fields']['raw_field']['label'] = 'Balance';
$handler->display->display_options['fields']['raw_field']['raw_sql'] = '(Select round(-1*ledger_value_numerator/ledger_value_denominator,2) from field_data_ledger_value f inner join ledger_account_entry ae on f.entity_id=ae.eid WHERE ae.eid=raw_sql_field)';
/* Filter criterion: Date: Date (ledger_transaction) */
$handler->display->display_options['filters']['date_filter']['id'] = 'date_filter';
$handler->display->display_options['filters']['date_filter']['table'] = 'ledger_transaction';
$handler->display->display_options['filters']['date_filter']['field'] = 'date_filter';
$handler->display->display_options['filters']['date_filter']['relationship'] = 'transaction';
$handler->display->display_options['filters']['date_filter']['value']['value'] = '2014';
$handler->display->display_options['filters']['date_filter']['exposed'] = TRUE;
$handler->display->display_options['filters']['date_filter']['expose']['operator_id'] = 'date_filter_op';
$handler->display->display_options['filters']['date_filter']['expose']['label'] = 'Date (ledger_transaction)';
$handler->display->display_options['filters']['date_filter']['expose']['operator'] = 'date_filter_op';
$handler->display->display_options['filters']['date_filter']['expose']['identifier'] = 'date_filter';
$handler->display->display_options['filters']['date_filter']['expose']['remember_roles'] = array(
2 => '2',
1 => 0,
3 => 0,
4 => 0,
5 => 0,
6 => 0,
);
$handler->display->display_options['filters']['date_filter']['granularity'] = 'year';
$handler->display->display_options['filters']['date_filter']['year_range'] = '-3:+0';
$handler->display->display_options['filters']['date_filter']['date_fields'] = array(
'ledger_transaction.timestamp' => 'ledger_transaction.timestamp',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'income-statement';