Print Page - IF THEN ELSE not working

Title: IF THEN ELSE not working
Post by: AJAYC on 19 Jun 2017 04:56:46 AM

Hi

I have an issue with "IF THEN ELSE" statement not working for me using a dimensional model.

Example

I am trying to test a prompt value and derive another member from it, so

IF
([CubeName].[Dimension].[Dimension]->?PromptMonth? ) = 'Oct' )
THEN
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD])
ELSE
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance]

I have tried a number of variations of this, including trying to include caption statements, and member statements but none with success.

I keep getting a combination of syntax errors, or no data being displayed.

Has anyone managed to get this working when using TM1 cubes as a source ?

Thanks
Ajay

Title: Re: IF THEN ELSE not working
Post by: Mimo91 on 19 Jun 2017 08:18:45 AM

Hi Ajay,

I am seeing that there is syntax error in your code (bracket mismatch). It should be;

IF
([CubeName].[Dimension].[Dimension]->?PromptMonth? = 'Oct')
THEN
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD])
ELSE
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance])

If it does not work you can try;

(([CubeName].[Dimension].[Dimension]->?PromptMonth? = 'Oct') AND ([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD])) OR (([CubeName].[Dimension].[Dimension]->?PromptMonth? <> 'Oct') AND ([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance]))

Thanks.

Title: Re: IF THEN ELSE not working
Post by: AJAYC on 19 Jun 2017 10:16:42 AM

Thanks Mimo91

Unfortunately no luck. I corrected the code as advised:

IF
( [CubeName].[Dimension].[Dimension]->?PromptMonth? = 'Oct' )
THEN
( [CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD] )
ELSE
( [CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance] )

What I have noticed is that the condition fails to satisfy, as per the attachment, the data item for the crosstab, does pickup the "Opening Balance" however no data is returned......not sure why it doesn't allow the prompted value of "Oct" to equal 'Oct' in the report expression above.

Ajay

Title: Re: IF THEN ELSE not working
Post by: cognos05 on 19 Jun 2017 02:22:04 PM

try this

if(?prmCountry?='China')
Then
([Revenue])
Else
([Quantity sold])

Thanks,
Nithya

Title: Re: IF THEN ELSE not working
Post by: AJAYC on 19 Jun 2017 03:16:59 PM

Hi Nithya

Thanks for your suggestion. I followed your code with the following version, but unfortunately it still didn't work.

IF
(?PromptMonth?='Oct')
THEN
([Oct YTD])
ELSE
([Opening Balance])

Regards
Ajay :(

Title: SOLVED>>>Re: IF THEN ELSE not working
Post by: AJAYC on 20 Jun 2017 06:34:07 AM

Hi Guys

I didn't get any luck with the "IF...THEN...ELSE" so I made changes TM1 side to accomplish what I needed instead.

I created an alternate hierarchy within the dimension which looks similar to this:

All Periods
--Oct
--Oct YTD
--Nov
--Nov YTD
etc

Then, I changed the report expression for the data item replacing the "Full Year" where my prompt is based to "All Periods", as below:

lead (
#Substitute ( 'Full Year', 'All Periods', prompt ( 'PromptMonth', 'memberuniquename' ))#,
1)

Hope this helps anyone else stuck on a similar issue. I am still concerned as to why the "IF..THEN..ELSE" failed to work

Thanks for your help

Ajay

Title: Re: IF THEN ELSE not working
Post by: yvswamy on 22 Jun 2017 05:24:10 AM

I believe you are working with Slicers.

Slicers won't accept IF ELSE or AND OR statements. You need to use macros to get what is required for you.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: AJAYC on 19 Jun 2017 04:56:46 AM