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

Crystal Reports: No data displays ANYWHERE if OBSVALUE is NULL

MEL, SQL, Oracle

Last Post by Anonymous 1 second ago

9 Posts

4 Users

0 Reactions

65 Views

RSS

vrdlc

(@vrdlc)

Posts: 135

Estimable Member

Topic starter

I have a few Crystal Reports that need modifying. They're quite old and not well documented, so I'm not sure exactly how they were constructed or how parts of them even work. I have to just add a few fields, but I'm encountering all kinds of problems.

Basically, I have to access a single OBS value for every patient. If there is a value in the table, then insert it. Currently, if the patient has a value, the report will insert the value in the field. But if the patient does NOT have that value, then the only value that is populated on the report is "Age". Nothing else pulls through.

I suspect it's tied to the Record Selection formula. In Record Selection, I wrote {OBS.HDID} = ObsNumber. This forces the HDID to be this number so it can pull out the value. If this IS in the Record Selection area, then the report fully populates, including the OBS value I want to pull through, but ONLY if the patient has a value in that OBS.

If this ISN'T in the Record Selection formla area, then every report fully populates, EXCEPT for the OBS value I want to pull through.

I hope this makes sense to someone. I know it's really specific, but I'm hoping someone has seen this before/knows how to solve it.

Final note: I can't use a SQL Expression, a stored procedure, or a Command, as there's something going on in this form that prevents the database from being accessed if I try to use one of these tools. I have no idea what... I'm at the point where I'm thinking about just starting from scratch, but that's going to take the rest of my week...

Posted : March 1, 2017 10:52 am

jfitzmd

(@jfitzmd)

Posts: 419

Reputable Member

I would be glad to set up a WebEx and look at your problem. Contact me directly if you are interested in pursuing.

Posted : March 1, 2017 11:25 am

rwilliams

(@rwilliams)

Posts: 454

Honorable Member

Make sure your obs table is left joined and not inner joined.

Your record selection should say something like

(

IsNull({OBS.HDID}) OR {OBS.HDID} = <HDID>

)

If you would like more help either upload your report or send it to me in an email and I will take a look at it for you.

Posted : March 1, 2017 1:29 pm

vrdlc

(@vrdlc)

Posts: 135

Estimable Member

Topic starter

Here's where we are...

We're trying to update tstorder.rpt and reforder.rpt. We want to add an additional field that comes off of a different table in the database. I can add new fields from a pre-existing view (PatientProfile) without a problem, but when I try to add new tables to the report, a connection error appears in CPS.

The worst part of the error is that there is no note or code. It literally says:
Database Connector Error: "
That's it. No number, no ID. This error only appears after I add a new table to the report from the database. Inside Crystal Reports, the data connections work, because I can right click on a field, click Browse Data, and retrieve a the real data from the database. I can also start a new report and pull in data from the database.

I think there's something specific about running Crystal reports in CPS that I just can't pin down. Any ideas?

Posted : March 2, 2017 10:59 am

gpincomb

(@gpincomb)

Posts: 278

Reputable Member

I agree with Ryan, your initial issue sounds like an issue of using the default join type, which is an inner join or an equal join. You'll want a left outer join with the first table being the primary table (left side) so that it pulls that data regardless if it finds a match in the right side table.

Your second issue that you described on March 2, sounds like it is a datasource issue. One thing to check, since you stated that you added a table to the tstorder.rpt and reforder.rpt, then I assume that you created a new datasource to do so. This could mean that you previously created a new datasource for another report and just simply used it in this report, but a new one was created at some point in time, correct?

Have you tried using the functionality "Set Datasource Location..." on the already existing table? When you are working on reports from within Crystal itself, 2 different datasources to the same database will usually work fine, but when you then pull that report into an application, I have seen reports act weird and even produce the error you describe. I would try (with a copy of course) setting both tables to use the same datasource, your custom one. Making sure it works in Crystal and then trying it from inside Centricity.

Another thing to double check is to make sure that it is not a security issue with the datasource connection and the database. If you continue to have problems have your DBA review the Roles on the database side.

Posted : March 3, 2017 7:16 am

vrdlc

(@vrdlc)

Posts: 135

Estimable Member

Topic starter

My new tables are Left Outer joined and I am using a single datasource of my selection. I think the problem has something to do with the type of report. The reforder and tstorder reports are Centricity default reports and don't live in the same place as all of our other reports. They aren't editable in the Business Reports section of Centricity either.

I have discovered that previously we had HealthCo customize these reports, so I get the feeling that there's something specific about how these reports are constructed that makes them very fragile. For example: I determined that the issue with loading in OBS values from the OBS data table was that if a client didn't have a particular OBS, there would be no matching row in the table. Centricity didn't know what to do when it was forced to look in a row that didn't exist, so it would just stop running the report.

It's not a datasource issue and it's not a join issue. I rolled back the report to an early version that allowed additional tables to be added. I then directed the table to directly access the information that I needed, and nothing broke. BUT. In my reforder form, I attempted to do the same thing, and now I'm getting the same "Database Connector Error: " " message. I have no idea what I've done differently. Obviously there's something, but I can't put my finger on it.

Posted : March 3, 2017 8:56 am

gpincomb

(@gpincomb)

Posts: 278

Reputable Member

I have modified our tstorder and reforder reports before to modify the SSN to display like XXX-XX-1234, but you did state something that makes me wonder.

You stated that the "don't live in the same place as all of our other reports."

These two reports should reside in the following directory:

Program Files (x86)\Centricity Practice Solution\Client\crwrpts\

Are you getting the modified report to work in the Crystal Reports application at all? What version of Crystal Reports are you using to make your modifications? What version of CPS are you on?

Once you have a working report inside the Crystal Reports application you will need to copy it back into the crwrpts folder for every client machine you have and this includes your Citrix template.

Posted : March 7, 2017 7:01 am

vrdlc

(@vrdlc)

Posts: 135

Estimable Member

Topic starter

The reports live in a folder on a share drive that all installs point to. The forms open in Crystal, and if I don't add any database info to the reports, I can alter them and the changes display in Centricity. For example, the first changes I had to make involved adding data from PatientProfile, which was a table that already existed in the report. I was able to make formulas and add fields using these data without a problem.

The issue I've encountered here is that one report, tstorder.rpt, works after I add in additional tables from the database. The other report, reforder.rpt, gives me a database connector error, even though the changes are identical to the changes made on tstorder.rpt. I have a ticket in with HealthCo. If they can solve this, I'll post the solution here for anyone else who may be having trouble.

Posted : March 7, 2017 7:09 am

vrdlc

(@vrdlc)

Posts: 135

Estimable Member

Topic starter

For those who may encounter this problem in the future: Crystal Reports autolinked two tables in the Database Expert that had already been linked before. Essentially, Table A and Table B were already linked on report load, but the autolink added a second link between Table A and Table B, breaking the SQL query. This took us three days and a two hour phone call with HealthCo to crack.

But the problem has been resolved and this has been put to bed. Thanks for the responses everyone.

Posted : March 10, 2017 3:35 am

24 Forums
9,940 Topics
39.9 K Posts
1 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