Question for MF

[imts]

Hi MF,

This is regarding getting Text in fact Cell of a crosstab.
I have literally tried everything and searched a lot of forums including Cognoise.

As per your experience, is it do-able? Or is it something that Cognos lacks now?

Regards,
Tarun Singh

[blom0344]

Well, I ain't MF (which you can also reach directly by PM), but as far as we found out you can only 'translate' numerical values in the crosstab by applying a report expression that translates the values to strings.

The measure involved HAS to have an aggregate set to show data and this rules out non-numerical values.

We basically use report expressions to 'translate' status codes (numericals) to textcodes in planning crosstabs by means of report expressions

[imts]

Thanks blom0344.

I have a limited amount of Text values to be diaplayed in the fact cells - (Egs - HIGH , LOW , MEDIUM .... )

I am able to display Numbers in the fact cells using String Variables - Assigning a number to a particular Text Value.

I dont know how to proceed after that ?

Can you please send me any sample report expression on how to 'translate' the numeric part into string ? And how to actually use that Report Expression ?

It wud be really really really helpful of you.

Regards,
Tarun

[blom0344]

The following report expression is used for a multilingual reports to translate numerical Fases to code strings in a crosstab:

Code Select Expand


CASE Locale()
WHEN 'nl' THEN
CASE
WHEN [Query1].[FASE_NUM] = 0 THEN '-'
WHEN [Query1].[FASE_NUM] = 1 THEN 'SO'
WHEN [Query1].[FASE_NUM] = 2 THEN 'SV'
WHEN [Query1].[FASE_NUM] = 3 THEN 'SB'
WHEN [Query1].[FASE_NUM] = 4 THEN 'EB'
WHEN [Query1].[FASE_NUM] = 5 THEN 'EP'
ELSE '' END
WHEN 'de' THEN
CASE
WHEN [Query1].[FASE_NUM] = 0 THEN '-'
WHEN [Query1].[FASE_NUM] = 1 THEN 'SE'
WHEN [Query1].[FASE_NUM] = 2 THEN 'BV'
WHEN [Query1].[FASE_NUM] = 3 THEN 'BB'
WHEN [Query1].[FASE_NUM] = 4 THEN 'BF'
WHEN [Query1].[FASE_NUM] = 5 THEN 'PE'
ELSE '' END
ELSE
CASE
WHEN [Query1].[FASE_NUM] = 0 THEN '-'
WHEN [Query1].[FASE_NUM] = 1 THEN 'SD'
WHEN [Query1].[FASE_NUM] = 2 THEN 'SoS'
WHEN [Query1].[FASE_NUM] = 3 THEN 'SC'
WHEN [Query1].[FASE_NUM] = 4 THEN 'EC'
WHEN [Query1].[FASE_NUM] = 5 THEN 'EP'
ELSE '' END
END


You basically unlock the crosstab intersection and select the text item. You can then change source type from cell value to the required report expression. In our case the report expression directly refers to the dataitem used as measure in the crosstab. This one needs to have an aggregate set, otherwise Cognos shows no values at all

[MFGF]

Hi Tarun,

This just goes to show you should always ask the expert (blom0344) rather than asking MF :)

Ties, you are a star - as always!

MF.

[imts]

Thanks both blom0344 and MF ! You Rock !! :)
-TS