![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Entry tags:
Why, Crystal? Whyyyyyy?
One of the most annoying things about working with Crystal is having it give you the same results multiple times when you only want them once. Say, for example, I want a report that gives me all the payments made over $1000, plus the total balances on those loans.
Crystal's default response here is to give me each payment and each loan balance over $1000 ... even if some of those payments are on the same loan. So:
| Loan # | Balance | Payment Amt |
| 123 | 10,000 | 1,100 |
| 234 | 20,000 | 1,200 |
| 123 | 10,000 | 2,000 |
| Total # loans: 3 | Total Balances: 40,000 | 4,300 |
My total payments makes sense, because that's all the individual payments over $1000. But the total number of loans and total balances doesn't, because it's counting loan 123 twice.
But! Crystal has a solution for this! DistinctCount, which will look at field in a record and only count that record once per time it appears in the report. Hence:
| Loan # | Balance | Payment Amt |
| 123 | 10,000 | 1,100 |
| 234 | 20,000 | 1,200 |
| 123 | 10,000 | 2,000 |
| Total # loans: 2 | Total Balances: 40,000 | 4,300 |
Now my count of loans is right! Except ... the balance total is still adding 123 twice.
Because DistinctCount only does counts. It will total the number of times unique values in a field occur. That's all it will do. It won't let you sum only unique values. It won't let you average them, or find the median value, or, well, anything else you might conceivably want to do. You can count.
WHY CRYSTAL WHY? Why could this not be a boolean value of "Distinct", at least? WHY?
So I put in this horrible little bit of code into one report to try to retroactively turn the count into a boolean so that I could use it to make a unique sum, which is a pain in the neck because Crystal DOES NOT WANT to look at anything that's happened in previous records on a report when it's reading a current record. Crystal is zen, with no past and no future: all there is is now. Except I know you can tell because how else could DistinctCount exist you little ratfink of a reporting tool?
And it seems to work, on that report.
Now I need it on a different report, and I'm trying to work myself up to adapting it to that report. And hoping it works again.
...
I really need to learn SQL. And convince the bank they should let me use that instead.
...
One of the frustrating things about Crystal is that it can do so much that's hidden away in some submenu of a submenu and you never even think to look there until two years later after you gave up on ever getting this right and you're complaining to someone about it and she says "Oh, can't you do that with a running total that changes by field?"*
When I first learned how to use Crystal, I used to tell my boss "You can't do that with Crystal". Now I tell her "You can do that but it's REALLY ANNOYING." Or "I don't know how to do that, which is not necessarily the same as 'it's impossible', but it might be."
Maybe there is a boolean value of Distinct in there, somewhere, and I'm just not finding it. Whee. Well, writing this out didn't give me any brilliant inspirations for doing this some other way, so I guess I'll go do it that hard way now.
* This is one workaround for the DistinctCount issue, actually, but it only works if you've got the records sorted by the field you want to use as the distinct one. It doesn't work for my payment example above because I want to sort by the payment amount.
no subject
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.
no subject
no subject
no subject