Giocoso: The Reporting Menu

I don't propose to spend a lot of time documenting the various reports which Giocoso allows you to create, using data from its database. They are fairly self explanatory, I think, and are in any case completely non-destructive: that is, you can run the various reports and see if they contain anything of interest without actually doing or changing anything. So click away and see how you get on, basically! That said, I will detail some points of interest for the various reports.

1. Aggregate Statistics

This is potentially one of the most useful of the reports and is what I export to this very website on a regular basis. Most of the 'notes' on that page therefore apply to the report when you produce it against your own music database. In particular, note that the 'average plays per day' statistic is calculated a little unusually. Specifically, it only divides a total number of plays by the number of days on which something at all was played. If you play 10 recordings on Monday, 10 on Tuesday and 10 on Thursday, then when you run the report on Saturday (6 days after you started) would be 10 (10+10+10/3), not 5 (10+10+0+10+0+0/6).

2 and 3. Top Composers

There are two variations of this report, depending on whether you want composers listed in the order of the number of times they've been played or by the number of hours their music has played for. The underlying data is the same for both reports, but the order in which rows are displayed is different in each case. Note that the reports will display play durations in either hours or minutes, depending on the value of the DURATIONS parameter in the persistent configuration file, which takes a value of either 'hours' or 'minutes' (and minutes is the default).

4. Genre Analysis

This report summarises both the recordings and plays tables by the genre associated with each recording. The aim is to spot when, for example, a large chunk of your music collection is made up of operas, but you are under-playing them for some reason.

5. Advanced Reporting by SQL

This one requires some explanation! Reporting menu Option 5 allows you to query any part of the database in any way you like, provided only that (a) you know how to write valid SQL select statements and (b) you don't try to issue update or delete commands (because those are not allowed!) By permitting free-form select statements in this way, you can cook up reports which Giocoso itself doesn't allow for, or which would otherwise require dedicated SQL reporting tools to construct. This is not the place to teach you how to write SQL statements, though, so if you don't know how to do that, steer clear of this particular program option!

For example:

Here I am running a report that summarises a composer's recordings and computes an overall total number of hours for each one. The report then lists them in the descending order of their sum durations:

Personally, I find this sort of report useful as a way to tell me whose music I should be buying more of -but, regardless of the specifics of the query, the point here is that you have just queried the Giocoso database for data summarised, ordered and presented in ways that Giocoso would not otherwise allow for. You could, of course, do this in dedicated tools like DB Browser for Sqlite, but it's nice to have a simple querying tool readily to hand within Giocoso itself, on occasion.

Incidentally, note that you can submit any queries to the database that are using valid sqlite3 syntax. For example, if you can't remember what the columns are within a table, you can issue the .schema <tablename> command to find out:

...which gives this result:

...and thus tells you that there are columns called 'COMPOSITION', 'PERFORMER' and so on in the recordings table.

Note that all reports produced by Reporting menu Option 5 are saved as a temporary files called $HOME/.local/share/giocoso3/txt/customreport.txt. If you want to save a report for later use, just open that file in a text editor of your choice and save it to a new location with an appropriate name. The original file is deleted every time you finish with the Advanced SQL reporting menu option.

6 and 7. Complete Lists

These two menu options simply output the complete contents of both the plays and recordings tables, respectively. Their data is stored in files called $CONFDIR/txt/allplays.txt and $CONFDIR/txt/allrecordings.txt, which are over-written every time a new version of the reports is produced.