Need help with CRXI R2 record selection

[snejsnej]

Hi there--

I'm dealing with a two issues:

1) SQL Expression evaluation

My report has a number of SQL Expression Fields that CR XI is evaluating at design time. That is, when I save a SQL Expression, it immediately checks it for syntax errors AND runs it against the database.

However, because the SQL expression is working with an unfiltered set of data (i.e. not at run time where a user filters the data by using the dynamic params), it often returns an error stating that more than one record is being returned. That is, it isn't taking the Select Expert formula into account, which it obviously can't at design time.

Is there some sort of setting in CR XI that 'tells' it not to immediately evaluate this SQL expression at design time? If not, then there must be something wrong with how I have the filtering process set up. This brings me to my second point...


2) Select Expert formula

It looks like this:

{Complete.PatientLastName} = {?Patient Parameters - PatientLastName} and
{Complete.PatientFirstName} = {?Patient Parameters - PatientFirstName}

I'm pretty new to CR in general, nevertheless this formula doesn't appear to be a Crystal formula, i.e. it is something that SQL 2005 can understand. If that is indeed the case, what could be causing the same run time error ("Subquery returned more than 1 value...") AFTER the dynamic parameter choices are made? Based on those choices, only 1 record should be returned to CR.

Thanks

 

[snejsnej]

There is only 1 record per PatientID, so if I can incorporate the PatientID into my SELECT statement, that would probably take care of the issue.

I've tried to do that already but had no luck; I don't know how to pass the PatientID to the statement. Everything I've tried has either been rejected or simply returns all the records I'm getting _without_ the PatientID parameter.

I was thinking that it would look something like this:

(SELECT OC.OutcomeCodeName FROM lu_outcomecode OC
INNER JOIN PatientArrival PA
ON OC.OutcomeCodeID = PA.PatientArrHOBNotMetCode
WHERE PA.PatientID = 14)

Obviously, the '14' wouldn't be hard-coded, but a variable that can be passed from the dynamic parameters. Does that make sense?

Thanks again for your help.
Jens

PS-- Re. the dynamic parameters... at the moment there are only 2: PatientLastName and PatientFirstName. I assume that if it IS possible to pass a dynamic parameter to the SQL expression above (replacing the '14'), I would need to include it as a dynamic parameter. I'd like to avoid that if possible since it confuses the user with a meaningless number in a 3rd drop down. Anyway, just a thought in case passing parameters is even possible.

 

[lbass]

If there is only one outcome record per patient, then SV's original suggestion should work:

(SELECT "OC"."OutcomeCodeName"
FROM "lu_OutcomeCode" "OC" INNER JOIN "PatientArrival" "PA"
ON "OC"."OutcomeCodeID" = "PA"."PatientArrHOBNotMetCode"
WHERE "PatientID" = "PA"."PatientID")

If there can be more than one outcome per patient, then you are misunderstanding. There may be only one record per patient in your main table, but the minute you look at the table containing outcomes where there can be multiple outcomes per patient, you now have multiple records per patient.

I think you should spend some time learning about how CR works. Try adding the main report table and the table referenced in the SQL expression, and link them on the patient ID. Then add a field from each table to the detail section of the report. Then you will see what I mean.

SQL expressions cannot use parameters. You can instead use a command object if you want to use parameters within a SQL query.

-LB

 

[snejsnej]

I spent a bit of time trying out command objects and it looks like this will be the only way to go. But: is there a way to use the already-existing dynamic parameters in a c.o.? I have the 'last-name / first-name' dyn params that I'd like to use if at all possible.

There is a more 'profound' problem: If you look at the query in your last post, the field "PA"."PatientArrHOBNotMetCode" is only 1 of 120 different fields in SQL that I need to extract the OutcomeCodeID from for the patient chosen from the dyn param drop downs. Each field has its own spot on the report.

Can this be done?

 

[lbass]

Why wouldn't you just use the dynamic parameter to choose the customer and then add the fields of interest to your report and insert a group on the patient ID?

Add the outcomes table and link it to the patient table. I think you are making this way too complicated.

If you want the ability to select from among the outcomes that are specific to a patient, then use a dynamic cascading parameter where the first level chooses the patient ID, and the second, the outcomes.

-LB

 

[snejsnej]

The design of the report makes it difficult to group on the patient since there are different categories. I posted a screen shot here (the patient data is bogus):

http://img258.imageshack.us/img258/1224/reportgt6.jpg

I may have to follow your suggestion anyway b/c this is too big of a headache.

 

[lbass]

This looks fairly straightforward, although I have no idea how your fields relate to the display. But in general, it looks like you could group by patient and then group by category (format it to underlay following sections), and add the description and other fields to the detail section.

-LB