Xojo Reports: Query multiple tables in Body-Section (inline Reports)

Databases
1

Hi everyone,

I saw it’s possible to add Group Headers to the Xojo Report Designer. But I don’t know how they work.
I have to test-tables:

  • Animals (Name, Size)
  • Cars (Name, Speed)
    I want to show each row of both tables on one report. First all the Animals, sorted by Name with the Group Header „Animals“ and below analog the Cars, sorted by Name.
    How can I do this, because I have only one Body-Section?
2

You could use a sql-query for that:

SELECT ‘header’ AS type, ‘Name’ AS key, ‘Size’ AS value
UNION
SELECT ‘animal’ as type, Name AS key, Size AS Value FROM Animals ORDER BY Name
UNION
SELECT ‘empty’ AS type, ‘’ AS key, ‘’ AS value
UNION
SELECT ‘header’ AS type, ‘Name’ AS key, ‘Speed’ AS value
UNION
SELECT ‘car’ as type, Name AS key, Speed AS Value FROM Cars ORDER by Name;

3

Thanks Marius,

why do I need the table „empty“ and „header“?

4

to have a break between both tables. In real it is only one…

5

Here you can find my Demo-Project with a Sample SQLite-Database. I don’t get, what I want as result. Maybe someone can look to the Demo please. Is it also possible to have dynamic Group Header Column Headers, depending on the “Header” for Name, Size/Speed? Thanks

Result

My Wish

6

Here it is:

example

Xojo-Project

7

Thanks Marius,

looks good and works fine for me at the moment. And I don’t need a Group Header. If there will be some more questions while doing some experiments on this topic, I will text at this post. :slight_smile:

8

[quote=383842:@Marius Dieter Noetzel]Here it is:

example

Xojo-Project[/quote]
Is it also possible with your variant to insert a column description of the individual tables in the group header, which is then displayed at the top of every new page, just like Group Header?

[code]ANIMALS
NAME SIZE <-- Column-Header
Ameise 1
Elefant 400
Giraffe 600
…

< Pagebreak >

NAME SIZE <-- Column-Header
Hase 50
Schimpanse 80

CARS
NAME SPEED <-- Column-Header
Audi 250
BMW 245
Nissan 225
Peugot 200
[/code]

I must add that this variant, i.e. the use of the UNION command, only works if the SELECT query of the tables has the same number of columns.

9

Not easyly with the pagebreak included.
You have to know how much row are on one page, than you have to build the query in a loop:

dim pagelines as integer = 20
dim itemsInAnimals = <query to db, SELECT COUNT() FROM animals>
dim itemsInCars = <query to db, SELECT COUNT() FROM acrs>
dim sql as string = "SELECT ‘1header’ AS type, ‘Animal’ AS key, ‘’ AS value UNION SELECT ‘2lineheader’ AS type, ‘Name’ AS key, ‘Size’ AS value UNION "
dim tmpindex as integer = 3

for i as integer = 1 to itemsinanimals step pagelines
sql = sql + “SELECT '” + tmpindex.tottext + “animal’ AS type, Name AS key, Size AS Value FROM Animals LIMIT " + pagelines + " UNION SELECT '” + str(tmpindex + 1) + "lineheader’ AS type, ‘Name’ AS key, ‘Size’ AS value UNION "
tmpindex = tmpindex + 2
next

and so on…
not tested…

you have to check, when the tables are changing, to add the amount of the first to the second table…

Marius

10

Thanks for your answer. OK, this is not possible to me, because I don’t know the count of rows of each table. But no problem, I can live with your solution above.

What if one table has 2 columns but the other has 5? The description of the UNION statement states that both tables must have the same number of columns. Is it possible to query a kind of “blank columns” in this case?

11

SELECT COUNT(*) as amount FROM animals
rs.field(“amount”).integervalue

or

Select * FROM animals

and when rs.recordcount

SELECT ‘animal’ AS type, Name AS key, Size AS Value, ‘’ AS blank1, ‘’ AS blank2, ‘’ AS blank3 FROM Animals

12

Nice! Last question to this:

I already described the structure of my two tables above, but there is another column called RecordID (integer).

There is a third table “Records”, with the properties
Price (Double)
Quantity (integer)
Available (Boolean).

Now the SQL query should query the 3 properties of the records table with the e.g. rowid = 1 and output all Animals and Cars with the RecordID 1. So that you can output the data of the record in the PageHeader so to speak and in the body area as already done the Animal and Car tables.

13

Build a second recordset with this data, assign it to the report and assign the data in beforeprint event of each filed

project
db

14

You really made my day. Thank you very much for your help.

I always thought Xojo’s report engine was very limited. Today, however, I learned that much more about Xojo Reports than I ever thought possible (even if there are still no dynamic body bands that adjust their height to the length of the text).