Using Case statements in crosstabs in Cognos Analytics

[fakxy]

Hi FOlks,

I am working on a DMR model in cognos analytics which has upgraded from 10.2. When I am trying to use a case statement to group a few values and show that in a crosstab, I am unable to see the value as expected. However, it works fine in a list report. I understand that relational and dimensional model works differently. Hence , I am trying with MUN. Can anyone please help in this regard.

Existing : Case  [region name] when 'A' then 'Y'
                                             when 'B' then 'Y'
                                             when 'C' then 'Y'
                                             when 'D' then 'N'

else [region name]  end.

I need to show Y or N in crosstab.



                               
                       

[bus_pass_man]

" I am unable to see the value as expected."

OK what do you see?  Knowing that could help.

Did this expression work before?  DQM or CQM?

Also what's region name?  Is it a level? An attribute? Something else?

Cross-tabs use MDX, which could explain why you get different results.



Create your calculation in the model, either as an attribute of the dimension or better yet in the business layer.  This should force the execution of the expression in the SQL. 

[fakxy]

Hello,

I am using a data item (TEST)  having this statement  --- >case [sales territory name] when 'ABC' then  'Y'
                                                                          when 'DEF' then 'Y'
                                                                          when 'XYZ' then 'N'
                                                                          when 'PQR' then 'N'
                                                                          else [sales territory name] end

When I run this in a list report, it gives the grouping correctly. When put in a crosstab, it just shows me the name of the data item(TEST). I tried changing from member caption to data item value , everything. It doesnt work.

[sales territory name] is a level having members as 'ABC', 'DEF', 'XYZ' and 'PQR'.

this expression is working fine in 10.2 but not in 11, the DMR package being the same.

I wanted to know, will using MUNs help, if yes..how should the expression be.

TIA.

[bus_pass_man]

In this picture, on the left is an attribute in a crosstab.  As you can see, the values of the attribute are displayed.  On the right is a query calculation with an expression similar in principle to yours.  As you can see, the values of the expression are displayed.

Based on what you say, it should be possible to see the values, so it isn't clear what you are trying.   

Your reference to member caption is confusing me.

I tried changing from member caption to data item value , everything.

This isn't as clear as one would wish.

[fakxy]

Hello,
Sorry for the confusion.But I am unablte to view the attachment. Could you please reattach it.

[bus_pass_man]

I was able to download it.