Looking for alternative formulas to suppress duplicates through Section Expert

[Former Member]

I'm using version 14.0.2.364 CR and having trouble building a customized treatment plan report for our case workers to manage to filter out duplicates. I'll do my best to give a layout and criteria:

GH: CDCLIENT.SAI_ID    STAFF.SORT_NAME

Case #	First Name	Last Name	Home Phone	DOB	Tx Plan Start Date	Tx Plan End Date
CDCLIENT.CASE_NUM	CDCLIENT.FIRST_NAME	CDCLIENT.LAST_NAME	CDCLIENT.PHONE	CDCLIENT.DOB	PLAN.BEG_DATE	PLAN.END_DATE

GF: CDCLIENT.SAI_ID    STAFF.SORT_NAME

The problem is that when pulling data for each client, it will bring forward all their tx plans, but I just want to show the newest one. I was inclined to go to section expert > Select Details > Suppress (No Drill down) and in X-2, put the following:

if {CDCLIENT.CASE_NUM} = next({CDCLIENT.CASE_NUM}) then true else false

This will suppress duplicate records ONLY if I have this sorted by Case #. The problem is, we want to sort by Tx Plan End Date, so case workers can identify which clients are coming up for expiring tx plans. The way the data is stored, I have the first 5 columns from one table, and the last two in another. I also tried to format the field, go to "Commons" on CASE_NUM and choose Suppress (if Duplicated) in combination with the Section Expert, but it was still pulling both if I have it the sort order start at PLAN.END_DATE.

Is there a way to preserve the most recent begin / end date and suppress any duplicates without sorting it by case #?

[JWiseman · Accepted solution]

hi Dominic,

there are a bunch of ways of doing this.

method A > using a suppression formula

right click on the Details name and go to Section Expert and change the Suppress formula to something like

{your end date field} <> maximum{{your end date field})

this assumes that you only have one client per report. let us know if there are more.

method B > using a SQL Expression

this again assumes that you only have one client per report. go to the field explorer and create a new SQL Expression Field with syntax like

max(`your table`.`your end date field`)

now go to the Report menu > Selection Formulas > Record and then create a filter like

{your end date field} = {%your sql expression field}

this will ensure that you only have one record shown per report. if this is not what you are looking for please give a lot more detail about your report structure...i.e. what groups you have, etc.

there is also method C > basing your report off of a Command object instead of tables, but this involves creating a report from scratch and you'll need some sql skills to write the command object.

please note that method B & C would be the recommended methods as method A uses Suppression. suppression does not filter out records at the database level but hides them in the report. this can cause performance issues if you're going against a large data set.

[Former Member · Accepted solution]

Jamie Wiseman wrote:

hi Dominic,

there are a bunch of ways of doing this.

method A > using a suppression formula

right click on the Details name and go to Section Expert and change the Suppress formula to something like
{your end date field} = maximum{{your end date field})
this assumes that you only have one client per report. let us know if there are more.

Jamie, thank you for your response. Unfortunately, that did not work because each client can have multiple treatment plans. For example:

5555 Case Manager Name Goes here (Group Header)

Case#	First Name	Last Name	Phone #	DOB	Tx Plan Start Date	Tx Plan End Date
123	John	Doe	555-555-5555	12/31/67	01/31/13	01/31/14
124	Jane	Smith	444-444-4444	04/04/64	02/20/13	02/20/14
123	John	Doe	555-555-5555	12/31/67	01/31/14	01/31/15

I commented out my old script formula, and put yours in Section Expert > Suppress (No Drill Down) (this item is also checked) on the Details Section:

{PLAN.END_DATE} = maximum ({PLAN.END_DATE})

Since "John Doe" is a returning client, the case worker has given them a new Tx Plan, thus, John Doe now has 2 records. The caseworker is interested in looking at the most recent (row 3) for John Doe.

Method B

I do not have experience with SQL expressions.

[JWiseman · Accepted solution]

you'll need to describe the structure of your report more...what are the groups? is the report using the Sort expert as well? please provide a screen shot of the Design mode of your report.

[Former Member · Accepted solution]

The grouping is based on the case worker (their caseload CDCLIENT.SAI_ID). In my visual example, I should've used multiple staff / case workers so you can get an idea how the report is suppose to look (I just did one to generalize).

The content of the group is the client list (original thread has mapped layout for the Detail Section). This report is given to several program managers and supervisors to monitor each caseworker's caseload looks like, and to make sure they're managing upcoming treatment plans for expiration. The report will show various case workers on the report (Group Sort default is by Staff ID) and their total clients for their caseload.

I'm using Select Sort Expert for PLAN.END_DATE, so I can ascend the data and make sure the caseworkers identify which treatment plans are expiring first. 

The goal is to identify the current treatment plan, and discard any previous records (i.e. two rows for John Doe, instead of current treatment plan).

[JWiseman · Accepted solution]

okay...you just need to modify the first suggestion then...you don't really need to worry about any sorts as you'll be using a suppression formula based on a group summary

steps:

1) right click on your date field and choose Insert > Summary and change the type to Maximum and the Summary Location to your Group

2) right click on the Details name and go to Section Expert and change the Suppress formula to something like the following, utilizing the new summary created in step 1

{your end date field} <> maximum({your date field}, {your group field})

have a look at the attached report as well...it selects only the latest Order for each customer based on a date field. see the suppression formula for the Details section. to view the report change the .txt extension to .rpt after extracting the contents.

MaximumOrLatestOrderDatePerCustomer.txt.zip

[Former Member · Accepted solution]

Jamie Wiseman wrote:

1) right click on your date field and choose Insert > Summary and change the type to Maximum and the Summary Location to your Group

All this did was create End Date summaries at the end of each group. To clarify, each caseworker (group) is going to have multiple clients.

 2) right click on the Details name and go to Section Expert and change the Suppress formula to something like the following, utilizing the new summary created in step 1

I tried this, but it's still pulling multiple records for the client.

I've reviewed your file and I think since mine is a little more complex (multiple tables), I must be missing something.

[JWiseman · Accepted solution]

use {your end date field} <> maximum({your date field}, {your group field})

as per above...unfortunately i edited the above and the code in the email sent to you was old / wrong. please also see the sample report posted above.

[JWiseman · Accepted solution]

or in the case of your report use this as the suppression formula...sorry again about the original code.

{AZCLPLAN.BEG_DATE} <> maximum ({AZCLPLAN.BEG_DATE}, {CDCLIENT.SAI_ID})

[Former Member · Accepted solution]

Jamie Wiseman wrote:

or in the case of your report use this as the suppression formula...sorry again about the original code.


{AZCLPLAN.BEG_DATE} <> maximum ({AZCLPLAN.BEG_DATE}, {CDCLIENT.SAI_ID})

The problem with this suppression is that for each Case Worker (Group) it is only pulling one client record forward, thus suppressing all other multiple unique clients associated with this caseworker.

What my original report (before your suggestions is this):

Case Manager Name (group CDCLIENT.SAI_ID)

CDCLIENT.CASE_NUM	CDCLIENT.FIRST_NAME	CDCLIENT.LAST_NAME	CDCLIENT.PHONE	CDCLIENT.DOB	PLAN.BEG_DATE	PLAN.END_DATE
1	Client A	Client A	111-111-1111	01/01/11	01/01/2013	01/01/2014
2	Client B	Client B	222-222-2222	02/22/22	02/02/2013	02/02/2014
3	Client C	Client C	333-333-3333	03/31/33	03/03/2013	03/03/2014
1	Client A	Client A	111-111-1111	01/01/11	01/01/2014	01/01/2015
4	Client D	Client D	444-444-4444	04/04/44	04/04/2014	04/04/2015

Notice, it finds two records for Client A, because they are an existing client that needs continuous treatment. The caseworker doesn't need to see the older record for Client A, but also needs to see all other unique records.

I basically want it to suppress the first row (older treatment plan) for Client A and have it display like this in conjunction with all other unique clients:

Case Manager Name (group CDCLIENT.SAI_ID)

CLIENT.CASE_NUM	CDCLIENT.FIRST_NAME	CDCLIENT.LAST_NAME	CDCLIENT.PHONE	CDCLIENT.DOB	PLAN.BEG_DATE	PLAN.END_DATE
2	Client B	Client B	222-222-2222	02/22/22	02/02/2013	02/02/2014
3	Client C	Client C	333-333-3333	03/31/33	03/03/2013	03/03/2014
1	Client A	Client A	111-111-1111	01/01/11	01/01/2014	01/01/2015
4	Client D	Client D	444-444-4444	04/04/44	04/04/2014	04/04/2015

What your Section Expert + Summary Code did, was essentially this:

Case Manager Name (group CDCLIENT.SAI_ID)

CLIENT.CASE_NUM	CDCLIENT.FIRST_NAME	CDCLIENT.LAST_NAME	CDCLIENT.PHONE	CDCLIENT.DOB	PLAN.BEG_DATE	PLAN.END_DATE
4	Client D	Client D	444-444-4444	04/04/44	04/04/2014	04/04/2015
					Maximum	04/04/2015

Let me know if this is possible.

[JWiseman · Accepted solution]

sure thing. if you want something with essentially 2 levels of hierarchy, then your report will need two levels of grouping. i.e. a group on client case number would also have to be created. and then a date summary created on the case number group level. and then the details level is suppressed based on this second level summary.

[Former Member · Accepted solution]

This is generally helpful( Thank you! ) , but since I only know CR well enough to get my hands dirty, would you mind giving me a walkthrough step by step when you have time?

If it's easier to apply the hierarchy and group levels to the actual file, I have it attached and then you can call out / summarize what you did to accomplish.

tx plan_TEST_NEW_SCRIPT_DD2b - Copy.txt.zip

[JWiseman · Accepted solution]

sure thing...attached. hopefully this will be okay but not 100% sure as your report doesn't have Saved Data.

steps:

1) go to the Insert menu > Group > choose Case_Num

2) go to your Details suppression formula and change it to

{AZCLPLAN.BEG_DATE} <> maximum ({AZCLPLAN.BEG_DATE}, {CDCLIENT.CASE_NUM})

3) Suppress the new group 2 (case_num) header & group 2 footer as you don't need to display them...they're only on the report in order to create the 2nd level of hierarchy along with the group summary used in the details suppression formula

tx plan_TEST_NEW_SCRIPT_DD2b.txt.zip