BRC - 2011-04-14

Hello, I´m trying to add in the checkouts.rpt the option to search&sort  by material an collection. So I look to one allready have it and what I get is this script:

.title "reportCheckouts"
.category Circulation
.column bibid hidden
.column copyid hidden
.column mbrid hidden
.column barcode_nmbr sort=barcode_nmbr
.column title sort=title func=biblio_link
.column author sort=author
.column member_bcode sort=member_bcode
.column name sort=name func=member_link
.column status_begin_dt sort=status_begin_dt
.column due_back_dt sort=due_back_dt
.column collection sort=material title="Collection"
.column material sort=collection title="Material"
.parameters
.       date due_before title="due_back_bf"
.       date out_since title="out_since"
.       select grade_sel title="grade"
.               item ''
.sql
# 0.6.0: SQL in RPT .select parameter should be one line, unwrapped
SELECT DISTINCT data AS value, data AS title FROM member_fields WHERE code = 'schoolGrade' ORDER BY title
.end sql
.       end select
.       select collection title="Collection"
.               item ""
.               sql
                        select coll.code as value, coll.description as title from collection_dm coll
.               end sql
.       end select
.       select material title="Material"
.               item ""
.               sql
                        select mat.code as value, mat.description as title from material_type_dm mat
.               end sql
.       end select
.end parameters
.sql
        SELECT c.bibid, c.copyid, m.mbrid, c.barcode_nmbr,
                b.title, b.author, c.status_begin_dt,
                c.due_back_dt, m.barcode_nmbr member_bcode,
                concat(m.last_name, ', ', m.first_name) name,
                school_grade.data school_grade, school_teacher.data school_teacher
        FROM biblio b, biblio_copy c, member m
        LEFT JOIN member_fields AS school_grade ON school_grade.mbrid=m.mbrid
                AND school_grade.code='schoolGrade'
        LEFT JOIN member_fields AS school_teacher ON school_teacher.mbrid=m.mbrid
                AND school_teacher.code='schoolTeacher'
        WHERE b.bibid = c.bibid
                AND c.mbrid = m.mbrid
                AND c.status_cd = 'out'
.       if_set due_before
                AND c.due_back_dt <= %due_before%
.       end if_set
.       if_set out_since
                AND c.status_begin_dt >= %out_since%
.       end if_set
.       if_set grade
.               if_equal criteria1 start
                        AND school_grade.data LIKE '%"grade%%%'
.               end if_set
.               if_equal criteria1 trunc
                        AND school_grade.data LIKE '%%%"grade%%%'
.               end if_set
.       else
.               if_not_equal grade_sel ''
                        AND school_grade.data = %grade_sel%
.               end if_set
.       end if_set
.       if_set teacher
.               if_equal criteria2 start
                        AND school_teacher.data LIKE '%"teacher%%%'
.               end if_set
.               if_equal criteria2 trunc
                        AND school_teacher.data LIKE '%%%"teacher%%%'
.               end if_set
.       end if_set
.       order_by_expr
.end sql
.sql
        select b.bibid, concat_ws(' ', b.call_nmbr1, b.call_nmbr2, b.call_nmbr3) callno,
                c.barcode_nmbr, c.create_dt, b.title, b.author,
                coll.description as collection, mat.description as material
        from biblio_copy c, biblio b,
                collection_dm as coll, material_type_dm as mat
        where b.bibid=c.bibid
                and mat.code=b.material_cd
                and coll.code=b.collection_cd
.       if_set newer
                and c.create_dt >= %newer%
.       end if_set
.       if_set older
                and c.create_dt < %older%
.       end if_set
.       if_not_equal collection ""
                and b.collection_cd = %collection%
.       end if_not_equal
.       if_not_equal material ""
                and b.material_cd = %material%
.       end if_not_equal
        group by c.barcode_nmbr, b.title, b.author
.       order_by_expr
.end sql

So, as you can see there are two reports in one working in separate ways.

my wondering is how to make this to work as just one report?

in other words, how do I add the option to search&sort by material and collection to the checkouts report?

thanks for your help
hrj