SQL Reports

If you want to put links to your report, you can use the SQL's CONCAT keyword in your SELECT clause.

for example, the following SQL Report will list all your biblio with a link to each of them.

SELECT
  biblionumber, 
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',title,'</a>') AS Title 
FROM biblio 
ORDER BY biblionumber

MySQL has some XML handling functions: http://dev.mysql.com/doc/refman/5.4/en/xml-functions.html

For example:

SELECT 
  ExtractValue((SELECT marcxml FROM biblioitems WHERE biblionumber=14), '//datafield[@tag="952"]/subfield[@code>="a"]') AS ITEM;

Returns the entire 952 data for all 952 fields for biblionumber 14 (without delimiting)

SELECT 
  ExtractValue((SELECT marcxml FROM biblioitems WHERE biblionumber=14), '//datafield[@tag="260"]/subfield[@code>="b"]') AS ITEM;

Returns the 260$b data for biblionumber 14

SELECT
  biblio.title, items.itemcallnumber, items.holdingbranch,
  items.homebranch, items.barcode, issues.issuedate 
FROM issues 
LEFT JOIN items ON issues.itemnumber=items.itemnumber 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE issues.branchcode !='LIBRARY' AND items.homebranch = 'LIBRARY' 
ORDER BY items.homebranch, issues.issuedate, biblio.title

SELECT
  biblio.title, items.itemcallnumber, items.holdingbranch,
  items.homebranch, items.barcode, issues.issuedate 
FROM issues 
LEFT JOIN items ON issues.itemnumber=items.itemnumber 
LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
WHERE issues.branchcode='LIBRARY' AND items.holdingbranch !=  items.homebranch 
ORDER BY items.homebranch, issues.issuedate, biblio.title

SELECT count(*),branchcode
FROM reserves
WHERE
 reservedate >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND
 reservedate <= LAST_DAY(now() - interval 1 month)
GROUP BY branchcode

SELECT count(*), branchcode
FROM old_reserves
WHERE
 (timestamp LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')))
AND
  (reservedate >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01')
AND
 reservedate <= LAST_DAY(now() - interval 1 month))
AND Found = 'F'
GROUP BY branchcode

SELECT count(*),branchcode
FROM old_reserves 
WHERE
 (timestamp LIKE concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-%')))
AND
 Found = 'F'
GROUP BY branchcode

SELECT  borrowers.cardnumber, borrowers.surname, borrowers.firstname, 
          FORMAT(SUM(accountlines.amountoutstanding),2) AS due 
  FROM borrowers LEFT JOIN accountlines ON (borrowers.borrowernumber=accountlines.borrowernumber) 
  WHERE accountlines.amountoutstanding > 0 
  GROUP BY borrowers.cardnumber 
  ORDER BY borrowers.surname ASC

  SELECT branchcode,categorycode,COUNT(*)
  FROM borrowers WHERE MONTH(dateenrolled) = 04 AND YEAR(dateenrolled)= 2009
  GROUP BY branchcode,categorycode 
  ORDER BY branch

SELECT borrowers.surname, borrowers.firstname, borrowers.phone, borrowers.cardnumber, borrowers.address, borrowers.city, borrowers.zipcode, issues.date_due, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', items.itype, items.itemcallnumber, items.barcode, items.homebranch, biblio.title, biblio.author 
FROM borrowers LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber) 
LEFT JOIN items ON (issues.itemnumber=items.itemnumber) 
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) 
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) > '30' AND issues.branchcode = 'LIBRARY' 
ORDER BY borrowers.surname ASC, issues.date_due ASC

 SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry 
  FROM borrowers 
  WHERE ' ' IN (email)

  SELECT issues, biblio.title, author, surname, firstname, borrowers.sort1, 
         items.itemcallnumber, items.barcode, issues.issuedate, issues.lastreneweddate 
  FROM issues 
  LEFT JOIN borrowers ON borrowers.borrowernumber=issues.borrowernumber 
  LEFT JOIN items ON issues.itemnumber=items.itemnumber 
  LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber 
  WHERE issues.branchcode='LIBRARY' AND sort1='2009'
  ORDER BY issues.branchcode, borrowers.sort1, borrowers.surname, issues.issuedate, biblio.title

  SELECT branch, month(datetime) AS month, year(datetime) AS year, count(datetime) AS count 
  FROM statistics 
  WHERE type LIKE 'issue' 
  GROUP BY branch, year, month 
  ORDER BY year, month DESC, branch ASC

  SELECT barcode, homebranch AS 'branch', itemcallnumber, title 
  FROM biblio, items 
  WHERE items.biblionumber = biblio.biblionumber AND homebranch = 'iola' AND location = 'io50' AND itemnumber NOT IN 
  (SELECT itemnumber FROM issues) UNION 
  (SELECT barcode, homebranch AS 'branch', itemcallnumber, title 
  FROM biblio, items 
  WHERE items.biblionumber = biblio.biblionumber AND homebranch = 'iola' AND location = 'io50' AND itemnumber NOT IN 
  (SELECT itemnumber FROM old_issues WHERE date(issuedate) BETWEEN 2008-05-01 AND curdate()) ) 
  ORDER BY itemcallnumber, barcode

  SELECT borrowers.cardnumber, borrowers.surname, borrowers.firstname, issues.date_due, items.barcode, biblio.title, biblio.author
  FROM borrowers 
  LEFT JOIN issues ON (issues.borrowernumber=borrowers.borrowernumber) 
  LEFT JOIN items ON (issues.itemnumber=items.itemnumber) 
  LEFT JOIN biblio ON (biblio.biblionumber=items.biblionumber) 
  WHERE issues.date_due = DATE_ADD(curdate(), INTERVAL 1 DAY) 
  ORDER BY borrowers.surname ASC

  SELECT frombranch, monthname(datesent) month,COUNT(*) 
  FROM branchtransfers WHERE tobranch="Me" AND YEAR(datesent)=YEAR(NOW())-1 
  GROUP BY month

SELECT items.homebranch, COUNT(*) 
FROM branchtransfers 
LEFT JOIN items ON (branchtransfers.itemnumber=items.itemnumber) 
WHERE (items.homebranch != branchtransfers.tobranch) 
AND (branchtransfers.frombranch != branchtransfers.tobranch) AND YEAR(datesent)=YEAR(NOW())-1 
GROUP BY items.homebranch

SELECT branchtransfers.tobranch, COUNT(*) 
FROM branchtransfers 
LEFT JOIN items ON (branchtransfers.itemnumber=items.itemnumber) 
WHERE (branchtransfers.tobranch != items.homebranch) 
AND (branchtransfers.tobranch != branchtransfers.frombranch) AND YEAR(datesent)=YEAR(NOW())-1 
GROUP BY branchtransfers.tobranch

SELECT  monthname(datesent) month,COUNT(*) 
  FROM branchtransfers WHERE frombranch="MMM" AND YEAR(datesent)=YEAR(NOW())-1 
  GROUP BY month ORDER BY month(datesent)

SELECT SUM(amount) 
  FROM accountlines 
  WHERE DATE(timestamp)=CURDATE() AND (accounttype='FOR' OR accounttype='W')

SELECT SUM(amount) 
  FROM accountlines 
  WHERE DATE(timestamp)=CURDATE() AND (accounttype='PAY' OR accounttype='C')

SELECT 
  round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged Yesterday'
FROM accountlines WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day)

SELECT 
 round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Lost Items Charged Yesterday'
FROM accountlines WHERE (accounttype = 'L' ) AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'M') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Sundry Fees Yesterday'
FROM accountlines WHERE (accounttype = 'M') AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'C') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Credits Yesterday'
FROM accountlines WHERE (accounttype = 'C') AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'N') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'New Card Fees Yesterday'
FROM accountlines WHERE (accounttype = 'N') AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'PAY') AND date = (now() - interval 1 day) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Payments Yesterday'
FROM accountlines WHERE (accounttype = 'PAY') AND date = (now() - interval 1 day)

SELECT 
  round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' 
FROM accountlines 
LEFT JOIN borrowers ON (accountlines.borrowernumber=borrowers.borrowernumber) 
WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND YEAR(date) = YEAR(NOW()) AND borrowers.branchcode = 'LIB'

SELECT round(Sum(accountlines.amount),2) AS 'Fines Charged YTD' FROM accountlines WHERE (accounttype = 'F' OR accounttype = 'FU' ) AND YEAR(date) = YEAR(NOW())

SELECT FORMAT(Sum(accountlines.amountoutstanding),2) FROM accountlines

SELECT round(Sum(accountlines.amount),2) AS 'Paid Yesterday' FROM accountlinesWHERE (accounttype = 'PAY' ) AND date = (now() - interval 1 day)

SELECT items.itemcallnumber,items.datelastborrowed,biblio.title,biblioitems.publicationyear FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) ORDER BY items.cn_sort ASC

SELECT GROUP_CONCAT(biblionumber SEPARATOR ', ') AS biblionumbers, title, author FROM biblio GROUP BY CONCAT(title,"/",author) HAVING COUNT(CONCAT(title,"/",author))>1