Report Design: Pulling different info from 2 fields

Print Page | Close Window

Pulling different info from 2 fields

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17587
Printed Date: 14 May 2024 at 11:30am

Topic: Pulling different info from 2 fields

Posted By: KevV
Subject: Pulling different info from 2 fields
Date 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

Replies:

Posted By: kevlray
Date 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}

Posted By: KevV
Date 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

Posted By: DBlank
Date 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.

Posted By: KevV
Date 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

Posted By: zanyar-Jalal
Date 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.

Posted By: DBlank
Date 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

Maximum( mailto:%7b@DOB%7d,%7btable.groupfield - {@DOB},{table.groupfield })

Posted By: KevV
Date 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

Posted By: DBlank
Date 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?

Posted By: KevV
Date 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

Posted By: DBlank
Date Posted: 19 Sep 2012 at 7:09am

make a formula field to combine the ID and name into one string

//name

totext(table.id,0,"") + " " + table.name

insert a crosstab in the report header

for row use the Name fomrula

for column use CUSTOMDEFNAME field

for the sumamrized field use CUSTOMDATA field set to a maximum

play with the formatting until you get it the way you want it

Posted By: KevV
Date Posted: 19 Sep 2012 at 11:39am

I got an error when I tried that formula because the field the number is in is already a string. I took the totext out of the statement but then I get an error saying a ";" is expected.

Thanks

KevV

Posted By: DBlank
Date Posted: 19 Sep 2012 at 11:43am

i gave you crystal syntax so make sure the crop dowm in the formual editor is set to "crystal syntax"

if you still get the error please copy and past the exact formula you are using

Posted By: kevlray
Date Posted: 19 Sep 2012 at 12:24pm

From KevV comments, I think the formula should be:
table.id+ " " + table.name

Posted By: DBlank
Date Posted: 20 Sep 2012 at 3:37am

I assumed that table.id+ " " + table.name was what was being used when the totext was dropped form the formula but the ";" eror was still happening.

If that was not the case then Kevlray's post would clearly be the next step to try.

Posted By: KevV
Date Posted: 20 Sep 2012 at 4:37am

I did get it to work the only problem is that it shows all the fields in the CUSTOMDEFNAME column and there are a few I dont want to see. It also dosen't give me the ability to arrange them on the report. The other way is close I just need to figure out how to get the two lines to combine and show the ID number and SS number on the same line.

Thanks

KevV

Posted By: DBlank
Date Posted: 20 Sep 2012 at 5:44am

easy way

create one running total per type youwant to see

example

RT name=DOB

field to summarize=CUSTOMDATA

type = maximum

evaluate=use a formula

{VP_PERSONCUSTDATA.CUSTOMDEFNAME}="Birthday"

reset=on change of group (select customer group)

place in group footer (RTs do not work in headers)

repeat and change the formula for each type

Posted By: DBlank
Date Posted: 20 Sep 2012 at 5:45am

You can go the route your were going an create one fomrula field per type

if{VP_PERSONCUSTDATA.CUSTOMDEFNAME}="Birthday" then {VP_PERSONCUSTDATA.CUSTOMDATA}

Place it ont he detail row and make sure it works correctly

then insert a summary as a maximum of this formual field at the group level

repeat both steps per type

Posted By: KevV
Date Posted: 20 Sep 2012 at 7:36am

I tried your second suggestion and I ended up getting a lot of extra records. I did it the first way and it looks good. I have a few reords that are showing all of the information like address and phone number but all the information that comes from the {VP_PERSONCUSTDATA.CUSTOMDEFNAME} field is blank. I am figuring it must be something in a record that is messing it up because if one of the numbers is missing on the report all of them are. I just have not figured it out yet. I checked the records in the tables and the information is there and formatted the same it just isn't pulling it.

Thanks

KevV

Posted By: DBlank
Date Posted: 20 Sep 2012 at 7:40am

i neglected to state that in both instances you should suppress the detail section and only show the group header or footer.

If you have more than one table check your joins to try and see if that explains the missing data

Also review any select statement

Posted By: KevV
Date Posted: 20 Sep 2012 at 9:15am

I did suppress all but the group footer and got just the one line. On the tables they are only joined by the id and the only select statement I have is a date range.

KevV

Posted By: DBlank
Date Posted: 20 Sep 2012 at 9:22am

It still might be the combinataion of the join and the select statement.

you have to think it through or replicate the data set in sql and look at the data set int he raw there.

If you look in a table and the data is there but it is not in the data set for the report the most logical reason is your join and/or select statement has filtered it out.

Posted By: KevV
Date Posted: 20 Sep 2012 at 10:32am

If it were a join issue would it not cause the fields to be blank in all of the people and not just a few?

KevV

Posted By: DBlank
Date Posted: 20 Sep 2012 at 10:34am

are the fields blank in the detail row or in your formula results?

Posted By: KevV
Date Posted: 20 Sep 2012 at 10:39am

In the formula results. I do not have anything in the detail row. It is all in the group footer.

KevV

Posted By: DBlank
Date Posted: 20 Sep 2012 at 10:41am

but is the 'raw' data in the data set as you expect it to be (which you can only see in the detail row).

Place the 'missing' raw fields in the details and see if it is in the data set.

You have to figure out if your formulas are wrong or the data set is wrong.

Posted By: KevV
Date Posted: 20 Sep 2012 at 12:01pm

I put it in there and I end up with around 25 to 30 rows for each person and the number shows sometimes but not always.

KevV

Posted By: DBlank
Date Posted: 20 Sep 2012 at 12:03pm

if it is not what you expect to see then you have to fix your join or your select statement.

I do not know your data (tables) or the relationship between them so it is hard to trouble shoot this for you.

Print Page | Close Window