[Solved] Automatically remove empty rows in Report

[Hagar Delest]

I have searched the forum and online but cannot find a simple answer for this - sorry if there is one...

I am trying to generate a pretty simply report from a table and am using what I believe is a now unsupported Oracle Report Builder extension (if there is a newer/better way to get a good looking report easily generated I haven't found it...). All I need to do is remove empty fields/rows from the Writer report that is generated so that there are not huge gaps when a field is empty, which there often are.

Can anyone advise a solution for this? I'm not fantastic with this program but I feel like I'm missing something simple. I cannot generate the report with Calc as I need to include images with each product entered, which I do not think Calc can handle.

[UnklDonald418]

Rather than using the “table” as a source of data for your report, create a View and use that to supply data to your report.
From the standpoint of a report, a View works the same as a table but uses SQL so you can select only those records with relevant data. If you click on Create View you will get essentially the same dialog as you get when creating a query.
The Oracle report builder extension is the best report writer currently available for Base.

[slakalot]

Thanks so much for the quick response. I am not sure a view is the answer. I need to show all records but there is no consistency to the empty fields within them.
My understanding is that when you create a report layout with fields it is rigid so that even if a record has empty fields, there will be blank rows on the generated report - or if text exceeds the row height you will get a red arrow indicating more text - so the row does not adjust in height but is set in the report design. Thinking about this (and again please forgive me for being unclear this program is very new to me) I think my question is is there a way to make a reports rows auto adjust to the amount of text/data? In writer?

[UnklDonald418]

As the name implies a Base table stores data in a tabular format. Reports are used to display that data on a printed page, so naturally that page will also be tabular.
Views/queries allow the selection of a limited set of rows and/or columns, but it will still be tabular.

Be aware that a relational database table with many blank fields in an indicator the the table design could be defective.
Are you looking for output to a printed page?
A form/subform can “auto adjust to the amount of text/data” when coupled with properly designed relational tables.
It isn't clear what you want.
Perhaps if you provide us with an example we can be of more help.

[Nocton]

I am not sure a view is the answer. I need to show all records but there is no consistency to the empty fields within them.

You just need to make a Query, select the fields you want in your report, and then set the Criterion for the fields that may be empty/null as 'IS NOT EMPTY'. If it is a fairly simple query then the report can read it directly without the need to create a View from the Query. You could also use a Conditional Print Expression in the report, but I think that is a more complicated option.

[slakalot]

I am not sure a view is the answer. I need to show all records but there is no consistency to the empty fields within them.

You just need to make a Query, select the fields you want in your report, and then set the Criterion for the fields that may be empty/null as 'IS NOT EMPTY'. If it is a fairly simple query then the report can read it directly without the need to create a View from the Query. You could also use a Conditional Print Expression in the report, but I think that is a more complicated option.

I'm either missing something (likely!) or this isn't doing the trick. If I set criterion the for the fields that may be empty as you mention above then the query leaves those records out entirely, so out of say 100 records I may only get 10...

[Nocton]

then the query leaves those records out entirely, so out of say 100 records I may only get 10...

Then what is is that you want to do? I don't understand.

[UnklDonald418]

I believe slakalot is looking for a way to dynamically adjust the Report Detail field height based on the contents of one or more table fields.

For example the detail field on a report might look something like:
John Doe 123 Beech St.
555-555-5551
-----------------------
Bill Blue 123 Oak St.
555-555-5552
-----------------------
Jack Jackson 123 Maple St.

-----------------------
Hank Henry 123 Pine St.
555-555-5553
-----------------------
the phone number for Jack Jackson is missing from the data set so the report leaves a blank line.

I believe slakalot wants the report to generate:
John Doe 123 Beech St.
555-555-5551
-----------------------
Bill Blue 123 Oak St.
555-555-5552
-----------------------
Jack Jackson 123 Maple St.
-----------------------
Hank Herry 123 Pine St.
555-555-5553
-----------------------

[slakalot]

Yes! This! ao sorry for my dodgy roundabout explaination!

[Nocton]

OK, I see what you want now. Sorry for being so slow!
You need a Conditional Print expression

Code: Select all   Expand viewCollapse view

NOT(ISBLANK([FieldName])

See attached example

[slakalot]

Thanks for replying Nocton I appreciate any help I can get here. I don't think this is the answer though as yes, it leaves informatiom/data blank, but there are still white spaces in the form that I want to adjust automatically. I don't know how to attach an image here but if you apply this condition to say Field Two, it will leave a space between One and Three. I want this space removed automatically as there will be thousands of spaces otherwise.

ID 0
One Fred Smith
Two 31 Front Street


ID 1
One Mary Jones

Three Utopia

ID 2
One Joe Brown
Two 99 Main Street
Three Anywhere

[Nocton]

Yes, I see what you mean. It is frustrating.

The only other option that I know of, which definitely works, is to use MailMerge where you have the option of hiding blank lines/fields. It is designed for addresses in letters and is probably not suitable for your application.

Another way would be to create the report as normal, but where a blank field is make it show BLANK, for example. Then, as it is a Writer document, you could do a Replace All to replace all the BLANKs with nothing.

[slakalot]

Thanks again for your suggestions Nocton. Mailmerge does not allow the grouping and sorting I need of records and BLANK eliminates the text but not the row. Looking into this further I need something similar to what Access does with a shrink function on fields I think, but Oo doesn't seem to have this functionality. If I wasn't stuck using a Mac for this project I'd use Access at this point.
I've resorted to outputting as spreadsheet as a report and can then use a filter to remove all blank rows, which is easy to do. The only problem (and the reason I didn't use spreadsheet over writer output in the first place) is I cannot display my images for each record. I'm still searching but I haven't found a way for images to show in calc, so this solution still isn't optimal. I'll look at starting a new thread for advice on this I guess!

[Nocton]

A Solution!
The Conditional Print does work as expected for Headers and Footers. So if you make a group you can do what you want. In the attached example I have made Field 'Three' into a group and put the field in the Footer, with the Conditional Print Expression as

Code: Select all   Expand viewCollapse view

NOT(ISBLANK([Three]))

All then works as you want. Of course, if you have several fields that may be blank you will need to set up several groups - a bit tedious, but does what you want.

[slakalot]

A Solution!
The Conditional Print does work as expected for Headers and Footers. So if you make a group you can do what you want. In the attached example I have made Field 'Three' into a group and put the field in the Footer, with the Conditional Print Expression as

Code: Select all   Expand viewCollapse view

NOT(ISBLANK([Three]))

All then works as you want. Of course, if you have several fields that may be blank you will need to set up several groups - a bit tedious, but does what you want.

Took me a while to get back to this but your solution worked - thank you so much for your help I very much appreciate it, I really didn't think this was going to happen with this software so I'm very relieved to not have to start from scratch!

[MSPhobe]

See also...

https://forum.openoffice.org/en/forum/v ... 42&t=91167

.. though I suspect the thread this comment appears to have found the best answer... in many cases. Each has its uses, though. (As the OP of the other- apologies for not finding this one before I asked my question. But the answer I received illustrates some interesting things, too.)