FGdb Reports

This is related to ticket number 12475.

Trend Reports

(These show trends over time)

Volunteer Related

• Numbers of volunteers volunteering over time.
  • filter on program (adoption, build, other)
  • filter on job (receiving, recycling, data entry)
• Same thing, but total of hours volunteered instead of volunteer count.
• Number of active volunteers over time (an active volunteer has logged >= 4 hrs in the last 90 days.) DONE
• hours by task category/program (does that exist yet?) OR distinct person count by task category/program over date range grouped by date
• - # new volunteers in build, adoption, other
• - # volunteers who logged hours in build, adoption, other
• - # hours logged in build, adoption,

Gizmo Related

• Numbers of (gizmo_types) received over time.
• Numbers of (gizmo_types) sold over time.
• Numbers of (gizmo_types) disbursed over time.
• Same as above but for (gizmo_categories) instead of (gizmo_types).
• Same as the above (gizmo_types) but be able to pick >1 type, so you can see (for example) the relation between laptops and systems received, sold, whatever.
• gizmos by type or category flow type (incoming or outgoing) over date range grouped by date, week, month, quarter, or year
• - # systems, monitors, printers granted
• - # systems, monitors, printers adopted
• - # systems, monitors, printers sold
• - # systems, monitors, printers recycled
• - % of systems, monitors, and printers granted, adopted, sold, and recycled
• * extra swanky: these numbers (or at least the non-recycle numbers) for laptops, macs

Income

• Average donations, fees, and total (i.e. front desk income) over time (DONE, pretty much).
• Average amount of sale over time. (Be able to select a (gizmo_type) or (gizmo_types) for comparison. OR Be able to look at all sales, regardless of (gizmo_type).)
• over date range grouped by day, week, month, or year donation income by fee and contribution sale income by discount type and totals
• Total sales ($) by gizmo_type (and gizmo_category) over a period of time. (This is a trends or "over time" type report.)
• Same as above, but limit to discount type -- that is I want to see trends on systems that were sold at a volunteer discount.

Other

• - # donations
  • - # unique donors
  • - avg. amount of money given per donation

• - # sales
  • - # unique store customers
  • - avg. amount spent per store transaction

Not trend reports

Some reports mentioned that are not trend report ("over time"):

• List all volunteers that have worked (more than | less than | between) a certain number of hours.
• List all organizations that have donated gizmos in a given date range that (are | are not) covered entities.

Questions PR wants answered (that could be reports)

Would need additional changes to database

• Recycling tonnage: How many tons of e-waste did we recycle last year?
• Collection events: "I helped organize 3 collection events (Hillsboro Library, Elementary School, and ECOHAUS) in 2008, how many others were there? For instance, I know that Hasson Realtors partnered with Free Geek on one."

Don't need additional changes

• How many folks volunteered at Free Geek in 2008?
• How many hours total did they collectively volunteer?

Not sure

• We donated approx. 1200 computers last year - how many were given to adoptees & how many to grantees?
• Do we keep track of numbers of equipment/gizmos processed for 2008: computers, printers, keyboards, etc.
• A list of organizations that received Free Geek computers in 2008.
• A list of all Free Geek cash donors and amounts for 2008-I've got the spreadsheet for the Give Guide donors. Can you combine the Causes and all others into one e-report? This would also include in kind donations from Companies/individuals. I would like to exclude the front desk donations unless they were notable amounts - $50+

Notes

One more report-request that I've heard of.

If the part of the gizmo report that lists disbursements could be a link to the actual disbursements showing the contacts who are getting disbursed to, that would be good and would save Dave a lot of time.

SQL samples for use in creating reports

Since there are so few categories, we could show multiple lines on the same graph if we want. This might help do that:

-- sales amount by all categories example:
SELECT 
  gizmo_categories.name,
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  LEFT JOIN gizmo_categories ON gizmo_types.gizmo_category_id = gizmo_categories.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  GROUP BY 1
  ;

-- sales count by all categories example:
SELECT 
  gizmo_categories.name,
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  LEFT JOIN gizmo_categories ON gizmo_types.gizmo_category_id = gizmo_categories.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  GROUP BY 1
  ;

Volunteer Related

Hours worked by volunteer task:

-- to get number of hours worked in volunteer_task_type = 28 (receiving) between Jan 1, 2008 and Dec 31, 2008:
SELECT SUM( duration ) 
  FROM volunteer_tasks 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND volunteer_tasks.volunteer_task_type_id = 28
  ;

To change this to a volunteer count instead of hours, change "SUM( duration )" to "COUNT(DISTINCT volunteer_tasks.contact_id)" like this:

-- to get number of volunteers that worked in volunteer_task_type = 28 (receiving) between Jan 1, 2008 and Dec 31, 2008:
SELECT COUNT(DISTINCT volunteer_tasks.contact_id) 
  FROM volunteer_tasks 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND volunteer_tasks.volunteer_task_type_id = 28
  ;

Sometimes we want to see adoption or build related tasks isolated out. This is by "program". Hours worked by program looks like this:

-- to get a list of programs do this:
SELECT 
  id, name 
  FROM volunteer_task_types 
  WHERE parent_id IN (SELECT id FROM volunteer_task_types WHERE name = 'program');

• Program 24 == adoption.
• Program 27 == build.
• Program 17 == all the other stuff

(Note there are some potential snags in the data since adoption and build also have a parent_id == 17. But this doesn't currently screw anything up.)

-- to get number of hours worked in program = 27 (build) between Jan 1, 2008 and Dec 31, 2008:
SELECT SUM( duration ) 
  FROM volunteer_tasks 
  LEFT JOIN volunteer_task_types ON volunteer_tasks.volunteer_task_type_id = volunteer_task_types.id 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND (volunteer_task_types.parent_id = 27 OR volunteer_task_types.id = 27)
  ;