I'm positive this is a pretty easy formula but I'm quite new to CR and having a difficult time with this...searched SO for my answer but couldn't find it, so please forgive me if this has been asked ad nauseam.
I have a list of patients and each year a patient is assigned a risk score, so each patient has multiple scores. Data looks something like this:
Patient_ID Score_Year Risk_Score
11111 2013 1.05
11111 2014 0.00
22222 2013 0.07
22222 2014 0.11
33333 2013 1.19
33333 2014 0.00
44444 2013 2.13
44444 2014 0.00
55555 2013 0.30
55555 2014 0.54
66666 2013 1.67
66666 2014 2.31
I want to create a field that assigns a single risk score for each patient but as you can see from the data, some patients have '0.00' for 2014, which I don't want to include.
My thought was to just create a formula that basically states if [Score_Year] = 2014 and [Risk_Score] <> 0 then ([Risk_Score] where [Score_Year] = 2014) else ([Risk_Score] where [Score_Year] = 2013) but hitting a brick wall.
In a nutshell, I need a formula to pull the 2014 score first but if that is 0 then default to 2013.
Is there a way to write this in a formula or would a select case be better? That's about the extent of my CR knowledge, so if you guys know of anything more efficient I'd love to learn!
The first two will probably work as SQL Expressions though it's possible they might be slow. The major benefit is that the calculated values are part of the data set and can be used in all passes of the rendering process.
(
select coalesce(max(Score_Year * 1000 + Risk_Score * 100.00) % 1000 / 100.00, 0.00)
from table T2
where (Score_Year <> 2014 or Risk_Score <> 0.00) and T2.Patient_ID = T.Patient_ID
) -- better to say year(getdate()) - 1 instead of hardcoding 2014??
This one is less of a hack but would only work on SQL Server (or maybe Sybase). Similar approaches will work for Oracle, DB2, etc.
coalesce(
(
select top 1 Risk_Score
from table T2
where Risk_Score <> 0.00 and T2.Patient_ID = T.Patient_ID
order by Score_Year desc
),
0.00
)
Here is a Crystal formula that would give the correct value (I'm might be rusty on the syntax though.) You would need to have a Crystal group on Patient_ID and then put the formula in a detail section and suppress it. Then you can use the formula anywhere in the group footer and get back the correct value.
// @RiskScore
whileprintingrecords;
static numbervar patient_id :: 0;
static numbervar risk_score := 0.00;
static numbervar score_year := 1900;
if (
risk_score = 0.00 and score_year < {T.Score_Year} or
patient_id <> {T.Patient_ID}
) then (
patient_id := {T.Patient_ID};
risk_score := {T.Risk_Score};
score_year := {T.Score_Year};
)
risk_score;
Following the pattern of the first SQL Expression you could convert that to pair of formulas. The drawback here is that the risk score is not available for use in row level (whilereadingrecords) formulas.
// @WeightedRiskScore
{T.Score_Year} * 1000 + {T.Risk_Score} * 100.00;
// @MostRecentRiskScore
max(@WeightedRiskSCore, "Patient_ID") mod 1000 / 100.00;
Depending on the specifics of your reports there could possibly be others options.