ifthen parse error

[bihat]

First post.

I am new to Cognos and have only been through a TM1 training class. Be gentle. lol

Currently this is a crosstab report. Currently I've only added this element.

My Expression definition

Code Select Expand


IF ( substring (cast([Stage].[Facilities].[Nbr],varchar(4)),4,1) = 0 ) THEN
    ( [Stage].[Facilities].[Nbr])


Based on my programming background I feel like this should work. When I remove the ifthen my substring/cast returns what I want and that is the 4th digit of the number. Yet when I run check the code, I get a CCLException parsing error.

I only want a result if it ends in 0.

Any advice is appreciated.

[bdbits]

(Because you are new, you might want to read http://www.cognoise.com/index.php/topic,24105.msg75293.html#msg75293.)

First, is this a dimensional or a relational package? Did you type in the MUN or use the expression editor GUI? Can you copy the specific error message you see and paste it in here?

[BigChris]

Is your original string [Facilities].[Nbr] always four characters long?

I don't think Cognos is generally too picky, but I'd tend to put your 0 in quotes, and I'd also tend to put an else clause into your statement so that you'd end up with something that looks like:

Code Select Expand

IF ( substring (cast([Stage].[Facilities].[Nbr],varchar(4)),4,1) = '0' )
THEN ( [Stage].[Facilities].[Nbr])
ELSE ('Something else')

[Robl]

IF ( substring (cast([Stage].[Facilities].[Nbr],varchar(4)),4,1) = '0' )
THEN ( [Stage].[Facilities].[Nbr])
ELSE ('Something else')

The problem I think is that your return values are different types.
[Stage].[Facilities].[Nbr] is a number
'Something else' is a string.

try
THEN (cast([Stage].[Facilities].[Nbr],varchar(4))
ELSE ('Something else')


Also
I agree that you'll need the '0' in quotes to compare a string with a string.
If you can have a chat to your development team that number should realy be changed to a string in the database/package.
Only measures should be numbers; otherwise you get a silly situation where code numbers can be accidentally aggregated on an auto group and summarise.