crystal uses very sql-like internal logic. "DISTINCT" in particular is an aspect of SQL. The thing to bear in mind (even if it's in the back of your mind) is that SQL is all about sets, not about processing individual items.
I would suggest grabbing a copy of one of the free developer versions of whatever backend you're running crystal against to play with on your own time.
Let's say you have a table that looks like this:
Loan# Balance$ Payment$
Then it probably came from a couple of tables, originally - one that says
Loan # - Balance - Other Loan Details
and another one that says
Loan # - Payment Amount - Date - Other Payment Info
and the database guys are handing you the results of a query that looks something like this:
SELECT Loan.LoanNumber, Loan.Balance, Payment.Amount from Loan INNER JOIN Payment ON Loan.LoanNumber = Payment.LoanNumber ORDER BY Payment.Date
That gives you back a table like
LoanNumber - Balance - Amount
What you seem to need is a Crystal view that shows that, but with
WHERE BALANCE >= 1000 or Amount >= 1000
And then you also need one that says:
SELECT COUNT(LoanNumber) AS NumLoans, SUM(Balance) AS TotalBalance, SUM(TotalPayments) AS TotalPayments FROM (SELECT Loan.LoanNumber, Loan.Balance, SUM(Payment.Payment) AS TotalPayments FROM Loan INNER JOIN Payment ON Loan.LoanNumber = Payment.LoanNumber WHERE (Payment.Payment >= 1000) GROUP BY Loan.LoanNumber, Loan.Balance) AS derivedtbl_1
This does, however, have one complication: You're doing two queries instead of one.
Short version: directly writing SQL queries would definitely let you do this. Crystal is what you'd use to take the queries and turn them into something that looks like a pleasant report.
I would suggest grabbing a copy of one of the free developer versions of whatever backend you're running crystal against to play with on your own time.
Let's say you have a table that looks like this:
Loan# Balance$ Payment$
Then it probably came from a couple of tables, originally - one that says
Loan # - Balance - Other Loan Details
and another one that says
Loan # - Payment Amount - Date - Other Payment Info
and the database guys are handing you the results of a query that looks something like this:
SELECT Loan.LoanNumber, Loan.Balance, Payment.Amount from Loan INNER JOIN Payment ON Loan.LoanNumber = Payment.LoanNumber ORDER BY Payment.Date
That gives you back a table like
LoanNumber - Balance - Amount
What you seem to need is a Crystal view that shows that, but with
WHERE BALANCE >= 1000 or Amount >= 1000
And then you also need one that says:
SELECT COUNT(LoanNumber) AS NumLoans, SUM(Balance) AS TotalBalance, SUM(TotalPayments) AS TotalPayments
FROM (SELECT Loan.LoanNumber, Loan.Balance, SUM(Payment.Payment) AS TotalPayments
FROM Loan INNER JOIN
Payment ON Loan.LoanNumber = Payment.LoanNumber
WHERE (Payment.Payment >= 1000)
GROUP BY Loan.LoanNumber, Loan.Balance) AS derivedtbl_1
This does, however, have one complication: You're doing two queries instead of one.
Short version: directly writing SQL queries would definitely let you do this. Crystal is what you'd use to take the queries and turn them into something that looks like a pleasant report.
Reply
Reply
Leave a comment