Notifications
Clear all
Topic starter
I am running a report to show patients what their races/ethicities are. There are some patients that don't have a race/ethicity listed so therefore its blank or null. How can I set up the selection expert to pull the person.racemid where the value is blank. When I tried entering a space or " " it keeps saying that a number is required here. Please help!! Thanks!
Posted : June 4, 2013 5:34 am
In the selection criteria, try
isnull({PERSON.RACEMID})
Posted : June 5, 2013 12:30 am
Topic starter
Thanks! It worked like a charm!
Posted : June 5, 2013 1:01 am
What would the code look like if you wanted to return any value including null?
Posted : June 5, 2013 11:02 pm
If you do not include isnull({PERSON.RACEMID}) in the selection criteria, then patients with a value in RACEID field and without any value (null value) will be displayed.
Posted : June 5, 2013 11:07 pm
"OUTER JOIN" may be the answer you are looking for.
By default all joins are LEFT INNER, I believe. You can change this when you create the table relationship links by right clicking on the line connecting the two tables. From there choose "LEFT OUTER JOIN" or "RIGHT OUTER JOIN".
Determining which to pick Right or Left is a question of from which table you want the nulls, blanks and anything else AND which way the arrow points in the line connecting the tables. This is a lot of words to do something that is simple if you just click around. Actually I think JOINS are easier in straight SQL because you can see them every time and with constant exposure it starts to make more sense... but I digress.
A proof of concept might be to create a report that has all Locations of Care and end user's names and NPIs. Not everybody has an NPI so without an OUTER JOIN some end users will not be listed (I think), but with OUTER JOIN properly chosen (again pick LEFT or RIGHT depending on the arrow direction and which side the USRINFO table is on) you should see all users including some having a blank field for NPI.
I might be slightly off in how the arrow works in Crystal, but OUTER JOIN may be a solution for this issue.
Good luck.
Gerald Neale, RN
Clinical Analyst
Rowan - SOM
Posted : July 26, 2013 12:57 am
Topic starter
How would I do it if I only wanted to show only the patients that have a value in the field? (isnotnull({PERSON.RACEMID})???
baj said:
If you do not include isnull({PERSON.RACEMID}) in the selection criteria, then patients with a value in RACEID field and without any value (null value) will be displayed.
Posted : February 4, 2014 10:42 pm
Topic starter
I have a report that's listing the right data until I add another field which contains null & non null data. What the report is currently doing is taking out the data that has null field and only showing the field data that has a value. I don't have any selection criteria for it so it should show the null data and non null data you would think?? I have it set for "Any value". Referring to AUDIT_EVENT_DETAIL.EVENT_VALUE_1_PARAM. Before I added this I had more records then when I added the field it showed less records omitting the null values for EVENT_VALUE_1_PARAM?? Any suggestions.....
Posted : February 5, 2014 4:20 am
