1 Topic by joshuA (edited by joshuA 2021-10-02 00:11:07)

  • Registered: 2019-12-24
  • Posts: 169

Topic: [SOLVED] Report Grouping Totals with NULL Values

Hello all,
.
I'm out of ideas, and I would appreciate any help with this...
.
I believe the problem might be in my SQL, but I've tried everything that I know of.
.
I get strange results in two different months (July, and September). However, things seem to display fine for August. See the example screenshot.
.
At first I was using:
.

SUM(tl.fuelGallons) as "TotalGallons"

.
So then, I suspected a problem with the null values and switched it to this:
.

case when SUM(tl.fuelGallons) IS NULL then 0 else SUM(tl.fuelGallons) end as "TotalGallons"

.
But FastReport seems to be rounding the values. I have the display format set to %2.3n in both cases.
.
I know derek recommends using the standard reports instead of a SQL report, but I haven't found a way to group the values together.  If this is possible, I would prefer it.  I tried replicating it without using SQL, but I can't get the same result.

Post's attachments

Attachment icon fleet-report-01.zip 490.7 kb, 346 downloads since 2021-09-26 

"Energy and persistence conquer all things."

2 Reply by sparrow (edited by sparrow 2021-09-27 10:59:12)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Report Grouping Totals with NULL Values

in some cases, values are passed as text. Calculation and display errors occur.
Try to use :

SUM(CASE WHEN tl.fuelGallons > 0 then tl.fuelGallons else '0,0' end) as "TotalGallons"

or

SUM(IFNULL(tl.fuelGallons,'0,0')) as "TotalGallons"
  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Report Grouping Totals with NULL Values

Very much appreciated sparrow!
.
They both seem to display the correct results.  I wish I could follow what was causing the problem, but that will be for another day.
.
Not sure if one is better than the other, but I will go with the 2nd one for now.

"Energy and persistence conquer all things."

4 Reply by sparrow (edited by sparrow 2021-09-27 13:45:57)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,027

Re: [SOLVED] Report Grouping Totals with NULL Values

During my attempt to rewrite the request, I got an unexpected result. After analysis, I can say: Your troubles are the result of a not very good database structure
and the TRIP and TRIPLEG tables do not match in the STATE (id_state) part. There may be other errors.
In the future, you may face unpredictable calculations.
Create the tables in the correct state with your data.

5 Reply by joshuA (edited by joshuA 2021-09-27 22:23:02)

  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Report Grouping Totals with NULL Values

Okay.  Again, I appreciate you looking at it (even further than my initial question).
.
I'm not sure that I follow your suggestion, but I will re-evaluate the data and the structure.  But once I inserted either of your SQL snippets, everything checked out.
.
Thank you for your input!
.
--- Edit ---
.
I'll try to explain my thought process for the structure as they pertain to your suggestion.
.

the TRIP and TRIPLEG tables do not match in the STATE (id_state) part.

.
This is intentional because the TRIP table has it's own id_state separate from the leg's in the TRIPLEG table, so they would not necessarily match.
.
There is only 1 id_state in the TRIP table (per trip) and this captures the State where the trip begins.
.
All of the States from the id_state in the TRIPLEG table is to capture all of the points where the truck exits a (and crosses into a different) state.
.
--- ASIDE ---
I have tested other designs where the id_state field is only linked to the TRIPLEG table.  But to me, the id_state fields in the TRIPLEG table represent something different from the one in the TRIP table.
.
Namely- those are all exit points, and the one in the TRIP table is the start point.
.
Maybe this isn't necessary, and this is where I lack experience.  However, it is why I chose to store the id_state in two different tables.
.
But the user does need the distinction, and this is one of the ways I could think of to achieve this and not get it confused in the tablegrid with all the (exit point) legs.
---
.
I would appreciate ANY other feedback.

"Energy and persistence conquer all things."

6 Reply by joshuA (edited by joshuA 2021-10-02 00:54:33)

  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Report Grouping Totals with NULL Values

I wanted to share another alternative that also resolves the issue I was having:
.

SUM(COALESCE(tl.fuelGallons,'0,0')) as "TotalGallons"

.
This is basically doing the same thing as your suggestions, but I stumbled upon it in other examples.  Not sure if either of them are better than the other, but maybe someone more experienced in SQLite can answer that one. smile
.
[edit]
So, coalesce is for when you have more than two values.  In my case, it's only necessary for the IFNULL.  :thumbsup:
.
Thank you again sparrow.

"Energy and persistence conquer all things."
  • Registered: 2019-12-24
  • Posts: 169

Re: [SOLVED] Report Grouping Totals with NULL Values

Here lately, I have been experimenting with FastReport a bit further (trying to identify the original problem I was having).  I am convinced that it is a datatype issue.
.
I believe that the data is being stored improperly (maybe somewhere in my code), and not necessarily with the SQL sent to FastReport.  In another quick ad-hoc project, it calculates the real values correctly using SUM().  So this far, I have gotten two different results with real values:

  1. SUM() produces a concatination of the real values (it does this with strings).

  2. SUM() produces a total of the real values (as expected).

In both cases, the datatypes are Real in both MVD and SQlite, and the `DisplayFormat` property in FastReport has no effect either.
.
I made sure (to my best ability) that the datatypes are correct in the code.  Even though MVD will not allow improper datatypes to be stored within the software, but I'm not sure how to verify it in SQLite.  FastReport seems to be treating the real values as strings with concat or something.
.
In this screenshot:

  • The left group - (these values have been clipped) but the integers in the left column are being calculated correctly with SUM(), but the real values in the right column are being concatinated.

  • The right group - the real values also being calculated correctly from the ad-hoc project.

.
This project is a bit large to just scrap and start over, so I'm trying to troubleshoot the problem at this point.
.
Although the IFNULL solution provided by sparrow seems to be working, I would rather identify the problem causing this concatination behavior in my first example if possible...

Post's attachments

Attachment icon screenshot.png 14.15 kb, 174 downloads since 2021-10-03 

"Energy and persistence conquer all things."