Need Help With Grouping Formula

[HaleyW]

I am using Crystal 8.5 to report on data exported from the Raiser's Edge database, which is non-profit donor software. I am specifically interested in exporting information about actions entered by our fundraising staff. For example, John Doe can enter a phone call, letter, or meeting on a donor's record. A really nice feature is that more than one fundraising staff member can be entered on one action--for example, John Doe and Mary Smith can both be listed as attending the meeting luncheon with Donor X--so we don't have to add duplicate actions just to record both fundraisers. A really nice feature--except when it comes to creating reports!

What happens is that the Solicitor Names (the fundraising staff) are all exported as a single string separated by a semi-colon. When I try to group in Crystal based on Solicitor name, I get "Doe, John", "Smith, Mary" and "Doe, John; Smith, Mary". What I want is for the single action to be placed under both "Doe, John" and "Smith, Mary".

I've toyed with parsing the names out into Solicitor1 and Solicitor2 variables--works great, but....I'm not really figuring out what to do with it. Can you use a loop in a Grouping formula? Is there some easier way that I'm missing here? Or is what I want to do just not possible?

Thanks for any suggestions you can give me.

 

[synapsevampire]

What are exporting this to, a text file, or?

Do you mean that you're connecting to Razor's Edge?

If you speak of exporting and then reading from an export, state technical information.

I would suggest importing this export into Access, and then create a Union All Query to break the rows out into individual rows, which is what is required for grouping as you suggest.

-k

 

[HaleyW]

Sorry for not being precise--I wasn't sure how much information might be needed. Yes, Raiser's Edge has a query and export facility that is separate from Crystal. I export the data into what Blackbaud calls a Blackbaud Report Writer Database (MDB) but basically is an Access 97 .mdb file. I then create the crystal report by linking to this file. Unfortunately, simply creating the report in Access is not really an option--I am trying to create a crystal report that others in my office can then automatically run through Raiser's Edge, which has a built-in Crystal Reader.

 

[lbass]

You could do a Union All within Crystal in the Show SQL Query area, where you essentially add a second set of records and use one field to distinguish which set of records is which, as in:

SELECT
Table.`SolicNames`, Table.`SomeString`
FROM
`Table` Table
UNION ALL
SELECT
Table.`SolicNames`, 'Second Name' as WhichOne
FROM
`Table` Table

Using this query, you could then create a formula:

if {Table.SomeString} = "SecondName" then
(
if ubound(split({Table.SolicNames},";")) > 1 then
split({Table.SolicNames},";")[2] else
{Table.SolicNames}
) else
split({Table.SolicNames},";")[1]

...and group on this.

-LB

 

[HaleyW]

Thanks for your suggestion! I will try that!

Haley Whalen

 

[synapsevampire]

I would elaborate on LB's suggestion in that instead of doing the Union All in Crystal, create a Query on the Access database that produces the UNION'd data, and use the query as the source for the Crystal Report.

This will prove faster and allow for simplified maintenance/reusability.

-k