Group Aggregate Issues

Comments

• gordk St.Catherines On. Canada.

  February 2011 edited 7:21PM

  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

  February 2011 edited February 2011

  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 St.Catherines On. Canada.

  February 2011 edited 7:21PM

  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

  February 2011 edited 7:21PM

  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 St.Catherines On. Canada.

  February 2011 edited 7:21PM

  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

  February 2011 edited 7:21PM

  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

  February 2011 edited 7:21PM

  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

  February 2011 edited 7:21PM

  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

  February 2011 edited 7:21PM

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