Print Page | Close Window

one to many selection

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=19616
Printed Date: 06 May 2024 at 1:09pm


Topic: one to many selection
Posted By: TorLang
Subject: one to many selection
Date Posted: 06 Jun 2013 at 5:24am
Issue:

Need to pull patients that only have a Primary Insurance.

Patient-------------Primary Insurance
           -------------Secondary Insurance
           -------------Tertiary Insurance

Somehow I need to have the report scan through the insurance table and if it finds a primary insurance, pull the record. If if findsa  primary and a Secondary, don't pull the record.

Obviously, it the patient does not have a Secondary, it won't have a tertiary either.

All my data is placed in a group footer (a patient / record pr. line, because I am only summarizing. (Patient total charges, patient total payment etc)

I need to select only on primary insurance in report selection. Pri / Sec / Ter Insurances have rank numbers, so I know what rank number to select on.

I suspect I somehow need to use a variable to do this but unsure as to how I would set this up.

Appreciate any insight.

 


Replies:
Posted By: DBlank
Date Posted: 06 Jun 2013 at 6:13am
you would have to use group select in crystal unless you want to write a command or stored procedure to do it.
in crystal pull all the patients and insurances
group on the client
insert a summary as the max value of insurance rank per patient
you an now use the group select as
maximum(rank,patient)=1
 
be aware that for other summarizations you will need to use variable formulas or running totals to do counts,sums, averages, etc. on the remaining records. Running Totals are created in a pass after the group select occurs.

Posted By: TorLang
Date Posted: 07 Aug 2013 at 7:08am
That works, but it looks like the record is still there, just with a blank secondary and tertiary insurance. I don't need to select those records at all (with sec. or tert. insurance)

Posted By: DBlank
Date Posted: 07 Aug 2013 at 10:52am
did you use a group select or record select?

Posted By: TorLang
Date Posted: 08 Aug 2013 at 3:48am
A group select..

Posted By: DBlank
Date Posted: 08 Aug 2013 at 4:35am
the recoerds will show up in the group tree but they should not show up in the body of the report.
Can you show some rows of data at the detail level before you apply the filter?

Posted By: TorLang
Date Posted: 08 Aug 2013 at 5:12am
Yep, moving my data to the detail line shows one record pr. insurance. If the patient have 3 insurances, I see 3 lines of records.

Posted By: DBlank
Date Posted: 08 Aug 2013 at 5:51am
is this correct...
In your detail row you have one field that is called something like Rank.
This has a 1, 2 or 3 in it for primary, secondary or tertiary.
if a client only has a primary there is only one row for that client with a Rank of 1.
If they have a secondary insurance they have two rows of data where one row of data has a rank of 1 and another row has a rank of 2?
 
Or is the set up diffent than that?

Posted By: TorLang
Date Posted: 08 Aug 2013 at 5:58am
Correct. Instead of the rank number, I am using the insurance name. But yes, you are correct. (We're talking records on detail line now)

Posted By: DBlank
Date Posted: 08 Aug 2013 at 6:11am
what is the exact select statement you are using in the group select?

Posted By: TorLang
Date Posted: 08 Aug 2013 at 6:35am
{TPM311_VISIT_PAYOR.pyr_seq_no} = 4981

payer sequence number, i.e. rank number. 4981 is rank number for Primary Ins.

Keeping this select in the group select, removes all sec. and tert. insurances from the screen, but the record count remains the same.

Posted By: DBlank
Date Posted: 08 Aug 2013 at 6:43am
what are the other possible values (code for secondary and tertiary)?
And you cannot have quaternary or more correct?

Posted By: TorLang
Date Posted: 08 Aug 2013 at 6:47am
Primary Insurance - 4981
Secondary Insurance - 4978
Tertiary Insurance - 4980

I know, the rank numbers really don't make sense, but I didn't build it :-)

We never use quaternary ins.

Don't know if this helps, but:

When keeping the data on a detail line, I have:

(if the patient has 3 insurances)

One record with just the tertiary ins.
Next record with secondary and tertiary ins.
Next record with primary, secondary and tertiary ins.

This is without the group select entered in...

Posted By: DBlank
Date Posted: 08 Aug 2013 at 7:28am
you have to use a group condition in the group select to make it include or exclude the whole group
in this case the group is a client
your condition is that you want clients with only a primary insurance
if you insert a summary of the insurance type at the client group level you can use the to find what you want.
Minimum(insurancetype,client)
any clinet group that has the value of 4981 meets your condition
use that in the group select
Minimum(insurancetype,client)=4981

Posted By: TorLang
Date Posted: 08 Aug 2013 at 9:04am
yayy, that worked. thanks :-))

Posted By: TorLang
Date Posted: 11 Sep 2013 at 2:46am
This statement in the group select worked:
Minimum ({TPM311_VISIT_PAYOR.pyr_seq_no}, {TPM300_PAT_VISIT.vst_ext_id}) = 4981.00

So can I use a similar group select to exclude patients with MEDICAID insurance? Not using Minimum of course, but something like:

not {insurance}, {client} = "MEDICAID"

I am sure the NOT statement syntax above is wrong though.

How would you formulate a statement that would exclude records with MEDICAID as primary, secondary or tertiary.

Posted By: DBlank
Date Posted: 11 Sep 2013 at 3:48am
create another formula to 'flag' the records you want
if insurance=medicaid and type in (primary, secondary, tertiary) then 1 else 0
sum this at the group level
sum(flag,client)
now any client with a sum>0 needs to be excluded so you use that in the group select
sum(flag,client)=0

Posted By: TorLang
Date Posted: 11 Sep 2013 at 6:26am
Results look good. I just have to spot check against real patient data. Thanks again.


Print Page | Close Window