[Solved] Add Extra Text Tables in a Report

[longi]

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!

[RPG]

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

[Nocton]

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

[longi]

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!

[Villeroy]

With Calc as report engine you have a lot more flexibility.
https://forum.openoffice.org/en/forum/v ... 75&t=18511

[Nocton]

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

[longi]

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

[arfgh]

but on that result you got, do you have several tables ? or the trick was just groups ?

[longi]

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!

[arfgh]

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.

[Villeroy]

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.

[longi]

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!

[arfgh]

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.

[Sliderule]

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

[longi]

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!

[arfgh]

Very good longy, and the way to set a macro to run on the hyperlink onClick event ?

[longi]

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!

[arfgh]

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...

[arfgh]

^^ any idea about ?

[Villeroy]

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.

[Villeroy]

Another macro thing that may help: viewtopic.php?f=29&t=81229

[arfgh]

well yes villeroy, but in this case it is more about to determine when the report finish to load data into tables...