Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Array or Subreport or .........? Post Reply Post New Topic
Author Message
JasonGreen
Newbie
Newbie


Joined: 26 Apr 2011
Online Status: Offline
Posts: 5
Quote JasonGreen Replybullet Topic: Array or Subreport or .........?
    Posted: 26 Apr 2011 at 7:15am
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?
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 26 Apr 2011 at 7:36am
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


So for example you currently have

["AZ", "CT", "DC"]

But if you are planning to group by state and then by city, I'm not sure what you mean by "have them combined"
IP IP Logged
JasonGreen
Newbie
Newbie


Joined: 26 Apr 2011
Online Status: Offline
Posts: 5
Quote JasonGreen Replybullet Posted: 26 Apr 2011 at 7:57am
Actually, when I browse the data of my formula field, it 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.
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 26 Apr 2011 at 8:17am
It is possible to create such a master list. However I'm trying to think about how exactly you would then go about grouping them, and whether crystal would even allow it considering when it actually creates such a list and when groupings occur.

If groups are formed before the master list is created, crystal probably isn't going to re-group them.

It's an interesting idea, but the closest I've done to something like this was to create a formula that would evaluate to a particular result based on the record and then group on the result.

However if what I did was possible, then it MAY indicate that you can process all of this information and still be able to group on it somehow.

But a problem I see is that each record contains a lot of information, but it's still one record. If you are aiming to create a report that will take all that information in the memo and split them into separate groups, a single report probably won't be enough. I'm not sure if subreports can handle this.

Edited by Keikoku - 26 Apr 2011 at 8:19am
IP IP Logged
JasonGreen
Newbie
Newbie


Joined: 26 Apr 2011
Online Status: Offline
Posts: 5
Quote JasonGreen Replybullet Posted: 26 Apr 2011 at 8:24am
What I was thinking was that once I was able to create that combined master list in my formula field that I would just add the formula field to the Group Expert, in hopes that Crystal would be smart enough to find a match in the Subsidiaries field.
IP IP Logged
JasonGreen
Newbie
Newbie


Joined: 26 Apr 2011
Online Status: Offline
Posts: 5
Quote JasonGreen Replybullet Posted: 27 Apr 2011 at 3:31am
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
IP IP Logged
JasonGreen
Newbie
Newbie


Joined: 26 Apr 2011
Online Status: Offline
Posts: 5
Quote JasonGreen Replybullet Posted: 29 Apr 2011 at 3:52am
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), ' ');
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.