Crystal Reports convert data rows to columns

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
PachecdaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

HTH,
Lady Linet
0
mlmccCommented:
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


0
PachecdaAuthor 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.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

PachecdaAuthor 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
0
mlmccCommented:
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
0
PachecdaAuthor 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.




0
mlmccCommented:
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
0
mlmccCommented:
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
0
PachecdaAuthor 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
0
mlmccCommented:
Only by putting them in separate fields in the report.

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

Thanks

0
mlmccCommented:
YOu would need to create 1 formula for each field.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Glad i could help

mlmcc
0
ovrdrvnCommented:
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.
0
mlmccCommented:
ovrdrvn  - Please ask a new question.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.