Revision 4bdd232e-caeb-46b1-80d4-8c004359461c

Interesting question, especially these phrases in it that caught my attention:

 - ... *writing queries using the PDO layer* ...
 - ... *an efficient query* ...

The [Forena][1] module is another option to consider. It comes with quite some [community documentation][2].

Forena is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some [reasons for considering Forena][3], which also explains how "**Forena complements the views module**" (suggested in some other answers here also), i.e.:

> - "**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."
> - "**What does it take to create reports / charts with the typical Business Intelligence tools?** Where would I start in case such reports are to be shown on a Drupal site ... **and the actual data may either come from any of Drupal's own tables, or in a PDO-style external database, or some XML or CSV file?**" ... Trying to answer these questions leads to more questions such as "How far can you get (and what would it take to get there) with the views module (not even talking about how to secure the access to such data)?

Visit the [Forena HowTos][4] page for some links (near the bottom) to some live demo / showcase links.

Refer to the example in my answer to http://drupal.stackexchange.com/questions/152166/fetching-drupal-articles-tags-image-directly-from-database/154746#154746 to get an idea of what it is like to use Forena, such as the SQL query and the source of the "custom report", which looks like so:

  <!-- language: lang-html -->

      <head>
        <title>My custom report title</title>
        <frx:category>Articles related Reports</frx:category>
        <frx:options skin="My_Skin_File_With_CSS_And_JS_To_Beaf_Up_My_Rpt"/>
      </head>
      <body>
        <div frx:block="MyDatabaseConnection/MySqlQuery">
          <p frx:foreach="*">
          {title}<Br/>
          {tags}<Br/>
          {body}<Br/>
          </p>
        </div>
      </body>

Consider the above sample as an alternative to what in one of the answers is mentioned, i.e.: "*Now if you try and do the above mentioned points in a custom module I am pretty sure you will run over a 500 lines of code to implement most of the features.Also don't forget the custom code which needs to be maintained.*". I do agree with that, in cases where Forena is not being considered (eg because not many people seem to know about it?).

Similar thoughts about what I read in another answer, i.e.: "*Views brings many additional features to the table as well. For example, **theming**, **caching**, **access control** and **integration** with hundreds of contributed modules through an easy to swallow UI.*". In case of Forena these additional features are available also, except that there are not 100s of such modules (though there are integrations with heavily used modules like [feeds][5], [bean][6], [views][8], [date][7], etc). In case of Forena these few modules will get you pretty far. After all, don't we always try to have as few as possible contributed module enabled? So I'd hope I don't need too much of all those 100 to just enhance the views based approach. And how many sites don't have at least views and date already enabled, probably also bean more and more.

What I don't seem to read anywhere here in the question, or any answers, are these Forena supported features that might make sense to consider also:

- How far do you get with [views][8] if the data are coming from an external database?
- What about aggregation types of operations, like the features of the [views_calc][9] module? Disclosure: I recently became a legacy maintainer of it's D6 version.

Refer to Forena's community docu mentioned above for details about how Forena does also address those issues (and how it complements views ...). If appropriate, I'd be happy to further expand this answer to explain Forena AbitMORE.

Disclosure: I'm a co-maintainer of this module,<br/>
I hope this does not violate the site's [policy on self-promotion](http://drupal.stackexchange.com/faq#promotion).

  [1]: https://www.drupal.org/project/forena
  [2]: https://www.drupal.org/node/2041363
  [3]: https://www.drupal.org/node/2358629
  [4]: https://www.drupal.org/node/2443761
  [5]: https://www.drupal.org/project/feeds
  [6]: https://www.drupal.org/project/bean
  [7]: https://www.drupal.org/project/date
  [8]: https://www.drupal.org/project/views
  [9]: https://www.drupal.org/project/views_calc