# Xojo Reports Bug: ReportFields in Footer Area not working

**URL:** <https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110>\
**Category:** General\
**Created:** [June 28, 2018, 9:14am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110 "2018-06-28T09:14:43Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 28, 2018, 9:14am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/1 "2018-06-28T09:14:43Z")

</div>

I have a SQLite Database which i use as a Source for a Report. Everything is working fine, except with one Field which should show the Grand Total of a Field (Type = Double) called “wert”.  
 ![](http://images.schneppmueller.de/transaktionen_table_schema.png)

Each Value of the “wert” Field is properly displayed in my Report, for each single Record. But at the end of each Page, i would like to show the Grand Total. So i have a 2nd ReportField in the Footer Area of my Report with the SummaryType “Grand Total”. This Field also uses the DataField “wert” but is always empty in my Reports?

`Xojo 2018 1.1
macOS 10.13.5
64bit (same issue when i try it with 32bit Build)`

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 28, 2018, 11:04pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/2 "2018-06-28T23:04:50Z")

</div>

I’ve now created a FR, because i think this is a Bug: \<[https://xojo.com/issue/52605](https://xojo.com/issue/52605)\>

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 29, 2018, 8:16am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/3 "2018-06-29T08:16:17Z")

</div>

Now that the issue has been verified by Xojo Inc. i am asking myself if this has been working in past Xojo Versions or if simply no one is using this “feature”.

Does anybody know of a workaround? Because i can’t afford the awesome [BKeeney Shorts](https://www.bkeeney.com/allproducts/bkeeney-shorts/) Addon. ☹

---

<div class="post-metadata">

**Author:** ![Ulrich\_Bogun](https://forum.xojo.com/user_avatar/forum.xojo.com/ulrich_bogun/32/1864_2.png) [@Ulrich\_Bogun](https://forum.xojo.com/u/Ulrich_Bogun)\
**Post date:** [June 29, 2018, 8:58am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/4 "2018-06-29T08:58:37Z")

</div>

Shouldn’t it be possible to create a sum with the recordset and assign it to a field in the footer?  
Like `rs = db.SQLSelect ("Select *, sum(wert) As Total From Table")` and name the field Total?

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 29, 2018, 9:08am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/5 "2018-06-29T09:08:14Z")

</div>

Thank you @Ulrich Bogun , i thought the same but i am not SQL Master and my attempts failed.

My original Querry looks like this:

`SELECT transaktionen.id, konten.konto, 
transaktionen.konto AS konto_id, 
gruppen.gruppe, 
transaktionen.gruppe AS gruppe_id, 
kategorien.kategorie, 
transaktionen.kategorie AS kategorie_id, 
orte.ort, 
transaktionen.ort AS ort_id, 
personen.person, 
transaktionen.person AS person_id, 
transaktionen.beschreibung, 
transaktionen.zeitpunkt 
FROM transaktionen  
INNER JOIN gruppen ON transaktionen.gruppe = gruppen.id  
INNER JOIN kategorien ON transaktionen.kategorie = kategorien.id  
INNER JOIN konten ON transaktionen.konto = konten.id  
INNER JOIN orte ON transaktionen.ort = orte.id  
INNER JOIN personen ON transaktionen.person = personen.id 
WHERE DATETIME(zeitpunkt) BETWEEN '2018-06-01 00:00:00' AND '2018-06-30 23:59:59' 
AND konto_id=1 
ORDER BY zeitpunkt`

and this leads to the following result(s):

`1, Haushaltsbuch, 1, Familie, 1, Lebensmittel, 1, "Marktstraße", 3, Sascha, 2, PomPort, "2018-06-21 07:32:05"
2, Haushaltsbuch, 1, Familie, 1, Unterhaltung, 5, "Marktstraße", 3, Familie, 3, Kino, "2018-06-26 08:23:10"
3, Haushaltsbuch, 1, Familie, 1, Lebensmittel, 1, "Marktstraße", 3, Familie, 3, Essen, "2018-06-26 08:21:47"
`

so i tried to get a SUM Value by using the following Querry:

`SELECT transaktionen.id, konten.konto, 
transaktionen.konto AS konto_id, 
gruppen.gruppe, 
transaktionen.gruppe AS gruppe_id, 
kategorien.kategorie, 
transaktionen.kategorie AS kategorie_id, 
orte.ort, 
transaktionen.ort AS ort_id, 
personen.person, 
transaktionen.person AS person_id, 
transaktionen.beschreibung, 
transaktionen.wert, SUM(transaktionen.wert), 
transaktionen.zeitpunkt 
FROM transaktionen  
INNER JOIN gruppen ON transaktionen.gruppe = gruppen.id  
INNER JOIN kategorien ON transaktionen.kategorie = kategorien.id  
INNER JOIN konten ON transaktionen.konto = konten.id  
INNER JOIN orte ON transaktionen.ort = orte.id  
INNER JOIN personen ON transaktionen.person = personen.id 
WHERE DATETIME(zeitpunkt) BETWEEN '2018-06-01 00:00:00' AND '2018-06-30 23:59:59' 
AND konto_id=1 
ORDER BY zeitpunkt`

while leads to the following result(s):

`3, Haushaltsbuch, 1, Familie, 1, Lebensmittel, 1, "Marktstraße", 3, Familie, 3, Essen, 21, 41, "2018-06-26 08:21:47"
`

I can’t make my Querry so that i get all 3 entries + a SUM of all 3 “wert” Values within 1 querry.

---

<div class="post-metadata">

**Author:** ![Ulrich\_Bogun](https://forum.xojo.com/user_avatar/forum.xojo.com/ulrich_bogun/32/1864_2.png) [@Ulrich\_Bogun](https://forum.xojo.com/u/Ulrich_Bogun)\
**Post date:** [June 29, 2018, 9:12am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/6 "2018-06-29T09:12:08Z")

</div>

You should be able to do so with a Union I guess:  
[https://xojoblog.me/2018/04/29/gastbeitrag-noch-flexiblere-reports/](https://xojoblog.me/2018/04/29/gastbeitrag-noch-flexiblere-reports/)

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 29, 2018, 10:02am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/7 "2018-06-29T10:02:51Z")

</div>

There must be something i do not understand the way it’s meant, because the “best” i can come up with is:

[code]SELECT transaktionen.id, konten.konto,  
gruppen.gruppe,  
kategorien.kategorie,  
orte.ort,  
personen.person,  
transaktionen.beschreibung,  
transaktionen.wert,  
transaktionen.zeitpunkt,  
‘’ as total  
FROM transaktionen  
INNER JOIN gruppen ON transaktionen.gruppe = gruppen.id  
INNER JOIN kategorien ON transaktionen.kategorie = kategorien.id  
INNER JOIN konten ON transaktionen.konto = konten.id  
INNER JOIN orte ON transaktionen.ort = orte.id  
INNER JOIN personen ON transaktionen.person = personen.id  
WHERE DATETIME(zeitpunkt) BETWEEN ‘2018-06-01 00:00:00’ AND ‘2018-06-30 23:59:59’  
AND transaktionen.konto=1

union

SELECT transaktionen.id, konten.konto AS konto\_id,  
gruppen.gruppe,  
kategorien.kategorie,  
orte.ort,  
personen.person,  
transaktionen.beschreibung,  
transaktionen.wert,  
transaktionen.zeitpunkt,  
SUM(wert) AS total  
FROM transaktionen  
INNER JOIN gruppen ON transaktionen.gruppe = gruppen.id  
INNER JOIN kategorien ON transaktionen.kategorie = kategorien.id  
INNER JOIN konten ON transaktionen.konto = konten.id  
INNER JOIN orte ON transaktionen.ort = orte.id  
INNER JOIN personen ON transaktionen.person = personen.id  
WHERE DATETIME(zeitpunkt) BETWEEN ‘2018-06-01 00:00:00’ AND ‘2018-06-30 23:59:59’  
AND transaktionen.konto=1

ORDER BY zeitpunkt[/code]

which leads to:

`1, Haushaltsbuch, Familie, Lebensmittel, "Marktstrae", Sascha, PomPort, 3.5, "2018-06-21 07:32:05", ""
3, Haushaltsbuch, Familie, Lebensmittel, "Marktstrae", Familie, Essen, 21, "2018-06-26 08:21:47", 41
3, Haushaltsbuch, Familie, Lebensmittel, "Marktstrae", Familie, Essen, 21, "2018-06-26 08:21:47", ""
2, Haushaltsbuch, Familie, Unterhaltung, "Marktstrae", Familie, Kino, 16.5, "2018-06-26 08:23:10", ""
`

You see, i now have the SUM of wert, but only as a duplicate of the row with the highest id and even while i could “fix” such a result with Xojo Code, i do not know how fix it for a Xojo Report.

BTW: Somehow this querry looks terribly “suboptimal”

---

<div class="post-metadata">

**Author:** ![Ulrich\_Bogun](https://forum.xojo.com/user_avatar/forum.xojo.com/ulrich_bogun/32/1864_2.png) [@Ulrich\_Bogun](https://forum.xojo.com/u/Ulrich_Bogun)\
**Post date:** [June 29, 2018, 2:53pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/8 "2018-06-29T14:53:42Z")

</div>

It must be something in your code: The demo project “Orders by Company” runs a subtotal and a grand total without flaws.  
I wonder if the second query must be that complicated. What would be the easiest query to get the total you want? Do you really have to join in all the other tables?

---

<div class="post-metadata">

**Author:** ![Bogdan\_Pavlovic](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/ba9def/32.png) [@Bogdan\_Pavlovic](https://forum.xojo.com/u/Bogdan_Pavlovic)\
**Post date:** [June 29, 2018, 3:02pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/9 "2018-06-29T15:02:37Z")

</div>

keep in mind that when use SUM() also sometimes you should also use GROUP BY.  
Also beside INNER JOIN you can also use LEFT and RIGHT JOIN as well.  
It would be a better if you can post structure of tables and define goal so others can see or post ideas to you… something like sql export of db structure with some foo data in it would be nice.

Hope you don’t mind on this.

---

<div class="post-metadata">

**Author:** ![Bob\_Keeney](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/54ee81/32.png) [@Bob\_Keeney](https://forum.xojo.com/u/Bob_Keeney)\
**Post date:** [June 29, 2018, 4:02pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/10 "2018-06-29T16:02:04Z")

</div>

One thing you might want to try is using a View if you have a number of joins that are making the SQL difficult. Let the database do the heavy lifting of gathering the data and the query from Xojo should be relatively simple.

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 29, 2018, 4:35pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/11 "2018-06-29T16:35:01Z")

</div>

> [@394084:@Bogdan Pavlovic](#):
>
> It would be a better if you can post structure of tables and define goal so others can see or post ideas to you… something like sql export of db structure with some foo data in it would be nice.

This is for a Housekeeping Book and i would like to create Reports of a Specific Account (“konto”) and a specific timespan, which shows the konto, the group, the person, the kategorie, the ort and the value. Ordered by Date. And at the end of each Page in the Report, the total Amount of the wert Entries, should be displayed.

![](http://images.schneppmueller.de/transaktionen_report.png)

[A dump of the Database with examples can be downloaded here…](http://images.schneppmueller.de/housekeepingbook.sql)

The Database and relationships of the Tables, looking like this:  
 ![](http://images.schneppmueller.de/transaktionen_db_image.png)

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 29, 2018, 4:41pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/12 "2018-06-29T16:41:03Z")

</div>

> [@394104:@Bob Keeney](#):
>
> One thing you might want to try is using a View if you have a number of joins that are making the SQL difficult. Let the database do the heavy lifting of gathering the data and the query from Xojo should be relatively simple.

I never worked with Views before but i am familiar with the concept and like your idea. I have to leave my computer in a few minutes for today and will start to dig into docs regarding SQLite Views tomorrow. Thank you all. 🙂

---

<div class="post-metadata">

**Author:** ![Bogdan\_Pavlovic](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/ba9def/32.png) [@Bogdan\_Pavlovic](https://forum.xojo.com/u/Bogdan_Pavlovic)\
**Post date:** [June 29, 2018, 8:03pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/14 "2018-06-29T20:03:11Z")

</div>

@Sascha S  
Here you go first query which bind all child tables to main table (transaktionen’ table and pulls all rows from it and attach other field values IF THEY exists in other tables such as gruppen, personen, orte, konten, kategorien…

```auto
SELECT
    `transaktionen`.*
    , `kategorien`.`kategorie`
    , `konten`.`konto`
    , `konten`.`budget`
    , `konten`.`uebernehmen`
    , `orte`.`ort`
    , `personen`.`person`
    , `gruppen`.`gruppe`
FROM
    `transaktionen`
    LEFT JOIN `kategorien` 
        ON (`transaktionen`.`kategorie` = `kategorien`.`id`)
    LEFT JOIN `konten` 
        ON (`transaktionen`.`konto` = `konten`.`id`)
    LEFT JOIN `orte` 
        ON (`transaktionen`.`ort` = `orte`.`id`)
    LEFT JOIN `personen` 
        ON (`transaktionen`.`person` = `personen`.`id`)
    LEFT JOIN `gruppen` 
        ON (`transaktionen`.`gruppe` = `gruppen`.`id`);
```

At the end of it you can add WHERE and OREDER BY part to make query to fit your spec. needs to pull all rows match criteria.

Please keep in mind that I don’t speak German lang.

To sort all results by konten and by zeitpunkt it would be something like to add on above query

```auto
ORDER BY `konten`.`uebernehmen` ASC, `transaktionen`.`zeitpunkt` ASC;
```

To get sum/total of records using above sql as start point SUM query would be something like bellow where data would be grouped by `transaktionen`.`konto` and `transaktionen`.`kategorie` and where SUM would apply to table field wert and will be named as totalwert.

```auto
SELECT
    `transaktionen`.`konto`
    , SUM(`transaktionen`.`wert`) as totalwert
    , `transaktionen`.`kategorie`
FROM
    `transaktionen`
    LEFT JOIN `kategorien` 
        ON (`transaktionen`.`kategorie` = `kategorien`.`id`)
    LEFT JOIN `konten` 
        ON (`transaktionen`.`konto` = `konten`.`id`)
    LEFT JOIN `orte` 
        ON (`transaktionen`.`ort` = `orte`.`id`)
    LEFT JOIN `personen` 
        ON (`transaktionen`.`person` = `personen`.`id`)
    LEFT JOIN `gruppen` 
        ON (`transaktionen`.`gruppe` = `gruppen`.`id`)
GROUP BY `transaktionen`.`konto`, `transaktionen`.`kategorie`;
```

Above query you can downstream to more simple query if you don’t want to describe data (by pulling relationships between tables in dbms) and get only totals/sums.

Example of sql would be like bellow:

```auto
SELECT
    `transaktionen`.`konto`
    , SUM(`transaktionen`.`wert`)
    , `transaktionen`.`kategorie`
FROM
    `transaktionen`
GROUP BY `transaktionen`.`konto`, `transaktionen`.`kategorie`;
```

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 30, 2018, 7:46am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/15 "2018-06-30T07:46:46Z")

</div>

> [@393987:@Sascha S](#):
>
> I’ve now created a FR, because i think this is a Bug: \<[https://xojo.com/issue/52605](https://xojo.com/issue/52605)\>

Xojo has confirmed the Bug and i hope it can be fixed soon, because all my attempts to create a workaround failed so far. I think i will pause now for the weekend and hope i can make a new attempt next week.

Thank you all for your help, i highly appreciate it. 🙂

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 30, 2018, 8:42am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/16 "2018-06-30T08:42:45Z")

</div>

Xojo Reports is driving me crazy…  
While i now was able to create a querry which will add a total of all wert values to each row, the Report is still giving a 0, but only in the footer area. If i put a ReportField into the body area and assing the total value to this field, the value shows up correctly. ☹

![](http://images.schneppmueller.de/transaktionen_db_working_querry.png)

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [June 30, 2018, 8:47am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/17 "2018-06-30T08:47:29Z")

</div>

It looks like ReportFields in the Footer Area are completely broken and always deliver an empty Field Value. Because, if i put the ReportField into the Header Area (where i do not need it/it makes no sense in my Report…) it works as expected.

☹

---

<div class="post-metadata">

**Author:** ![AlbertoD](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/dbc845/32.png) [@AlbertoD](https://forum.xojo.com/u/AlbertoD)\
**Post date:** [June 30, 2018, 1:44pm UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/18 "2018-06-30T13:44:51Z")

</div>

I haven’t use the report section yet. I’m sorry that this is happening to you but I thank you for finding the problems with it. I’m sure it will help people when they use the reports.

---

<div class="post-metadata">

**Author:** ![Bogdan\_Pavlovic](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/ba9def/32.png) [@Bogdan\_Pavlovic](https://forum.xojo.com/u/Bogdan_Pavlovic)\
**Post date:** [July 2, 2018, 8:52am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/19 "2018-07-02T08:52:04Z")

</div>

One more thing regarding to Xojo and reporting.

Did you look their example (sample) which comes with Xojo?

‘Examples \> Printing and Reporting \> Reporting \> Orders 1’ and  
‘Examples \> Printing and Reporting \> Reporting \> Orders 2’

?

Bellow you have download url of example where I add sum field at the end of report which isn’t present by default in first Order 1 example.

[https://drive.google.com/open?id=1iWoLGgQFYXXS4fbvwZM0WBWwzCVHu1ah](https://drive.google.com/open?id=1iWoLGgQFYXXS4fbvwZM0WBWwzCVHu1ah)

This should solve you beginning needs for this. Also you can use page header/footer on same way I think. Didn’t have much time to see/track down that as well since I noticed that Xojo 2017r3 and 2018r1 are crushing like crazy on Windows x64.

---

<div class="post-metadata">

**Author:** ![Sascha\_S](https://forum.xojo.com/user_avatar/forum.xojo.com/sascha_s/32/22328_2.png) [@Sascha\_S](https://forum.xojo.com/u/Sascha_S)\
**Post date:** [July 2, 2018, 9:33am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/20 "2018-07-02T09:33:12Z")

</div>

Thank you @Bogdan Pavlovic

The workaround using Groups is smart and could work for my Report needs. 🙂

---

<div class="post-metadata">

**Author:** ![Bogdan\_Pavlovic](https://forum.xojo.com/letter_avatar_proxy/v4/letter/b/ba9def/32.png) [@Bogdan\_Pavlovic](https://forum.xojo.com/u/Bogdan_Pavlovic)\
**Post date:** [July 2, 2018, 9:52am UTC](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110/21 "2018-07-02T09:52:54Z")

</div>

Now you can combine above sql part from my end and this with report part and get total solution of your idea and problem solving.  
Cheers!

[Next page](https://forum.xojo.com/t/xojo-reports-bug-reportfields-in-footer-area-not-working/44110.md?page=2)