Looking for some help here if I may.
I have a Variable Dashboard prompt, which when placed on a dashboard can be used to select Multiple Sites. This variable is called V_SITES. Its list is built using a SQL statement which functions as expected.
I have entered the following into a Column Formula.
sum(Case
When "Folder_Heading"."SITES" IN (@{V_SITES}{'Site1','Site2','Site3'})
Then 1
Else 0
End)
This Works beautifully when I don't assign the Variable. However as soon as I assign the Variable say ('Site1','Site2','Site3').
It fails, with the following message.
Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P:OI2DL65P
SQL Issued: SELECT sum(Case When "Folder_Heading"."SITES" IN (Site1,Site2,Site3) Then 1 Else 0 End) FROM "Test_Cube"
I noticed that it loses those all important quotes (Site1,Site2,Site3)... If this is causing the issue, I don't understand why this is happening as I have told the V_SITES to be TEXT.
Please help!
Many Thanks
P
EDIT: After loads of testing, I'm fairly confident that the problem is Oracle is not wrapping my text in single quotes when building the sql string... Just need to figure out how to make it...
.
Turns out I had the same issue, and whilst Shakey's solution works, the real solution is to use ['@'] in the formula (From Oracle tech support: Formula Syntax to Populate Multi-Select Presentation Variable (Doc ID 2063624.1) )
sum(Case
When "Folder_Heading"."SITES" IN (@{V_SITES}['@']{'Site1','Site2','Site3'})
Then 1
Else 0
End)