[SOLVED]Using SUM function with variables

[Guylain]

I have a report fetching values from a stringgrid using the OnGetValue event. The report has a header, one MasterData and a Group.
I use variables declared in report, and values from the stringgrid appear for each row.

So far so good, but the SUM function with those variables in the GroupFooter shows nothing. The Text Memo looks like this:  [[SUM([InvoiceAmount])]. Of course, the MasterData band is linked to a frUserDataSet.

I use SUM function usually with database fields with no problem. My question is: Is the SUM function working only with fields?

[korba812]

Generally aggregate functions should work with TfrUserDataset. Do you have all 3 events implemented (OnCheckEOF, OnFirst, OnLast)?

Also try with the extra parameter with band name:
[SUM([InvoiceAmount], 'MasterData1')]

[Guylain]

Yes, these 3 events are as usual for a StringGrid:
EOF->   Eof := FRow = (Form1.SG_Data.RowCount-1);
First: FRow := 1
Next: FRow := Inc(FRow)
My StringGrid has 1 fixed row.

[korba812]

How do you get value from TStringGrid? Do you convert string to number or just pass string? Can you show OnGetValue implementation?

[Guylain]

I include some picture explaining the process.
1. The data is fetched from some DBF files into the stringgrid. I use FloatToStrF function to format numeric values.
2. My system works with the ',' decimal separator, so I tried to change to '.' with same result.
3. The OnGetValue function uses declared report variables to obtain values. I use a Case statement.
4. The report shows very well the row values, but not the totals

[dseligo]

When you assign values you want to use in 'SUM', then do something like this in OnGetValue:

Code: Pascal  [Select][+][-]

1. var myNum: Double;
2. begin
3.   ...
4.   myNum := WhateverYouUseToGetValueFromStringList;
5.   ParValue := myNum;

That way you will be sure that you are providing number and not string.

[Guylain]

I tried with Val function, wich gives me a error code on the decimal separator. I change the cell content with a '.' instead of ','. This time Val is Ok, but still nothing with SUM.

I changed my program default decimal separator to '.'
Now the cells show numbers with '.' automatically. Even then the report shows row values with  the ',' decimal separator. And of course, the SUM gives '0'

I'll try to find if LazReport has his own decimal separator...
Edit: The code in LazReport handles both separator, using tryStrToFloat with '.' first and and ',' if unsuccessful. So the problem is somewhere else.
Edit:
I changed my computer regional parameters to use '.' instead of ',' as decimal separator: reports shows once again rows values with ',' and SUM=0.
I changed the format of both row values and total in LazaReport Memos to use ',': now I see '.' in rows values, but Sum=0

[korba812]

You should convert string to a number in the OnGetValue handler. Don't let LazReport do this. I am sending a sample project.

[Guylain]

I already tried something like yours with no success. Val function gives a double value as you suggested in preceding post.
Begin
   Cells[22,FRow] := StringReplace(Cells[22 ,FRow], ',', '.', []);
   Val(Cells[22 ,FRow], DblValue, Code);
   If Code = 0 Then ParValue := DblValue
                     Else ParValue := 0;
 end;

and your more robust solution, and each time with same result.
By the way, I can't run your sample project. Lazarus tells me there is no source code. And no form appears in the IDE. I use Lazarus 2.0.0

[korba812]

I'm using version 2.2. Create a new project and replace unit1.pas and unit1.lfm files from my project. It should work.

[Guylain]

[SOLVED]As I was not able to compile your project, I put Lazarus 2.2.0 on another computer and now i can see your project, which works without a itch.

I see you don't use any memo format in lazreport. Also, your variables are not defined in Lazreport variables.

So I tried to mimic your setup in my regular installation (Lazarus 2.0.0) (no variables, no formats, function dealing with decimal separator), and guest what? Still same problem.

Next step: migrate my program to LAZARUS 2.2.0 (lot of packages to install...). Be back with follow up soon.

2022/03/31: Migration completed. No modifications needed. The report still doesn't calculate SUM.

The strange thing is other report in the same application can calculate SUM, with the same type of report: Report title, Group Header, Master Data, Group Footer and Report summary, no variables.

So it's obviously something in my code.
2022/04/01: I found it. In my «OnGetValue» procedure, i use a «Case» statement to fetch value from the stringgrid to report.

As I experienced program exit without notice coming from this procedure, I put a «Else» statement at the end of case («Else ParValue := '0'»), which prevents the program to exit.

This is why the SUM formulas in my report were not calculated. I suppose LazReport parser calculates formulas when no variable ou field is present. With the «Else» statement, I bypass this process.

Now, my SUM works like it was supposed to be. But... only for some records in the stringgrid.

Effectively, if I ask for a list of all transactions for my report, the program hangs somewhere, surely because a value in the stringgrid is not compatible with numeric or boolean. The date values are in string format («2022/03/05»), and I'm pretty sure strings values are not a problem. So I will checks and corrects numeric and boolean values in the stringgrid before transferring them to report with the ParValue statement.