COGNOiSe.com - The IBM Cognos Community

Hi,

I have a cross tab with a column name code and the value for this column is 0 and 1 . I wanted to have some text instead of 0 and 1.

I am not sure on how to achieve this, I have been trying long using conditional blocks.
Can anyone let me know the steps if we could achieve this through conditional blocks or other way.

Thanks,
Manu

In the code data item write the expression like this-

if ([code)='0') then ('A') else('B')

Hi Kishore,

My value is a numeric.

I get an error invalid to compare values with different data types.

If it was a string then it would work.

[code] value can be any number .

Thanks,
Manu


I did this once using the technique described here:

http://www.cognosonsteroids.com/2012/01/crosstab-with-non-numeric-fact.html (http://www.cognosonsteroids.com/2012/01/crosstab-with-non-numeric-fact.html)

HTH
Michael

Hi Michael,

Thanks for the link, my measure is numeric and its a quantity
qty        AAAA      BBBB   Code
a            3             33         0
c            3             13         1
d            4            22          1

Now I have to replace the value in code column from O to a text say "ABC" and 1 to " CEF".

So what I do is create a string variable
with expression
case [Query1].
when 0 Then 'ABC'
when 1 Then 'CEF'
Else 'Null'
End

Then add 2 values to this string variable as "ABC" and "CEF".

Now when I unlock the cell and try to select only the text of code of column, all of my fact cells are selected.

I am stuck at this point,

Thanks,
Manu

@ Manu

I've gone back to the report I mentioned, and I too was basing my cell text on a quantity.

Crosstab Intersection - Text Source - Source Type : Report Expression
Report Expression : case
when [Query1].[Quantity] > 1400000 then 'lots'
when [Query1].[Quantity] >  800000 then 'medium'
when [Query1].[Quantity] >  120000 then 'little'
else 'low'
end



Try this and let us know the results.

If you're on 10.2.1.3 or greater you can use this method: http://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/

try this:

1. create the data item you need (in my test, i created something like: if (tuple([month];[amount]) > 100) then ('A') else ('B')
2. add it to the row in your crosstab
3. go to query explorer
4. select the query
5. set override dimension info = yes
6. drag the data item in #1 into the dimension info tab,  to create a new dimension
7. set your crosstab to suppress rows
8. run the report

good luck

Khayman,

Thanks for the suggestions.

Cross tab looks like

Qty                 Jan2014   Feb2014  Mar2014 Apr2014         CalculatedColumn                                                                             Status
Account1        323           22            2             1            if(total(QTy within set(Feb,Mar,Apr))>0 then 1,Else 0)            if(Calculated column =0) then 'Active' Else'InActive'
Account2         22             2             2             2            if(total(Feb,Mar,Apr)>0 then 1,Else 0) 
Account3          11            0             0             0            if(total(Feb,Mar,Apr)>0 then 1,Else 0)

so now the status column will have to check the value on calculated column and assign a text based on 0 or 1.

Your post says to add it to the rows in the crosstad? and where can i find the dimension info tab.

Thanks,
Manu

oh ok. the sample crosstab i created looks like this:

                                        Jan2014    Feb2014
Account 1       Active           20                 1
Account2        Not Active     0                   1

i put the active/not active in rows.

Quote from: Manu0521 on 26 Jan 2015 07:49:20 AM
Your post says to add it to the rows in the crosstad? and where can i find the dimension info tab.

if you follow my instructions, you will see the dimension info tab appear after step #5. the tab however is located at the lower portion of the Data Items window, beside Projected Data Items.