Rowyn (rowyn) wrote,
Rowyn
rowyn

Crystal Not-So-Clear

I 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:

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.
Tags: work
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 12 comments