Array or Subreport or .........?

[JasonGreen]

Basically, my problem is that I have data that is all bunched together that I need to massage and format so that I can use it in the Group Expert on my report.

In the database, I have company records that have a memo type field for the location of their subsidiaries. The subsidiary data was entered in as State Abbreviation SPACE City ENTER.
So the data looks like this:

CompanyName:
CompanyA

Subsidiaries:
AZ Phoenix
AZ Scottsdale
FL Jacksonville
FL Orlando

CompanyName:
CompanyB

Subsidiaries:
AZ Phoenix
CT Wallingford
DC Washington

What I am trying to do is to Group by the State then by the City. I am currently replacing the Carriage return with a character combination that wont be found elsewhere in the data, then splitting on that to create and array. Then I loop through that array and split on the space in between the state and city. Problem is that this is creating a string of all of the states for that company only where I need them to somehow be combined. See my code below.

dim i as number
dim strLocation as string
strLocation = {vwNACEJCIndexDetails.OppByLocation}
dim strarrLocation() as string
dim strarrState() as string
dim strOutput as string

strLocation = Replace(strLocation, chr(13) + chr(10), "!~!")

strarrLocation = Split(strLocation, "!~!")

if Ubound(strarrLocation) >= 1 then
for i = 1 to ubound(strarrLocation)
strarrState = Split(strarrLocation(i), " ")
strOutput = strOutput + strarrState(1)
next
else
strOutput = strOutput + strLocation
end if

formula = strOutput

Any ideas?

 

[lbass]

Using the above sample, what is the result you expect to see?

-LB

 

[JasonGreen]

Well when I browse the data of my formula field, it currently looks like this:

ALCACACACAFLFLFLLAMAMDMSNMTNTNTNTXVAVA
AZCOCOFLGAIAKSMNMONCNVORTNTXTXWA
AZCOCOFLGAIAKSMNMONCNVORTNTXTXWA
AZCACACACACOCOFLGAMDMDMDMDMNNJNMNYNYOHPASCTXTXTXVA
AZAZFLMN

Which makes sense, as my code is creating an array of states for each company. Each line above would represent a different company. What I am trying to do, is to combine all of the states for all of the companies so that I can have a "master" list of states to group by. If that will even work.

 

[lbass]

I meant, what do you WANT to see, using the sample above.

-LB

 

[JasonGreen]

Ultimately, I want the report to look like this:

AZ
Phoenix
CompanyA
CompanyB

Scottsdale
CompanyA

CT
Wallingford
CompanyB

DC
Washington
CompanyB

FL
Jacksonville
CompanyA

Orlando
CompanyA

 

[JasonGreen]

Ok, so I decided to do this in SQL before sending the data to the report. I found a function online to help me split and pivot the data and return it as a table for me. I modified it to accept 2 parameters. The first one being the carriage return that separates the values in the SQL TEXT field, and the second being the space between the state and city values within the first split. I ran this last night and it ran for 45 minutes before I stopped it. Then I tried running it with just one row by selecting top 1 and it still didn't return any records. Can anyone show me how to optimize my function, or a better way to get this done?

Here the modified function:

create function fnsplit (@list varchar(max), @delimiter1 varchar(8), @delimiter2 char(1))
returns @shards table (value1 varchar(8000), value2 varchar(8000))
with schemabinding
as
begin
declare @i int;
set @i = 0;
declare @j int;
set @j = 0;
while @i <= len(@list)
begin
declare @n int;
set @n = charindex(@delimiter1, @list, @i);
if 0 = @n
begin
set @n = len(@list);
end
while @j <= len(@n)
begin
declare @o int;
set @o = charindex(@delimiter2, substring(@list, @i, @n-@i+1), @j);
if 0 = @o
begin
set @o = len(substring(@list, @i, @n-@i+1));
end
insert into @shards (value1,value2)
values (substring(substring(@list, @i, @n-@i+1), @j, @o-@j+1), substring(substring(@list, @i, @n-@i+1), @o, len(substring(@list, @i, @n-@i+1))));
set @i = @n+1;
end
end
return;
end
go


Here is how I have been calling the function:

select columnname
from tablename
cross apply fnsplit(columnname, CHAR(10), ' ');

select top 1 columnname
from tablename
cross apply fnsplit(columnname, CHAR(10), ' ');

 

[lbass]

I didn't forget this thread--just hadn't figured out a good solution yet. And I can't help with your last approach. But do you have a master table of states and cities by any chance?

-LB

 

[JasonGreen]

I do have a master table of states, but not cities.
I do have a address table where I could select distinct records from and create a master table though.

 

[lbass]

If you can create a table of cities and states, then you can add only this table to a new report and insert a group on state and then on city. Then insert a subreport that uses the company table and place it in a city group header_b section. Link the sub to the main report by adding the state field and the city field as linking fields. In the sub, go to report->selection formula->record and change the formula to:

{?pm-table.state} in {table.subidiaries} and
{?pm-table.city} in {table.subsidiaries}

Then add the Company info to the detail section of the sub. The advantage of using a subreport is that it addresses the issue that one record can only be in one group. Without a sub, you would have to find a way to force multiple records in order to get the desired results.

-LB

 

[JasonGreen]

Are you busy right now? I'm just going to give you RDP access. j/k

Thank you very much. I am going to give this a shot right now and let you know my results.

Thanks again.

 

[JasonGreen]

Ok, I got that all setup. What it happening is that crystal is unable to match the values of my subsidiaries field to the groups. Meaning, it doesn't know that my data that looks like this:

VA Alexandria, USA
VA Arlington, USA
MA Boston, USA
TX Irving, USA
CA San Diego, USA

Should show up under these groups:
CA
San Diego
MA
Boston
TX
Irving
VA
Alexandria
Arlington

 

[lbass]

Sorry, I'm not following. Please explain what you did, and then show the results you are getting and identify the report sections you are displaying.

Your main report should be grouped on state and then on city, using your new table, which I'm assuming has a state field and a city field. If you then create two links to the sub, one on state and one on city, you should get the desired matches, using the formula I showed earlier.

-LB

 

[JasonGreen]

Sorry, what I did was create the table with 2 columns (state, city) we'll call it tblCityState.
Then I created a new report and added tblCityState.
Then I went into the Group Expert and grouped by tblCityState.state, then by tblCityState.city.
Then I added a Group Header B section for the tblCityState.city group.
In the Group Header B section, I added a subreport.
In that subreport, I added the company/subsidiary table called tblCoSub.
Then I added tblCoSub.CompanyName to the details section of the subreport.
Back on the main report, I right clicked on the subreport and chose Change Subreport Links.
In there I setup the links from the main reports tblCityState.State field to the subreports tblCoSub.Subsidiaries field and the main reports tblCityState.City field to the subreports tblCoSub.Subsidiaries field.

When I preview the main report, the groups are showing correctly. It is grouping by state first then city. But where the company info should display, it is blank.

I'm guessing that crystal cannot make the match because the tblCoSub.Subsidiaries field is not split down to its basic parts (ie. State and City) and is instead a SQL TEXT field that multiple states and cities combined.

Should the subreport link function work like a SQL Like operator where it will look for a match in any part of the field string?

 

[lbass]

After adding the links, you have to go into the subreport record selection formula and change it to:

{?pm-table.state} in {table.subidiaries} and
{?pm-table.city} in {table.subsidiaries}

-LB

 

[JasonGreen]

OMG, you are the king! Sorry it took me a while to give this a try, but it worked perfectly. I didn't see that I was doing and "equals" instead of an "in" in my record selection formula.

One more question. Is there a way to not display the city and/or state group sections if there is no data? It's not a huge deal, but it would cut the report size down considerably.

Seriously, thank you very much. You are a great help.

 

[lbass]

That's a little complex. You would have to create a copy of the subreport and add it to a state group header_1a section (with the regular group header info in GH1_b). Link the sub on the state field only (retain the city link but remove it from the selection formula). Then within the sub you would

Create a formula like this for the subreport header:
whileprintingrecords;
shared numbervar st := 0;
shared stringvar city := "";

Create a formula like this for the detail section:

whileprintingrecords;
shared numbervar st;
shared stringvar city;
if isnull({table.subsidiaries}) or
not({?pm-table.state} in {table.subsidiaries}) then
st := st else
st := st + 1;
if isnull({table.subsidiaries}) or
not ({?pm-table.city} in {table.subsidiaries}) then
city := city else
city := city + {table.subsidiaries};

In the main report, use a section suppression formula for GH1_b like this:

whileprintingrecords;
shared numbervar st;
st = 0 //note no colon

For GH2, use a section suppression formula like this:
whileprintingrecords;
shared stringvar city;
instr(city,{table.city}) = 0

To make GH_1a disappear, suppress all sections WITHIN the sub, remove the borders, format the sub (subreport tab) to "suppress blank subreport", and in the main report section expert, format GH_1a to "suppress blank section".

-LB

 

[JasonGreen]

When you say "Create a formula like this for the subreport header:", do you mean, create a formula field and put it into the header?

 

[lbass]

Yes.

-LB

 

[JasonGreen]

Ok, then I am doing something else wrong. It's showing all states and only a handful of cities and companies.

 

[lbass]

I can't really help without knowing exactly how you implemented this.

-LB