Search code examples
crosstabcognos

How to replace crosstab cell's value with a string

I have column fact, it can carry some difference values:

 - Positive values - real values, need to be outputted as is
 - 0 is null, output as is
 - -1 - special value. Need to ouput "VAC" string in cell.
 - -2 - special value. Need to output "SICK" string in cell.

I tried to do it with editing dimension, i replace it with:

case 
    when [BaseEmp].[FACT_VALUE] = -1 then 'VAC'
    when [BaseEmp].[FACT_VALUE] = -2 then 'SICK'
    else to_char([BaseEmp].[FACT_VALUE])
end

But now I see error: ORA-01722 invalid number (i think, because strings cannot be aggregated). In column properties I select "min" as aggregate function.

How to replace my special values with strings?

Solution

  • You don't need to change value to VAC, SICK etc. You need to change DISPLAYED value.

    1. Unlock the padlock in RS.
    2. Select text in your cell
    3. Set text source to "Report Expression"
    4. Write expression like
    CASE
    WHEN cellValue() = -1 THEN 'VAC'
    WHEN cellValue() = -2 THEN 'SICK'
    WHEN cellValue() = 0 THEN ''
    ELSE cellValue()
END