show total in the last row of the masterdata
Good morning,
i'm trying to solve this problem but i can't find a way.
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.
i use the groupheader just to group the clients but the total i just can not put at the row the way i want.
this is the way i need... (i hope you can understand)
Ex.:
CLIENT_________PRODUCT___1?? PAY_________COMIS____RECEIVED
JOAO DA SILVA__PROD_1____R$ 1.000,00_____R$ 80,00__R$ 1.080,00
JOSE PEREIRA___PROD_1____R$ 1.500,00_____R$ 120,00
_______________PROD_2____R$ 1.200,00_____R$ 96,00
_______________PROD_3____R$ _800,00_____R$ 64,00____R$ 3.780,00
TI??O MACAL??___PROD_1_____R$ 1.300,00____R$ 104,00
______________PROD_2_____R$ 1.700,00____R$ 136,00___R$ 3.240,00
TOTAL OF PAYMENTS________R$ 7.500,00_____R$ 600,00___R$ 8.100,00
Felipe.
i'm trying to solve this problem but i can't find a way.
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.
i use the groupheader just to group the clients but the total i just can not put at the row the way i want.
this is the way i need... (i hope you can understand)
Ex.:
CLIENT_________PRODUCT___1?? PAY_________COMIS____RECEIVED
JOAO DA SILVA__PROD_1____R$ 1.000,00_____R$ 80,00__R$ 1.080,00
JOSE PEREIRA___PROD_1____R$ 1.500,00_____R$ 120,00
_______________PROD_2____R$ 1.200,00_____R$ 96,00
_______________PROD_3____R$ _800,00_____R$ 64,00____R$ 3.780,00
TI??O MACAL??___PROD_1_____R$ 1.300,00____R$ 104,00
______________PROD_2_____R$ 1.700,00____R$ 136,00___R$ 3.240,00
TOTAL OF PAYMENTS________R$ 7.500,00_____R$ 600,00___R$ 8.100,00
Felipe.
Comments
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)
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...
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.
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..
(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
In the masterband obp put In the masterdata band put a memo with the contents 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...
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
This works perfect.
Tanks.