Stop printing of Blank Pages

[Cozmo2]

Each section on a Bill is checked for a specific condition in the 'Print When Condition'. This can cause blank pages to be created and printed, it there a way to not print blank pages?

I use CF10

[EddieLotter]

As I mentioned in your other thread about this scenario, if you prevent your report query from returning records that meet those conditions then you don't have to worry about handling them in the report itself.

[Cozmo2]

I'm having trouble with the query. I think I have to GROUP BY all the fields I am selecting, because the data is not the same for each record it is not summing the amount field. Here is my query:

SELECT tmptract.soc_sec, tmptract.p_bal, tmptract.bal, tmptract.semester, tmptract.sch_yr, tmptract.due_date, tmptract.asofdate, name.last_name, name.first_name, name.mi, address.st_addr, address.add_addr, address.add_add2, address.city, address.state, address.zip, address.add_rid,  address.salutation, sysvar.title, sysvar.schaddr1, sysvar.schaddr2, sysvar.schaddr3,

'' as invoice_no, transact_prebill.ref_id, SUM(transact_prebill.amt_1), transact_prebill.offered, transact_prebill.loan_fees, transact_prebill.date, '' as check_no, transact_prebill.transact_rid, tcodes.act_code, 2 as bill_type

FROM tmptract, name, address, transact_prebill, tcodes, sysvar

WHERE tmptract.soc_sec = name.soc_sec

    AND address.soc_sec = name.soc_sec

    AND transact_prebill.soc_sec = tmptract.soc_sec

    AND tcodes.tcodes = transact_prebill.tcodes

    AND sysvar.school_id <> tmptract.soc_sec

    AND address.labels2 = 1

    AND tmptract.token = '#session.token#'

    AND tcodes.inc_bill = 1

    AND transact_prebill.posted = 0

    AND transact_prebill.exclude_from_bill = 0

    AND transact_prebill.accept not in('n','v')

    AND transact_prebill.date <= tmptract.asofdate

    AND transact_prebill.semester = tmptract.semester

    AND transact_prebill.sch_yr = tmptract.sch_yr

GROUP BY tmptract.soc_sec, tmptract.p_bal, tmptract.bal, tmptract.semester, tmptract.sch_yr, tmptract.due_date, tmptract.asofdate, name.last_name, name.first_name, name.mi, address.st_addr, address.add_addr, address.add_add2, address.city, address.state, address.zip, address.add_rid,  address.salutation, sysvar.title, sysvar.schaddr1, sysvar.schaddr2, sysvar.schaddr3,

transact_prebill.ref_id, transact_prebill.offered, transact_prebill.loan_fees, transact_prebill.date, tcodes.act_code

ORDER BY name.last_name, name.first_name, name.mi, tmptract.soc_sec,address.add_rid, bill_type desc, act_code, transact_prebill.date

The transact_prebill.ref_id, transact_prebill.transact_rid, transact_prebill.offered, transact_prebill.loan_fees, transact_prebill.date, tcodes.act_code can all contain different data. Is it possible to group on just the tmptract.soc_sec?

[EddieLotter]

It depends on what fields you need to show in the report.

Do all the fields in your select clause get printed in the report?

[Cozmo2]

The address.add_rid & transact.transact_rid are not shown on the report.

[EddieLotter]

Okay, so your report shows quite a lot of detail.

Why are you summing amt_1 in your SQL query? Surely you want to show the actual amount of each transaction next to the transaction and have a total at the bottom of the transactions.

[Cozmo2]

This is a student's bill. For this version of the bill we do not want to print bills where the balance due is less than $20.00.

[EddieLotter]

As I said in my first reply, you want to use your SQL query to prevent data from reaching your report that you don't want to print. That way you won't have to decide whether or not to print something in the definition of the report itself. In other words, the report can assume that any data it receives are data that must be printed.

Remove the grouping from your query and add a condition that will only return records where the transaction amount is at least $20. Something like this:

SELECT tmptract.soc_sec, tmptract.p_bal, tmptract.bal, tmptract.semester, tmptract.sch_yr, tmptract.due_date, tmptract.asofdate, name.last_name, name.first_name, name.mi, address.st_addr, address.add_addr, address.add_add2, address.city, address.state, address.zip, address.add_rid,  address.salutation, sysvar.title, sysvar.schaddr1, sysvar.schaddr2, sysvar.schaddr3,'' as invoice_no, transact_prebill.ref_id, transact_prebill.amt_1, transact_prebill.offered, transact_prebill.loan_fees, transact_prebill.date, '' as check_no, transact_prebill.transact_rid, tcodes.act_code, 2 as bill_type

FROM tmptract, name, address, transact_prebill, tcodes, sysvar

WHERE tmptract.soc_sec = name.soc_sec

    AND address.soc_sec = name.soc_sec

    AND transact_prebill.soc_sec = tmptract.soc_sec

    AND tcodes.tcodes = transact_prebill.tcodes

    AND sysvar.school_id <> tmptract.soc_sec

    AND address.labels2 = 1

    AND tmptract.token = '#session.token#'

    AND tcodes.inc_bill = 1

    AND transact_prebill.posted = 0

    AND transact_prebill.exclude_from_bill = 0

    AND transact_prebill.accept not in('n','v')

    AND transact_prebill.date <= tmptract.asofdate

    AND transact_prebill.semester = tmptract.semester

    AND transact_prebill.sch_yr = tmptract.sch_yr

WHERE NOT tmptract.soc_sec IN
(
  SELECT transact_prebill.soc_sec
  FROM transact_prebill

  GROUP BY transact_prebill.soc_sec
  HAVING (SUM(transact_prebill.amt_1) < 20)
)

ORDER BY name.last_name, name.first_name, name.mi, tmptract.soc_sec,address.add_rid, bill_type desc, act_code, transact_prebill.date

Your SQL engine syntax may differ, but you can adjust it as necessary. You will also need to add your other criteria to the sub-query, for "posted" and "exclude_from_bill" etc.