CalMHSA 117 – Service Note Search Report

Report Description

This Report is designed to be used to view the service notes and other notes in one report. It also has the ability to do a 1-keyword search functionality, it includes all fields with a note, and you can have flexible search dates.

Report Name

Menu Path

Client Based

Report RDL Name

CalMHSA 117 – Service Note Search Report

CalMHSA 117 – Service Note Search Report (Client)

Yes

RDLCALMHSA_117_ServiceNoteSearch

Parameters

Data Type

Hidden

Comments

Clinical Data Access GroupId

Integer

Y

Passed by system at report run time based on currently logged in Staff

ExecutedByStaffId

Integer

Y

Passed by system at report run time based on currently logged in Staff

ClientId

Integer

Y

Passed by system at report run time based on currently selected client

StartDate

Date

A Date parameter that allows users to specify the beginning of the data range for the report to use. The report will defaults this parameter’s value to be the current date of the report being ran – 365 days. Example if current date is 5/27/2025, the default value will be 5/27/2024.

EndDate

Date

A Date parameter that allows users to specify the beginning of the data range for the report to use. The report will defaults this parameter’s value to be the current date of the report being ran.

Authors

Multiple Select

A multiple select parameter that allows users to specify the author(s) for the report to pull from. Parameter default is to have all author options be selected.

Programs

Multiple Select

A multiple select parameter that allows users to specify the program(s) for the report to pull data from. Parameter default is to have all program options be selected.

Procedures

Multiple Select

A multiple select parameter that allows users to specify the procedure code(s) for the report to pull data from. Parameter default is to have all program options be selected.

SearchText

Sring

A open text parameter that allows users to specify a string the report can use to only pull service notes where the “Progress Note Information” and “Progress Note Care Plan” contain the string. This parameter can be NULL, this is indicated by the NULL checkbox next to the parameter.

DataSets

Form(s)

CDAG enforced

Comments

Primary

Service Notes

Y

This query is the main query for the report. The data is pulled from the Document table with left outer joins to the following tables:

·       Services; join on Document’s Serviceid = Services’ Serviceid

·       DocumentCodes; join on Documents’ DocumentCodeId = DocumentCodes’ DocumentCodeid

·       CustomDocumentProgressNotes; join on Documents’ CurrentDocumentVersionid = CustomDocumentProgressNotes’ DocumentVersionid

·       CustomMiscellaneousNotes; join on Documents’ CurrentDocumentVersionid = CustomMiscellaneousNotes’ DocumentVersionid

·       DocumentPsychiatricNoteGenerals; join on Documents’ CurrentDocumentVersionid = DocumentPsychiatricNoteGenerals’ DocumentVersionid

·       DocumentPsychiatricNoteMDMs; join on Documents’ CurrentDocumentVersionid = DocumentPsychiatricNoteMDMs’ DocumentVersionid

·       DocumentIPPsychiatricNoteGenerals; join on Documents’ CurrentDocumentVersionid = DocumentIPPsychiatricNoteGenerals’ DocumentVersionid

·       DocumentIPPsychiatricNoteMDMs; join on Documents’ CurrentDocumentVersionid = DocumentIPPsychiatricNoteMDMs’ DocumentVersionid

·       CustomDocumentCalMHSAMobileCrisisDispatch; join on Documents’ CurrentDocumentVersionid = CustomDocumentCalMHSAMobileCrisisDispatch’s DocumentVersionid

·       CustomDocumentCalMHSACrisisProgressNote; join on Documents’ CurrentDocumentVersionid = CustomDocumentCalMHSACrisisProgressNote’s DocumentVersionid

·       CustomDocumentCalMHSAGenericNote; join on Documents’ CurrentDocumentVersionid = CustomDocumentCalMHSAGenericNote’s DocumentVersionid

·       CustomCalMHSAPsychNote; join on Documents’ CurrentDocumentVersionid = CustomCalMHSAPsychNote’s DocumentVersionid

·       CustomDocumentWeeklyNotes; join on Documents’ CurrentDocumentVersionid = CustomDocumentWeeklyNotes’ DocumentVersionid

·       CustomDocumentCalmhsaServices; join on Documents’ CurrentDocumentVersionid = CustomDocumentCalmhsaServices’ DocumentVersionid

·       ProcedureCodes; join on Services’ ProcedureCodeid = ProcedureCodes’ ProcedureCodeid

·       Programs; join on Services’ Programid = Programs’ Programid

·       Staff; join on Documents’ Authorid = Staff’s Staffid

·       Clients; join on Document’s Clientid = Clients’ Clientid

The data that is pulled is based on the following criteria:

·       All Documents data is in regards to the client id that is set on the Clientid parameter

·       Only pull Documents data where the documentcodeid is within the following list:

o   60193  Generic Note

o   47000  Weekly Note

o   60136  Progress Note

o   60080  Psychiatric Note

o   60248  Mobile Crisis Progress Note

o   60329  Psych/Medical Note

o   60124  IP Psychiatric Note

o   60183  PEIServices

o   115 Narrative

o   60247  Mobile Crisis Dispatch Screening

·       Only pull Documents data that are “in-process” or “signed” status

·       Only pull Documents data where it’s RecordDeleted column is NULL or equal to ‘N’.

·       Only pull Service data where it’s date of services is within the time range setup by the Start and End date filters

·       Only pull Documents data where it’s authored matches up with what been selected on the Authors Filter

·       Only pull Services data where it’s programid matches up with what been selected on the Programs Filter

·       Only pull Services data where it’s procedurecodeid matches up with what been selected on the Procedures Filter

·       Checks if the SearchText Filter is NULL (I.E. no input) or not. If the filter is not NULL, then search through the Service Notes to see if parts of the note contains what been entered. What part of the service note that is being searched are the following:

o   Progress Note’s Information and CarePlan

o   Narrative’s Narration

o   Psychiatric Note’s TodayChiefComplaint and PlanComment

o   IP Psychiatric Note’s TodayChiefComplaint and PlanComment

o   Mobile Crisis Dispatch Screening’s CallReason and CrisisDetails

o   Mobile Crisis Progress Note’s InterventionsProvided and CrisisPlanning

o   Generic Note’s Notes

o   Psych/Medical Note’s SubjectsNotes, Client History, MsePe, AssessmentPlan, and AdditionalInformation

o   Weekly Note’s NoteText

o   PEIServices’ TitleofService, ServiceLocation, and NotesPurposeOfContact

Things to note about the data that is being pulled:

·       The query will pull the following service note data and label it as “NoteInformation”

o   Progress Note’s information

o   Narrative’s Narration

o   Psychiatric Note’s TodayChiefComplaint

o   IP Psychiatric Note’s TodayChiefComplaint

o   Mobile Crisis Dispatch Screening’s CallReason

o   Mobile Crisis Progress Note’s InterventionsProvided

o   Generic Note’s Notes

o   Psych/Medical Note’s (have a “sub” list)

§  SubjectNotes as “Subject Notes”

§  ClientHistory as “History”

§  MsePe as “MSE”

o   Weekly Note’s NoteText

o   PEIServices Note’s (have a “sub” list)

§  TitleOfService as “Title”

§  ServiceLocation as “Location”

·       The query will pull the following service note data and label it as “NoteCarePlan”

o   Progress Note’s CarePlan

o   Narrative’s Narration

o   Psychiatric Note’s PlanComments

o   IP Psychiatric Note’s PlanComments

o   Mobile Crisis Dispatch Screening’s CrisisDetails

o   Mobile Crisis Progress Note’s CrisisPlanning

o   Psych/Medical Note’s (have a “sub” list)

§  AssessmentPlan as “AssessmentPlan”

§  AdditionalInformation as “AdditionalInformation”

o   PEIServices Note’s NotesPurposeOfContract

·       The query will pull the following service note data and label it as “Display1”

o   Progress Note – “Progress Note Information”

o   Narrative – “Narrative”

o   Psychiatric Note– “Psych Note Chief Complaint”

o   IP Psychiatric Note – “IP Note Chief Complaint”

o   Mobile Crisis Dispatch Screening – “Mobile Dispatch Call Reason”

o   Mobile Crisis Progress Note – “Mobile Progress Interventions”

o   Generic Note– “Crisis Inquiry Note”

o   Psych/Medical Notes – “Psych/Med Subject/History/MSE”

o   Weekly Note – “Weekly Note”

o   PEIServices Note – “PEI Title/Location”

·       The query will pull the following service note data and label it as “Display2”

o   Progress Note – “Progress Note Care Plan”

o   Psychiatric Note– “Psych Note Plan”

o   IP Psychiatric Note – “IP Note Plan”

o   Mobile Crisis Dispatch Screening – “Mobile Dispatch Crisis Details”

o   Mobile Crisis Progress Note – “Mobile Progress Crisis Planning”

o   Psych/Medical Notes – “Psych/Med Assessment/Additional”

o   PEIServices Note – “PEI Purpose of Contact”

Authors

Staff/Users(Admin) -> Staff Details

Service Notes

Y

This query is designed to populated the Authors filter. It pulls Staff record’s first name, last name, and staff id. The data pulls from the Documents table with left outer joins on the following tables:

·       Services; join on Documents’ Serviceid = Services’ Serviceid and Services’ RecordDeleted = NULL

·       ProcedureCodes; join on Services’ ProcedureCodeid = ProcedureCodes’ ProcedureCodeid

·       Programs; join on Services’ Programid = Programs’ Programid

·       Staff; join on Documents’ Authorid = Staff’s Staffid

The data that is pulled is based on the following criteria:

·       Only pull Documents data where the it’s client id matches to what is set on the Clientid parameter

·       Only pull Documents data where the documentcodeid is within the following list:

o   60193  Generic Note

o   47000  Weekly Note

o   60136  Progress Note

o   60080  Psychiatric Note

o   60248  Mobile Crisis Progress Note

o   60329  Psych/Medical Note

o   60124  IP Psychiatric Note

o   60183  PEIServices

o   115 Narrative

o   60247  Mobile Crisis Dispatch Screening

·       Only pull Documents data where the status is either “in-process” or “signed” status

·       Only pull Documents data where the RecordDeleted column is NULL or equal to ‘N’.

·       Only pull Service data where it’s date of services is within the time range setup by the Start and End date filters

Procedures

Procedure/Rate (Admin) ->  Procedure Code Details

Service Notes

Y

This query is designed to populated the Authors filter. It pulls Services record’s procedurecodeid and ProcedureCodes record’s DisplayAs. The data pulls from the Documents table with left outer joins on the following tables:

·       Services; join on Documents’ Serviceid = Services’ Serviceid and Services’ RecordDeleted = NULL

·       ProcedureCodes; join on Services’ ProcedureCodeid = ProcedureCodes’ ProcedureCodeid

·       Programs; join on Services’ Programid = Programs’ Programid

·       Staff; join on Documents’ Authorid = Staff’s Staffid

The data that is pulled is based on the following criteria:

·       Only pull Documents data where the it’s client id matches to what is set on the Clientid parameter

·       Only pull Documents data where the documentcodeid is within the following list:

o   60193  Generic Note

o   47000  Weekly Note

o   60136  Progress Note

o   60080  Psychiatric Note

o   60248  Mobile Crisis Progress Note

o   60329  Psych/Medical Note

o   60124  IP Psychiatric Note

o   60183  PEIServices

o   115 Narrative

o   60247  Mobile Crisis Dispatch Screening

·       Only pull Documents data where the status is either “in-process” or “signed” status

·       Only pull Documents data where the RecordDeleted column is NULL or equal to ‘N’.

·       Only pull Service data where it’s date of services is within the time range setup by the Start and End date filters

Programs

Programs(Admin) -> Program Details

Service Notes

Y

This query is designed to populated the Authors filter. It pulls Programs record’s Programid and ProgramName. The data pulls from the Documents table with left outer joins on the following tables:

·       Services; join on Documents’ Serviceid = Services’ Serviceid and Services’ RecordDeleted = NULL

·       ProcedureCodes; join on Services’ ProcedureCodeid = ProcedureCodes’ ProcedureCodeid

·       Programs; join on Services’ Programid = Programs’ Programid

·       Staff; join on Documents’ Authorid = Staff’s Staffid

The data that is pulled is based on the following criteria:

·       All data is in regards to the client id that is set on the Clientid parameter

·       Pull only programs that have been set on a service note(s) that have been written for the client selected

·       Pull only Programs that have been set on a service note(s) where the date of service is within the time range setup by the Start and End date filters.

·       Service notes need to be “in-process” or “signed” status

·       Service Note Document record’s and associated Service record’s RecordDeleted column is NULL or equal to ‘N’.

All mentioned service notes include the following: Narrative, Weekly Note, Psychiatric Note, IP Psychiatric Note, Progress Note, PEIServices, Generic Note, Mobile Crisis Dispatch Screening, Mobile Crisis Progress Note, and Psych/Medical Note

Default User Roles