show total in the last row of the masterdata

Comments

• collectivefriction

  March 2013 edited 10:45AM

  I did something similar and created an sql query which listed my payments and totaled my payments (it wasn't paymenst in my case - but the same idea) and included a group by clause the the sql query. I then added a new summary band after the masterdata and displayed the summary fields on that band.
  
  I am no sql guru but you could possibly do query such as
  
  select client, product, pay, comis, received, sum(pay), sum(comis), sum(received) from table group by client, product
  
  then your master data woudl contain client, product, pay, comis, received,
  
  and your summary band would contain sum(pay), sum(comis), sum(received)
• Felipe Pucci

  March 2013 edited 10:45AM

  ok.. but at realy my problem is not the sum... but where i must display it...
  
  the total values are simple to do.. but i have to show it at the last row of the masterdata... for each client
  
  just like this...
  
  [line 1] - JHONNY pay $1000 + $100
  [line 2] - JHONNY pay $1500 + $150
  [line 3] - JHONNY pay $1200 + $120 = [I have to show this total value only here at the master data row]
  next cliente i do the same thing....
  
  with the masterdata the total value is displayed on every row or only at the first row.. but i have to show it at the last row...
  
  i tryed crossdata but it doesn't word the way i need it made the sum for each column...
• technisoft

  March 2013 edited March 2013

  Create a variable Balance
  In the group header of client you set Balance to 0
  In the before print event of masterdata you add the current transaction value to Balance
  
  That is the easy part. The more difficult one is how can the masterdata know it is the last band before it prints?
  A subquery should be able to deliver that information.
  
  SELCT A.*
  , (SELECT MAX( B.ID ) FROM TRANSACTION B [WHERE clause][ORDER clause] ) AS MAX_ID
  FROM TRANSACTION A
  [WHERE clause]
  [ORDER clause]
  
  Both WHERE and ORDER clauses must be identical
  
  In the report you can then test for
  frxDataset."A.ID" = frxDataset."MAX_ID"
  and manage the visibility of the Balance field. If equal show the field, otherwise hide.
• Felipe Pucci

  March 2013 edited 10:45AM

  ok... it's perfect...
  
  i made a (select count(*).....) in the query and get the total records from each client...
  
  on the onbeforeprint i put a variable count and campare it to the total count select...
  
  great..
  
  thanxs..
• hsm

  March 2013 edited March 2013

  Felipe Pucci wrote: »

  ok.. but at realy my problem is not the sum... but where i must display it...
  
  the total values are simple to do.. but i have to show it at the last row of the masterdata... for each client
  
  just like this...
  
  [line 1] - JHONNY pay $1000 + $100
  [line 2] - JHONNY pay $1500 + $150
  [line 3] - JHONNY pay $1200 + $120 = [I have to show this total value only here at the master data row]
  next cliente i do the same thing....
  
  with the masterdata the total value is displayed on every row or only at the first row.. but i have to show it at the last row...
  
  i tryed crossdata but it doesn't word the way i need it made the sum for each column...

  Add a line to your sql to see how many payments each client has made like this
  
  (select count(PaymentID) from TablePayments as T where T.ClientID = TablePayments.ClientID) as NumberOfPaymentsThisClient,
  
  Then Add a variable to the report's code eg
  var
  MasterLineNumber : integer;
  
  In the Client header obp put

  MasterlineNumber := 0;

  
  In the masterband obp put

  MasterLineNumber := MasterLineNumber + 1;

  In the masterdata band put a memo with the contents

  [IIF(<MasterLineNumber> = <[frxDBDataset1."NumberOfPaymentsThisClient"], frxDBDataset1."Sum",'')]

  so it shows the sum if it's the last line for that client or nothing if its not
• Felipe Pucci

  March 2013 edited 10:45AM

  hsm wrote: »

  hsm wrote: »

  ok.. but at realy my problem is not the sum... but where i must display it...
  
  the total values are simple to do.. but i have to show it at the last row of the masterdata... for each client
  
  just like this...
  
  [line 1] - JHONNY pay $1000 + $100
  [line 2] - JHONNY pay $1500 + $150
  [line 3] - JHONNY pay $1200 + $120 = [I have to show this total value only here at the master data row]
  next cliente i do the same thing....
  
  with the masterdata the total value is displayed on every row or only at the first row.. but i have to show it at the last row...
  
  i tryed crossdata but it doesn't word the way i need it made the sum for each column...

  Add a line to your sql to see how many payments each client has made like this
  
  (select count(PaymentID) from TablePayments as T where T.ClientID = TablePayments.ClientID) as NumberOfPaymentsThisClient,
  
  Then Add a variable to the report's code eg
  var
  MasterLineNumber : integer;
  
  In the Client header obp put

  MasterlineNumber := 0;

  
  In the masterband obp put

  MasterLineNumber := MasterLineNumber + 1;

  In the masterdata band put a memo with the contents

  [IIF(<MasterLineNumber> = <[frxDBDataset1."NumberOfPaymentsThisClient"], frxDBDataset1."Sum",'')]

  so it shows the sum if it's the last line for that client or nothing if its not

  
  
  
  this is what i did... it's perfect... thanxs...
• Petter S.

  March 2013 edited 10:45AM

  Felipe Pucci wrote: »

  i have to do a list of payments with many clients but i have to put the total value received at the last row of the client, but
  i can't use the group footer because it adds one more line.
  i just want to show the total value at the last line of the client in the masterdata.

  
  Actually, as an alternative, you could have solved this by using a group footer:
  
  1. Add a group footer 'MyFooter', and a summary in a memo 'MyMemo' inside the group footer. Ajust the memo horizontally correct.
  2. Add code in main code area of the report:
  
  MyFooter.height := 0;
  MyMemo.top := 0 - MyDetailband.height + MyDetailMemo.top; // or something like this to align vertically correct with your details
  
  
  Petter
  
  
• Dalio

  February 2017 edited 10:45AM

  Very Good.
  
  This works perfect.
  
  Tanks.