I need to know if a nested CASE statement would function as expected in Cognos 8.4.
Example:
CASE parameter
WHEN 'A' THEN formula_1
WHEN 'B' THEN formula_2
WHEN 'C' THEN (CASE table_data
WHEN 'A' THEN formula_1
WHEN 'B' THEN formula_2
ELSE NULL
END)
ELSE NULL
END
When running this, option C produces no data, but there is data for all of the options. If I remove either of the options from the nested CASE, it will work, but when both options are there it will not.
Any suggestions?
TIA,
Mark
You should use a token prompt for this.
Replace the 'USE' values in the prompt with
Description | Use
---------------------------
Description 1 | formula_1
Description 2 | formula_2
Description 3 | CASE table_Data WHEN 'A' THEN formula_1 WHEN 'B' THEN formula_2 ELSE null end
In the data item in the query, replace your case statement with this: #prompt('parameter','token','default_formula,_if_there_is_one,_can_go_in_this_optional_prompt_parameter')#
When the user selects a value in your prompt, Cognos will run that code as if it were typed into the data item. From a performance perspective this is much cleaner and much more efficient than having it done in the SQL level.
Being new to Cognos, I will say that I had to do some research on the 'token' prompt, but I think I understand it.
I do have another question on this subject. In the original case statement, the A & B options are actually in the database, but the C option was added in at the prompt level. Does your solution allow for this or are there other actions I would need to take?
Thanks again,
Mark
If I understand correctly, your prompt is fed by a simple query that returns two values. On top of that you have static value C.
Does the query feeding the prompt ever return anything other than those two values? You can save valuable database time by adding the values to the static choice.
No, the query only produces two values, the third was added into the static values.
That said, will your previous suggestion work?
If that parameter is only being used by the case statement, it should work. Just remove the reference to the query and add items A and B to the static values with the use values formula_1 and formula_2.