Hello,

I try to make a report that will do a free text search in the field copy_desc in the biblio_copy table (where I keep the book location data). I want to be able to sort it on copy_desc and then on author, title. I thought it would be easy doing some slight changes to the Bibliograhies on Subject report, but no luck yet. This is what I have managed to come up with, and it makes all of OpenBiblio to freeze when I click on the reports tab :)

Any help on which syntaxes and parameters to change to make it work are most welcome.

Sr Marie

.title "Bibliographies by Subject - Omgjord till placeringssök"
.category "Inventering"
.column bibid hidden
.column copy_desc title="Placering" sort=copy_desc
.column barcode_nmbr title="Steckkod" sort=barcode_nmbr
.column callno title="Call Num." sort=callno
.column collection title="Finns/dtm"
.column author title="Författare" sort=author
.column title func=biblio_link title="Title"

.parameters
. select plac1st default=any title="Placeringens 1a bokstav"
. item ''
. sql
# 0.6.0: SQL in RPT .select parameter should be one line, unwrapped
SELECT copy_desc AS plac FROM biblio, biblio_copy ORDER BY author
. end sql
. end select
. select criteria default=start title="eller placeringen"
. item start title="Börjar med"
. item trunc title="Innehåller"
. end select
. string plac title=" "
. order_by default=copy_desc
. item copy_desc title="Placering" expr="concat(copy_desc, author)"
. item author title="Författare" expr="concat(author, copy_desc)"
. item callno title="Klassificering"
. end order_by
.end parameters

.sql
SELECT b.bibid, c.copy_desc, concat_ws(' ', b.call_nmbr1, b.call_nmbr2, b.call_nmbr3) callno,
b.title, b.author, c.barcode_nmbr
FROM biblio b, biblio_copy c
WHERE b.bibid=c.bibid
AND c.copy_desc !=''
. if_set plac
. if_equal criteria start
AND c.copy_desc LIKE '%"copy_desc%%%'
. end if_set
. if_equal criteria trunc
AND c.copy_desc LIKE '%%%"copy_desc%%%'
. end if_set
. else
. if_not_equal copy_desc1st ''
AND c.copy_desc LIKE '%"copy_desc1st%%%'
. end if_set
. end if_set
. order_by_expr
.end sql