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?

 

[JasonGreen]

Ok, inserted a section below my Group Header 1 so I would get Group Header 1a and 1b. I moved the Group #1 Name down to GH1b. Then I inserted my subreport into GH1a. I setup the links to State and City. Then I edited the subreport and removed the city record selection and changed the state record selection from "equals" to "in" like this - {?pm-table.state} in {table.subidiaries}. Then I created a formula field called HeaderField with the following formula:

whileprintingrecords;
shared numbervar st := 0;
shared stringvar city := "";

I drug HeaderField into the header of the subreport. Then I created a formula field called DetailField with the following formula:

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};

Then I drug DetailField into the details section of the subreport.

Then in the main report I went into the section expert, clicked on GH1b and checked "Suppress (No Drill-Down)", clicked the X-2 button, and added the following formula:

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

Then I clicked on GH2, checked "Suppress (No Drill-Down)", clicked the X-2 button, and added the following formula:

whileprintingrecords;
shared stringvar city;
instr(city,{table.city}) = 0

Finally, I went into the sub and suppressed all of the sections, removed the borders from the sub, formatted it and checked "Suppress Blank Subreport", and set GH1a to "Suppress blank section"

 

[lbass]

Well, you have just copied my formulas rather than showing yours, but assuming you have implemented this correctly, do you see any patterns to the results that you can explain?

Please also create two formulas in the main report like this:

//{@testst}:
whileprintingrecords
numbervar st;

//{@testcity}:
whileprintingrecords;
stringvar city;

Place these in GH1_b and let me know whether you see expected values--you should see the state and the contents of all subsidiary fields that belong with those groups.

Also try creating the reset formula that I had you put in the sub report header in the main report instead, and place it in the group footer for the state group.

-LB

 

[JasonGreen]

Sorry about that. Here are the formulas with my table names.

Subreport Details formula:
whileprintingrecords;
shared numbervar st;
shared stringvar city;
if isnull({tblCoSub.Subsidiaries}) or not({?Pm-tblCityState.State} in {tblCoSub.Subsidiaries}) then
st := st
else
st := st + 1;
if isnull({tblCoSub.Subsidiaries}) or not ({?Pm-tblCityState.City} in {tblCoSub.Subsidiaries}) then
city := city
else
city := city + {tblCoSub.Subsidiaries};


GH2 Suppression Formula:
whileprintingrecords;
shared stringvar city;
instr(city,{tblCityState.City}) = 0

The rest are the same as yours.

I created the 2 new formula fields and placed them in the GH1b.
The state variable just displays 0.00 for all of them. And the city variable is blank for all of them.

Then I created the reset formula and put it in the state footer, but didn't notice a change.

I've attached a picture of the results.

 

[lbass]

I'm sorry, those formulas should have been:

//{@testst}:
whileprintingrecords;
shared numbervar st;

//{@testcity}:
whileprintingrecords;
shared stringvar city;

Make sure all formulas that reference these variables say "shared".

-LB

 

[JasonGreen]

Not a problem. That got the numbers to display. Please see the image below.

 

[lbass]

If you format the city formula to "can grow", you should see all of the cities within a particular state (along with others that aren't that are with a relevant city in the same field). If you do, see the appropriate cities contained in the city variable, then the city suppression formula should work. Remove the section suppression on GH2 and then try a test formula that you create in the field explorer->formula->new:

whileprintingrecords;
shared stringvar city;
instr(city,{tblCityState.City}) = 0

Place this in GH2 and see if it displays 'true' for cities that have no subreport values. Also, since you have placed the reset formula in GF1 of the main report, remove it from the RH of the subreport (not sure it matters though).

-LB

 

[JasonGreen]

I did the "can grow" and now can see the cities. I also created the test formula with the following code and put it into the GH2:

whileprintingrecords;
shared stringvar city;
instr(city,{tblCityState.City}) = 0

This didn't seem right to me because only 2 of the states displayed cities under them and it was only one city each. So I tried changing the formula as below:

whileprintingrecords;
shared stringvar city;
instr({tblCityState.City},city) = 0

That displayed all the cites correctly and the subreport data, but something is missing because it does not hide the cities without subreport data even though the formula returns "False".

 

[lbass]

No, you can't switch them around like that.

I meant for you to first remove the suppression formula and then add the first formula to the GH2 to see whether it correctly displays true and false.

-LB

 

[JasonGreen]

Ok, sorry bout that.
I changed the formula back to the original, and removed the suppression formulas and all cities display "True" even if the subreport is blank.

 

[lbass]

Please show samples of how the city is displayed in the main report and also show how the related city would appear in the subisidiaries field. Is the case the same in both the main report city field and in the subsidiaries field?

-LB

 

[JasonGreen]

Ok, attached is a sample of what the main report looks like now.

 

[JasonGreen]

Here is what the data of the subsidiaries field looks like.

 

[lbass]

The sub should be linked on the state field as mentioned earlier, so you shouldn't be seeing all states in the field as shown in your image.

Why do you show a GH2b section--what is in this section?

I just don't see anything wrong with the current setup to suggest what the problem is.

-LB

 

[JasonGreen]

Yep, the subreport is linked as you said.

The subreport is in the GH2b section.

I have a screenshot of the design attached.

 

[lbass]

Please do this:

Place these formulas in GH2a of the main report, next to the groupname for city:

//{@sharedcity}:
whileprintingrecords;
shared stringvar city;

//{@whileprintingrecords;
shared stringvar city;
{tblCityState.City} in city

Then show the results.

-LB

 

[JasonGreen]

Ok, attached are the results.

 

[lbass]

One of the formulas shows false, and one shows true, but I don't know what the formulas are. Please show the contents of both and identify the result that each is returning.

-LB

 

[JasonGreen]

Sorry about that. The one that is returning FALSE is:

whileprintingrecords;
shared stringvar city;
instr(city, {tblCityState.City}) = 0

And this is the one that is returning TRUE:

//{@whileprintingrecords;
shared stringvar city;
{JCCityState.City} in city

 

[lbass]

Well, I think the suppression formula should work then. Try one of the following in the section expert for GH2:

whileprintingrecords;
shared stringvar city;
instr(city, {JCCityState.City}) = 0

whileprintingrecords;
shared stringvar city;
not({JCCityState.City} in city)


They both do the same thing.

-LB

 

[JasonGreen]

I tried them, and at first glance it seems like they are working, but then I checked California, where I know there are several company matches under multiple California cities and none of them show. When I remove the suppression formula and page through the report, I see the companies under the California cities, but the following formula fields for those records return blank and FALSE.

//{@sharedcity}:
whileprintingrecords;
shared stringvar city;

//{@whileprintingrecords;
shared stringvar city;
{tblCityState.City} in city