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

Help needed setting up a query

Trading Post

Last Post by Anonymous 1 second ago

4 Posts

2 Users

0 Reactions

67 Views

RSS

suefox51

(@suefox51)

Posts: 113

Estimable Member

Topic starter

If possible, I need to set up a query, by physician with a date range of the patients on the portal. I can pull those whose primary contact is by email, but unable to figure out how to get the portal info in there.

Any one??? Yell

Posted : September 22, 2014 2:16 am

ronmoses

(@ronmoses)

Posts: 158

Estimable Member

The portal data is in a separate database, possibly on a separate server. On our system the CPS database is EMRDBPROD.CentricityPS, and the portal database is EMRDMPP.Enterprise. So any querying we do across the two requires the servers to be linked (which is super-easy to do if you have the necessary permissions). If your databases are on the same server, you don't have to worry about that, you can query across the databases easily enough.

I have yet to fully understand how the Enterprise database works. But I was able to take a bit of code left behind by GE and use it to give me a list of patient PIds who have registered on the portal.

SELECT dp.Identifier AS PId
FROM EMRDMPP.Enterprise.dbo.KUser ku
INNER JOIN EMRDMPP.Enterprise.dbo.DCommunityAccount dca
ON ku.UserID = dca.UserID
INNER JOIN EMRDMPP.Enterprise.dbo.DPartner dp
ON dca.DCommunityAccountKey = dp.DCommunityAccountKey

I'm not yet sure how much of that is strictly necessary, but it works. Using this, either as a derived table, a common table expression, or to populate a temporary table, we can then identify patients in the CPS database who are registered on the portal.

Does that help at all?

Posted : September 22, 2014 2:44 am

suefox51

(@suefox51)

Posts: 113

Estimable Member

Topic starter

I was hoping I could do a query in the "patient reports"!

Posted : September 22, 2014 2:52 am

ronmoses

(@ronmoses)

Posts: 158

Estimable Member

Technically it's possible to include data from two databases in a Crystal Report, but I'm not sure how CPS will handle it.

One possible solution would be to create a custom table in your CPS database to store portal patient PIds...

CREATE TABLE cus_PortalPatients
(PId numeric(19,0) PRIMARY KEY);

Set up a nightly or weekly SQL Agent job to populate the table...

TRUNCATE TABLE cus_PortalPatients;
INSERT INTO cus_PortalPatients
SELECT DISTINCT dp.Identifier
FROM EMRDMPP.Enterprise.dbo.KUser ku
INNER JOIN EMRDMPP.Enterprise.dbo.DCommunityAccount dca
ON ku.UserID = dca.UserID
INNER JOIN EMRDMPP.Enterprise.dbo.DPartner dp
ON dca.DCommunityAccountKey = dp.DCommunityAccountKey;

Now you can use that table as a reasonably reliable list of portal patients within your CPS database. Adding that table to your Crystal Reports is a piece of cake from that point, just be sure the "report" login has rights to the new table. And make a note to check for the existence of that table after your next upgrade to make sure it didn't get wiped out.

Posted : September 22, 2014 3:07 am

Share:

24 Forums
9,942 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

Powered by wpForo Powered by wpForo version 2.4.0