How to run a report in Koha to find out which call numbers grow at higher rates

I have inherited a Koha 3.05 installation, and now need to figure out how to run a report within Koha to find out which call numbers grow at higher rates... I know it's going to be like a SQL query, but I don't understand the actual process at this point, much less how to craft this particular query.

Any help is appreciated, I'm a Koha admin newbie.

Tamoko

• koha

Comments

• Gem: To clarify, are you looking for circ increase or item count increase? Depending on your level of comfort with SQL, this site might provide some hints: http://wiki.koha-community.org/wiki/SQL_Reports_Library There are a couple of queries that look like they could be modified to be close to what you want but I don't have a Koha database to test them on to be sure.

Answer by ND Geek

If you're talking about collection growth, the best way I can think of to find that is to pull out a monthly count by call number, and then graph it in Excel. The query to pull this data out gets a bit messy, though, if you're using LC call numbers, as MySQL doesn't care to parse substrings based on patterns:

SELECT date_format(items.dateaccessioned, '%M %Y') AS "Month Added",
    SUBSTR(TRIM(items.itemcallnumber), 1, LEAST(
        IF(LOCATE(' ', TRIM(items.itemcallnumber)) > 0, LOCATE(' ', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('0', TRIM(items.itemcallnumber)) > 0, LOCATE('0', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('1', TRIM(items.itemcallnumber)) > 0, LOCATE('1', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('2', TRIM(items.itemcallnumber)) > 0, LOCATE('2', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('3', TRIM(items.itemcallnumber)) > 0, LOCATE('3', TRIM(items.itemcallnumber)), 999),
        IF(LOCATE('4', TRIM(items.itemcallnumber)) > 0, LOCATE('4', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('5', TRIM(items.itemcallnumber)) > 0, LOCATE('5', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('6', TRIM(items.itemcallnumber)) > 0, LOCATE('6', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('7', TRIM(items.itemcallnumber)) > 0, LOCATE('7', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('8', TRIM(items.itemcallnumber)) > 0, LOCATE('8', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('9', TRIM(items.itemcallnumber)) > 0, LOCATE('9', TRIM(items.itemcallnumber)), 999)
    ) - 1) AS 'LC Range', COUNT(*) AS "Count"
FROM items
GROUP BY date_format(items.dateaccessioned, '%M %Y'),
    SUBSTR(TRIM(items.itemcallnumber), 1, LEAST(
        IF( LOCATE(' ', TRIM(items.itemcallnumber)) > 0, LOCATE(' ', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('0', TRIM(items.itemcallnumber)) > 0, LOCATE('0', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('1', TRIM(items.itemcallnumber)) > 0, LOCATE('1', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('2', TRIM(items.itemcallnumber)) > 0, LOCATE('2', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('3', TRIM(items.itemcallnumber)) > 0, LOCATE('3', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('4', TRIM(items.itemcallnumber)) > 0, LOCATE('4', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('5', TRIM(items.itemcallnumber)) > 0, LOCATE('5', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('6', TRIM(items.itemcallnumber)) > 0, LOCATE('6', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('7', TRIM(items.itemcallnumber)) > 0, LOCATE('7', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('8', TRIM(items.itemcallnumber)) > 0, LOCATE('8', TRIM(items.itemcallnumber)), 999),
        IF( LOCATE('9', TRIM(items.itemcallnumber)) > 0, LOCATE('9', TRIM(items.itemcallnumber)), 999)
    ) - 1)

This will give you 'Month Added', 'LC Range', and 'Count'. Once you have this, export it as a CSV file, which you can then open in Excel. Once in Excel, select the data range, and insert a PivotTable. You'll want your "Column Labels" to be the LC Range, the "Row Labels" to be the Month Added, and the "Values" to be the Count. Once you add the Count to "Values", click on it, and select "Value Field Settings..." to open a dialog box. In that dialog box, select the "Show Values As" tab, and then in the drop-down, select "Running Total In" and set the "Base Field" to Month Added. Click "OK". If you want a graphical representation of this data, now you just need to select "PivotChart" and select a Line style (or whatever style you prefer).

Comments