?
Solved

How to Subtract in Data Report

Posted on 2003-03-06
4
Medium Priority
?
3,023 Views
Last Modified: 2010-04-29
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
Comment
Question by:melch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:tonydspaniard
ID: 8362187
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
 
LVL 3

Accepted Solution

by:
tonydspaniard earned 340 total points
ID: 8362718
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question