On Sun, Mar 8, 2009 at 10:02 AM, Shilo A. <
[email protected]> wrote:
- ActiveRecord is great (statement), and I want to use it in this
users and their birthdays: http://pastie.org/410902
It’s very incomplete, but I’m hoping someone can point me in the right
direction… thanks.
There are so many little decisions.
1.) I have found it good for me to create a method in a controller to
create, and store the report. Reports of the same type go in the same
controller. For example, I have reports that are “weekly”, “monthly”,
“quarterly”, and “fiscal year”. There are reports for single users,
groups
(of users) and accounts (groups of "groups). This tends to proliferate
controllers, but it does tend to keep them short. So, there is an
ActiveUserUsageReport.rb that has a weekly methods, quaterly method,
etc. At
a first past, I put the sql directly in the method, essentially
find_by_sql.
But after all tests pass, I create a database stored procedure and move
the
sql code there. I try to do as much of the data massaging in the
database.
But, YMMV. My database is highly normalized, which tends to subvert the
needs for reporting. I don’t have the time to create a reporting
database
which flattens out the structure.
2.) I find that my sql’s for reports get too complicated to try to use
AR
all the time. Pushing SQL’s into the database works best for me. BTW, I
use
postgres.
3). This can get interesting. Whereever you put the report output it has
to
be a place that the rails process has access to. I have found, for
example,
on my own ubuntu 8.10 the running rails process needs to have
envirenment.rb
owned by www-data (the apache process actually running). www-data, then,
needed to be able to read and write to whatever file system I choose to
store reports. I actually use app/reports to store them. The directory
is
swept by a cron job to remove old reports. I provide reports in
comma-delimted format as well as .png for the graphs. This reports are
created and then spooled back to the user. There is an html option to
see
the report “on-line” as it were, so I work with end users to keeps as
much
of the html display “row oriented” to cut down on the number of, or type
of
views.
Naming reports is an issue as well. User reports have to be tied to the
users, groups to groups, etc.I currently use, for a user for example,
username.reporttype.datetime. However, you get users who are forever
changing the start date and end date of the report and rerunning it over
and
over. Hence, the need for the cron job to clean up from time to time.
Stored
procedures (actually functions) inside postgres handles repeated reports
very well, since the function is compiled only once, but is used again
and
again.
This is what I have worked out over time.
HTH
Charles