Group Aggregate Issues

Monte Carver

FR Gurus.

To determine the MAX or MIN within a GroupFooter, I get the expected results as an example from:

[MIN(<dsPANEL_DATA."TS.SUPPLY_AIR_TEMP">,MasterData1)]

HOWEVER, if I try to return the SUM or AVG the value is incorrect. What am I doing wrong? The AVG function is of course:

[AVG(<dsPANEL_DATA."TS.SUPPLY_AIR_TEMP">,MasterData1)]

I also find it interesting that the COUNT function returns the expected number of records.

Please advise.

Regards,
Monte Carver

gordk

use the aggregate function builder and set the correct flag for the aggregate expression.
see the user manual chapter on groups, aggregates.
BTW when posting about a possible bug please state what version and build of fr you are using

Monte Carver

Which is exactly what I did. This syntax is a copy from the Aggregate builder

[AVG(<dsPANEL_DATA."TS.VFD_CURR_SPEED">,MasterData1)] and of course SUM is
[SUM(<dsPANEL_DATA."TS.VFD_CURR_SPEED">,MasterData1)] and of course SUM is

As perhaps a point of reference, the COUNT returns 17 records, which is correct. Averaging a particular column that contains the value of "80" for every row produces the following values:
Expected Produced
MAX = 80.0 80
MIN = 80.0 80
AVG = 80.0 4.7534165181224 E32
SUM = 1360 8080808080808080808080808080808080
COUNT = 17

Clearly something is ... umm wrong !

Again... Your thoughts on this subject are appreciated.

Version info: FR 4.10.1

Regards,
Monte Carver

gordk

I don't see any flag set in the sum function
[SUM(<dsPANEL_DATA."TS.VFD_CURR_SPEED">,MasterData1)]
should have a flag set after bandname, flag

Monte Carver

Changing the syntax to include a Flag of "1" to read as follows

[AVG(<dsPANEL_DATA."TS.VFD_CURR_SPEED">,MasterData1, 1)]

or

[SUM(<dsPANEL_DATA."TS.VFD_CURR_SPEED">,MasterData1, 1)]

Has no obvious impact on the results

gordk

you will need it for the count function
sample using nested group report of main demo
band8 memo15
note set displayformat to text and use inline formatting
for eachline in the memo that needs it.
Total this order: [Sum(<Sales."Qty">*<Sales."List Price">) #n%2,2m]
items [COUNT(Band6,1)]
[MAX(<Sales."List Price">,Band6) #n%2,2m]
[MIN(<Sales."List Price">,Band6) #n%2,2m]
[AVG(<Sales."List Price">,Band6) #n%2,2m]

Monte Carver

As I expected, adding the formatting does not fix the problem.

Could my problem be from trying to summarize a varchar data type. This data is coming from a SQL Server Pivot query (procedure). This query returns about 50 columns. Although most of them contain numeric data the data types are VarChar because some of the data contains text.

Best I can tell, the report is configured correctly. It simply can't digest the data retrieved from the given columns. Am I trying to achieve what Fast Reports can't do? Perhaps using a CrossTab report instead or even FastCube?

Thoughts?

Regards,
Monte Carver

Monte Carver

I confirmed that the VarChar columns was the issue. There is likely a work around within FR to handle this issue. But the "text" within the columns were the exceptions as opposed to the rule.

SO... problem solved. Thanks for the help.

Regards,
Monte Carver

Mick.pl

I understand that nobody wants to show detalis of one's job and whole report but it is so difficult to give a good advice in more complicated cases without seeing the query :-(

In my opinion, just now you've told about root of the problem with agregate functions.
I mean contents of your query with mixed data types.

If it is possible try to simulate a similar situation with FastReport demo data and include here a PDF/JPG file to better show the problem. Sure - it may be difficult as demo database may not offer as much syntax as the database you use while developing the report. If others see the whole example then it will be easier to look for the solution :-)

You can also try my idea with DBCross I wrote about earlier in another topic that you had started.

Mick

Mick.pl

I see I was few minutes late :-(
I was writing my post too long, and while I was writng you found the solution :-)

Mick