Selecting Records within Group Selection Criteria

[talibm]

Hi all,

Last August I created a report using Crystal Reports XI. The report is one that displays the payers for each client. A client can have multiple payers. What I am trying to do is select all clients with payer #5000 and clients that have both #5000 and #1000 payer. The report has a date range. In the selction criteria I treied and payer Id # in ["5000", "1000"], but this give me cliients with just #1000 which I do not want. typical client records are like:
client Smith
payer 5000
payer 1000
payer 9999

Client Jones
payer 5000
payer 1500

pmax9999 provided me with a solution that work very well. See (thread767-1716506)

This is a little different to how I understood it from your first post, so the {@Player_Test} formula needs to change as follows:


CODE --> {@Player_Test}
If {Table.PayerID} = '5000'
Then 'A'
Else
If {Table.PayerID} = '1000'
Then 'B'
Else 'C'
The Group Selection Code should look like this:


CODE --> GroupSelection
Minimum({@Payer_Test},{Table.Client}) = 'A' and
Maximum({@Payer_Test},{Table.Client}) in ['A', 'B']


Now the report has evolved to where I need to add the services provide to each client with these payers. For example i want services for payer 5000 and 1000 or just payer 5000 or just payer 1000. My problem is that when a client has services for 1000 they are not appearing in the report if there are no services for payer 1000. I hopethis makes sense. thanks

talib

 

[pmax9999]

If I understand the changed requirements correctly, you want to see records that have:

1. Only a payer of 1000;
2. Only a payer of 5000; or
3. Payers of Both 1000 and 5000.

If this is what you are looking for, replace the {@PayerTest} formula with the following code:

If      {Table.PayerID} in ['5000', '1000']
Then    'A'
Else    'C'

Then replace your Group Selection formula with the following code:

Maximum({@Payer_Test},{Table.Client}) = 'A'

Let me know how it goes.

Cheers
Pete

 

[pmax9999]

Sorry talib, just realised there is a floor in my code. The Group Selection formula should be:

Minimum({@Payer_Test},{Table.Client}) = 'A' and
Maximum({@Payer_Test},{Table.Client}) = 'A'

Cheers
Pete

 

[talibm]

Hello Pete,

The first solution was almost perfect, well it was perfect at that time. Then things changed. Attached to the payers are services. The 5000 payer is still a base requirement and 1000 when present with 5000. Initially I was asked for services for 1000 when their were services for the 5000 payer. What this did not provide was services for the 1000 payer if the were no 5000 payer services. I need the 1000 services in the scenario where both 5000 and 1000 guarantor are present but no services exist for the 5000 payer. their is client table, the payer table and services table
client table - guarantor table relationship one-to-many
payer table - services table relationship one-to-many

When client with 5000 and 1000 payers has services for 1000 payer only neither client nor services appear in the report. I hope this makes sense. I can see the client and services in the preview pane but they do not appear in the report. thanks for all your help.

talib

 

[pmax9999]

Hi talib

I suspect this is to do with the table joins but am having trouble understanding the data. Can you please:
1. Confirm you are using an Outer Join from the payer table to the services table;
2. Provide some sample data showing what should and shouldn't be returned (as you did in your first post, but expanded to include services data); and
3. Post a copy of your Record Selection formula (Outer Joins can be over-written if fields are referred to within the Record Selection).

Cheers
Pete

 

[talibm]

Hello Pete,

I have a left outer join from the payer table to the services table. I have parameter fields for service start date and service end date. the data should look like this:
date range 04/10/2014 to 04/15/2014

client Smith
payer 5000
04/15/2014 Counseling service 60 min
04/19/2014 Counseling service 60 min
payer 1000
04/11/2014 assessment service 120 min

Client Jones
payer 5000
no service
payer 1000
04/10/2014 assessment service 180 min

client Green
payer 5000
04/12/2014 counseling service 60 min

service not wanted
client johnson
payer 1000
04/12/2014 counseling service 60 min

client Brown
payer 1500
04/14/2014 assessment service 120 min

the first scenario is good client has services for both payers 5000 and 1000. the second scenario is good. Client has both payers but services for only the 1000 payer. this is where I have the problem. The report does not print the services for 1000 payer in the 5000 payer does not have any services. I need it to print when both 5000 and 1000 have servcies and I need it to print if the client has both payers and only 1000 payer has services and also if the client only has 5000 payer with or without services.

record selection:

{billing_guar_subs_data.PAYER_ID} in ["1000", "5000"] and IsNull({billing_plan_assigned.level_end_date}) and
{billing_tx_charge_detail.date_of_service} in {?Start Date} to {?End Date} and
{billing_tx_charge_detail.payer_liability} > 0.00

I hope this makes some sense. Thanks again Pete for all your help with this

talib

 

[pmax9999]

I think the problem here is the second line in your Record Selection "{billing_tx_charge_detail.date_of_service} in {?Start Date} to {?End Date} and".

In the Client "Jones" example above, there is no Service that meets the date range requirement (or possibly no service at all for that payer), and therefore there will be no record where the payer is 5000. If you remove that line from the record selection, do you get the Jones record?

We can fix this with some changes to the Record Selection but without understanding the table structure it is very difficult. To help me understand that structure, please post the SQL Query generated by the report (Database => Show SQL Query). Please also explain why the Jones example should appear on the report when the service dates do not meet the record selection requirements.


Cheers
Pete

 

[talibm]

Hi Pete,

this report is basically a payer 5000 report. I was then asked to added the 1000 payer with services for both. The latest request was to show the payer 1000 services even when there are no services for the 5000 payer, but the client must have the 5000 payer even if there are no services during the selected date range. SQL below

SELECT DISTINCT "billing_tx_charge_detail"."GUARANTOR_ID", "billing_tx_charge_detail"."v_service_value", "billing_tx_charge_detail"."date_of_service", "billing_tx_charge_detail"."PATID", "billing_tx_charge_detail"."v_client_name", "billing_plan_assigned"."max_monthly_responsibility", "billing_tx_charge_detail"."service_status_value", "billing_tx_charge_detail"."v_claim_date", "billing_plan_assigned"."level_end_date", "billing_guar_subs_data"."GUARANTOR_ID", "billing_tx_charge_detail"."billing_units", "billing_tx_charge_detail"."v_PROVIDER_NAME", "billing_tx_charge_detail"."JOIN_TO_TX_HISTORY", "billing_tx_charge_detail"."guarantor_liability"
FROM ("SYSTEM"."billing_guar_subs_data" "billing_guar_subs_data" LEFT OUTER JOIN "SYSTEM"."billing_tx_charge_detail" "billing_tx_charge_detail" ON ((("billing_guar_subs_data"."PATID"="billing_tx_charge_detail"."PATID") AND ("billing_guar_subs_data"."EPISODE_NUMBER"="billing_tx_charge_detail"."EPISODE_NUMBER")) AND ("billing_guar_subs_data"."GUARANTOR_ID"="billing_tx_charge_detail"."GUARANTOR_ID")) AND ("billing_guar_subs_data"."FACILITY"="billing_tx_charge_detail"."FACILITY")), "SYSTEM"."billing_plan_assigned" "billing_plan_assigned"
WHERE (("billing_tx_charge_detail"."PATID"="billing_plan_assigned"."PATID") AND ("billing_tx_charge_detail"."FACILITY"="billing_plan_assigned"."FACILITY")) AND "billing_tx_charge_detail"."guarantor_liability">0 AND ("billing_tx_charge_detail"."date_of_service">={d '2014-01-01'} AND "billing_tx_charge_detail"."date_of_service"<={d '2014-01-31'}) AND "billing_tx_charge_detail"."v_service_value" NOT LIKE '%Rolled%' AND ("billing_guar_subs_data"."GUARANTOR_ID"='1000' OR "billing_guar_subs_data"."GUARANTOR_ID"='5000') AND "billing_plan_assigned"."level_end_date" IS NULL


thanks

t

 

[Charliy]

I had a feeling this was Avatar. Post this in the Crytal Reports group on Netsmart Community, I'm sure John Sawyer will have the perfect SQL for you very quickly - he lives for that stuff.

 

[pmax9999]

Hi talibm

As I suspected, the inclusion of fields from the table "billing_tx_charge_detail" in the Record Selection formula are over-riding the Outer Join to that table.

I am happy to keep working with you on this if you want, however given charliy's comments that there is a specialist forum for this application, that may be the best place to go for assistance.

I would need to clarify exactly what records should be returned (please remember I have no knowledge of your system or its data so you need to explain it in basic logic). My understanding is that the following records should be included:

[ul]
[li]Any with Payer 5000 alone - with services in the date range[/li]
[li]Any with Payer 5000 and 1000, both with services in the date range[/li]
[li]Any with Payer 5000 (no services) and 1000 (with services in the date range)[/li]
[/ul]
If you would like me to continue assisting, please confirm whether my understanding is correct.


Cheers
Pete

 

[talibm]

Hello Pete,

I did post this thread to to the Netsmart Community and received a response from John, but I am still trying to get this to work. I would like for you to continue to help me with this. You are right in the records that I want to appear in the report. thanks

talib

 

[pmax9999]

OK, this might require a bit of going backwards and forwards unfortunately. It looks like we may be in different time-zones (I am in Australia) which is going to drag it out a little longer than it otherwise should.

I am a little confused by the SQL Query you posted. The criteria in the WHERE clause are not reflected in the Record Selection formula you posted, which suggests that you may be using a Command, but if that was the case I would not expect to see any Record Selection formula at all (all of the selection criteria should be in the Command itself). Also I noticed that the join to "billing_plan_assigned" table has been done via the Where clause rather than a Link in the Database Expert and while this may be possible via the Record Selection formula, again the code you posted does reflect such an approach (and having never tested it I am not absolutely certain it would even work).
[ol 1]
[li]Did you use the standard "drag and drop" approach to adding/linking tables of have you used a Command?[/li]
[li]Please advise whether what you posted above as the Record Selection formula is the entire code. If not, please post the entire code.[/li]
[/ol]

Once I get the answers to these questions I hope to be in a position to provide some guidance.

Cheers
Pete

 

[talibm]

Hi Pete,
Sorry for the delay in responding. I had an emergency to deal with. To answer your questions I used drag and drop. I did not use a command to create the report. What I posted was the entire sql query. I also used the group selection formula you gave me to solve my initial problem. I hopr this helps and thanks for all your help.

talib

 

[pmax9999]

As I mentioned previously, the SQL generated by the report looks strange in that the join between billing_tx_charge_detail and billing_plan_assigned is in the Where clause which I have not seen Crystal do before.

Are you able to post a copy of the report somewhere I can access it (DropBox, SkyDrive or similar)? If not, can you post an image of the Database Expert Linking screen so as to confirm that the two tables in question have been liked there.

As requested in my previous post, please confirm that the Record Selection Formula you posted in your original post is all of the code. If not, please post the entire formula.

I am also curious about the advice given to you by John at the Netsmart Community. Are you able to post that here - it might help me to understand the data.

Apologies for all of the questions, but if I don't understand the problem I am very limited in the advice I can give.

Cheers
Pete

 

[talibm]

Hello Pete,

Below is John's response. Basically it boils dwon to how to I get the report to print services for the 1000 payer where there are null values the 5000 guarantor. I am not sure how I go about get screen shots to you or a copy of the report, sorry. I am juggling a lot of things at the moment so sometimes it takes a little time for me to respond and our time diffences. I am in Ohio (USA). thank you again for taking the time to help me with this. thanks

talib


You are left joining to billing_tx_charge_detail, but in your WHERE statement, you are not allowing for the fact that you want to allow cases where there is NO record in that table for what you want to see
If you want to see 5000 guarantors where there is no liability, then you need to allow for nulls in the charge detail table
Easier way IMO is to use a sub-select either within this comand or as its own stand alone command
Something like:
SELECT patid , episode_number , facility
FROM billing_guar_order_current where guarantor_id = '5000'
would be your distinct list of clinets with a current 5000 guarantor in their financial eligibility

 

[pmax9999]

You didn't answer my question as to whether the Record Selection formula code you posted previously was complete. Assuming it was, please try replacing it with the following code:

[Code Record_Selection]
(
IsNull({billing_plan_assigned.level_end_date}) and
{billing_guar_subs_data.PAYER_ID} in ["5000", "1000"] and
{billing_tx_charge_detail.date_of_service} in {?Start Date} to {?End Date} and
{billing_tx_charge_detail.payer_liability} > 0.00
)
or
(
IsNull({billing_plan_assigned.level_end_date}) and
IsNull({billing_tx_charge_detail.date_of_service}) and
{billing_guar_subs_data.PAYER_ID} = "5000" and
{billing_tx_charge_detail.payer_liability} > 0.00
)
[/Code]

You will still need to use the {@Player_Test} formula and Group Selection formula from the previous thread.

I am not able to test this, and am still confused by the SQL Query generated by Crystal so I am far from convinced it will work, but is really all I can offer at this point.

I hope it helps.

Cheers
Pete

 

[talibm]

Thanks Pete,

Yes, the record selection formula code was complete. I will give this a try. With the existing record selection formula the services for payer '1000' appear in the report with or without the presence of services for the '5000' payer. However, when the group selection formula is added the the clients with '1000' payer without services for the '5000' do not appear in the report. I am trying to figure out how to modify the group selection criteria to include the clients with both payers and services for only the '1000' payer. thanks

talib

 

[talibm]

Hello Pete,

I tried your last suggestion, but the services for the '1000' payer with null value for services for the '5000' payer are still not showing up in the report. Not sure where to go from here, but I will keep at it. thanks for all the time you spent helping me with this. cheers

talib

 

[pmax9999]

Without seeing the actual report it is difficult to know where to go next with this.

If you are prepared to make a copy of the report (with data saved) available via Dropbox Skydrive or similar, I am happy to take a look at it but otherwise there isn't anything more I can offer.


Cheers
Pete