Display text in crosstab cell column

[Manu0521]

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

[kishor2010]

In the code data item write the expression like this-

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

[Manu0521]

Hi Kishore,

My

Code Select Expand

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


[Michael75]

I did this once using the technique described here:

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

HTH
Michael

[Manu0521]

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].

Code Select Expand


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

[Michael75]

@ 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 :

Code Select Expand

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.

[CognosPaul]

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/

[Francis aka khayman]

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

[Manu0521]

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

[Francis aka khayman]

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.

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.