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

  1. 5 months ago

    Martin T

    Apr 17 Pre-Release Testers Germany

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

    Apr 21 Pre-Release Testers Herford, Germany

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

    Apr 21 Pre-Release Testers Germany

    Thanks Marius,

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

  4. Marius D

    Apr 21 Pre-Release Testers Herford, Germany

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

  5. Martin T

    Apr 21 Pre-Release Testers Germany

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

    My Wish
    -image-

  6. Marius D

    Apr 21 Pre-Release Testers Herford, Germany
    Edited 5 months ago

    Here it is:

    example

    Xojo-Project

  7. Martin T

    Apr 21 Pre-Release Testers Germany

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

  8. Martin T

    Apr 22 Pre-Release Testers Germany
    Edited 5 months ago

    @Marius Dnbsp;Noetzel Here it is:

    example

    Xojo-Project

    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?

    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

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

    Apr 22 Pre-Release Testers Herford, Germany

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

    Apr 22 Pre-Release Testers Germany

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

    Apr 22 Pre-Release Testers Herford, Germany

    @Martin T I don't know the count of rows of each table

    SELECT COUNT(*) as `amount` FROM `animals`
    rs.field("amount").integervalue

    or

    Select * FROM `animals`

    and when rs.recordcount

    @Martin T Is it possible to query a kind of "blank columns" in this case

    SELECT 'animal' AS `type`, `Name` AS `key`, `Size` AS `Value`, '' AS `blank1`, '' AS `blank2`, '' AS `blank3` FROM `Animals`

  12. Martin T

    Apr 22 Pre-Release Testers Germany

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

    Apr 22 Pre-Release Testers Herford, Germany

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

    Apr 22 Pre-Release Testers Germany
    Edited 5 months ago

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

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

or Sign Up to reply!