We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# Crystal Reports convert data rows to columns

on
Medium Priority
3,882 Views
Hi Experts,

I have the following reports with crystal reports vs 9.

User   Name                      Value
1       James                       1024    this value should be name Tracking
1       James                       10240  this value should be name Prim_id
1       James                       june    this value should be name Sec_name
2       Mario                        0000   this value should be name Tracking
2       mario                        0123   this value should be name Prim_id
2       mario                        daniel this value should be name Sec_name

I want to conver the name data rows to columns and show them as follows

User Tracking  Prim_ID   Sec_name
1      1024       10240      june
2      0000       0123        daniel
Any Suggestion
Comment
Watch Question

## View Solution Only

Commented:
I had similar problem and solved it by using runing totals.
I would group stuff by User field first...

HTH,
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Will the data always be in this format and order.

Try this
Add a group to the report on User.

Name - DeclVars
Formula
Global StringVar MyRecord := "";

Name - InitVars
Formula
Global StringVar MyRecord;
MyRecord := {UserField}

Add a formula to the detail section
Name CreateRecord
Formula
Global StringVar MyRecord;
MyRecord := MyRecord & "     " &  {ValueField}
""

In the group footer add a formula
Name DispRecord
Formula
Global StringVar MyRecord;
MyRecord

mlmcc

Commented:
Ok mlmcc

I did try your suggestions and I'm almost there. However the create record creates one field that combines tracking and the value.

User Tracking                Prim_ID   Sec_name
1      Tracking 1024       10240      june
2      Tracking 0000       0123        daniel

Is there a way to get rid of the tracking and show only the 1024. By the way the tracking number is dependent upon a selection criteria and so prim_id and Sec_name...etc.

Commented:
User   Name   ColName                   Value
1       James    Tracking                  1024    ' this value should be name Tracking
1       James    PrimID                     10240  ' this value should be name Prim_id
1       James    Sec_Name                june    ' this value should be name Sec_name
2       Mario     Tracking                   0000   ' this value should be name Tracking
2       mario     PrimID                     0123   ' this value should be name Prim_id
2       mario     Sec_Name               daniel  ' this value should be name Sec_name

So you can better understand. My selection xpert selects the Tracking, PrimID, Sec_Name...etc. I want the report to look like this

User   Name Tracking   PrimID  Sec_Name
1       James 1024        10240   june
2       Mario  0000         0123    daniel

This will be export to excel through crystal enterprise, so I wan the records above to be a separate field.

Thanks
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Maybe I am not understanding the database.

Is there a single record for a person or is it multiple?

Show the formula you built for CreateRecord

mlmcc

Commented:
Hi mlmcc

For each user there are 3 types of records under the field name Col Name with its respective value. For each record in the Col name-field  such as Tracking, PrimID and Sec_Name, I want to make a separate column. That is, one column for Tracking showing its value, Onother column for PrimID Showing its value and another column for SEC_name showing its value.  This is my cyrrent database:

User   Name   ColName                   Value
1       James    Tracking                  1024
1       James    PrimID                     10240
1       James    Sec_Name                june
2       Mario     Tracking                   0000
2       mario     PrimID                     0123
2       mario     Sec_Name               daniel

Below is my desire output.  See how the ColName was converted to Columns.
User   Name Tracking   PrimID  Sec_Name
1       James 1024        10240   june
2       Mario  0000         0123    daniel

I did create the formulas according to your suggestion. Below is a description of my report

Select Expert = Column Name is one of Tracking, PrimID, Sec_Name
Fields in report
Grouped by UserID in Group Header
Global StringVar MyRecord:="";
Group header #1 and Formula= InitVars
Global StringVar MyRecord;
MyRecord:= {ORDERS.USERID}
Detail: Fields UserID & Formula Create Record:
Global StringVar MyRecord;
MyRecord := MyRecord &"    "& {USER_AUX_DATA.COL_VALUE}&"";
Group Footer: Formula= DispRecord
Global StringVar MyRecord;
MyRecord

The results of your suggestion gives me the following view of the report.

UserId  UseName   ColName           Create Record
GH      1        James

Details       1       James        Tracking            1  1024
1        James        PrimID               1  10240
1        James        Sec_Name         1  june

GH      2        Mario

Details      2      Mario          Tracking               2  0000
2       Mario          PrimID                 2  10240
2       Mario          Sec_Name           2  june

Please note that the createrecord formula shows the userid and the value for Tracking, primid and secname together in one field.

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I forgot one thing.  Add this as the first line of each formula

WhilePrintingRecords;

Hide the Group Header and Detail section.

mlmcc
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You may want to play with the creation of records to ensure the spacing is right.

For instance
Ensure the User number is 5 wide
Ensure the Name is 15 wide

Name - InitVars
Formula
Global StringVar MyRecord;
MyRecord := Left(ToText({User},0) & "          ",5)  & "          "  & Left({Name} & "               ",15)

Similarly for CreateRecord

mlmcc

Commented:
HI Mlmcc.

The report works as I wanted, but when I export the report to excel, it includes all the data in one column. Is there a way to break the data into separate fields.

Thanks
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Only by putting them in separate fields in the report.

mlmcc

Commented:
Ok, I will try that. Is that mean I need to add more formulas and modify the original.

Thanks

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
YOu would need to create 1 formula for each field.

mlmcc

Not the solution you were looking for? Getting a personalized solution is easy.

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:

mlmcc

Commented:
I could use help with a simpler version of this: I have fields "email","lastname","firstname", "sessiontitle","approved" The first three fields are from a personal table and the last two from a sessions table (one to many relationship) I need an Excel sheet that will be used for a mail merge so one row per email,first,last spread across columns a,b,c and then the sessiontitle and approved (boolean) across the next columns for as many sessions that presenter has.

I tried multi-columns on the details section with the peronsal information in the details with suppress repeats. I tried with the personal information in the group headers (groups are by last name, then first).

I can't ever get this to go....HELP!

Thanks.
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:

mlmcc
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile