Page 1 of 1

Problem with blank lines in report & too many in Excel file.

Posted: Sat Feb 25, 2012 7:42 pm
by John Dykema
I really have 2 issues here:
In Edit Output Fields I've grouped Envelope number to GRP1 and Contributions to (SUM).
It's a Column Layout Report type. The Detail Band consist of simiple fields being: First-last name, Sum-amount, and Visit Area Description. I've set each field to Print-When and Remove Line if Blank is checked for each of the 3 fields fields.

For the name I have: IIF(Qmf_temp.sum_amount < 1200,Qmf_temp.first_last,"") in the Detail Band field and do the same IIF test for the other 2 fields. I am getting correct lines for those that qualify < 1200.
1. The problem is that I'm getting a bunch of blank lines between each "correct" line of data.
Is there some coding I'm leaving out?
I'm only Filtering on just Envel Numbers, Mailing Category, and Contribution Dates and then checking for "sum_amount < 1200" in the Detail band, as I said above.

2. Also, If I change the Output type to an Excel Spreadsheet, all the people who pass the filter get in the Excel file, not just those I see on the Column Report.
I'm assuming there's no way to Filter on a persons "total contributions < 1200" before the report is executed, is there? The treasurer want these people in spreadsheet format.
Please advise.

Re: Problem with blank lines in report & too many in Excel f

Posted: Mon Apr 30, 2012 9:17 pm
by Jeff
I know this is an old post, but it doesn't look like there has been a reply.

To remove the blank lines you need to check the option that says "Remove line if blank" this will be on each of the field objects in the report. You also need to check the band height to make sure it is not set to a constant band height.

When you export a report to excel, it exports the data from the query and does not include any formatting or logic you put in the layout process.

Re: Problem with blank lines in report & too many in Excel f

Posted: Mon May 07, 2012 3:49 pm
by John Dykema
Thanks for getting back Jeff. I've been onto other things.
Yes, I have the "remove line if blank" set on each of the 3 fields in the detail line. Does it kick out the line as soon as it determines a blank field or does it goes through each of the 3 fields in a row?

Here's how I check each of the 3 fields:
IIF(Qmf_temp.sum_amount < 1200,Qmf_temp.first_last,"")
IIF(Qmf_temp.sum_amount < 1200.00,Qmf_temp.sum_amount,"")
IIF(Qmf_temp.sum_amount < 1200.00,Qmf_temp.visit_desc,"")

I also have the detail line NOT at a constant height (box not checked).
After further looking again I did find that one of my IIF's had a lowcase "f" like IIf and that got rid of most of the blank lines but there still are a few. Strange enough is that the blank lines are of variable height and not of equal measure using a ruler. :wall: Now I'm thinking that my detail data is correct but it appears that the line spacing has 'slippage'. What do you think?

Re: Problem with blank lines in report & too many in Excel f

Posted: Mon May 07, 2012 3:55 pm
by Zorak
I think the issue that you are having here is specifically with the "in Excel file" part in the subject line of this topic.

Variables, additional fields, logic in the "Print When", grouping, and everything else in the Report Designer itself is ignored when you are generating a report to file. Whatever is listed in the output fields is what will come directly into the file created.

You can specify all that logic in the Report Designer to ignore blank records, or to treat things with this value one way, while things of another value a different way, but that logic will only happen on printed reports, not on file output.

Re: Problem with blank lines in report & too many in Excel f

Posted: Tue May 15, 2012 11:40 am
by John Dykema
OK, That's good to know for future cases about the Excel files.

What my biggest problem is when I change it to a Report in Column Layout fashion it prints all the data lines correctly but then it prints several blank lines randomly between the data lines.

It's not an end of the world thing but it does waste paper and doesn't look like I know what I'm doing (although that is the case :wink: )
John