Koha How-To

Creating Graphs in the Reporting Module

Pie Charts, Line Graphs, Bar Graphs - oh my!

In Koha 18.11, there is now the functionality to create graphs within the reporting module based on the library's SQL reports. A library can create either a Pie Chart, Line Graph, or Bar Graph to display its report results.

Create a Report or Use an Existing Report

During the tutorial video, Jessie and Kelly show you how to use an existing report to create both a bar chart and pie chart.

Once an existing report has been run, there is a new button above the report details -"Show Chart Settings".

The chart settings will change given which type of chart that is chosen to be created, a pie, bar, or line chart.

Pie Chart options will allow the library to choose the "x" column and the "y" column. Pie charts can be constructed on a two column result. The report can have more information, however, the chart can only be created on two of those columns.

Both the Line and Bar Chart can have more variables included in a report. For example, a library could create a report to show transactions during each month. This report could include Issues, Renewal and Returns for the months desired

The possibilities are endless for how to write reports to create charts from or use existing reports to create charts!

Note: There is a way to remove data that may be added up in the sql report - there is a checkbox to exclude the last line, this is if the report is adding up numbers in some fashion and would throw the chart off inconsistency wise.

Video Tutorial

Reports used during video

Report for Stats by Genre

SELECT

i.itemcallnumber, b.title AS 'TITLE',

ExtractValue (bi.metadata,'//datafield[@tag="245"]/subfield[@code="b"]') AS "Subtitle",

ExtractValue (bi.metadata,'//datafield[@tag="100"]/subfield[@code="a"]') AS "Author",

ExtractValue (bi.metadata,'//datafield[@tag="655"]/subfield[@code="a"]') AS "655(genre)",

i.itemcallnumber AS 'Callnumber', i.barcode AS 'Barcode',

i.dateaccessioned AS 'Accessioned',

i.issues, i.renewals, (IFNULL(i.issues, 0)+IFNULL(i.renewals, 0)) AS Total_Circ, TRUNCATE( ( (IFNULL(i.issues, 0)+IFNULL(i.renewals, 0) ) / (DATEDIFF (CURDATE(), i.dateaccessioned)/365) ), 2) AS 'TURNOVER RATE'

FROM biblio b

LEFT JOIN items i USING ( biblionumber )

LEFT JOIN biblio_metadata bi USING ( biblionumber )

WHERE i.itype=<>

AND i.ccode= <>

AND ExtractValue (bi.metadata,'//datafield[@tag="655"]/subfield[@code="a"]') LIKE CONCAT('%', <>, '%')

AND i.withdrawn=0

AND i.notforloan =0

AND i.itemlost IN (0, 2, 4, 6)

ORDER BY ExtractValue (bi.metadata,'//datafield[@tag="100"]/subfield[@code="a"]'), b.title

Report for Patrons Expiring by Category

SELECT categorycode, COUNT(*)
FROM borrowers
WHERE dateexpiry > <>
GROUP BY categorycode

Want more 18.11 information?

Koha 18.11 will be coming to ByWater Solutions partners early summer! Here are some more resources to get ready!

Stay tuned for new blog posts and tutorial videos for 18.11 and our upgrade webinar schedule!