asked on

How to Subtract in Data Report

Hello there,
I am using Data Report Designer in Visual Basic 6 and its data are based in my Data Environment.
I just want to ask is how can I subtract the Total Balance of a customer to its Return Amount?
For example:
Date
3/6/2003 1,500.00
3/7/2003 1,000.00
2,500.00
--------
Return
3/8/2003 1,000.00
--------
1,500.00
========
There is no rptFunction for subtraction right? How will I do these in Data Report?
Please help me, thanks very much.

tonydspaniard

OK, this is what i normally do with these issues:

create a query that creates the first DB consultation including the ID of the CUSTOMER... then a second query that includes the first query and calculates the substraction from their return amount, and do not forget to include the CUSTOMER ID in this query too.

now, when you design the report.. you MUST add a group header-footer and include all the fields that you will fill with the SECOND_QUERY (ie, customer details, and in the footer the calculated field). On the fields within the group header and footer just set the DATAFIELD property of the fields not the DATAMEMBER, this last one leave it empty. then, within the details section put the boxes that will hold the fields of the FIRST_QUERY (see below) and set the DATAFIELD as above explained but this time set the DATAMEMBER to the appended query (Final_Balance -see below).

then you just need to create a SHAPE command for a recordset that you will attach to the report's datasource:
'
' missing recordset initialization....
SQL = "SHAPE {SELECT * FROM SECOND_QUERY} AS Customer_Details APPEND ({SELECT * FROM FIRST_QUERY} AS Final_Balance RELATE SECOND_QUERY_ID TO FIRST_QUERY_ID)"

recordset1.source = sql
recordset1.open()
Set datareport.datasource = recordset1
datareport.show
set recordset1 = nothing

i have done this many times and it works perfect.

by the way, sorry for my bad english :)

ASKER CERTIFIED SOLUTION

tonydspaniard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial