Stop printing of Blank Pages

Report · Jul 11, 2014

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

Report · Jul 14, 2014

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.

Report · Sep 04, 2014

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?

Report · Sep 08, 2014

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?

Report · Sep 08, 2014

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

Report · Sep 08, 2014

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.

Report · Sep 08, 2014

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.

Report · Sep 08, 2014

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.