COGNOiSe.com - The IBM Cognos Community

Hi everyone,

I was wondering if anyone can help in resolving this case statement.
I am using this as a detail filter in my report where [CYBud], [CYBud1],[CYBud2] are attributes in the cube
The report is filterted based on the value of these attributes
I gives me correct data only for the first option i.e when selected year is '2017'

case
when (?pYear? = '2017')
then ([CYBud]=1)
when (?pYear? = '2018')
then ([CYBud1]=1)
when (?pYear? = '2019')
then ([CYBud2]=1)
end

Thank You.

Any chance a Line Feed is a problem?

Try ...
case
when (?pYear? = '2017')  then ([CYBud]=1)
when (?pYear? = '2018')  then ([CYBud1]=1)
when (?pYear? = '2019')  then ([CYBud2]=1)
end

.. or ...
case
when ?pYear? = '2017'  then [CYBud]=1
when ?pYear? = '2018'  then [CYBud1]=1
when ?pYear? = '2019'  then [CYBud2]=1
end

HTH, Bob

Thanks for the reply.

The 1st option suggested by you looks exactly what I had mentioned but I still tried putting them in 1 line.
Tried 2nd option too by removing the brackets but the issue still remains the same.

Do we have any other idea/clue.

Thanks.

Try something like this.. 

Case ?pyear?
When 2017 then ..
When 2018 then...
End


Thanks
Kiran

Sent from my ZUK Z2132 using Tapatalk

If that doesn't work, you can try having three different filters like ...

1) [cbud]  = case when ?Pyear? = 2017 then 1 end
2) [cbud1]  = case when ?Pyear? = 2018 then 1 end
Etc

Thanks
Kiran

Sent from my ZUK Z2132 using Tapatalk

Thanks for the reply, Kiran.

With Option 1: the issue still remains the same means it gives correct data only for 2017
With Option2: I doesn't fetch me any data.

thanks

I'd try something like this

1 =
((?pYear? = '2017') AND ([CYBud]=1))
OR ((?pYear? = '2018') AND ([CYBud1]=1))
OR ((?pYear? = '2019') AND ([CYBud2]=1))

Let me know if it works. I'll probably be able to test it on monday :(

Quote from: cognos4321 on 19 Jan 2018 01:00:27 PM
Thanks for the reply, Kiran.

With Option 1: the issue still remains the same means it gives correct data only for 2017
With Option2: I doesn't fetch me any data.

thanks

Replace case with if statement

Thanks
Kiran

Sent from my ZUK Z2132 using Tapatalk

Hi Ray,

This doesn't work.
Ultimately what we are trying to get is [CYBud]=1, [CYBud1]=1, [CYBud2]=1 as per the selected year in the detail filter.
So, when the report is run for 2017 , the entire data should be filtered for the condition ([CYBud]=1).

thanks for the reply though

Hi Kiran,

I have tried replacing case stmt with IF then else and vice versa.
Tried nesting them also but nothing works.
I guess the problem could be the end result is not a simple value , its ([CYBud]=1) where [CYBud] is an attribute.

I am not sure if anyone ever dealt with such condition in case stmt.

Thanks.

Hi

Yes, I faced similar problem in DQM models though CQM doesn't give any problem. That is why one of my suggestions was to convert these conditions into simple filters like the below, which you mentioned did not work.

1) [cbud]  = case when ?Pyear? = 2017 then 1 end
2) [cbud1]  = case when ?Pyear? = 2018 then 1 end

May be you should create calculations with these statements for each of these items cbud and cbud1 and use in your report.

Thanks,
Kiran

So this is in a cube? And you are using detail filters? Don't even think about it. You want slicers or filter() expressions.

http://www.cognoise.com/index.php/topic,18290.msg53585.html#msg53585 (http://www.cognoise.com/index.php/topic,18290.msg53585.html#msg53585)