Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Report Error

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

9 Posts

3 Users

0 Reactions

83 Views

RSS

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

joeg1962

(@joeg1962)

Posts: 843

Noble Member

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

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

dmontgomery

(@dmontgomery)

Posts: 59

Trusted Member

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

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

Topic starter

I posted above..thanks for the reply

Posted : April 14, 2021 3:53 am

dmontgomery

(@dmontgomery)

Posts: 59

Trusted Member

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.

https://www.centricityusers.com/wp-content/uploads/2019/11/BO2_06-CHUG-Fall-2019-Using-Crystal-Reports.pdf

Posted : April 14, 2021 5:37 am

jbaileysemmesmurp

(@jbaileysemmesmurp)

Posts: 25

Eminent Member

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

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed