# Create reports with ActiveRecord to download by user

**URL:** <https://www.ruby-forum.com/t/create-reports-with-activerecord-to-download-by-user/162315>\
**Category:** Rails\
**Created:** [March 8, 2009, 4:02pm UTC](https://www.ruby-forum.com/t/create-reports-with-activerecord-to-download-by-user/162315 "2009-03-08T16:02:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![gte351s](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5f9b8f/32.png) [@gte351s](https://www.ruby-forum.com/u/gte351s)\
**Post date:** [March 8, 2009, 4:02pm UTC](https://www.ruby-forum.com/t/create-reports-with-activerecord-to-download-by-user/162315/1 "2009-03-08T16:02:32Z")

</div>

Hi -

I want to enable the user to generate a report based on the database  
used by the application. I have a few related questions:

1. Assuming my script is called ‘create\_report.rb’ and stored under  
/lib/tasks, how do I execute it from my controller once the user clicked  
the link? I read that ‘send\_file’ is probably what I want.

2. ActiveRecord is great (statement), and I want to use it in this  
script. Do I need to require ‘activerecord’ or anything in the actual  
file? I would like to use AR much like I do in my views (@users =  
User.find(:all), user.full\_name, etc.) - Does it just work or do I need  
to somehow load the existing models to memory.

3. where is the logical place on the server to store files I create (if  
there is one)? I was thinking /public/files/ or something, or even  
outside my application root.

4. for the sake of example, I want to return a csv file containing all  
users and their birthdays: [http://pastie.org/410902](http://pastie.org/410902)

It’s very incomplete, but I’m hoping someone can point me in the right  
direction… thanks.

---

<div class="post-metadata">

**Author:** ![gte351s](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5f9b8f/32.png) [@gte351s](https://www.ruby-forum.com/u/gte351s)\
**Post date:** [March 8, 2009, 4:39pm UTC](https://www.ruby-forum.com/t/create-reports-with-activerecord-to-download-by-user/162315/2 "2009-03-08T16:39:26Z")

</div>

On 8 Mar 2009, at 15:02, Shilo A. wrote:

> 1. ActiveRecord is great (statement), and I want to use it in this  
> script. Do I need to require ‘activerecord’ or anything in the actual  
> file? I would like to use AR much like I do in my views (@users =  
> User.find(:all), user.full\_name, etc.) - Does it just work or do I  
> need  
> to somehow load the existing models to memory.

So either you create a file somewhere on disk and use send\_file (or  
one of the speedier alternatives) (the file doesn’t have to be in /  
public, bear in mind that anyone can get files from there) or you  
create the data in ram and use send\_data.

If your report generation is a standalone process then you have to  
worry about setting up the right environment, but you may not need to  
do that - you could call the report code directly from your app (or to  
put things another way ‘report code’ is just the same as normal code).

Fred

---

<div class="post-metadata">

**Author:** ![gte351s](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5f9b8f/32.png) [@gte351s](https://www.ruby-forum.com/u/gte351s)\
**Post date:** [March 9, 2009, 11:22am UTC](https://www.ruby-forum.com/t/create-reports-with-activerecord-to-download-by-user/162315/3 "2009-03-09T11:22:41Z")

</div>

On Mar 8, 5:02 pm, Shilo A. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> Hi -
> 
> I want to enable the user to generate a report based on the database  
> used by the application. I have a few related questions:
> 
> 1. Assuming my script is called ‘create\_report.rb’ and stored under  
> /lib/tasks, how do I execute it from my controller once the user clicked  
> the link? I read that ‘send\_file’ is probably what I want.

lib/tasks is for rake tasks. You should either remake your script to  
rake task (and then execute it using Rake::Task[“task\_name”].invoke)  
or put it to lib and then execute it by calling method from it.

> 1. ActiveRecord is great (statement), and I want to use it in this  
> script. Do I need to require ‘activerecord’ or anything in the actual  
> file? I would like to use AR much like I do in my views (@users =  
> User.find(:all), user.full\_name, etc.) - Does it just work or do I need  
> to somehow load the existing models to memory.

If you’re using script from lib folder, then it has full access to  
rails, your models and etc.

> 1. where is the logical place on the server to store files I create (if  
> there is one)? I was thinking /public/files/ or something, or even  
> outside my application root.

I’d suggest somewhere outside public, maybe /reports?

---

<div class="post-metadata">

**Author:** ![gte351s](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/5f9b8f/32.png) [@gte351s](https://www.ruby-forum.com/u/gte351s)\
**Post date:** [March 9, 2009, 5:01pm UTC](https://www.ruby-forum.com/t/create-reports-with-activerecord-to-download-by-user/162315/4 "2009-03-09T17:01:38Z")

</div>

On Sun, Mar 8, 2009 at 10:02 AM, Shilo A. \<  
removed\_email\_address@domain.invalid\> wrote:

> 1. ActiveRecord is great (statement), and I want to use it in this  
> users and their birthdays: [http://pastie.org/410902](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