How do I split String Array?

[khoilewisclark]

I use Crystal Reports XI. I'm trying to create a proposal report, grouped by solicitors. Currently, it looks like this (solicitors are placed in the group header):

John Smith
Proposal A Ask amount X
Proposal B Ask amount Y

Jane Doe; Mary Holt
Proposal C Ask Amount Z
Proposal D Ask Amount W

I need to spit Jane Doe and Mary Holt. I want the output to look like this (but don't want to double count the ask amount for the same proposals):

John Smith
Proposal A Ask amount X
Proposal B Ask amount Y
Jane Doe
Proposal C Ask Amount Z
Proposal D Ask Amount W
Mary Holt
Proposal C Ask Amount Z
Proposal D Ask Amount W

Please help! What formula should I use here?

Thank you!!!

 

[lbass]

Is this required just for display purposes or do you need to work with each case independently? (I already understand the need not to double count.)

-LB

 

[khoilewisclark]

I need to work with each case independently.

Thanks
K.

 

[lbass]

Save the main report under another name and then insert it as a subreport in the group footer that is linked on the groupfield (just as it is). In the main report, replace the groupname with a formula:

split({table.groupfield},";")[1]

In the subreport, replace the groupfield with:

if ubound(split({table.groupfield},";"))>= 2 then
split({table.groupfield},";")[2]

Suppress the subreport conditionally using this formula:

instr({table.groupfield},";") = 0

The main report calculations will be unaffected by the subreport. If you need data from the sub for any calculations, you would need to use shared variables to bring it into the main report.

-LB

 

[khoilewisclark]

Thank you very much. Let me try.

K.

 

[khoilewisclark]

LBass,

I almost got what I need. Here's the new outcome:

John Smith
Proposal A Ask amount X
Proposal B Ask amount Y

Jane Doe
Proposal F Ask Amount H

Jane Doe
Proposal C Ask Amount Z
Proposal D Ask Amount W

Mary Holt
Proposal C Ask Amount Z
Proposal D Ask Amount W

The reason why Jane Doe appears twice is that she not only has her own proposal (F) but also shares proposals C & D with Mary Holt.

How can I make Jane Doe appear only once and put all her proposals together (in one group) so that I'll be able to calculate a subtotal of how many proposals she has.

Thank you!!!
K.

 

[khoilewisclark]

Well,

Let me take the above back because it actually looks like this:
John Smith
Proposal A Ask amount X
Proposal B Ask amount Y

Jane Doe
Proposal F Ask Amount H

Jane Doe
Mary Holt
Proposal C Ask Amount Z
Proposal D Ask Amount W

The reason why Jane Doe appears twice is that she not only has her own proposal (F) [in the main report) but also shares proposals C & D [in the subreport] with Mary Holt.

How can I make Jane Doe appear only once and put all her proposals together (in one group) so that I'll be able to calculate a subtotal of how many proposals she has.

Thank you
K.

 

[lbass]

My formulas would not have returned the results as shown in your last post, unless you conditionally suppressed the details.

Anyway, let's start over. You cannot create a group that will return Jane and a separate group that will return Mary, since a record will only appear in one group. This means you either have to create additional records in order to have the groups appear, or you have to use running totals to calculate individual's results. I am unclear on whether you want to group just because you think you need to in order to determine the results per individual (you don't) or whether this is the desired end display. Please clarify this.

Also, it would help to know about how many total solicitors there are (a few or many) and what the maximum number of solicitors that can appear in one record combined with others.

-LB

 

[khoilewisclark]

LBass,

Okay, let's start over. I want to group the report by individual solicitors because I need to display them this way AND to determine the results for each of them. Ultimately, I'll want to create a parameter field so that end users will be able to select which individual solicitor they want to see and to make this report a drill-down one, which will only show only their names and their results (subtotals). By double-clicking on the results or names, end users will be able to see the details (proposals + ask amounts) within each individual solicitor.

There is not a maximum number of solicitors that can appear on each record, but currently the report only has 2 solicitors on each record. I can enter as many solicitors in the field as I want to but haven't seen more 2 solicitors.

So, here's what the final report should look like:
Group Header: John Smith
Details (hidden): Proposal A Ask Amount: $20,000
Proposal B Ask Amount: $25,000
Group Footer:# of proposals: 2 Total ask amount: $45,000

Group Header: Jane Doe
Details (hidden): Proposal F Ask Amount: $40,000
Proposal C Ask Amount: $10,000
Proposal D Ask Amount: $15,000
Group Footer:# of proposals: 3 Total ask amount: $65,000

Group Header: Mary Holt
Details (hidden): Proposal C Ask Amount: $10,000
Proposal D Ask Amount: $15,000
Group Footer:# of proposals: 2 Total ask amount: $25,000

Seems like I'm trying to do something beyond Crystal's capability

Thank you very much!
K.

 

[lbass]

Do you have a table that contains all solicitor names? Your best bet would be to use the solicitor name from that table in a main report. Then add a subreport that uses the proposal submission names, proposal ID, and amount. Link the subreport by linking the main report solicitor name to the proposal name in your current table. In the sub, change the selection formula to:

{?pm-maintable.solicitorname} in {sub.proposalname}

You can group on the name in the subreport and hide the details so that you can drilldown.

-LB

 

[khoilewisclark]

Well, I do have a dynamic table that contains all solicitor names. Let me try your method.

Thanks a lot
K.

 

[khoilewisclark]

Actually, the solicitor table is like this:
John Smith
Jane Doe; Mary Holt

I'd still need to split Jane Doe and Mary Holt.

K.

 

[khoilewisclark]

I am not sure I know where to change the selection formula in the subreport to {?pm-maintable.solicitorname} in {sub.proposalname}

 

[lbass]

This is a poor database design if you need to report on individuals, and I think you should see about changing this field to accept only one name per record.

I think you can use a command to accomplish this. I don't know what database you are accessing, but you could create a command like the following as your datasource, and you would be able to group on name without using a subreport. The syntax in the following is for MS Access--this would have to be adapted for your database. Note, however, this is only set up to accommodate two names:

Select 'First Name' as Solicitor, {fn substring(table.`solicitorname`,1,{fn locate(';',table.`solicitorname`)-1})} as Name, table.`proposalID`,table.`askamt`
from table
where {fn locate(';',table.`solicitorname`)} > 0
Union
Select 'Second Name' as Solicitor, {fn substring(table.`solicitorname`,{fn locate(';',table.`solicitorname`)+1})} as Name, table.`proposalID`,table.`askamt`
from table

Then insert a group on {command.Name}. You can use the {command.Solicitor} field if you need to distinguish the first from the second person in the original record.

-LB

 

[khoilewisclark]

Oh, this looks like a GREAT idea. Let me try this.

Thanks a bunch.
K.