asked on

Group Selection Question in Crystal Reports

I have a report written in crystal 11 off a sql 2008 server.
In this report, I have a list of patient numbers which has the doctor number that patient saw.
A patient will see the primary doctor and will see a backup doctor.
If I group by the patient number, I will see all the doctor numbers that the patient has seen.

My end result is to create a parameter that the user will use to select the Primary Doctor #, then the data that will show are all the accounts #s that were equal to those of the primary doctor, but have the backup doctors' #.

Example of current record selection:
Acct#	Backup DR	Primary Doctor
2617150	TEST0	TEST0 PORTAL
2617150	TEST0	TEST0 PORTAL
3832232	TEST0	TEST0 PORTAL
4268651	TEST0	TEST0 PORTAL
2617150	TEST1	TEST1 PORTAL
4268651	TEST1	TEST1 PORTAL
2617150	TEST2	TEST2 PORTAL
3832232	TEST2	TEST2 PORTAL
 
If I was to select Test0portal as the my primary dr., I would like the following to return:
 
PORTAL, TEST1		
2617150	TEST1	TEST1 PORTAL
4268651	TEST1	TEST1 PORTAL
		
PORTAL, TEST2		
2617150	TEST2	TEST2 PORTAL
3832232	TEST2	TEST2 PORTAL

Open in new window

Kurt Reinhardt

I'm a little confused by what you want. In your code sample you're selecting one primary doctor, but returning 2 others, thereby excluding the primary doctor you selected. Also, why are you using the group selection editor, which only filters data that's already been returned from the database?

Metalteck

ASKER

I know it sounds confusing. But your right, if I select Test0, I need to get all the records that Test0 has equal to Test1 and Test2, but not include Test0's list.

Mike McCracken

Try selecting records like this

{PrimaryDoctor} = {?DrParameter} AND
( Not IsNull({SecpndaryDoctor}) AND {SecondaryDoctor} <> {?DrParameter})

mlmcc

Metalteck

ASKER

Is this under the record selection or group selection?

Metalteck

ASKER

When I place this formula in the record selection, I'm still getting all the records. Nothing has change.

Mike McCracken

Can you copy the record selection you used and the SQL Crystal generates?

mlmcc

Metalteck

ASKER

{FOLLOW_UP.DOCTYPE_ID} in [679, 202] and
{FOLLOW_UP.TYPE} = "3" and
not isnull({FOLLOW_UP.QUEUE}) and
{FOLLOW_UP.QUEUE}<>{?Dr} and
{PHYSICIAN_FILE.PHYSICIAN_NAME} = {?Dr}

Metalteck

ASKER

This is the output I recieve. All the accounts for Test0 and not Test 1 or 2

Acct#      Backup      Primary Dr
2617150      TEST0      TEST0 PORTAL
2617150      TEST0      TEST0 PORTAL
2617150
3832232      TEST0      TEST0 PORTAL
4268651      TEST0      TEST0 PORTAL

Mike McCracken

What are yiu entering for ?Dr

mlmcc

Metalteck

ASKER

For the parameter I'm entering Portal, TEST0.

Mike McCracken

Then you only get the records where the primary dr is portal, test0, which is what you are getting.

mlmcc

Metalteck

ASKER

Thats the problem, If I select Test0 as the parameter value, I don't want to get the records where the doctor names are test 0, I want to get all the doctors that have the same account as test0.

Ex. Test 0 has accounts 2617150, 4268651 3832232. But Test 1 has 2617150 and 4268651, while test2 has 3832232.

So for a result set, when I select test0, I want to get all accounts equal to test0, but not show test0's results.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial