Selecting Records with Different Values 1

[talibm]

Hi all,

I have 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

client Williams
payer 1000

What I want is for my report to select records similar to the first two and not the last one.

thanks

 

[Madawc]

Do something like

(payer Id #} in ["5000", "1000"] and "payer" in {payer Id #})
or
("5000" in (payer Id #} and "client" in {payer Id #})

Put @Reportable on your report next to the unselected data, to check it shows True or False in the way you want.

Then add @Reportable to your selection statement.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[talibm]

Hi Madawc,

This doesn't seem to work for me. What I want is all the records with payer # "5000" and any records that have payer # "1000". I still want the payer # "5000" records that do not contain payer # "1000". When I used your formula I got true for payer # 1000 but false for 5000.
below is what I used
{payer id} in ["5000", "1000"] and "5000" in {payer id}
or
("5000" in ({payer id}) and "1000" in {payer id})

thanks

talib

 

[talibm]

Hi Madawc,

I don't think that I am explaining what I am trying to accomplish very well. What I have is a report with client records containing information ao payers. Clients typically have multiple payers, Payer 5000 and payer 1000 are the examples I used. What I want the report to do is display all records with payer 5000 exclusively and records that have both payer 5000 and payer 1000. I am using crystal xi. thanks

talib

 

[pmax9999]

A few ways to achieve this, with varying levels of complexity/efficiency.

The simplest way (although not very efficient) would probably be something like this:

1. Use the Record Selection to limit only by Date;

2. Group by Client;

3. Create this formula and place in details section
[Code {@PayerTest}]
If (Table.PayerID} = '5000'
Then 1
[/Code]

4. Then use a Group Selection formula of:
[Code Group_Selection]
Sum({@PayerTest},{Table.Client}) > 0
[/Code]

Then, only those Clients with at least 1 (Table.PayerID} of "5000" will be reported.

Note, it is not necessary to test for = (Table.PayerID} = "1000" as your stated aim was to only include those if the Client also had a (Table.PayerID} of "5000".



Hope this helps
Pete

 

[talibm]

Hi Pete,

I have tried your suggeation, but I can't get it to work for me. I am not sure where the group selection code should be placed. There are over 50 payer combinations that a client can have. I want all clients with only the 5000 payer and clients with the 5000 and 1000 payers. In the details section I have 1 for payer 5000 and 0 for payer 1000. I still have the payer Id # in ["5000", "1000"] to eliminate the other payers. I am not sure where to place the group code. thanks

talib

 

[pmax9999]

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'
[/Code]

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']
[/Code]

To enter the Group Selection, click on Report => Selection Formulas => Group.

The Record Se;lection formula should make no reference to PayerID. Table/Field names need to be changed to refelect your database.

The result will be Clients that have only PayerIDs of 5000, or those with 5000 and 1000.

Hope this helps.

Pete

 

[talibm]

Hi Pete,

This worked like a charm! Thank you very much for your help