Xojo Reports Bug: ReportFields in Footer Area not working

Sascha_S · June 28, 2018, 9:14am

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

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)

Sascha_S · June 28, 2018, 11:04pm

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

Sascha_S · June 29, 2018, 8:16am

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

Ulrich_Bogun · June 29, 2018, 8:58am

Shouldnt 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?

Sascha_S · June 29, 2018, 9:08am

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.

Ulrich_Bogun · June 29, 2018, 9:12am

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

Sascha_S · June 29, 2018, 10:02am

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”

Ulrich_Bogun · June 29, 2018, 2:53pm

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?

Bogdan_Pavlovic · June 29, 2018, 3:02pm

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.

Bob_Keeney · June 29, 2018, 4:02pm

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.

Sascha_S · June 29, 2018, 4:35pm

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.

A dump of the Database with examples can be downloaded here…

The Database and relationships of the Tables, looking like this:

Sascha_S · June 29, 2018, 4:41pm

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.

Bogdan_Pavlovic · June 29, 2018, 8:03pm

@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…

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

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.

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:

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

Sascha_S · June 30, 2018, 7:46am

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.

Sascha_S · June 30, 2018, 8:42am

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.

Sascha_S · June 30, 2018, 8:47am

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.

AlbertoD · June 30, 2018, 1:44pm

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.

Bogdan_Pavlovic · July 2, 2018, 8:52am

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

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.

Sascha_S · July 2, 2018, 9:33am

Thank you @Bogdan Pavlovic

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

Bogdan_Pavlovic · July 2, 2018, 9:52am

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!