ARRAY issue

[Pablito9]

Could somebody help me resolving this issue? (for me a problem!!).
I supose this should be done with an array.

Note that the tables could be populated with more data than the example shows.

I am using Crystal XI-R2

The first table (1) contains the security groups based on 2 fields; one for
the group code and the second for the description of the group.
Example;
Group Code Description
office Office Staff
sales Sales Staff
shop Shop Employee
prod Production

The second table (2) contains the user information, one of the fields contains
the group code separated with a “~”
Example;
UserID Groups
marc office~prod~shop~sales
jenny prod~office
erik shop~sales
aarlon
bonnie prod~sales

I would like to retrieve data in my report in this way

UserId Description Group Code
marc Office Staff office
marc Production prod
marc Shop Employee shop
marc Sales Staff sales
jenny Production prod
jenny Office Staff office
erik Shop Employee shop
erik Sales Staff sales
bonnie Production prod
bonnie Sales Staff sales

In this way I will be able to group by Group Code

Many thanks in advance

Erik

 

[GJParker]

Thefirts thing you should do is shoot the person who designed this schema.

You can't create this report with the data in the form it is you will need to split the data in your 2nd table into separate rows of data 1 rrecord for each userID and group.

Without knowing what db your using I can't give specifics on how to do this but The work does need to be done at the db and not crystal.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[dsweet81]

Yes this is bad design, however it is possible to do as you want. Using an array:

Create first formula:

--------- First Formula -------------------
Local stringvar arrary Splitlines:= Split({UserInformation.groups},chr(126));
//chr(126) "~"
Stringvar Test:=Join(SplitLines,",");

-------- Second Formula ------------------
StringVar Test;
{Securitygroup.groupCode} in Test


The Second formula will now create a true/false field..

You now suppress the detail section ensuring that only recordsets with the second formula as true is shown. Now you can group by the group code. Hope this resolves you problem. If you want a copy of the report, I can email you.

Kind Regards


DSweet

 

[synapsevampire]

dsweet: I don't think thqt will work, they need to get the description using a join.

Try posting your database and version with any post, you might use advanced SQL in a Command, View or SP to accomplish this.

-k

 

[Pablito9]

Many thanks for trying to help me.
First of all, I wasn't able to shoot the the programmer.

The database we are using is a Progress one (OpenEdge 10.0B) and we use the "DataDirect 4.20 32 Bit OpenEdge SQL v10.0B" driver PGPRO1019.DLL for connecting to the database.

Applying the suggestion result that the second formula will be FALSE when the field contains a "~". But still I need to join the second table (comment of synapsevampire). I really have no idea how to it.

Any suggestions are welcome.

Erik

 

[synapsevampire]

Yeah, you're kinda scrwed here...

One fugly solution would be to create a whole bunch of formulas and corrsponding subreports, wherein you parse out the field into formulas, then join the subreports to the formulas to go get the descriptions...

whileprintingrecords;
stringvar array Names:= Split({UserInformation.groups},chr(126));
stringvar fld1;
stringvar fld2;
stringvar fld3;
stringvar fld4;
stringvar fld5;
stringvar fld6;
stringvar fld7;

Now create different formulas for each (as many as there might be):

whileprintingrecords;
stringvar array Names;
if ubound(Names) > 0 then
fld1:= Names[1];

whileprintingrecords;
stringvar array Names;
if ubound(Names) > 1 then
fld2:= Names[2];

whileprintingrecords;
stringvar array Names;
if ubound(Names) > 2 then
fld3:= Names[3];

whileprintingrecords;
stringvar array Names;
if ubound(Names) > 3 then
fld4:= Names[4];

whileprintingrecords;
stringvar array Names;
if ubound(Names) > 4 then
fld5:= Names[5];

whileprintingrecords;
stringvar array Names;
if ubound(Names) > 5 then
fld6:= Names[6];

Use these to join to a subreport which contains the table with the descriptions.

There are trickier ways to use SQL to do this in Oracle, that's why I asked about the database.

-k

 

[synapsevampire]

Ooops, the formulas to join to ned their variables set and qualified, s in:

whileprintingrecords;
stringvar array Names;
stringvar fld1:="";
if ubound(Names) > 0 then
fld1:= Names[1];

etc...

-k

 

[Pablito9]

Many thanks again. I was able to reproduce your formulas in my report and it worked. But it is , as you call it, a very ugly solution!

Erik