Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Crystal Reports convert data rows to columns

Posted on 2006-03-23
15
Medium Priority
?
2,785 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
0
Comment
Question by:Pachecda
15 Comments
 
LVL 7

Expert Comment

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

HTH,
Lady Linet
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16277627
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
 

Author Comment

by:Pachecda
ID: 16283006
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Pachecda
ID: 16284740
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16285471
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
 

Author Comment

by:Pachecda
ID: 16302356
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16307686
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16307708
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
 

Author Comment

by:Pachecda
ID: 16326201
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16329184
Only by putting them in separate fields in the report.

mlmcc
0
 

Author Comment

by:Pachecda
ID: 16334461
Ok, I will try that. Is that mean I need to add more formulas and modify the original.

Thanks

0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 16338431
YOu would need to create 1 formula for each field.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16366113
Glad i could help

mlmcc
0
 

Expert Comment

by:ovrdrvn
ID: 16388868
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 16397197
ovrdrvn  - Please ask a new question.

mlmcc
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month20 days, 19 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question