Notifications
Clear all
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???
Posted : September 22, 2014 2:16 am
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
Topic starter
I was hoping I could do a query in the "patient reports"!
Posted : September 22, 2014 2:52 am
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
