Split & Trim the Frist Name

[crogers111]

CR XI
SQL


Issue: Data entry of different first names for the same individual causes the chart to show a spearate entry for each entry


Sample Data


MemberID FirstName LastName Total
E224 Jan Long 800
E224 Jan P Long 500
E455 John Smith 1100
E698 A J Jones 700
E698 AJ Jones 600


I need to show the names on the chart, so my thought is to split & parse the first name so it consisent for each member.
My initial thought is to strip the middle initial from FirstName where it exists and for intials like: A J if len(FristName) = 3 then remove the space and put the intials together like: AJ I'm not sure how to accomplish this.


The report itself groups on MemberID and has a Group Selection based on the Total per MemberID....so I don't think I have to worry about 2 individuals with the same name

I need help with the formula to split and parse the first name. Or is there is a better way to go about this
so that only 1 name shows for each individual on a chart ? Currently I have the chart set where 'on change of'is a concatenation of FirstName & Lastnname.

Thanks

 

[IanWaterman]

Can you group by Member ID, sum your total and show Max(name Formula)

Place all this in MemberId group footer and suppress details and header.

Then chart off the group summary

Ian

 

[crogers111]

Thanks for the input.

I gave this a try but it doesn't appear this will work for a couple of reasons...

- I created a maximum on @FullName and placed this summary in the MemberID footer, but it is not available in the Chart Expert for 'on change of'. Not sure why. I cannot suppress the details and group header but they are hidden as this is a 'drill-down' report that needs to have this functionality.

@FullName
{Data.FirstName} & " " & {Data.LastName}


- Also, I noticed a user that was married and the Max(FullName) appears to pull her maiden name, not her current name. (I follow why it does that)

Maiden Name: Sally M Lee
Current Name: Sally M Doyle

There is a DatePaid Field I did not list in my sample. I have been playing around with formulas & SQL Expressions trying to pull the First Name & Last Name based on the latest/Maximum Date but no luck yet...thus I thought the split First name option might be the path to pursue.

 

[crogers111]

I believe I finally figured this out with 2 SQL Expressions that pull the most recent First and Last Name for each Member based on the Checkdate. With not a lot of experience using SQL Expressions, getting the syntax correct with the quotes and aliases is quite a challenge. In the below, D is the alias and if I follow correctly, an alias must be used since the same table is used in the report.


%LastName
(Select TOP 1 "LastName"
from Data D
where D."MemberID" = Data."MemberID" AND D."Checkdate" =
(
select max("Checkdate")
from Data D
where D."MemberID" = Data."MemberID" )
)


%FirstName
(Select TOP 1 "DEP_FirstName"
from Data D
where D."MemberID" = Data."MemberID" AND D."Checkdate" =
(
select max("Checkdate")
from Data D
where D."MemberID" = Data."MemberID" )

)

I then have a CR formula to concatenate the 2 names which I can display in my MemberID GF and use in the chart

@PatientName
ProperCase({%FirstName}) & " " & Propercase({%LastName})