Crystal Not-So-Clear

[rowyn]

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.

Comments

[telnar.livejournal.com]

On the SQL-fu front, what you really want is two select distincts within each borrower with the results merged together. I could do it cleanly (generating one set of output results) using a SQL procedural language (like PL-SQL for Oracle or T-SQL for SQL Server), but here's something in vanilla SQL which doesn't come that far from what you want:

Select Distinct Borrower, Loan
From Borrowers left outer join loans
on Borrowers.BorrowerID = Loans.BorrowerID
Order by Borrower, Loan

Select Distinct Borrower, Guarantor
From Borrowers left outer join loans
on Borrowers.BorrowerID = Loans.BorrowerID
left outer join Guarantors
on Loans.LoanID = Guarantors.LoanID
Order by Borrower, Guarantor

I know nothing about crystal reports, but there is probably a way to use it to create simple queries like these (just a two table join with a distinct). Btw, those joins are "left outer" so that you also get a NULL row to indicate when a borrower is in the system, but has no loans or no guarantors.

This gives you the data you want (distinct loans and guarantors for each borrower). It's just in two separate lists that you will then have to merge to build the final report.

[rowyn]

Doing them as distinct lists I can manage. It's the merge part that I can't figure out -- how to get the list of borrwers & loans to line up with the list of borrowers & guarantors. :/

[telnar.livejournal.com]

One way to do it would be to write an Excel macro to insert blank lines in each list until the borrowers are spaced one borrower per 10 lines (or whatever number it takes to cover the largest number of rows for any borrower). That will leave you with both lists in the same alignment. Then copy the data from one list to the other one. At that point, you have a single list (if with a few extra blank lines) which is just a whitespace eradicating macro away from what you want.