I work in the financial world and therefore present a lot of data with graphs, charts, tables, and so forth. As a result, I use a lot of pivot queries to extract the necessary information for presentation. So far I’ve been able to build models to meet my needs, but the more information being requested the more models I find myself building for the same table of data.
Let me try to illustrate below:
I have one table with the following information:
Name Year Product Count
John 2011 X 10
Jane 2010 Y 11
Jake 2010 B 20
Jill 2012 A 30
I may display it as follows:
Name 2010 2011 2012
John 0 10 0
Jane 22 0 0
Jake 30 0 0
Jill 0 0 30
Or I may display it as:
Product John Jane Jake Jill
X 10 0 0 0
Y 0 11 0 0
B 0 0 20 0
A 0 0 0 30
Please excuse the simple illustration. With a larger table though there are dozens of way to display this information. In the past I have simply created models for each graph/table that I want to display, but this no longer seems viable and just feels wrong.
So my questions are: Is there a way to create dynamic attributes for a model based upon the query? If not, what are the best practices in a case like this? If anyone can point me in the right direction I would greatly appreciate it.
I know I could query the table and build this information with PHP, but I would like the database to do as much of the work as possible. Also please keep in mind I have zero control over the database structure. This is data created by other programs.