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

Pachecda
Pachecda asked
on
Medium Priority
3,882 Views
Last Modified: 2008-03-04
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

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

HTH,
Lady Linet
Mike McCrackenSenior 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.

Add a formula to the report header
Name - DeclVars
Formula
Global StringVar MyRecord := "";

Add a formula to the Group header
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


Author

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.

Author

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
Mike McCrackenSenior 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

Author

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

Now to Answer your questions.

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
Report Header: Formula= DeclVars
  Global StringVar MyRecord:="";
Page header: Fields UserID, UserName
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.




Mike McCrackenSenior 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.

Put the Column headers in the page header

mlmcc
Mike McCrackenSenior 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

Author

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
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Author

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.

Ask the Experts
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad i could help

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.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
ovrdrvn  - Please ask a new question.

mlmcc
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.