Using CR 8.5 and SQL database
We run a case list report at the beginning of the month that shows all open cases for each clinician. It involves linking 6 different views. It takes 45 minutes plus to run the report and I am thinking we could change something to reduce the run time, I just don't know what. We are fairly experienced with CR but certainly not experts. I am not sure what information to show so here is what I know.
The following is the formula viewable in the select expert:
not ({Episode_Open_Case_View_dmc.consumer_ID} in [20227, 20228, 20229, 20345, 20357, 20435]) and
not ({Employee_View.people_last_name} in ["", "BEHAVIOR MANAGEMENT", "CONTRACTED", "FRITSCHY"]) and
{Employee_View.supervisor__employee_ID} in [161, 162, 168, 169, 170, 186, 43, 62]
The report displays the following data elements for the clinicians use:
Consumer Name, consumer ID, phone number, episode begin date, last service date, last assessment date, last periodic update date, and current insurance type.
The report has 7 groupings and is grouped by
1)Employee_View.supervisor_employee_ID,
2)Employee_view.people_last_name,
3)Consumer_view.people_last_name
and all of the above group headers are hidden (grayed in design view).
Group 4 is by consumer_view.people_first_name and is not suppressed but the data elements as described above are placed in this group header and display on the report.
Group 5 is psychosocial_view.psychosocial_ID,
Group 6 is PeriodicSummary_view.periodicsummary_ID,
Group 7 is Consumer_Service_view.begin_date
Group footers 7 thru 3 are hidden (grayed in design view).
Group footer 2 shows the total number of open cases for the clinician. Group footer 1 is grayed and empty.
The other footers have distinct counts and sums but are grayed in design view (hidden).
The details section is hidden.
The report footer displays the grand total of open cases.
The report is about 58 pages and the status line says records = 3863877.
There are 960 open consumers for about 47 clinians.
Should it take this long to run and what can we do to make this more efficient for processing? I didn't write it but my staff say its because of all the groups and having to find the last date for several of them.
Any help would be greatly appreciated!! Thank you in advance!
We run a case list report at the beginning of the month that shows all open cases for each clinician. It involves linking 6 different views. It takes 45 minutes plus to run the report and I am thinking we could change something to reduce the run time, I just don't know what. We are fairly experienced with CR but certainly not experts. I am not sure what information to show so here is what I know.
The following is the formula viewable in the select expert:
not ({Episode_Open_Case_View_dmc.consumer_ID} in [20227, 20228, 20229, 20345, 20357, 20435]) and
not ({Employee_View.people_last_name} in ["", "BEHAVIOR MANAGEMENT", "CONTRACTED", "FRITSCHY"]) and
{Employee_View.supervisor__employee_ID} in [161, 162, 168, 169, 170, 186, 43, 62]
The report displays the following data elements for the clinicians use:
Consumer Name, consumer ID, phone number, episode begin date, last service date, last assessment date, last periodic update date, and current insurance type.
The report has 7 groupings and is grouped by
1)Employee_View.supervisor_employee_ID,
2)Employee_view.people_last_name,
3)Consumer_view.people_last_name
and all of the above group headers are hidden (grayed in design view).
Group 4 is by consumer_view.people_first_name and is not suppressed but the data elements as described above are placed in this group header and display on the report.
Group 5 is psychosocial_view.psychosocial_ID,
Group 6 is PeriodicSummary_view.periodicsummary_ID,
Group 7 is Consumer_Service_view.begin_date
Group footers 7 thru 3 are hidden (grayed in design view).
Group footer 2 shows the total number of open cases for the clinician. Group footer 1 is grayed and empty.
The other footers have distinct counts and sums but are grayed in design view (hidden).
The details section is hidden.
The report footer displays the grand total of open cases.
The report is about 58 pages and the status line says records = 3863877.
There are 960 open consumers for about 47 clinians.
Should it take this long to run and what can we do to make this more efficient for processing? I didn't write it but my staff say its because of all the groups and having to find the last date for several of them.
Any help would be greatly appreciated!! Thank you in advance!