1 Topic by joshuA (edited by joshuA 2021-10-07 00:17:08)

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

Topic: [SOLVED] SUM concatination

Hello MVD fans,
.
I pulled this from an older project, but I added a sample report to demonstrate the issue that I've been trying to solve recently.  IMHO this project has a pretty solid database structure and schema with enough sample data to show my problem (again).
.
I'm also calling the standard report from MVD without using SQL, but I still have the same issue described from one of my other topics: [SOLVED] Report Grouping Totals with NULL Values
.
Everything works prefectly except for (this time integer) values being treated as strings (which I believe is) causing this concatination behavior.  It was the Real values in my other post.  I can't seem to pin down what I'm doing wrong to make this happen?
.
<see the screenshot>
.
I would appreciate any help or ideas.

Post's attachments

Attachment icon fleet-v2_2021-10-05.zip 339.48 kb, 334 downloads since 2021-10-06 

"Energy and persistence conquer all things."

2 Reply by sparrow (edited by sparrow 2021-10-06 11:33:23)

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

Re: [SOLVED] SUM concatination

Hi
These NULLs cause this result 

name          OD         start     finish      diff
Kelon Decee    637381    637381    NULL    NULL
Kelon Decee    637500    637500    637381    119
Kelon Decee    637782    637782    637500    282
Noren Boley    635729    635729    NULL    NULL
Noren Boley    635917    635917    635729    188
Noren Boley    636129    636129    635917    212

please modify CALCULATED FIELD  cfLegMileage

IFNULL((select max(x.od) from bleg x where x.od = bleg.od and x.id_atrip = atrip.id)  
-
(select max(x.od) from bleg x where x.od < bleg.od and x.id_atrip = atrip.id),0)

and simplify , works faster 

IFNULL((SELECT bLeg.OD - max(b.OD) FROM bLeg b WHERE bLeg.id_aTrip = b.id_aTrip and b.OD  < bLeg.OD),0)

AFTER

name            OD        start    finish    diff res
Kelon Decee    637381    637381    NULL    0
Kelon Decee    637500    637500    637381    119
Kelon Decee    637782    637782    637500    282
Noren Boley    635729    635729    NULL    0
Noren Boley    635917    635917    635729    188
Noren Boley    636129    636129    635917    212

You need to be careful when you create your databases and queries to it.

If you create related columns in your tables, then do not forget to fill them in these tables.
If you refer to a table in a query and mention a related column in another table,
do not forget about the JOIN (INNER or LEFT) of the other table ON related column.
If you have created a column, think about whether to put a default value (zero or something else).
I drew your attention to these things in the previous topic.


Yes, your project has gone relatively far, but you need to strive to simplify and optimize databases, tables, queries.


To test the correctness of your program, create yourself a database with the most correct data.
Otherwise, you will end up running in circles between your code and the database.


Strive for your program to fill in all the necessary data in the tables and then work with the output forms.
Create a form for yourself with one table and display the data of the tables in it one by one to control the correctness of filling.
To work with the database, you can use SQLlite administration programs.


Everything else works correctly in an MVD program (almost smile).


Sorry. Translate by Google.

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

Re: [SOLVED] SUM concatination

Possible solution via FastReport script.
The solution is to replace NULL with '0'.
Slightly more complicated than I suggested above.

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

Re: [SOLVED] SUM concatination

Hi sparrow,
.
Thank you for reviewing this issue and I appreciate your patience answer this question (again).  It seems that it's coming from the same error in the beginning: with the null values which are missing from the table during input.
.
It is very much appreciated!  This makes sense, and I will pay more attention to this in future projects now.
.
As for your second post, I answered another user here (perhaps too late) that was asking how to do that very thing.  At the time, I wasn't sure... but now I am running into the same problem.  LOL  Apparently they were able to identify this at an earlier stage than myself.
.
Hats off to you.

"Energy and persistence conquer all things."

5 Reply by sparrow (edited by sparrow 2021-10-07 07:56:46)

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

Re: [SOLVED] SUM concatination

joshuA wrote:

I answered another user here (perhaps too late) that was asking how to do that very thing.

Hi, smile

Yes, your approach is correct. But there is a difference between your case and this one. In your case, this is not enough, since the SUM will not work.
Create event in TextObject OnBeforePrint
For your case in the script 

var
 leg_M: integer;    

procedure ReportbLegcfLegMileageOnBeforePrint(Sender: TfrxComponent);                       
begin           
  if not ValidInt(<Report."bLeg.cfLegMileage">) then                                   
    leg_M := 0 else leg_M := StrToInt(<Report."bLeg.cfLegMileage">);                                
end;

begin

end.

in MasterData for TextObject instead of [Report."bLeg.cfLegMileage"] write your variable [<leg_M>].
in GroupFooter1 for TextObject instead of [SUM(<Report."bLeg.cfLegMileage">,MasterData1)] write [SUM(<leg_M>,MasterData1)].
in GroupFooter2 for TextObject instead of [SUM(<Report."bLeg.cfLegMileage">,MasterData1)] write [SUM(<leg_M>,MasterData1)].


Lets you revert to a numeric representation (integer in this case)
It is easier to write IFNULL in the request.

That's all


P.S. For 7410_project and your project.
       If you do not want to display 0. You can enable in TextObject properties - HideZeros: True and the field remains blank.

6 Reply by joshuA (edited by joshuA 2021-10-09 01:59:46)

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

Re: [SOLVED] SUM concatination

Wow sparrow, thanks for pointing out those details.  I had not looked into it far enough to determine that.  I didn't intend for you to make a comparison with the two hmm
.
I appreciate all your help even though I don't follow everything in your last post.  Your previous solutions seem to have fixed the problems that I was having, but I'm bookmarking this if I notice something else down the road.

"Energy and persistence conquer all things."