Reporting

[juddey]

The content of this issue has moved here.

---

User Personas

(These are just some personas to help us understand typical reporting requirements. They may, or may not, exist in your church.)

Persona	What they might need, or want
Anna Admin	Runs daily transactional reports, such as name and address listings.
Pete Pastor	Probably gets Anna to run and compile special reports for him. Unlikely to be interested in transactional reports. More interested in trends and patterns.
Sally Small-Groups	wants to know about attendance in her groups; summary and detailed listings of who has attended when. Collates and feeds information to Pete the Pastor on small group attendance.
Petunia Pastoral-Care	Would like to get a schedule of her upcoming visits and appointments so she can plan for the week.
Eugene Event-Manager	Hey, we've got an event coming up and we'll need someone who can take good photos for the church.io post. Can you find me those people who have said they're into photography, please?
Kathy Kids-Worker	I'd like to know how many kids attended last weekends am service to see if we need to put another room on for JC Crew class. They were saying the area is getting too small.
Ian Interns	Can you tell me all of those people that have expressed an interest in our internship programme next year? I'd like to follow up with them personally to talk through details of the internship changes for next year.
Igor IT-Admin	Give me SQL and a text editor, and I'll get my own information. :)

From the personas outlined above, you can see there is a wide range of requirements, and different levels of ability with a reporting tool.

Different levels of ability are:

1. Run a report
2. Create a simple report (perhaps just report on one table and simple filtering critieria)
3. Create a complex report (eg multiple tables, aggregation, joins and multiple filtering criteria)
4. Create a report beyond the limits of an "in-solution" reporting tool (i'm so cool, I've got my own tools :)

   Basic Requirements

   • We'd need to support different levels of ability (as outlined in the table above)
   • Certain data might be classified as sensitive. We might want to omit this from reporting tools.
   • People love Excel (or csv-to-Excel) for manipulating extracted data, so we should consider this. Then the end user can get lovely user-defined graphs and time-series and financial analysis and all sorts of stuff that a rails app shouldn't be expected to do in its internals.
   • If we've got long running reports, we should consider moving them to another process/background worker so they don't impact front-end performance.
   • We'd need to support the custom fields specification.

     What's already there?

Reporting seems to me like a "universal" requirement of an information system. There are probably enough reporting "tools" out there that we don't need to create our own. Here's a few we might consider:

Tool	A brief description	Last Activity
Active Admin	Active Admin is a framework for creating administration style interfaces. axlsx provides an excel download feature.	August 2014
Ruport	Ruby Reports (Ruport) is an extensible reporting system, own DSL	about a year ago
Munger	A data munge + format + output tool	5 years ago
dossier	Ruby-based report generation/presentation Rails engine.	June 2014
Thin Reports	ThinReports is Open Source Reporting Solution for Ruby. It provides a GUI Designer and a Library for Ruby. (Japanese)	August 2014

An approach to OneBody reporting

So, based on what I have seen, and what I know, here's how I'd approach "Reporting":

1. Provide some bulk-standard reports that most congregations would find useful (like printed directory, maybe attendance tracking, kids check in type listing). We'd use a pre-existing report generation tool for this.
2. Implement Active Admin(?) to allow reporting and filtering for simple report creation.
3. For those that require database level access, recommend a SQL tool that can be acquired separately to OneBody.
4. I'm not sure what to do about complex reporting yet....

   For the future

   • Poll OneBody user base to see what custom reports they might have created
   • When looking at a new feature, make sure to include any relevant reports (if any) in the feature
   • Is it worth us trying to create a "report creator" ie, something that reads a set of config tables and produces an output based on that? too complex?

     Other Notes:

   • I've noticed that lots of websites make use of profile search type reporting, eg punch in the criteria you are looking for, (eg. doctor) and out spits a web page of all those who have nominated the criteria, plus their names and contact details, and their photograph(s)
   • Do we care about performance, ie reporting on a transactional table in "office" hours
   • Do we care about providing SQL access... probably read-only for this purpose, perhaps that's just a recommendation we make to users.
   • Heres another take on the presentation layer.

[seven1m]

@x-light thank you for this summary. My general approach would be something like this:

1. Create several general reports using Dossier.
2. Give users with special "Create Reports" privilege* ability to write SQL that will be run through a generic Dossier report model. This SQL would be executed via a separate database connection, using a read-only MySQL user (only SELECT ability). Configuration of separate database connection and MySQL user TBD.
3. Document official recommendation for using a more advanced reporting tool, such as JasperReports or Crystal Reports.

* The "Create Reports" privilege will come with a big warning that basically states that any user with permission will have the ability to read all data in the database, including encrypted user passwords, and the user must be trusted to not execute destructive queries.

Next Steps

1. Add Dossier.
2. Create three attendance reports:
   1. Attendance Totals: Given a date range, attendance totals per class
   2. Weekly Attendance Detail: Given a date range, attendance records (individuals attending, individuals who missed), per week

Recommend we archive this ticket and open a new one with immediate next steps above.

Feedback

Comments, questions? Don't be shy!!!

[juddey]

@seven1m, thanks for the comments.

This issue was quite long and I wondered about how much detail should have been added. It sort of reads like a "where we're going" (wiki/roadmap doc) rather than "create me a specific piece of functionality now" (issue ticket). So I'm happy to close this off and just reference it from the Roadmap page, and create the new issue as you've suggested.

Depending on complexity, I'll have a look at the new ticket for integrating dossier. About time I did some code (the alternative is documentation :)

I do think its worth at least exploring something like activeadmin so I might have a look at that too.

I've got another document on the brew for Giving which I'll put up in the wiki as well.

But I'll be doing all of this tomorrow - as its time for me to go :)

[seven1m]

:+1: for moving the research/summary work you did to the wiki.

I created #189 for next step.

[seven1m]

I'm not opposed to ActiveAdmin per se, but I'm nervous adding another admining section to the site, when we already have one that is custom and specific to workflows of a church. I'd really rather focus energy on building specific UI to help churches rather than integrating a generic one such as ActiveAdmin.

I'm not shutting down that possibility though -- I can certainly be persuaded if you think there's some use-case I'm overlooking. :smile:

[juddey]

@seven1m Thanks for those thoughts and views. Its helpful to me to know where you're headed.

I read the word "use-case" at the end of your comment and I think maybe I have seen ActiveAdmin as a way to fulfil the following: "Give users a way to download a models' data in an ad-hoc way using various simple filters" - because you could seem to do that using that gem.

I'm happy to park this for now and reconsider it later in the wider context of what we do with reporting.