Drupal, Charts and SUM Aggregation: 2011 and still no solution?

Additional idea that will not work: Views 3 aggregation (SUM): there's a bad issue between CCK and Views that doesn't allow Views to wrap the query correctly. :-(

It seems to me that getting a good SUM and an unambiguous field label are what's required to make any combination of Views and charting work right.

I've been looking at http://drupal.org/project/views_customfield but I fear my SQL-fu isn't strong enough. Theoretically you get precisely the query you want (SUM away!) by just putting it directly into the custom field, but since I'm trying to dynamically filter the output with an argument, I'm not really sure how to pass that argument into the customfield's PHP. It's worth a couple hundred dollars to me for someone with PHP/SQL powers to doodle up the queries for these fields, because I'm pretty sure I'd get a chart I could use out of it.

Once upon a time, apparently, there were charting functions baked into Views Calc. Reading that, wherever that was, made me think this whole subject had taken a pretty big step backward. I have faith that that backward step is probably necessary for some long-term reasons, but it still sucks for this project right now.

As far as I can tell, you're not missing anything.

I've tried every possible combination of modules-only -- no injected php code or custom SQL in a Views Custom Field -- in both D6 (Views 2 and 3) and D7. There's always one missing thing: Either you get the field labels but no aggregation, or aggregation but no field labels.

Tried Views GroupBy, View Field Views (trying to run the calculation in an outside View and feed it back into the chart renderer), Views Calc of course, and Views3 aggregation functions. I think that's the whole list.

On the chart-rendering side I've tried the Google Chart API, Views Charts, Highcharts, Charts and Graphs...I think maybe there's one more. Can't think of it any more.

I did, however, get a useful acknowledgement of the problem here: http://drupal.org/node/1359840. At least someone with real juice admits there's an issue.

Looks like Views PHP is the next version of Views Custom Fields. Same problem as before, I don't know enough PHP to hack out the solution myself.

Still offering the bounty if someone wants to give it a go.

I was very active in this group last year but was discouraged by the scope of the problem space vs. the amount of developer interest this topic carries. I tried to rally folks at DrupalCon Chicago, but no luck.

"this should be fairly simple - and I must just be missing something "

Unfortunately this is not a simple problem space, especially when using something like Views that has undergone some serious changes in the past few years. The reason why developer interest so low is because it's just damn easier to write some code to generate exactly what you need than write a general UI for graphing data in the thousands of different ways it is possible. If a developer is going to seriously tackle this, it's going to have to be their passionate hoby or there needs to be a lot of funding, but there never is funding because it's cheaper to just write the code for what the client needs. In my opinion the best effort to date has been the Chart and Graphs module + the Views Charts module.

http://drupal.org/project/charts_graphs
http://drupal.org/project/views_charts

There's a great demo of it that iraqli did at a DrupaCon. You should be able to find it if you google around a bit. I started porting it to Drupal 7 but other hobbies took over.

Porting the Charts and Graphs module and the Views Charts module could probably be done for $1,500. I would have to do some research into exactly which use cases that would solve though as there has been a lot of changes in Views since Views Charts was written.

Which use cases are folks interested in solving? Having a concise list would help the discovery process on this.

I actually already ported it to Drupal 7 almost a year ago but the maintainers abandoned the project and I'm sure some things have changed since then that would require some bug fixes. [#1096702] With a little love we could fork the charts_graphs project and get it running. The Views Charts module is a much more complicated problem. The API is ok. There was something about the style of how you feed it data that I didn't like, don't remember exactly now, but that's probably just an issue of expanding the API as opposed to substantially rewriting it.

@metzlerd What is Forena Reports? What will make the "group by" issue trivial?

Forena is a report writer project that I maintain. You can find it at http://drupal.org/project/forena. You basically create .sql files that have parameterized sql in them. (e.g. SELECT node_type,COUNT(1) AS TOTAL FROM {node} where node_type = any :node_types ). Results get prerendered into XML and then run trough a templating engine. Reports are expressed as .frx files which are basically a custom extention of .xhtml, but there wysiwg editor support as well. It also supports custom css files per report and per report "form".

So the idea is that anything you can write as a select statement can be the source of a report. Want group by with rollup? Just write the query in SQL and save it as a file. It currently doesn't support graphs, but that's on the roadmap, and I'm trying to identify best strategies as to how to do it. I might consider cloning the chart API so that version dependency isn't an issue.

I use this tool to create web sites that report against external databases, which is much easier to do in forena than trying to go through the views methodology. Been using this for about a year as an enterprise reporting solution against Oracle, Postgres, Mysql and MSSQl databases and have developed 100s of reports with the tool.

If you have a 7.x version of a charting api that you'd be willing to share I'd love to give it a look see. Contact me on the forena issue queue or via my contact form.

Update about what David (@metzlerd) wrote above, back in 2012: David has done an amazing job to further enhance Forena, the support for graphing was added quite some time ago, using the "SVG Graph" library.

https://www.drupal.org/node/2362447 contains an overview of supported chart types. And https://www.drupal.org/node/2363985 contains a comparison of forena with other charting modules, which includes a reference to the brand new feature request https://www.drupal.org/node/2364427 ... about using the "Charts & Graphs API", as an alternative to the already supported SVG Graph library. It'll transform Forena in something that virtually supports any charting library, on top of making the "group by" issue trivial ...

If you haven't done so yet, it may be worth the effort to review the brand new forena release from earlier this week, together with the documentation (videos and tutorials) that comes with it.