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.
Reports used during video
Report for Stats by Genre
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 )
AND i.ccode= <>
AND ExtractValue (bi.metadata,'//datafield[@tag="655"]/subfield[@code="a"]') LIKE CONCAT('%', <>, '%')
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(*)
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!
- 18.11 Manual
- 18.11 Release Documents
- 18.11 Schema Page- with a new LOOK!
- ByWater Solutions Press Release for 18.11
Read more by Kelly McElligott