Writing Code: Trying to get a proper record selection

Print Page | Close Window

Trying to get a proper record selection

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19062
Printed Date: 02 May 2024 at 11:00pm

Topic: Trying to get a proper record selection

Posted By: Alb2tead
Subject: Trying to get a proper record selection
Date Posted: 07 Feb 2013 at 9:58am

I am trying to get a list of physicians to monitor their credentials. This is the formula that I am using for the record selection:

IsNull({STAFF_TERMINATION_HISTORY.TERMINATION_DATE})

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

OR
{SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} > {STAFF_TERMINATION_HISTORY.TERMINATION_DATE}

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

The problem that I am having is that the formula directly after the "or" is causing people to show up that are no longer employed. Our system tracks the termination date and the registration date and sometimes people have four or five stints of employment. I am only wanting if the registration date is after the latest termination, but this formula pulls for all terminations. How can I recode the formula to get the desired results.

Replies:

Posted By: hilfy
Date Posted: 07 Feb 2013 at 11:45am

You'll need to use parentheses to "group" the two different sets of criteria. Something like this:

(

IsNull({STAFF_TERMINATION_HISTORY.TERMINATION_DATE})

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

)
OR

(
{SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} > {STAFF_TERMINATION_HISTORY.TERMINATION_DATE}

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

)

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Alb2tead
Date Posted: 08 Feb 2013 at 3:32am

Thanks for you help, but this gave the same results.

What is happening is the part of the code:

{SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} > {STAFF_TERMINATION_HISTORY.TERMINATION_DATE} is returning multiple values which I have suppressed. But the newest value is still showing up on the report, even if the person is no longer with us. What I am trying to do is get the newest registration date be greater than the newest termination date, if that makes sense.

Posted By: hilfy
Date Posted: 08 Feb 2013 at 3:54am

First, you need to check the joins. Go to the Database Expert and look at the way your tables are linked together. You need to make sure that the link TO

STAFF_TERMINATION_HISTORY is a left outer join. This link needs to be FROM the SITE_SPECIFIC_STAFF_ENROLLMENT table or, if you have it, some sort of STAFF master table which would also be linked to SITE_SPECIFIC_STAFF_ENROLLMENT. The direction and configuration of the joins is important!

Then simplify the selection criteria:

(

IsNull({STAFF_TERMINATION_HISTORY.TERMINATION_DATE})

{SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} > {STAFF_TERMINATION_HISTORY.TERMINATION_DATE}

)

And

{STAFF_ENROLLMENT_HISTORY.PRACTITIONER_CATEGORY_VALUE} In ["PHYSICIAN", "PSYCHIATRIST"]

And

{STAFF_ENROLLMENT_HISTORY.STAFFID} <> "000004"

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Alb2tead
Date Posted: 12 Feb 2013 at 4:33am

I did it how said and I am pulling more people because the

IsNull({STAFF_TERMINATION_HISTORY.TERMINATION_DATE}) is on an or and does not have the "Physician" "Psychiatrist" drill-down. Is there any way to get the most recent {SITE_SPECIFIC_STAFF_ENROLLMENT.REGISTRATION_DATE} and most recent {STAFF_TERMINATION_HISTORY.TERMINATION_DATE}.

After re-linking the tables as you described and used my original formula, (because the new one as stated above came back with people outside the parameter) it gave the same people as before re-linking the tables.

Posted By: hilfy
Date Posted: 12 Feb 2013 at 5:32am

This is a tough one to do just linking tables. How good are you SQL Skills? In this particular situation I usually base the report on a Command (SQL Select statement that I've written) instead of on linked tables.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Alb2tead
Date Posted: 12 Feb 2013 at 5:37am

To be honest, I have never done an SQL. I will look at some informations to see if I can figure it out. Most of my Crystal Report skills are self taught, so I will see if there is some information to help. If I get stuck, I will post. Thanks for you help

Print Page | Close Window