Solved

Crystal Reports, 9, Formula Assistance

Posted on 2007-12-04
5
2,415 Views
Last Modified: 2010-04-21
I need some help on a report. My report currently has 5 detail bands (A - E) and each utilize the @AgeNumber formula. Its seems the weeks and months at certain levels trigger the wrong form. I just came across an 8 week old and a 7 month old that were incorrect and printing the 7-18 yr forms. I am a bit stumped as to where the defining problem is. Perhaps I should rework this entirely to pull the right form. Any questions, please ask. I appreciate any help!

@AgeNumber Formula
StringVar yeardiff := LEFT(totext(YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate})),LENGTH(totext(YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate})))-3);
StringVar yeardiff_minus1 := LEFT(totext((YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate}))-1),LENGTH(totext((YEAR(CurrentDate)-YEAR({SB1001_main.PatientBirthdate}))-1))-3);
NumberVar birthmonth := MONTH({SB1001_main.PatientBirthdate});
NumberVar currentmonth := MONTH(CurrentDate);
StringVar monthdiff := LEFT(totext(MONTH(CurrentDate)-MONTH({SB1001_main.PatientBirthdate})),LENGTH(totext(MONTH(CurrentDate)-MONTH({SB1001_main.PatientBirthdate})))-3);
NumberVar birthday := DAY({SB1001_main.PatientBirthdate});
NumberVar currentday := DAY(CurrentDate);
StringVar daysdiff := LEFT(TOTEXT(CurrentDate - {SB1001_main.PatientBirthdate}),LENGTH(TOTEXT(CurrentDate - {SB1001_main.PatientBirthdate}))-3);
//StringVar noweeks := LEFT(TOTEXT(ROUND(ToNumber(daysdiff)/7,0)),LENGTH(TOTEXT(ToNumber(daysdiff)/7))-3);
//StringVar nomonths := LEFT(TOTEXT(ROUND(ToNumber(daysdiff)/30,0)),LENGTH(TOTEXT(ToNumber(daysdiff)/30))-3);

StringVar noweeks := LEFT(TOTEXT(Truncate(ToNumber(daysdiff)/7,0)),LENGTH(TOTEXT(Truncate(ToNumber(daysdiff)/7,0)))-3);
//StringVar nomonths := LEFT(TOTEXT(Truncate(ToNumber(daysdiff)/30,0)),LENGTH(TOTEXT(Truncate(ToNumber(daysdiff)/30,0)))-3);
NumberVar diffinmonths := DATEDIFF('m', date({SB1001_main.PatientBirthdate}), CurrentDate);
NumberVar diffindays := DATEDIFF('d', DATEADD('m', diffinmonths, date({SB1001_main.PatientBirthdate})), CurrentDate);

IF diffindays < 0 THEN
    diffinmonths := diffinmonths - 1;
StringVar nomonths := LEFT(TOTEXT(Truncate(ToNumber(diffinmonths),0)),LENGTH(TOTEXT(Truncate(ToNumber(diffinmonths),0)))-3);  


StringVar nomonths_plus12 := LEFT(TOTEXT(12 + MONTH({SB1001_main.PatientBirthdate})),LENGTH(TOTEXT(12 + MONTH({SB1001_main.PatientBirthdate})))-3);

NumberVar diffone = ROUND(ToNumber(daysdiff)/30, 0);
NumberVar difftwo = Truncate(ToNumber(daysdiff)/31, 0);


IF ToNumber(daysdiff) > 119 AND ToNumber(daysdiff) <= 730 THEN
IF diffone <> difftwo THEN
    nomonths := LEFT(TOTEXT(Truncate(ToNumber(daysdiff)/31,0)),LENGTH(TOTEXT(Truncate(ToNumber(daysdiff)/31,0)))-3);

IF ToNumber(daysdiff) >= 0 AND ToNumber(daysdiff) <= 6 THEN
daysdiff
ELSE
IF ToNumber(daysdiff) > 6 AND ToNumber(daysdiff) <= 119 THEN
noweeks
ELSE
IF ToNumber(daysdiff) > 119 AND ToNumber(daysdiff) <= 730 THEN
nomonths
ELSE
IF ToNumber(daysdiff) > 365  AND ToNumber(daysdiff) <= 730 THEN
nomonths_plus12
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth = currentmonth  AND currentday > birthday THEN
yeardiff
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth = currentmonth  AND currentday < birthday THEN
yeardiff_minus1
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth = currentmonth  AND currentday = birthday THEN
yeardiff
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth > currentmonth  THEN
yeardiff_minus1
ELSE
IF ToNumber(daysdiff) > 730 AND birthmonth < currentmonth  THEN
yeardiff


Birth to 6 yrs Old. (Details A)

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'

OR 
 

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'F'

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'
 

Female 7 - 18 yrs old (Details B)

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'

OR 
 

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'F'

OR
 

(ToNumber({@AgeNumber}) in (0 to 6))

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'
 

Male 7 - 18 yrs old (Details C)

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'

OR 
 

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'F'

OR
 

(ToNumber({@AgeNumber}) in (0 to 6))

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'
 

Adult Female (over 18 yrs old) (Details D)

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'M'

OR 
 

(ToNumber({@AgeNumber}) in (0 to 6))

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'
 

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'
 

Adult Male (over 18 yrs old) (Details E)

ToNumber({@AgeNumber}) >= 19 and {SB1001_main.PatientSex} = 'F'

OR 
 

(ToNumber({@AgeNumber}) in (0 to 6))

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'M'
 

OR
 

(ToNumber({@AgeNumber}) in (7 to 18)) and {SB1001_main.PatientSex} = 'F'

Open in new window

0
Comment
Question by:Jeff S
  • 3
  • 2
5 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 20407562
Rather than trying to work through your formula, what are you trying to do?

What numbers do you need from the formula?
Years, months, weeks, etc?

What does your data look like?
Is it a number, string or date field?

mlmcc
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20408383
Cut and dry, this report was formatted by an ex-employee and i am trying to fix it . What I need is to have it pull certain detail bands when a criteria is met (age and sex). I have coded in SQL the patients age ... and this report is currently pulling in the sex. Maybe with your help I can part with this @AgeNumber formula all together and edit the suppress statements?!
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 20416979
Let me look at it in more detail.  Does the formula work in most cases?

Do you have some sample data that I could test with (even just the age or date field and the sex field)

What data types are the fields?

mlmcc
0
 
LVL 7

Author Comment

by:Jeff S
ID: 20450760
Ok this formula was giving me some nasty headaches. So I went into SQL and coded Age. I have attached a section of this Stored Procedure and attached it below.  I need 5 Suppress statements now that pull based off gender and age ranges (if possible).
Declare @ApptDate datetime

Select @ApptDate = a.ApptStart

FROM    Appointments a

WHERE   a.AppointmentsId = @AppointmentsId
 

SELECT  '290.PatientName'=IsNull(pp.First,'') + ' ' + isnull(pp.Middle,'') + ' ' + isnull(pp.Last,'')+ ' ' + isnull(pp.Suffix,''), 

        '291.PatLast'=IsNull(pp.Last,''),

        '292.PatFirst'=IsNull(pp.First,''),

        '293.PatMiddle'=IsNull(pp.Middle,''),

        '294.PatientAddr1'=IsNull(pp.Address1,''),

        '295.PatientAddr2'=IsNull(pp.Address2,''),

        '296.PatientCity'=IsNull(pp.City,''),

        '297.PatientState'=IsNull(pp.State,''),

        '298.PatientZip'=IsNull(pp.Zip,''),

        '299.PatientCountry' = ISNULL(pp.Country,''),

        '300.PatientBirthdate' = pp.Birthdate,

        '301.PatientSex'=IsNull(pp.Sex,''), 

        '302.PatientPhone1'=IsNull(pp.Phone1,''),

        '303.PatientSSN'=IsNull(pp.SSN,''),

        '304.PatOccupation'=IsNull(pp.EmpOccup,''),

        '305.PatSchool'=IsNull(pp.MedicalRecordNumber,''),

        '306.PatBudget'=IsNull(g.Budget,0),

        '307.PatientSameAsGuarantor'=IsNull(pp.PatientSameAsGuarantor,0),

		'308.PatSuffix'=IsNull(pp.Suffix,''),

		'400.PatientAge' = CASE	WHEN datediff(day, pp.birthdate, @ApptDate) <= 6 THEN cast(datediff(day, pp.birthdate, @ApptDate) as varchar(10)) + ' dys'

			WHEN datediff(day, pp.birthdate, @ApptDate) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 7) as varchar(10)) + ' wks'

			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) as varchar(10)) + ' mos'

			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(@ApptDate) THEN cast(datediff(month,pp.birthdate, @ApptDate) - 1 as varchar(10)) + ' mos'

			WHEN floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, @ApptDate) as decimal) / 365.25) as varchar(10)) + ' yrs'

			ELSE '' END

FROM    PatientProfile pp 

		LEFT JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId

WHERE	pp.PatientProfileID = @PatientProfileId

Open in new window

0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31412716
Thanks for looking and helping ... your tips helped
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now