I keep getting an error when running the report that has a calculated fieldwith a sql case statement. I'm trying to pull a value from another table based on the current record on the datapage. I'm using the following:
Each record has a field called "Phase" with the values in the 'when' clause. Trying to pull from a table of agreements that has the amount of funding for each of these phases based on a project ID.
What am I doing wrong? The Verify button says it's all good, but when I preview the report it says there's an error.
CASE
WHEN [@field:Phase]="CON"
THEN
(SELECT ConAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active')
WHEN [@field:Phase]="ROW"
THEN
(SELECT ROWAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active')
WHEN [@field:Phase]="PSE"
THEN
(SELECT PSEAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active')
WHEN [@field:Phase]="PAED"
THEN
(SELECT PAEDAmount FROM Reimb_Agreements WHERE [TUMF_ID]=[@field.ProjectID] AND [Status]='Active')
Else 'No Agreement'
END
