Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Howto create a report with two diferent files

Help
BRC
2011-04-14
2013-05-30
  • BRC
    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

     


Anonymous


Cancel   Add attachments