[Solved] Add Extra Text Tables in a Report

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Add Extra Text Tables in a Report

Postby longi » Sat Jun 14, 2014 7:41 pm

Hi, guys!

You should be patient with me because my English.....Sorry!

Well, I'm trying to deal with reports, and there is an issue that I'm not able to fix: I have a report with three steps or tables (two groupfields and the last one a normal field, as you will see in the attached example).
I want to put a new table (which doesn't come from the data base) after some of the tables that the report has. My intention is to select some tables from the report by a index or similar, and then add some new information in the new table (below the selected table)...Yes! a subreport if I am able to connect with tables from other report previously made for other purpose, but it could also be a table with extra information got from a webpage, etc.

I tried it for long, but I didn't get a correct insertpoint, only they were put at the end of the report or at the beginning, but I can't get a point beetwen two text tables.
It is possible to put bookmarks after each table when we are opening the report?

Ah!, The example has 6 different macros ( only variations ), but I didn't make any Form to use them, so you should search in "macros section".....

Thanks in advance!
Attachments
FORMATO 3.7z
(58.84 KiB) Downloaded 128 times
Last edited by longi on Mon Jun 16, 2014 11:50 am, edited 1 time in total.
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: Add Extra Text Tables in a Report

Postby RPG » Sat Jun 14, 2014 10:12 pm

Hello

I believe there is no sub-report. I have search for the line to prove it but can not find it. I believe I have read once that Sun did have had plans to make sub-reports but on this moment we must be glad Sun/Oracle report builder is working.

Romke
LibreOffice 6.1.5.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2170
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Add Extra Text Tables in a Report

Postby Nocton » Sun Jun 15, 2014 12:18 pm

RPG is correct. Even with the Sun/Oracle report builder, OO has no sub-report option. This has long been a major omission. You will either have to have two reports or use a query to gather all the information together and then put the last table in the report footer. I use this technique to give a consistent header to reports, but it does mean that the header information is duplicated for every record.

Regards

Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 504
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Add Extra Text Tables in a Report

Postby longi » Sun Jun 15, 2014 1:41 pm

Hi!
Thanks for your fast reply. However the main question wasn't if I can make or not a subreport in the old fashioned report builder system.I already knew it is not posible, but with macros I've got some multiserie graphs in a report, and they work properly, or a conditional formating, a new column with a calculated field....

The main question is if I can put a table or whatever textobject between two tables which had been made inside a report. How I can get a correct insertpoint?

Then, we can try to do a subreport or not, but If we can have this insertpoint we can put in it for instance a graph caught from a stock-market-webpage, and each time we launch the report we have updated data......Only is an example, and I think it could be possible to adapt this work to use it in Oracle Report Builder, but I know nothing about this tool. I have to learn about it, but later....

Thanks!
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: Add Extra Text Tables in a Report

Postby Villeroy » Sun Jun 15, 2014 2:02 pm

With Calc as report engine you have a lot more flexibility.
https://forum.openoffice.org/en/forum/v ... 75&t=18511
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27094
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Add Extra Text Tables in a Report

Postby Nocton » Sun Jun 15, 2014 4:21 pm

If we can have this insertpoint we can put in it for instance a graph caught from a stock-market-webpage, and each time we launch the report we have updated data


Calc and Oracle Report Writer have different features. The Report Writer is more powerful for traditional database reporting where a single line in the report generates a large table of output and this would normally be your best and easiest option, although for charts I now always use Calc (since OO 3.4 the chart options in Report Writer have been unusable). But for your quoted example, especially with the absence of subreports, I do agree with Villeroy that Calc is probably the best option. Each part of the Calc 'report'/output sheet can point to/use different sets of data, including data downloaded from the web.

Regards

Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 504
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Add Extra Text Tables in a Report

Postby longi » Mon Jun 16, 2014 11:50 am

Well!

I've just fixed that I wanted to do. I've only read better the Pitonyak's advices. (Only to put a new table after the table I want, no subreport, for the time being)

Thanks for reading!
I attach the solution
Attachments
FORMATO 4.rar
(58.42 KiB) Downloaded 162 times
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Sat Feb 16, 2019 2:10 pm

but on that result you got, do you have several tables ? or the trick was just groups ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby longi » Sat Feb 16, 2019 6:01 pm

Hi!
I did not remember this thread!
The trick was to get more than one table in te place I wanted. Then we can fill it with data from a query.
The last effect is that we are able to put a subreport if we are able to create a query related with the previous table.
I also used this technique in order to put a chart under the table and fill it with data from the table (which I consider as another type of subreport)
Everybody say that this target is impossible in AOoo or in LOo (whith or without ORB).
Sorry! Villeroy got it in a different way.

In this thread you have examples of nonsense things with reports, but you can get there examples about charts and subreports without ORB. (Explanations in Spanish. Sorry!)

https://forum.openoffice.org/es/forum/viewtopic.php?f=70&t=11605

Greeting! ;)
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Sun Feb 17, 2019 2:17 pm

ok
then let me request something. Are you able to insert a hyperlink into the report cells ? I tried and failed, only works if i do it on the table head.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby Villeroy » Sun Feb 17, 2019 4:04 pm

Hyperlinks can be added to Calc reports without macro coding. Just append a calculated field right of the import range with a HYPERLINK formula. The field adjusts to the row set size on every refresh.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27094
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Add Extra Text Tables in a Report

Postby longi » Sun Feb 17, 2019 5:08 pm

Hi!

First of all I've to apologize about my English!

I've never used hyperlinks, so I've never used this feature in my code and I know nothing about them, but it seems to me that hyperlinks could be used in different ways (in order to go to another cell, or in order to go to a specific part of a text or to go to a webpage, etc). Maybe, depending on what kind of use you want, the path could be different.

Anyway! If you are using ORB you have to take into consideration the huge number of tables you can have in each report (you can have lots of tables with only one row, but the aspect is only one table), and you must go through all of them to put the hyperlink you want.

If you bring us a little example and a bit more of information, we can try to study it, but we can't guarantee that it will be achieved.

I want to remember that a report is a writer documment, so, tables in it are text tables, and not spreadsheet tables (similar, but not equal), and SRB uses simple text tables, while ORB uses complex text tables. However, I'm not the better person to teach nobody. I'm sure that Villeroy and other users have a much much better knowledge to spread.

Dreams! ;)
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Sun Feb 17, 2019 5:35 pm

i see no way to add hyperlinks in ORB...
And in base reports, i can add it on the table heads, only, because if i try to add one to the cells, as soon the cells get filled by content, the hyperlinks dissapear.
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby Sliderule » Sun Feb 17, 2019 5:58 pm

I sometimes use a JAVA JAR file, SqlTool.jar that is a part of the HSQL database download.

SqlTool.jar has an option to create 'reports' using HTML output from any java database, such as:
  1. HSQL
  2. H2
  3. MySQL
  4. Microsoft Access when connected with UCanAccess java
By learning and using SqlTool.jar, the report options can create tables output ( from row sets from a Select Query ), and / or placement of data as desired. Additionally, multiple Select statements, or, CALL statements can be used as desired.

Explanation:
  1. You canNOT connect to an OpenOffice / LibreOffice Embedded database just as you canNOT connect any other outside database tool to an Embedded database. If your database is an external HSQL database, try SqlTool.jar .

  2. For documentation on SqlTool.jar, please review:

    1. SqlTool.jar documentation: http://www.hsqldb.org/doc/2.0/util-guide/index.html

    2. SqlTool.jar documentation - HTML Chapter: http://www.hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html#sqltool_report-sect
Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1184
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Add Extra Text Tables in a Report

Postby longi » Sun Feb 17, 2019 6:59 pm

Hi!
After a simple search, I encountered this link:

https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=54467&p=238176&hilit=.createInstance+Hyperlinkurl#p238142

In it it is explained how to insert a hyperlink in a texttable cell.

Here you have a macro for a single cell in a single texttable in a SRB report:

Code: Select all   Expand viewCollapse view
Sub Hyperlink
    '--------------------------------------------------------------------
    ' 0 We declare the variables
   
    Dim oreportdoc As Object, ocontroller As Variant
    Dim oTextTable As Variant, oCell As Variant
    Dim oCurs As object, oText As Variant
    '------------------------------------------------------------------------------
    '1º We open our report
   
    ocontroller = Thisdatabasedocument.currentController
    if not ocontroller.isconnected then ocontroller.connect                                        ' The connection
    oreportdoc = Thisdatabasedocument.reportdocuments.getbyname("MyReport").open                   ' We open the report
    oTexttable = oreportdoc.Texttables(0)                                                          ' The table we have inside the report
    '-------------------------------------------------------------------------------
    '2º We choose the cell

    oCell = oTextTable.getCellByPosition(1, 2)                                                     ' Second column third row
    oText = oCell.Text                                                                             ' Text inside the cell
    oCurs = oText.createTextCursor()                                                               ' We create a text cursor
    oCurs.gotoEND(True)                                                                            ' We select the entire text
    oCurs.setString("Click here")                                                                  ' We replace the pre-existing text
    oCurs.gotoStart(False)                                                                         ' We go to the start of the new text
    oCurs.gotoEnd(True)                                                                            ' We select all new text
    oCurs.HyperLinkURL = "http://user.services.openoffice.org/en/forum/"                           ' We put the Hyperlink
   

End Sub


I hope it could be useful for you!

Bye! ;)
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Sun Feb 17, 2019 9:56 pm

Very good longy, and the way to set a macro to run on the hyperlink onClick event ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby longi » Mon Feb 18, 2019 10:37 am

As you can see, this is another topic.
Robleyd changed it to the base forum.
Also I think you need one another person or maybe to explain a bit more your needs.

Bye!
OpenOffice 4.1.5 on Windows 10
LibreOffice 5.1 on Windows 7
LibreOffice 6.0.1 on Windows10
longi
 
Posts: 108
Joined: Mon Jul 15, 2013 5:04 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Mon Feb 18, 2019 1:16 pm

yes longi, that solution you showed is good but has other difficult problems in add. Example, how to set the event hyperlink onclick on each added hyperlink on the table. And the most important that i cant solve, at the moment, how to determine when the report has been finished loading. Specially that last I was trying last night without success, none of the existant events help with that... With this last problem, imagine that you want to add the hyperlinks when we load a report with base, not from macro. The object Texttables(0) isnt set, and it is required to know when all the data was filled on the table to run our macro...
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Tue Feb 19, 2019 9:33 pm

^^ any idea about ?
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] Add Extra Text Tables in a Report

Postby Villeroy » Tue Feb 19, 2019 10:35 pm

A year ago I managed to generate a set of reports and save them as separate Writer documents. These Writer documents could be merged in a bigger Writer document.
viewtopic.php?f=100&p=450328#p450328
This is not an easy example but two weeks ago I managed to rebuild my Writer document with modified database content from 8 subreports. You need to be familiar with "power filtering" and with SQL UPDATE commands. The UPDATE query updates a value in the filtering table from a SELECT query, runs a report and saves it, fetches the next value from the SELECT query, updates the filter value, runs the record and so on.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27094
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Add Extra Text Tables in a Report

Postby Villeroy » Tue Feb 19, 2019 11:01 pm

Another macro thing that may help: viewtopic.php?f=29&t=81229
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27094
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Add Extra Text Tables in a Report

Postby arfgh » Wed Feb 20, 2019 2:29 pm

well yes villeroy, but in this case it is more about to determine when the report finish to load data into tables...
OpenOffice last version - Windows 8.1 Enterprise x64 - Windows XP x64 SP2
arfgh
 
Posts: 443
Joined: Tue Mar 05, 2013 6:44 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 0 guests