Column formula - multiple case statments
866720Mar 1 2012 — edited Mar 1 2012Hi experts! I am looking for help with a case statment in a column formula. OBIEE 10g 10.1.3.4
I have a dashboard with a prompt and a request. In the prompt I have a varchar2 field (Name) that is defaulted into Multi-Select that is populated by a SQL returning 3 entries: ('JEFF', 'TOD', 'BOB'). Meaning all 3 entries come up by default.
In the request I am trying to show weekly sales that would depend on selected employee name. So, by the default I should get 9 lines of sales (one for every past week of this year) for all 3 employees and If I change the Name selection in the prompt the sales should be only for what I selected. The name field is not displayed on the report but it is listed as 'prompted' in the filter.
To do so I have case statments in the sale field as following:
CASE WHEN EMPLOYEE.Name = 'JEFF' THEN FILTER SALES USING (EMPLOYEE.flag = 'J'))
ELSE
CASE WHEN EMPLOYEE.Name = 'TOD' THEN FILTER SALES USING (EMPLOYEE.flag = 'T'))
ELSE
CASE WHEN EMPLOYEE.Name = 'BOB' THEN FILTER SALES USING (EMPLOYEE.flag = 'B')) END END END
With the listed case statments I only get sales for the first listed in the prompt name. It seams as though only the first Name gets passed to the request
and the sales total shows that employee's sales.
Please help!!