Report Query Builder

[Pletoo]

As a non-programmer, I'm a big fan of the query builder, and I frequently explore it to test capabilities. It is especially handy to use with the many additional fields and flags I have added.

I often need reports from specific time frames and usually have one of the date criteria set
Could the criteria "ask user for year" and "ask user for month" be added, please?

I was quite pleased when the "group" feature was added, but now I am spoiled. It would be nice if the "Sort" dropdown list behaved like the "group" one in that it only showed the possible options rather than everything. For example, in this situation, it seems like the only sort options should be AnimalName or EarTip.

PS: I think I asked before, but are collapsed dropdown lists for criteria and/or fields possible? Not a high priority if complicated, but it sure would make finding things easier rather than scrolling through the long lists - there is so much on there.

[bobintetley]

When you say "ask user for year" and "ask user month", I'm not sure how options that are less granular than the existing choice of two dates would make sense? Also which field would they be comparing to?

It's deliberately possible to sort by fields that aren't in the field list as it's a feature of SQL queries (although I'm not sure how many people actually want to do this), so I'm not sure we can restrict the sort options.

We don't have a collapsible dropdown widget I'm afraid. If you have a keyboard, you can type the first characters of a field to go to it in the list, but yes, for animal records there are quite a lot of fields to choose from.

[Pletoo]

I am often asked to print information for specific months or from last year. Also, when creating a new report, sometimes it takes multiple tries to get what I want. Either situation usually means running a report many times and putting in dates is not a really big deal I suppose, just tedious to do repeatedly. There are pre-made reports that ask for a year or a month - but they aren't usually the information I need, so I was hoping it would be a fairly easy thing to add, but if not, there are definitely higher priorities for your time. I am in the process of learning SQL and HTML in my rare bits of free time, so I still rely on the query builder. I will add that to my list of things I specifically want to learn how to do,

I have used the query builder to create reports that resulted in an error message when I ran it, so I assumed that it was not possible to sort without selecting that field. If I am now understanding correctly, there are some sorts based on fields that must be selected and there are some sorts based on things that don't have a corresponding field that can be selected. I imagine it would be complicated to have separate dropdown lists for those two cases, so I will continue scrolling through. Fortunately I have since learned that if I want to sort (and group) by something without it being printed as a column on the report, I can edit the HTML portion after using the query builder.

How about an appendix in the manual for a list of all of the options possible on the builder? I don't always know exactly what I am looking for so I have to scroll. I am constantly checking the manual for the CSV imports and wordkeys. I've found the same information has different names. For example I believe these all reference the same information?
Wordkey: DeceasedCategory
CSV: ANIMALDECEASEDREASON
Query builder: PTSReasonName