• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3040
  • Last Modified:

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.
0
melch
Asked:
melch
  • 2
1 Solution
 
tonydspaniardCommented:
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 :)
0
 
tonydspaniardCommented:
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 :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now