![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Crystal Not-So-Clear
Jul. 16th, 2009 02:16 pmI wrote this out in the hopes that I'd figure out a solution once I had it laid before me, but nooooo. So I'm turning to my much smarter readers for advice. /o.o\
I need to do a report showing all loans to a given customer, and all guarantors on those loans. The tools that I have for accomplishing this task are Crystal Reports and Excel 2003.
This information is stored in an SQL database, in a format where Customer is linked to Loans and Loans are linked to Guarantors. So, for example:
Foo Inc has loan #s 100, 101, 102 and 103. Loan #100 is guaranteed by Fi Foo and Fum Foo. Loan #101 is guaranteed by Fi Foo, Fum Foo, and Fi Fum Enterprises. Loan #s 102 & 103 hav no guarantors at all.
Now, I can query my database using Crystal Reports and get the following table:
But I actually don’t care which loans are guaranteed by which individuals. All I want is each unique loan number and each unique guarantor. So what I want is more like:
Or, perhaps less confusing:
Aaaaand …
I can’t figure out how to do this. >.<
If I wanted to show all the guarantors and didn’t care if I showed all the loans or not, I could do that by grouping on the guarantors and hiding the details (where all the loans would be hidden). Or vice-versa, if I wanted to show all the loans and didn’t care if I showed all the guarantors or not. But I need to show the unique results for each, and I need to do this for 100+ borrowers, who may have only one loan and no guarantors, or might have 20 loans with eight guarantors each. Showing only unique instances would be the difference between a report that fits on 10 pages and one that takes up 200.
I don’t want to do this by hand, and I don’t want the people who are currently doing it manually keep doing it manually, because that is suckage beyond words. Gah.
I need to do a report showing all loans to a given customer, and all guarantors on those loans. The tools that I have for accomplishing this task are Crystal Reports and Excel 2003.
This information is stored in an SQL database, in a format where Customer is linked to Loans and Loans are linked to Guarantors. So, for example:
Foo Inc has loan #s 100, 101, 102 and 103. Loan #100 is guaranteed by Fi Foo and Fum Foo. Loan #101 is guaranteed by Fi Foo, Fum Foo, and Fi Fum Enterprises. Loan #s 102 & 103 hav no guarantors at all.
Now, I can query my database using Crystal Reports and get the following table:
| Borrower | Loan Number | Guarantor |
| Foo Inc | 100 | Fi Foo |
| Foo Inc | 100 | Fum Foo |
| Foo Inc | 101 | Fi Foo |
| Foo Inc | 101 | Fum Foo |
| Foo Inc | 101 | Fi Fum Enterprises |
| Foo Inc | 102 | -- |
| Foo Inc | 103 | -- |
But I actually don’t care which loans are guaranteed by which individuals. All I want is each unique loan number and each unique guarantor. So what I want is more like:
| Borrower | Loan Number | Guarantor |
| Foo Inc | 100 | Fi Foo |
| Foo Inc | 101 | Fum Foo |
| Foo Inc | 102 | Fi Fum Enterprises |
| Foo Inc | 103 | -- |
Or, perhaps less confusing:
| Borrower | Loan Number | Guarantor |
| Foo Inc | 100 | Fi Foo |
| 101 | Fum Foo | |
| 102 | Fi Fum Enterprises | |
| 103 |
Aaaaand …
I can’t figure out how to do this. >.<
If I wanted to show all the guarantors and didn’t care if I showed all the loans or not, I could do that by grouping on the guarantors and hiding the details (where all the loans would be hidden). Or vice-versa, if I wanted to show all the loans and didn’t care if I showed all the guarantors or not. But I need to show the unique results for each, and I need to do this for 100+ borrowers, who may have only one loan and no guarantors, or might have 20 loans with eight guarantors each. Showing only unique instances would be the difference between a report that fits on 10 pages and one that takes up 200.
I don’t want to do this by hand, and I don’t want the people who are currently doing it manually keep doing it manually, because that is suckage beyond words. Gah.
