1

This is an interesting one. I'm writing a Crystal XI Report for a nonprofit based on an export from their fundraising database. The export creates an MDB file, which is used as the data source for the report.

There are three tables in my data source with a field containing a semicolon-delimited list of donation solicitors. I need to combine these three fields into a single column of distinct names. The results of this command will be used for both filtering and grouping. This is the table setup and desired output:

Table 1

Doe, John; Doe, Jane; Smith, Bob

Table 2

Smith, Bob

Table 3

Doe, John; Smith, Bob

I must combine this to create:

Doe, John
Doe, Jane
Smith, Bob

Easy enough, right? However, I must do this using only a SELECT statement. Why, you ask?

  • The MDB is overwritten on every run. This means adding UDFs or stored procs to the MDB aren't an option, because they would be deleted during the next export run.
  • End users don't have Access installed, so even if I wrote a script for them to add an object to the MDB as a step between the export and my report, they couldn't run it.
  • SQL commands in Crystal can't declare variables, so I can't write a cursor or anything where processing is done between a BEGIN and END.
  • I have no control over the process that generates my data source, so I can't fix it there.

I'm not sure if this is even possible to achieve within these limitations, but they are concrete. I'm almost at a point of experimenting with saving a UDF in a separate db and running the SQL command as a cross-database query so I can leverage the function, but I don't know if Crystal's SQL command can handle a cross-db query.

Any ideas for solutions, within these limitations?

Nicholai
  • 818
  • 7
  • 17
  • I don't really understand the question. You spent lots of words explaining why you can't do certain things, but it's still not clear what you're trying to achieve. – Marc May 13 '12 at 17:03
  • "There are three tables in my data source with a field containing a semicolon-delimited list of donation solicitors. I need to combine these three fields into a single column of distinct names. The results of this command will be used for both filtering and grouping." e.g. Table 1 Doe, John; Doe, Jane; Smith, Bob Table 2 Smith, Bob Table 3 Doe, John; Smith, Bob I must combine this to create: Doe, John Doe, Jane Smith, Bob – Nicholai May 13 '12 at 17:08
  • Is there a fixed/limited number of names on a single line or could it go on the the maximum length of the field? – bendataclear May 13 '12 at 17:32
  • It's technically an unknown number, but realistically it will max out around 5 or 6. – Nicholai May 13 '12 at 17:36
  • 1
    Will you always have less than a thousand distinct names total? If so, have you tried pushing the processing to Crystal and just throwing the names into a string array? – Ryan May 13 '12 at 18:19
  • Yes, it will always be less. For most report runs it will be fewer than 50, though may occasionally be higher. I haven't worked with arrays in Crystal; would I be able to use the array in a similar manner to a table (display fields, group, sort, and filter)? – Nicholai May 13 '12 at 18:28
  • For a way to do this in SQL, check out the answer to http://stackoverflow.com/questions/10572858/optimise-sql-function-to-get-common-elements. – Gordon Linoff May 13 '12 at 18:42
  • @Nicholai You would have to do all of things manually with formula code and the feasibility of doing it this way will depend on what you want your end product to be. If you can break it down into rows in SQL I'd do it that way. – Ryan May 13 '12 at 19:08
  • Thanks Gordon; it's an interesting solution, but it appears that Crystal can't handle WITH blocks either. I'm starting to think we'll need to hard-code the solicitor names into a generic SELECT, and update the report as new solicitors are occasionally added to the system. The names are key to the data, so that list must exist for comparison prior to the WhilePrintingRecords part of the report. – Nicholai May 13 '12 at 19:13

1 Answers1

0

If you use a Command, you will be able to combine the three recordsets:

SELECT NAME_FIELD
FROM   TABLE_1

-- eliminate duplications
UNION

SELECT NAME_FIELD
FROM   TABLE_2

UNION

SELECT NAME_FIELD
FROM   TABLE_3

You could then create a formula that splits the values into an array:

//{@names}
Stringvar Array names;
Split({Command.NAME_FIELD}), ";");
true;

Unfortunately, you won't be able to use this formula to turn arrays into 'rows'. Crystal just doesn't support this. Essentially, you are at a "dead end".

Your best option (and only one given the requirements) is to have the source system (i.e. the one that creates the MDB file) split the fields' values into equivalent recordsets. This will require you to create either a stored procedure or user-defined function in the source system. If you are using Oracle, see How can I combine multiple rows into a comma-delimited list in Oracle?.

Community
  • 1
  • 1
craig
  • 25,664
  • 27
  • 119
  • 205
  • Thanks. I didn't think it would be possible within the limitations I've been given. It looks like I'm going to have to bypass their export feature, write it in a sproc (which is much easier to work with), and force them to use the CR Viewer application to run this report instead of their fundraising program. – Nicholai May 14 '12 at 17:22