TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Kedar Pawgi
1.6k
136
63.8k
Query not building properly and crystal report showing sum of supressed rows
Feb 29 2012 1:58 PM
i have question regarding SQL, i am using Crystal report and c#2008.
I am building a report which has two table one is order and other is payment. What i want is :--its a small software for a building contractor, he has couple of building site where few supplier give material, what i want is a Ledger report left side should be the supplier order for perticular supplier 'A' for that site with in the perticular date range and one right side i want is the payment made by my client with in that perticular date range for that site for that perticular supplier 'A' and for that perticular site.
the query i made is as follows :--
SELECT `order`.`orderBillNo`, `order`.`siteName`, `order`.`orderDt`, `order`.`vendorName`, `order`.`totalAmt`, `vendorpayments`.`InvoiceNo`, `vendorpayments`.`Date`, `vendorpayments`.`Paymode`, `vendorpayments`.`amt`, `vendorpayments`.`Chqddno`, `vendorpayments`.`bankname`
FROM `order` , `vendorpayments`
where `order`.`vendorName`='A'
AND `vendorpayments`.`vendor` ='A'
AND `order`.`sitename`='B'
AND `vendorpayments`.`sitename`='B'
AND (`order`.`orderDt`>="2012/02/01" and `order`.`orderDt`<="2012/02/25")
AND (`vendorpayments`.`date`>="2012/02/01" and `vendorpayments`.`date`<="2012/02/25")
i am using ADODB recordset, and using crviewer.setdatasource(adoRS) for showing report to CR. i m using CR8
If i run using this query then report is showing proper result but when i do the sum of the order.totalamout & vendorpayments.amt then it shows sum with suppressed rows as well this is perticular query the order.totalamount should be 7,500 but its showing 30,000. and for vendorpayment it should show two rows with pay of 1,500 and 500 and total should be 2000, but its showing 4 rows and total of 4000,it its taking order.totamt also 4 times so i m getting 30000. I have used the suppressed option but still its not showing correct amout. I want help about how to remove this extra sum. Does there is problem with my query , does my query is giving more more iterations so 4 rows are coming. If any can correct my query iwill be thankful.Its very urgent
thanks in advance.
Reply
Answers (
0
)
Select a driver being used for first time
How to select values