Is the views module a viable options for creating reports with calculated totals?

Question

I have an application built in Drupal 7 that records properties as nodes and relates property nodes to charge nodes and payment nodes.

I would like to add the ability to view a report by selecting a property and specifying two dates, a start date and an end date for the report, that shows a total of payments, a total of charges and then a balance total for a particular property.

However, the charges content type is only used to record additional charges to a property. There is a monthly service charge which is stored as an amount in a field of the property node. So when generating the report, I simply add one service charge payment for each month the report spans.

A summary of the report:

Select a property and give a date range. Produce a list of charges by :

1. Adding one service charge for each month between the two dates. (Note: The service charge amount is given in a field of the property node)
2. Adding amount from all charge nodes related to this property.
3. Give a total field for all the charges.
4. Show all payments from payment nodes related to this property.
5. Total the payments.
6. Give a balance total(charge total - payment total)

What I can't figure out is if views can build the report I need.

Can this be done by adding calculated fields to views, in particular the service charge payments, or do I have to do this in a template file (views-view--foobar--page.tpl.php for example)?

I know how to get all payments and charges related to a property into a view, but I am not sure where to go from there. Maybe this comes down to personal preference, but this is my first venture into views and I would appreciate some advice on where I should be concentrating my efforts!

Answer 1

Another Module you can consider is the views summarize

Enables an extra display style which displays summaries of a column on the last row.

You can also use the views calc mentioned by @Felix together with the views aggregator module

Answer 2

Would this be a suitable case for using the Views Calc module?

Example image from module page:

Answer 3

It seems that views is not a good way to go when trying to build reports that require more than one query of the database. Problems I found were:

-- 1) I could not bring in all payment nodes to the view using a property node id and then call in all charge nodes for the same property. The query returned the same information into the view multiple times, once for each payment and once for each charge. I wanted separate tables and then I wanted to make total fields from the data in each table. It seems a solution to this is to use hook_views_pre_execute() to edit the query to create a union query as described here but this was honestly mostly over my head!

-- 2) I could not calculate fields base on a value from a field in the property node and add them to the view (the service charge I talk about in the question).

The help offered in the previous two answers (if ordered by oldest date) solved the problem of creating a sum of a column in a views table, but didn't allow me to add a computed field of my own based on data from the view. For example, take two summed fields, subtract one from the other and put the result into a custom field. Views' custom field would be the way to do this as it allows you to use php snippets to build the field value, although again I am not sure it would allow you access to other custom fields in the view (just the view results).

Furthermore, I would then have to format the results by a call to some template hook function (sorry, I haven't figure out which one yet!).

So instead my solution was to use hook_menu() to add a path to create my own report

  $items['property_account_summary'] = array(
    'title' => 'Property account summary',
    'page callback' => 'MYmodule_property_account_summary_view',
    'page arguments' => array(1),
    'access callback' => 'user_access',
    'access arguments' => array('List Charge by Prop'),

and in MYmodule_property_account_summary_view it was pretty straight forward to construct the two queries I needed to collect the payment and charge nodes I needed. For example, for payments (where $prop_nid contains the node id of the property node I am working from:

  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
     ->entityCondition('bundle', 'payment');
  $query->fieldCondition('field_payment_property', 'target_id', $prop_nid, '=');
  $result = $query->execute();
  $node_list = $result['node'];

and for charges:

  $query = new EntityFieldQuery();
  $query->entityCondition('entity_type', 'node')
     ->entityCondition('bundle', 'charge');
  $query->fieldCondition('field_charge_property_id', 'target_id', $prop_nid, '=');
  $result = $query->execute();
  $node_list = $result['node'];

and from there build the html and add the totals.

Now I just need to format the values and add links to the term values in the Type column.

It just seems like a lot less work than trying to force views into doing what I needed. I must reiterate that this is my first attempt at using views (other than the straight forward grab and show data) and maybe I missed something amongst the views hooks that would have made my life a lot easier. But for now, I will use a solution within my module.

Answer 4

Another module to consider for addressing these requirements is forena. Here is a quote from one of its community docu pages, i.e. Reasons for considering Forena:

"Why not use views instead of forena?" ... The answer is generally something along the lines of "Most forena users love views, views is great! If you want to report against external data though, or you want to do aggregation or analytics, Forena is much more suited to address these requirements."

Answer 5

About Views Calc, mentioned in a prior answer: because of the "entity reference" comment in that prior answer, I assume this question is about D7 (consider refining your question to clarify that).

For anybody still in D6 (there are over +8K sites of them these days while for D7 there are only about 6K): be aware that the D6 version WAS unsupported until just a few days ago. Refer to issue 2198015 for more details on that, which also includes some details about renewed D6 support that happened recently.