We help IT Professionals succeed at work.

How to achieve this?

pvsbandi
pvsbandi asked
on
Hi,

 I have a crystal report, with data coming from a stored procedure. My data looks like below.

ID            TYPE                        RESOURCE
123            INTERVENTION            COMMUNITY
123            INTERVENTION            COUNSELOR
123            INTERVENTION            ENFORCEMENT
123            OTHER                        COMMUNITY
123            OTHER                        COUNSELOR
123            OTHER                        ENFORCEMENT

The layout i'm looking for is below:
EXPECTED LAYOUT:

ID                  TYPE                              RESOURCE
123                  INTERVENTION,OTHER            COMMUNITY,COUNSELOR,ENFORCEMENT

Please help.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try this idea.

Add a formula to the report header
WhilePrintingRecords;
Global StringVar Array TypeList;
Global StringVar Array ResourceList;
Global NumberVar TypeCount;
Global NumberVar ResourceCount;
""

Group the report on
Group 1 - ID field
Group 2 - Type Field
Group 3 - Resource field

In the group1 header add a formula
WhilePrintingRecords;
Global StringVar Array TypeList;
Global StringVar Array ResourceList;
Global NumberVar TypeCount;
Global NumberVar ResourceCount;
ReDim TypeList[1];
ReDim ResourceList[1];
TypeCount := 0;
ResourceCount := 0;
""
 
In the group2 header add a formula
WhilePrintingRecords;
Global StringVar Array TypeList;
Global NumberVar TypeCount;
TypeCount := TypeCount + 1;
ReDim Preserve TypeList[TypeCount ];
TypeList[TypeCount ] := {TypeField};
""

In the group 3 header


WhilePrintingRecords;
Global StringVar Array ResourceList;
Global NumberVar ResourceCount;
If Not {ResourceField} IN ResourceList then
(
    ResourceCount := ResourceCount  + 1;
    ReDim ResourceList[ResourceCount];
    ResourceList[ResourceCount] := {ResourceField};
);
""

In the group 1 footer add formulas

WhilePrintingRecords;
Global StringVar Array ResourceList;
Join(ResourceList,", ")


WhilePrintingRecords;
Global StringVar Array TypeList;
Join(TypeList,", ")

mlmcc
Commented:
Add 3 groups to the report

Group 1 : ID
Group 2 : Type
Group 3 : Resource

Then use formulas to create the display strings .

//@Reset - place this in GH1
Shared StringVar Type := ''
Shared StringVar Resource := ''

Open in new window


//@AccumType - place this in GH2
Shared StringVar Type := Type & {sp.Type} & ','

Open in new window


//@AccumResource - place this in GH3
Shared StringVar Resource := Resource & {sp.Resource} & ','

Open in new window


//@DisplayType - place this in GF1
Shared StringVar Type
Left(Type, Len(Type) -1)

Open in new window


//@DisplayResource - place this in GF1
Shared StringVar Resource
Left(Resource, Len(Resource) -1)

Open in new window







Author

Commented:
@GJParker : Thanks! your formula got me the types fine, but the resources are coming back with duplicates. Like, "Community, Counselor, Enforcement,community,Counselor,Enforcement"

@mlmmc : Thanks! Your formula is giving me  ,,,Enforcement for Resource.
  and "Intervention,Intervention,Intervention,Other,Other,Other" for Type..

 how can i get rid of the duplicates? Please help.

Author

Commented:
attaching my report..
Test.rpt
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
I made some changes

mlmcc
TestRevA1.rpt
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You put the GH2 formula in GH3 thus the duplicates.

mlmcc

Author

Commented:
Thank you so much!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.