Conditional Sum

Comments

• chymerra

  August 2008 edited 6:22PM

  I've tried this:
  
  [IIF ([<frOfficeIncome."clipaymethod">=0], SUM(<frOfficeIncome."clipayrec">,MasterData1,2),0)]
  
  but this does not work, i get an Invalid Variant Operation
  
  also tried like this:
  [IIF ([<frOfficeIncome."clipaymethod">=0], SUM(<frOfficeIncome."clipayrec">,MasterData1,1),0)]
  
  and like this:
  
  [IIF ([<frOfficeIncome."clipaymethod">=0], SUM(<frOfficeIncome."clipayrec">,MasterData1),0)]
  
• gordk St.Catherines On. Canada.

  August 2008 edited 6:22PM

  create 2 typed variables on the code page
  Var
  totpaytype1,totpaytype2: extended;
  in the empty block initialize the var to 0.00
  begin
  totpaytype1 := 0.00;
  totpaytype2:= 0.00;
  end.
  in the obp event of the databand writ code to add the value of the datfield to the variable.
  begin
  if <datsetname."paytype"> = 1 then totpaytype1 := totpaytype1 + <datasetname."payrecieved'>;
  if <datsetname."paytype"> = 2 then totpaytype2 := totpaytype2 + <datasetname."payrecieved'>;
  end;
  you can then display the variables in any textobject using [ ] around the variablename.
• chymerra

  August 2008 edited 6:22PM

  Thank you very much,
  
  I was just wondering what you think is more efficient method to do these calculations a) the report level or [img]style_emoticons/<#EMO_DIR#>/cool.gif" style="vertical-align:middle" emoid="B)" border="0" alt="cool.gif" /> at the sql level? meaning if i change the query to add the following: select client_name, case when paytype = 1 then payrecieved else 0 as paytype1, case when paytype = 2 then payrecieved else 0 as paytype2, ... Just wondering, but thank you again for the swift response it's much appreciated.[/img]
• gordk St.Catherines On. Canada.

  August 2008 edited 6:22PM

  i would probable use an entirely different way.
  first just a standard select query to display the data records then in a in the footer or summary band
  use a dbcrosstab to do your grouping using the same query.