Author Topic: Using Case statements in crosstabs in Cognos Analytics  (Read 537 times)

Offline fakxy

  • Full Member
  • ***
  • Join Date: Aug 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
    • cognoise.com
Using Case statements in crosstabs in Cognos Analytics
« on: 05 Sep 2020 10:02:39 pm »
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.



                               
                       

Online bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 419
  • Forum Citizenship: +43/-0
Re: Using Case statements in crosstabs in Cognos Analytics
« Reply #1 on: 06 Sep 2020 04:44:25 pm »
Quote
" 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. 

Offline fakxy

  • Full Member
  • ***
  • Join Date: Aug 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
    • cognoise.com
Re: Using Case statements in crosstabs in Cognos Analytics
« Reply #2 on: 07 Sep 2020 07:59:55 am »
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.







Online bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 419
  • Forum Citizenship: +43/-0
Re: Using Case statements in crosstabs in Cognos Analytics
« Reply #3 on: 09 Sep 2020 02:23:03 pm »
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.

Quote
I tried changing from member caption to data item value , everything.
This isn't as clear as one would wish.


Offline fakxy

  • Full Member
  • ***
  • Join Date: Aug 2020
  • Posts: 5
  • Forum Citizenship: +0/-0
    • cognoise.com
Re: Using Case statements in crosstabs in Cognos Analytics
« Reply #4 on: 10 Sep 2020 02:16:05 pm »
Hello,
Sorry for the confusion.But I am unablte to view the attachment. Could you please reattach it.

Online bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 419
  • Forum Citizenship: +43/-0
Re: Using Case statements in crosstabs in Cognos Analytics
« Reply #5 on: 10 Sep 2020 02:22:48 pm »
I was able to download it.