Notifications
Clear all
Topic starter
Has anyone ever come across this error in the reports module? The query works in sql, excel, etc. The crystal report works when previewing in SAP crystal, but I am getting this error when I put in CPS Reports module....
Posted : February 11, 2021 10:17 am
A couple of things to ponder:
1) Crystal Reports in Preview mode looks only at first x records (where x may be 1000) to begin building report. So possible did not come across error while in subset of data.
2) May want to adjust the Crystal Report to be Person.Searchname <= "AZZ" to only capture people with last name beginning with letter A. Then see how it runs.
Posted : February 23, 2021 5:11 am
Topic starter
Thanks for the suggestions... Unfortunately, I still receive the same error.
It just seems like crystal is not liking the max functions I have in the SQL query.
Posted : March 8, 2021 3:53 am
Topic starter
Another note to add here..
SQL records:
Crystal records
I am able to see the full data set in the preview sample view in Crystal Reports. When adding the report to CPS 12.3.3 Reports module, it just throws the error above in the original post... very odd. but I am sure I am missing something here
Posted : March 8, 2021 6:53 am
It could be an issue with the report query you enter into Centricity, I've been known to have a type-o when adding the different variables and parameters from the controls.
Can you copy your code here to have a look at?
Posted : April 14, 2021 2:18 am
Topic starter
SET NOCOUNT ON
SELECT a.AppointmentsId,
convert(varchar,a.ApptStart,101) [Appt Start],
isnull(max(o.OBSVALUE),'') [Reschedule?],
isnull(max(o1.OBSVALUE),'') [OrigSXDate],
pp.first+' '+pp.last [Patient Name],
pp.PatientId,
Facility= dff.Listname,
dfd.Listname AS DoctorName,
mlas.Description AS Status,
at.Name AS Type,
max(case when d.DOCTYPE = '11' then d.DB_CREATE_DATE else ' ' end) as [Hospital WS Created],
isnull(max(o2.OBSVALUE),'') [Codes Reviewed],
isnull(max(o3.OBSVALUE),'') [Coded By],
isnull(max(o4.OBSVALUE),'') [Pre-Cert Started],
isnull(max(o5.OBSVALUE),'') [Pre-Cert Started By],
isnull(max(o6.OBSVALUE),'') [Pre-Cert Completed],
isnull(max(o7.OBSVALUE),'') [Pre-Cert Completed By]
FROM Appointments a
JOIN DoctorFacility dff ON a.FacilityId = dff.DoctorFacilityId
JOIN PatientProfile pp ON a.OwnerId = pp.PatientProfileId
JOIN DoctorFacility dfr ON a.ResourceId = dfr.DoctorFacilityId
LEFT JOIN DoctorFacility dfd ON a.DoctorId = dfd.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId
LEFT JOIN Medlists mlas ON a.ApptStatusMId = mlas.MedlistsId
left join DOCUMENT as d on pp.PId = d.PID
left join obs as o on pp.pid = o.pid and o.hdid = '200229'
left join obs as o1 on pp.pid = o1.pid and o1.hdid = '15500010'
left join obs as o2 on pp.pid = o2.pid and o2.hdid = '136858'
left join obs as o3 on pp.pid = o3.pid and o3.hdid = '109158'
left join obs as o4 on pp.pid = o4.pid and o4.hdid = '200924'
left join obs as o5 on pp.pid = o5.pid and o5.hdid = '200922'
left join obs as o6 on pp.pid = o6.pid and o6.hdid = '200919'
left join obs as o7 on pp.pid = o7.pid and o7.hdid = '200921'
WHERE ApptKind = 1 AND ISNULL(Canceled,0) = 0 AND
a.ApptStart >= ISNULL(getdate(),'1/1/1900') AND a.ApptStart < dateadd(d, 90, getdate())
AND --Filter on resource
(
('22228,23899,22221,23161,22223,22222,22226,22376,22573,22217,22241,22220,6055,6051,1848,22640,22224,22225' IS NOT NULL AND a.ResourceID IN (22228,23899,22221,23161,22223,22222,22226,22376,22573,22217,22241,22220,6055,6051,1848,22640,22224,22225)) OR
('22228,23899,22221,23161,22223,22222,22226,22376,22573,22217,22241,22220,6055,6051,1848,22640,22224,22225' IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND a.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND a.DoctorId in (NULL)) OR
(NULL IS NULL)
)
and mlas.Description not in ('In Patient','Arrived')
group by a.AppointmentsId,
convert(varchar,a.ApptStart,101),
pp.first+' '+pp.last, pp.PatientId,
dff.Listname,
dfd.Listname,
mlas.Description,
at.Name
order by convert(varchar,a.ApptStart,101)
Posted : April 14, 2021 3:52 am
Topic starter
I posted above..thanks for the reply
Posted : April 14, 2021 3:53 am
I believe it is the question mark in your column name of [Reschedule?].
Question marks are utilized by reports in Centricity to identify variables\parameters you are passing in via the Centricity Reporting screens.
If you remove the question mark I have a feeling it will resolve the error.
Here is a powerpoint I found that might be helpful in explaining the syntax better than I can.
Posted : April 14, 2021 5:37 am
Topic starter
That is it indeed!!
I didn't even think to look at the alias I had used in the query could be throwing a syntax error in crystal. I love how the simple things do that haha! Thanks for your help!
Posted : April 14, 2021 5:43 am
