Showing Max date based on filtered services, possible subreport help needed

[Former Member]

We created a caseload report for our clinicians to see all their clients and treatment plans. In addition, I want to be able to pull the most recent medication service, based on date and filtered by service codes that are identified as med services. Since I'm gathering two sets of data (service code and date), I wasn't sure how this would display most recent date. Here is my criteria for service codes:

{CLIENTSERVICES.SVC_ID} in [11, 20, 21, 22, 23, 24, 25, 29] and
{CLIENTSERVICES.DATE} = ??? (maximum)

Would this be a sub report I set within the existing report?

[abhilash_kumar]

Hi Dominic,

If you use a record selection formula as above, wouldn't that get rid any 'treatment plans' that don't equal the Max Date?

It seems you need 'all' treatment plans and hence, essentially no filter conditions for that record set.

Do you have a mock-up of how you visualize this report and then perhaps I could suggest whether a subreport would be needed or not.

-Abhilash

[Former Member]

Correct, I can't have max date for the overall report because I already have that set for my treatment plans, pulling the most recent, which is why I'm leaning towards subreport, but have limited experience. Here's my intended layout of what I have that currently works:

Case #	Client Name	Treatment Plan ID & Expiration	Last NonMed Service Date	Last Med Service Date	Last Assessment Date	Last Psych Eval Date
CDCLIENT.CASE_NUM	CDCLIENT.SORT_NAME	TXCLPLAN.END_DATE ".TYPE_ID	GetMaxNonMed Date	GetMaxMedDate	GetMaxAsmntDate	GetMaxPsychEvalDate
Address	CDCLIENT.ADDRESS	DOB: CDCLIENT.DOB	ADMIT DATE: CDASSIGN.DATE	DIAGNOSIS AXIS: DXDIAGCD.AXIS	DIAGNOSIS CODE: DXDIAGCD.ICD9
City/State/Zip	CDCLIENT.CITY ".STATE ".ZIP	PHONE: CDCLIENT.PHONE	ADMIT PROGRAM: CDASSIGN.ID	DIAGNOSIS DESC:	DXDIAGCD.DESC

GetMaxNonMedDate

1. CLIENTSERVICES.SVC_ID: 5,10,12,13,30,31,33
2. CLIENTSERVICES.DATE (most recent)

GetMaxMedDate

1. CLIENTSERVICES.SVC_ID: 11, 20, 21, 22, 23, 24, 25, 29
2. CLIENTSERVICES.DATE  (most recent)

GetMaxAsmntDate

1. AXCLPLAN.PLAN_ID= CAA
2. AXCLPLAN.DATE (most recent)

GetMaxPsychEvalDate

1. AXCLPLAN.PLAN_ID= PSYCHEV
2. AXCLPLAN.DATE (most recent)

Items in blue are currently working. My grouping is by Case_Num Pic attached.

[abhilash_kumar]

Hi Dominic,

1) For the GetMaxNonMedDate, insert a Subreport pointing to a table that can get you the date field and the 'Case Number' field

2) Right-click the Subreport > Select Change Subreport Links > Move the Case number field from the Pane on the left to the Pane on the right > From the dropdown at the bottom right that says 'Select data in Subreport based on field', choose the 'Case number' field

3) Get inside the Subreport and create a Group on the 'Case Number' field

4) Go to the Report Option on the top > Record Sort Expert > Choose the Date field > Select Descending as the sort order.

5) Place the Date field on the Group Header section and suppress all other sections

6) Go to Report > Selection Formulas > Record. You should find a formula in there for the Case Number. e.g:

{Case Number} = {?Pm-Case_Number}

Just add an 'Add' so as to include the svc_id logic:

{Case Number} = {?Pm-Case_Number}

AND

{CLIENTSERVICES.SVC_ID} IN [5,10,12,13,30,31,33]

7) Repeat Steps 1 to 6 for all other Subreports

P.S: If you wish the report to perform better, you should create one single SQL query and use that as the datasource for the report.

-Abhilash

[Former Member]

Thank you. This works when adding the initial sub report. However, when adding a second sub report, it pulls today's date instead of the corresponding date specified by group and SVC_ID.

EDIT: Instead of suggesting save Step 1-6, Save subreport and change parameters. For some reason, when I tried to re-create an additional sub report exactly as the original, but modifying Step 6 values for SVC_ID, it would use today's date.

Abhilash Kumar wrote:

Hi Dominic,

1) For the GetMaxNonMedDate, insert a Subreport pointing to a table that can get you the date field and the 'Case Number' field

2) Right-click the Subreport > Select Change Subreport Links > Move the Case number field from the Pane on the left to the Pane on the right > From the dropdown at the bottom right that says 'Select data in Subreport based on field', choose the 'Case number' field

3) Get inside the Subreport and create a Group on the 'Case Number' field

4) Go to the Report Option on the top > Record Sort Expert > Choose the Date field > Select Descending as the sort order.

5) Place the Date field on the Group Header section and suppress all other sections

6) Go to Report > Selection Formulas > Record. You should find a formula in there for the Case Number. e.g:

{Case Number} = {?Pm-Case_Number}

Just add an 'Add' so as to include the svc_id logic:

{Case Number} = {?Pm-Case_Number}
AND
{CLIENTSERVICES.SVC_ID} IN [5,10,12,13,30,31,33]

7) Repeat Steps 1 to 6 for all other Subreports

P.S: If you wish the report to perform better, you should create one single SQL query and use that as the datasource for the report.

-Abhilash

[abhilash_kumar]

Could you check the links and the Record Selection formula once again?

We don't have a filter on the date field anyway.

-Abhilash

[Former Member]

Hi,

Try this:

1. On the menu go to report > Section Expert
2. Left side select Details
3. right side press the X-2 for suppression
4. enter your date field not equal to the maximum date - ie: {CLIENTSERVICES.DATE} <> MAXIMUM({CLIENTSERVICES.DATE})

This will suppress all the other date and show the maximum/recent dates.

I hope this helps you.

Thanks,

DJ