Last week Mr. Gautam Mukhopadhyay, Librarian, Chandrapur College in Burdwan, West Bengal wrote in with a request:
Respected Sir,
I’m writing this seeking a solution for the problem relating to a report generation from Koha. I want to get a list of titles under a particular broader subject field-tag (650). Quite a number of times I’ve checked from SQL Report. But all were in vain as those were not the same what I actually want to get. Following is the specimen of the opted report:Sr. No. Title Author Copy No. Subject1 ………. X 3 BengaliUnder the subject Bengali or English or whatsoever, I want to get the titles those are belong to that particular subject. However, it won’t be a problem if there are different reports for different subjects. It’s Ok. But the SQL Query should be a general query structure that can be applicable for all such reports on the titles belong to a broader subject like Bengali, History, Geography etc.Sir, please let me know the query structure, if possible.Regards,GM
Here is a possible solution for his request, which pretty much does what Mr. Mukhopadhyay had specified in his request. In this example we’ll use a sample MARC21 file which can be downloaded from here to try out this example. This dataset has a 14 unique bibliographic records with a total of 42 item (holdings) record, belonging to 03 specific broader subjects i.e. English, Economics and Political Science. As per Mr. Mukhopadhyay’s use-case, the MARC field 650 holds the broader subject classification. However, to match real world scenarios the 650 fields in some of the cases have other subject headings defined including narrower terms. Also additionally we are going to add an additional column to our report – the biblionumber, so that if required we can cross check a title in the report generated against the biblionumber in the database.
CAVEAT EMPTOR: If you are going to try out this example, we suggest that you define a new Koha library and import this MARC file into it. Mixing this sample data with your existing records is strongly advised against.
Step #1 – Create a new Koha instance and set it up
koha-create Debian command to create a new Koha instance and we shall call our instance as demo.sudo koha-create --create-db demo
You may calls your instance by whatever name you like. If you are not aware of the koha-create command, please read up “Commands provided by the Debian packages“. Next we will do a default setup and proceed to define a Library that we’ll call “L2C2 Technologies Demo Library” identified by the code “MAIN”, using these instructions here.
NB.: To use the marc file used in this example you must set the library code for your demo branch as “MAIN”, the name (of the library branch) can be whatever you want it to be.
Step #2 – Define a new Authorize value category
Step #3 – Define our custom SQL report
SELECT (@row:=@row+1) AS `S/N`, gmData.Title, gmData.Author, gmData.Copies, REPLACE (@TargetSubject:=<<Select the subject|SUBLOOKUP>>, '%', '') AS Subject, gmData.biblioid AS `Biblionumber` FROM (SELECT biblio.title AS Title, biblio.biblionumber as biblioid, ExtractValue(biblioitems.marcxml,'//datafield[@tag="245"]/subfield[@code>="c"]') AS Author, count(items.itemnumber) AS Copies, ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]') AS Subject FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) GROUP BY biblio.biblionumber ORDER BY biblio.biblionumber) as gmData, (SELECT @row := 0) r WHERE Subject LIKE <<Re-select the subject tag|SUBLOOKUP>>
(@row:=@row+1) AS `S/N`,
and
(SELECT @row := 0) r
The use of the @row variable and the counter (@row:=@row+1) gives us our “serial number” column in the report listing. We can also see the authorized value list “SUBLOOKUP” that we had defined earlier referenced here in the SQL.
NOTE: As you may note, we are asking the user to select the subject *twice*, (first time: ‘Select the subject’ and second time: ‘Re-select the subject tag’). While theoretically we should not be required to do so, thank to the use of the runtime variable @TargetSubject, in reality we ran into a type casting error (see below), thus we used this less than pretty way of asking the user to select the subject twice, to get our job done.
Step #4 – Running the report
After the report is saved, it is now time to run it, using the “Run report” option. What we’ll see now will be like this:
We need to select the *same* subject from both the drop-down lists and click on “Run the report” button. Selecting “Economics” we shall in our case get the following report:
Step #5 – Prettifying the custom report user interface
Having the user to select the subject twice is cumbersome as well prone to human error, so we decided it is time for some jquery magic to streamline this and leave the users with one only a single drop-down to choose from. For this we’ll turn turn to the IntranetUserJS system preference and add the following jquery snippet:
$("label[for='sql_params_Reselectthesubjecttag']").hide()
$('#sql_params_Reselectthesubjecttag').hide();
$("#sql_params_Selectthesubject").change(function() {
var subval = $('#sql_params_Selectthesubject').val();
$("#sql_params_Reselectthesubjecttag").val(subval);
});
If this is the first time you are hearing about the IntranetUserJS system preference, you should definitely read up this. Those of you who are indeed familiar with IntranetUserJS, all we are doing here is to (1) hide the second subject selection dropdown and its label and then (2) we are defining that whenever the user chooses a value from the *first* drop-down, the second (and now hidden) drop-down should also have the same value selected automatically. After saving the IntranetUserJS update, on running the report we shall see this:
And bingo! We are done!
Extraa Innings: To see the actual report in action
- Go to the URL https://demo-staff.l2c2academy.co.in/
- Use User name / Password : demo / demo
- Go to the section Home › Reports › Guided reports wizard › Saved reports
- Select “Run” from the “Actions” dropdown at the right.
- Play with the subject selection options to see the different outcome.
The last step is to click on “Delete Selected”, this will open a File Save dialog with the title “Remaining Records”. In the case, we provided the name LG-32016-32979_ISBN.mrk and saved it and exited from this deletion utility.
In this case everything went well and we were presented with the following screen that said “Merge Completed” and gave us the full path and filename to our merged file LG-32016-32979_ISBN_MERGED.mrk.
