Implementor
Palante Technology Cooperative
Palante Tech works with social justice organizations on a tight budget to be more effective through technology. CiviCRM allows us to provide a high-quality low-cost database for community organizing, donor and membership management.
End-user and Developer
Woolman Sierra Friends Center
Working with CiviCRM enriches our commonwealth. Any investment in CiviCRM is
shared by the community as a whole. Community organizations naturally complement the spirit of Free/Libre Software.
Implementor, Developer
AGH Strategies
CiviCRM allows our clients to have a robust tool for tracking and engaging their supporters that can grow with them. I began as an end user, and now I work with CiviCRM full-time.
End-user, Administrator, Trainer
Progressive Technology Project
CiviCRM is helping us serve member-based community organizing groups across the
U.S. to keep better track of their events, fundraising, and membership data. It's helping our community to aim higher in terms of what kind of questions they should be asking and what kind of data they should be collecting. We chose CiviCRM because it's the best all-around tool to do what our groups need, AND because it's open source.
Developer, Implementor
Réseau Koumbit
As non-profit consultants working for non-profit organizations, we found CiviCRM to be particularly well suited to answer the common needs of activist associations, charities and other medium-sized groups. Based in Montréal, we've helped local and international organizations migrate to CiviCRM to manage their memberships, events, communications and fundraising campaigns. We empower our clients and assist them when they need us.
CORE TEAM MEMBER
WEB ACCESS INDIA PVT. LTD.
Its great to work on a project that has a profound impact on non profits. I am very excited about the work we do on CiviCRM which involves building on each other's ideas to create best of breed solutions for non profits. The fact that CiviCRM is an open source project with an amazing community and dedicated developers is an icing on the cake.
Administrator and End-user
CiviCRM is a powerful tool that could be really useful for many non-profits in Mexico.
Unfortunately the community is very small in my country. I hope that in the next years the community expands around Latin America.
DEVELOPER AND IMPLEMENTER
Implementor
Progressive Technology Project
The organizations we work with are experiencing the benefits of a robust tool that is
easy to use, supports their work, and allows them to collect and track data from various parts of their organization, such as membership, fundraising, communications, and organizing into a centralized database. CiviCRM as an open-source solution also allows us to nurture and build a user community to share and create a common vision of future features that would be useful to the community organizing field. Just two years after our pilot project, we're currently supporting 30 community organizing groups to use CiviCRM, and the community is steadily growing.
Implementor, Trainer, Documentator and Developer.
Third Sector Design
CiviCRM helps us help non profits to do fantastic things with their data.
Being closely involved with the developers and documentation team on a daily basis ensures that we can give our clients the best and most up to date advice on how they can use CiviCRM to meet their needs.
End-user and Developer
Woolman Sierra Friends Center
If it weren't for CiviCRM we'd be using at least 5 different
systems for Woolman: one for donor management, another for email newsletters, a third for our school enrollment, a fourth for our summer camp registration, and then a whole bunch of spreadsheets for keeping track of things like event attendance, prospective students, CSA memberships, etc. And of course none of those systems would talk to each other or make it possible to get a whole picture of the many ways one person might participate in our education center's activities. Migrating all of our scattered data and disparate systems to CiviCRM was a long and challenging process, but the results have been more than worth it. Our ability to track and report on our programs has improved dramatically, while the burden on staff to do data entry has been greatly reduced, and our participants are happy that they can now register/enroll online rather than mailing or faxing paper forms.
Comments
What do you mean
By sortable, you mean grouped by ?
eg when you "sort by" Events, mean you generate a "sub chapters" for each event ?
Sorry, confused, what's the difference between what you suggest and the grouped by ?
As for the sorted per se, wouldn't it be easier to generate (already sorted if you think perf is an issue) and add as a param to the table plugin to sort by this column (eg so you can still use it to sort by another column if you want to);
X+
"ORDER BY" clause vs. "GROUP BY" clause
The "Group by" settings create an SQL "GROUP BY" clause, which basically says: if there is more than one row with the same value in column X, only show one of those rows.
What we're doing here is focused on the SQL "ORDER BY" clause, allowing the user to order by one or more columns, with any mix of ASC/DESC keyword.
We're also looking to allow inserting section headers into the table output when the value of a given column changes from one row to the next. In long reports, it can be helpful to break things into sections. For example, if you're sorting a list of people by country, gender, and last_name, it can be very helpful to have section headers for each country, and within each country, section headers for each gender. This can be preferable to having 80 rows that have "Albania" in the first column and "M" in the second column, followed by 70 rows with "Albania" in the first column and "F" in the second column. Because sorting -- and thus the optional section headers -- can be defined for columns that don't display, the user has the choice whether to display the columns for country and gender, or instead to save a little space and just show that info in the section headers (see the mockup: the event column is not displayed, but the event is named in the section header).
Since sections are tied to sorting, you can pretty easily calculate the switch from one value to the next, and insert a header in that spot. And since the section header is optional, you can insert section headers only for the fields you want, or not at all.
Another nice thing about sections is the ability to show, in the section header, the total number of rows contained in that section. So in the country/gender/last_name example, we would see "150" showing as the total row count for Albania in its section header, and 80 and 70 showing in the section headers for M and F, respectively. This can can be pretty useful when dealing with long reports.
UI for order of fields for sort versus order for display
A big thanks for stepping up to the plate on this.
There are two places where the order of the fields in the sort get defined: in code, and in the browser.
I'm a bit confused by the order_by versus sort by - aren't they the same? I think you likely meant group_bys and order_bys.
If you try to let any order by field take sorting precedence over group by fields you'll run into problems: you should take that precedence as a design constraint both in code and in the UI.
I'm also a bit confused as to what is meant by the check box at the right of order by fields versus the functionality provided by group by, eg event field. Is it that you want the checkbox to give a heading, while the group by currently is designed to provide a tally whenever the value changes, if I'm not mistaken? My sense is that you might be better off adding options to group by's for things like making it a header rather than a column, rather than making it an order by option.
From a UI perspective, I think that there are two or three ways to go to specify the order of columns for sorting, and then whether ascending or descending:
HTH,
Joe
"any column available for
"any column available for display is also available for sorting"
It would be good to limit the ability of a report writer to take down the database. You could limit it to only columns that have an index. That's a list of known columns, plus custom fields that are "searchable".
"as many as you want, as long as you don't want more than 10"
Again, for damage control you might want to limit that to 3-5.
good points
Thanks dalin. These are good points. 3-5 columns is probably more than enough in most cases. It might be wiser to limit the "order by" options only to those specified in order_bys, and let the report developer decide what goes in there, without insisting that all display columns be included. Letting the code check for indexes and insist on sorting only by indexed columns would be safer, but I would rather not insist on it and let the report developer decide.
group by vs order by vs report breaks
I think we need to be careful with SQL terminology vs end-user terms.
With SQL "group by", the detail rows are not shown in the result set. What most end-users want is the detail rows, with what I am going to call "report breaks" . To me what many end-users ask for is "show me all the records for x, with a visual break whenever a new category is reached.
In the screen print above, the light blue row with the text "Event: Fall Fundraiser Dinner(12)" is what I would describe as a "report break" followed by 12 detail records.
When people use "report breaks" in their reports, allowing the end-user to re-sort the table by clicking a column heading will produce nonsense in most/many cases.
I like the idea of having a report parameter called "order by" or something similar to allow the end-user to do what they want, without the danger of producing nonsense.
I like the report break
As for the "order by", I think it should be end user translated. "display sum" ?
Has one of you an access/crystal report see how that's called ?
X+
Not exactly the same
Sarah's comment (above yours) explains it a little better, perhaps. The sum is only displayed within the section header -- or what Sarah is calling a "report break." And this section header is only displayed if the "create section header" check-box is checked. So defining an "order by" column won't always "display sum."
Report Break options for end-users
In a previous reporting solution I was involved with, we boiled down the options for the end-user as follows:
------
Do you want report breaks? yes/no
--------
If yes is chosen, ask which column values should breaks be tied to. If there are 15 fields in the query, present the user with a check box for each field. ( In an event income report, the end-user may want a report break every time there is a new ContributionType, and a new month. So within the section for "event fee A" there would be a sub-section for each month. Then for "event fee B", there would be a sub-section for each month, and so on)
If the end-user chooses "yes" to a report break for any field, then ask them what they want to display at the report break. It could just be a visual spacer, or just the field value that caused the report break, or it could include some subtotals, averages, counts, etc.
In the case of that other reporting tool, if the end-user wanted counts, subtotals, etc within a report break, the code that looped and created the array to send to the browser, also did the report break calculations for subtotals, counts, etc. The SQL code did not calculate those report break numbers. ( Since we had to loop through all the detail records anyhow, it was not any extra work to count records, or get a subtotal. )
Makes sense
That's pretty much what we're doing here, except that in this design (as it stands now) the options are not so varied as to what a section break does, only whether to have a section break or not, and then section breaks include, by design, the field label, field value, and section total.
Would be nice in the future to allow more configurability in the section headers. Even at this point, we're considering making the "section total" count optional, to improve performance when that number isn't really needed.
Could you also include filtering using any 'Type' column?
This may not be the appropriate place for this comment, so feel free to shift it.
As a companion to improving the sorting behaviour of reports, I would like to see the filtering options for reports improved. It would be really useful to be able to filter by any "Type " column that is displayed in the report. By "type" column I really mean any column that contains predefined options eg. Event Type, member type, participant status (for events), Payment instrument, contribution type etc.
Hi Does anyone know if this
Hi Does anyone know if this has been developed or what stage it is at. This would be a huge benifit and we might be able to contribute depending of price and speed of dev.
Thanks
Not yet done ...
this is not part of the 3.4/4.0 series
If you can help "make it happen" (similar to http://civicrm.org/mih) and/or be the lead sponsor that would be great. To add it in a generalized manner and extend 5-10 reports is expected to take 25-40 hours. We suspect we can do this in a future release since it does not involve changing the schema
ping us on irc / email / forums if u'd like to take this forward