Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Pulling different info from 2 fields Post Reply Post New Topic
Page  of 3 Next >>
Author Message
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Topic: Pulling different info from 2 fields
    Posted: 18 Sep 2012 at 10:00am
I thought this would be an easy formula but I cant seem to gert it to work. I have a table that list a persons name and then has two fields called:
 
{VP_PERSONCUSTDATA.CUSTOMDEFNAME} and{VP_PERSONCUSTDATA.CUSTOMDATA}
 
They display info like:
 
{person}   {CUSTOMDEFNAME}   {CUSTOMDATA}
John Doe       Anniversary                01/15/2001
John Doe       Birthday                      09/21/1980
John Doe       SS Number                  524241234
John Doe       ID Number                  012345
 
I am trying to pull and show them in different spots, so if I want just the Birthday info I tried a formula like:
 
if{VP_PERSONCUSTDATA.CUSTOMDEFNAME}="Birthday" then {VP_PERSONCUSTDATA.CUSTOMDATA}
 
but it does not show anything.
 
Thanks
KevV
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 18 Sep 2012 at 10:15am
Is there any chance that there are leading or trailing blanks in the {VP_PERSONCUSTDATA.CUSTOMDEFNAME}field.  If so try this,

if trim({VP_PERSONCUSTDATA.CUSTOMDEFNAME})="Birthday" then {VP_PERSONCUSTDATA.CUSTOMDATA}
IP IP Logged
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Posted: 18 Sep 2012 at 10:28am
I checked and there were no blanks but I did try it just to see and it still did not show anything.
 
KevV
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Sep 2012 at 11:18am
It looks fine....
are you trying to "compress" these multiple rows into one row with multiple columns?
If so and you are placing the formual field on a group footer (for grouping on person) then it is only evaluating for the last row in the group. In your sample data this is the ID number so it is returning "".
 
There are ways to do the conversion of rows into columns. The easiest based on your sample data would be a crosstab.


Edited by DBlank - 18 Sep 2012 at 11:19am
IP IP Logged
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Posted: 19 Sep 2012 at 2:46am
I am grouping by person and seniority which is another field in that column and this formula is in the group footer.
I am not for sure why but as part of the select statement I did find:
 
{VP_PERSONCUSTDATA.CUSTOMDEFNAME} = "ID Number"
 
If I change it to another field then it will show what ever I change it to. If I just take the formula out it displays two lines for each person one with the ID number and one without.
 
KevV
 
IP IP Logged
zanyar-Jalal
Newbie
Newbie
Avatar

Joined: 06 Sep 2012
Location: Iraq
Online Status: Offline
Posts: 12
Quote zanyar-Jalal Replybullet Posted: 19 Sep 2012 at 3:26am
Dear: KevV

as I understood you can create a parameter field on {CUSTOMDEFNAME}, because parameter fields enabled you to chose required data dynamically.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Sep 2012 at 4:02am

if you use a condition in the select expert it will limit all rows of data to only what you tell it in the statement.

As I indicated before, if you palce the formual in a group footer it only evalautes the last row of dat in the group
Again I assume youare trying to show DOB in one column, SSN in another, etc.
You can do this be crating one formula per type and then insert a summary for each fomrual field that is per group leve you want.
example
//DOB
if{VP_PERSONCUSTDATA.CUSTOMDEFNAME}="Birthday" then {VP_PERSONCUSTDATA.CUSTOMDATA}
//summary of DOB for display in group footer or group header
 
IP IP Logged
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Posted: 19 Sep 2012 at 5:58am
I have tried a couple of things but I guess I must be doing something wrong. I took the ID Number out of the select statement and did the two formulas and placed the one in the group footer. Now I get two records showing for each person with the ID Number showing on one line and the Birthday showing on the other.
 
 
KevV
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Sep 2012 at 6:00am
are you trying to get a list of all clients with one column the name, one column the dob, one column the ssn, etc?
basically what do you want your report to look like when you are done?

Edited by DBlank - 19 Sep 2012 at 6:01am
IP IP Logged
KevV
Senior Member
Senior Member


Joined: 19 May 2011
Online Status: Offline
Posts: 106
Quote KevV Replybullet Posted: 19 Sep 2012 at 6:59am
Correct a column for each Person a header with ifo below so something like:
 
ID Number     Name           DOB            Anniversary    SS Number
12345          John Doe    09/21/1980    01/15/2001   524241234
 
What I am getting right now is:
 
 
ID Number     Name           DOB            Anniversary    SS Number
12345          John Doe    09/21/1980    01/15/2001 
                    John Doe    09/21/1980    01/15/2001   524241234
 
KevV


Edited by KevV - 19 Sep 2012 at 7:03am
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.