[Solved] Long text and multi-line text in ORB

[Villeroy]

LibreOffice 5.4 with ORB and HSQL 2.4.1
Textual information only with group concatenations and multi-line fields in the result set.
Q1: Is it possible to get report "cells" with flexible heights like text table rows in Writer which grow or shrink with their content (multi-line cells with line wrap).
Q2: Is it possible to show text values with line breaks. The field is blank, no matter if I use char(10) or char(13) or both.

The report design looks like this:

[ =GROUPED TITLE ]
[ =item1,item2,item3 by group_concat ]
[ column labels... for details]
[ordinal] [short text] [long text] [text with line breaks char(10) or char 13)]

[Villeroy]

Dragging the query into a spreadsheet with some spreadsheet voodoo (text styles with line wrapping, hidden columns and substitutions) solved this for me. It looks more professional than the ORB report.

P.S. for the records. ORB provides a very professional look if you are patient enough to find the right position, size, font size and orientation for every little box and every dividing line in every section. The little boxes showing the field contents are "formatted fields" for numbers, dates etc. which are formatted with number format "@" (Text) in case of text data. There is no specialized text box with layout capabilities for long text (e.g. word wrap, resize to fit). My Calc reports with manual cell merging, hidden columns and calculated fields where good enough for next day's demo. Meanwhile I have redone most of them in ORB.

[longi]

First of all I have to apologize because of my English!
Also I have to apologize because I’ve been out of line for a while, so, I couldn’t post anything here.
Now I can:
ORB is a bit strange for me and I prefer the old fashioned system, however, we can run the auto-height property working with macros ( I know you hate them, sorry!)
We have to know that the simplest ORB report has a three-table structure (one in the report header, other in the report footer and the other one which is the main table (detail table).
If you have an ORB report you can edit the document. If you edit it and then click on menu-view-text-limits, so you can see the complexity of the table in which ORB is working (comlex text-table, but only text-table).
Normally, as I’m a really bad hobby programmer, I try to figure out the number of the row (counting rows) in which I have a textbox in order to put inside the macro this row with auto-height property.
I’m sure that is more efficient to establish an indefinite cycle that run over all rows and put all of them in the ‘auto-height mode’.
If the ORB is not the simplest, you can have more and more tables, but you can calculate the rule in order to act in each table you need.
All this considerations are the same, but having also in consideration the columns, in order to use the border cells for a specific cell. This is other thing that ORB has to develop.
In the example of code I put below I run over all tables except the first and the last one. I try to detect in the first cell a specific piece of text (“SERVICIO:”), which is the signal, in this case, to work in the next table (Z+1).
Finally I run over all rows in that table in order to put all of them in auto-height way:

Code: Select all

    oTexttable.getRows().getByIndex(i).IsAutoHeight = True                                               ' Autoheight

Code: Select all

    '-------------------------------------------------------------------
    '1º We open the report
    
    ocontroller = Thisdatabasedocument.currentController
    if not ocontroller.isconnected then ocontroller.connect
    oreportdoc = Thisdatabasedocument.reportdocuments.getbyname("Inf_ModQuirofanos_ORB").open

'===================================================================
    '----------------------------------------
    ' We start the autheight patch
    
    NTexttable = oreportdoc.Texttables().GetCount()                                                      ' Number of tables
    For z=2 to NTexttable-1                                                                              ' We see all the tables except the first one and the last one
    oTexttable = oreportdoc.Texttables(z)                                                                ' The actual table inside the cycle
    Filas = oTexttable.rows.count-1                                                                      ' Rows of the table
    Columnas = oTexttable.Columns.count-1                                                                ' Columns of the table
    Fila = oTexttable.rows                                                                               ' Row object

    '------------------------------------------------------------------------
    ' 2º We will inspect each table, to select the next one of which has in thefirst cell the especific text

    oCell = oTextTable.getCellByPosition(0, 0)                                                           ' The first cell of table                                                            
    oText = oCell.Text                                                                                   ' Text inside the cell
    oCurs = oText.createTextCursor()                                                                     ' Cursor in the cell 
    oCurs.gotoEND(True)                                                                                  ' Cursor to the end of the cell  
    if oCurs.GetString ="SERVICIO:" Then                                                                 ' Signal to select the table
    oTexttable = oreportdoc.Texttables(z+1)                                                              ' We select the nest table
    Filas = oTexttable.rows.count-1                                                                      ' Rows of the table
    'oCell= oTextTable.getCellByPosition(1, 0)                                                            ' Selected cell
    'oCell.setPropertyValue("BackColor", RGB(255, 255, 0))                                                ' Colour in the selected cell
 
    For i=0 to Filas                                                                                     ' We run all rows
    Fila = oTexttable.rows(i)                                                                            ' Specific row in each cycle
'    Fila.BackColor = RGB(162,162,122)                                                                    ' Colour
    oTexttable.getRows().getByIndex(i).IsAutoHeight = True                                               ' Autoheight
    Next                                                                                                 ' The next row
    End if                                                                                               ' We finish the condition to select the table
    Next                                                                                                 ' We go to the next table
    '------------------------------------------------------------
    ' We finish the autoheight patch

Lines as :

Code: Select all

    'oCell.setPropertyValue("BackColor", RGB(255, 255, 0))                                                ' Colour in the selected cell

or

Code: Select all

'    Fila.BackColor = RGB(162,162,122)                                                                    ' Colour

were used while I was trying to detect if the code was correct or not.
Obviously the report should be opened by the macro, if not the auto-height property won’t be used.

I hope it could be useful for somebody!

Best regards!

[DaddySmack66]

(I originally posted this to the Tutorial section, sorry)

Thank you Villaroy for your dedicated help with Base and this post. I am actively (re)creating an “IT” database and now have a need for the Reporting and have struggle with the Base Report writer and the lack of grow/shrink with the Text box. I saw other posts concerning reporting and you recommending Calc. Last night I was able to get the report working and solved the quick grow/shrink in my “notes” cell by a macro.

I placed the Calculated fields (sum of column) above the column headers and can refresh data and replace query data, so far so good. Can you embed any other “Calc” as a report engine for Base in this thread for myself and future Base Report writing needs? Again, thanks for your help to the forum.

The Record Macro feature in Calc seems to work quite nice enough for quick formatting and to placing the Macros’s in the Menu makes it quick work. Can push buttons be added to a Calc Spreadsheet?

[Villeroy]

[Tutorial] Using registered datasources in Calc

[DaddySmack66]

I skimmed through the Tutorial, excellent stuff! I plan to use Calc as a report engine to complement Base. You may have mentioned, but I assume and just tested but I want to ask anyway: the "import1" range is a one-way sync correct? If I make a change on the spreadsheet in the range, change a Calc cell value, delete a row in the range, it DOES NOT delete or change data in the database correct? I want to ONLY make data changes IN Base and NOT Calc. I tested with a Cell change in the Import1 range, then refreshed and the data did not save to the database.

[Villeroy]

Yes, it is a one-way sync. Cell styles (not macros) are the way to go for consistent formatting.

Can push buttons be added to a Calc Spreadsheet?

Entire input forms if you want: viewtopic.php?t=88516&p=416210#p416210